How-Tos
Create Database and User
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;"
Get Table Sizes of Given Schema
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;"
Drop Huge Tables
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;
Becoming MySQL-root on Debian-based Systems
Needs to be run with root privileges.
mysql --defaults-extra-file=/etc/mysql/debian.cnf
Connections per host
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
Compressed MySQL-Dump
database=schema-name; mysqldump --default-character-set=utf8mb4 --no-tablespaces --routines --opt --single-transaction --quick --databases $database | gzip > $(hostname)-$database-$(date +%F_%s).sql.gz
mysqldump --default-character-set=utf8mb4 --no-tablespaces --routines --opt --single-transaction --quick --all-databases | gzip > $(hostname -f)-$(date +%F_%s).sql.gz
Create Database with UTF8 collation
create database <database_name> character set UTF8mb4 collate utf8mb4_bin;
List Table Sizes
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;
MySQL Read/Write Ratio
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;