How-Tos

DBUSER=foo; DBUSERPW=123; DBNAME=foo;  mysql -e "create database if not exists $DBNAME; grant all privileges on $DBNAME.* to '$DBUSER'@'127.0.0.1' identified by '$DBUSERPW';grant all privileges on $DBNAME.* to '$DBUSER'@'localhost' identified by '$DBUSERPW';FLUSH PRIVILEGES;"

DBNAME="mydb01" && mysql -e "SELECT table_name AS 'Tables',  round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB'  FROM information_schema.TABLES  WHERE table_schema = '$DBNAME' ORDER BY (data_length + index_length) DESC;"

CREATE TABLE new_sys_log LIKE sys_log;
RENAME TABLE sys_log TO old_sys_log, new_sys_log TO sys_log;
DROP TABLE old_sys_log;

Needs to be run with root privileges.

mysql --defaults-extra-file=/etc/mysql/debian.cnf

mysql -e 'show processlist;'|awk '{print $3}'|awk -F":" '{print $1}'|sort|uniq -c
mysql -BNe "select host,count(host) from processlist group by host;" information_schema

database=schema-name; mysqldump --routines --opt --single-transaction --quick --databases $database | gzip > $(hostname)-$database-$(date +%F_%s).sql.gz

mysqldump --routines --opt --single-transaction --quick --all-databases | gzip > $(hostname -f)-$(date +%F_%s).sql.gz

create database <database_name> character set UTF8mb4 collate utf8mb4_bin;

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

SELECT table_name AS "Tables", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
ORDER BY (data_length + index_length) DESC;

SELECT @total_com := SUM(IF(variable_name IN ('Com_select', 'Com_delete', 'Com_insert', 'Com_update', 'Com_replace'), variable_value, 0)) AS `Total`,
 @total_reads := SUM(IF(variable_name = 'Com_select', variable_value, 0)) AS `Total reads`,
 @total_writes := SUM(IF(variable_name IN ('Com_delete', 'Com_insert', 'Com_update', 'Com_replace'), variable_value, 0)) as `Total writes`,
 ROUND((@total_reads / @total_com * 100),2) as `Reads %`,
 ROUND((@total_writes / @total_com * 100),2) as `Writes %`
FROM information_schema.GLOBAL_STATUS;