#Is autoincrement-Max-Value near?
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IF(LOCATE('unsigned', COLUMN_TYPE) > 0,1,0 ) AS IS_UNSIGNED, (CASE DATA_TYPE WHEN 'tinyint' THEN 255 WHEN 'smallint' THEN 65535 WHEN 'mediumint' THEN 16777215 WHEN 'int' THEN 4294967295 \ WHEN 'bigint' THEN 18446744073709551615 END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0,0,1) ) AS MAX_VALUE, AUTO_INCREMENT, AUTO_INCREMENT / (CASE DATA_TYPE WHEN 'tinyint' THEN 255 WHEN 'smallint' THEN 65535 WHEN 'mediumint' THEN 16777215 \ WHEN 'int' THEN 4294967295 WHEN 'bigint' THEN 18446744073709551615 END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0,0,1) ) AS AUTO_INCREMENT_RATIO FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME) \ WHERE TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema') AND EXTRA='auto_increment' AND AUTO_INCREMENT / (CASE DATA_TYPE WHEN 'tinyint' THEN 255 WHEN 'smallint' THEN 65535 WHEN 'mediumint' THEN 16777215 WHEN 'int' THEN 4294967295 \ WHEN 'bigint' THEN 18446744073709551615 END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1) ) > '0.8';
#Which account do not close the connection correct?
SELECT ess.USER, ess.HOST, (a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) - ess.COUNT_STAR not_closed, ((a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) - ess.COUNT_STAR) * 100 / (a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) pct_not_closed FROM performance_schema.events_statements_summary_by_account_by_event_name ess \ JOIN performance_schema.accounts a ON (ess.USER = a.USER AND ess.HOST = a.HOST) \ WHERE ess.EVENT_NAME = 'statement/com/Quit' AND (a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) > ess.COUNT_STAR;
#Which host has the most connections open at the moment?
select * from hosts order by CURRENT_CONNECTIONS DESC LIMIT 10;
#Who killed the Radio Star?
SELECT user, host, SUM(count_star - sum_errors) kills FROM events_statements_summary_by_account_by_event_name WHERE event_name LIKE '%kill%' GROUP BY user, host HAVING kills > 0;
#Show ALTER TABLE-Statements to convert Tables to InnoDB
select concat('alter table ',TABLE_SCHEMA ,'.',table_name,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables where table_type='BASE TABLE' and engine = 'InnoDB';
#Get all Not-InnoDB-Tables
select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES where ENGINE='MyISAM' and TABLE_SCHEMA NOT LIKE 'information_schema' AND TABLE_SCHEMA NOT LIKE 'mysql';
#Get all Tables using Fulltext-Index
select TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE INDEX_TYPE='FULLTEXT';
#Get all Tables without Primary Key
SELECT CONCAT(t.table_schema,".",t.table_name) as table_name FROM information_schema.TABLES t LEFT JOIN information_schema.TABLE_CONSTRAINTS tc ON t.table_schema = tc.table_schema AND t.table_name = tc.table_name AND tc.constraint_type = 'PRIMARY KEY' WHERE tc.constraint_name IS NULL AND t.table_type = 'BASE TABLE';
#Get Size of each Database
select table_schema "Database",round(sum(data_length+index_length)/1024/1024/1024,4) "Size (GB)" from information_schema.tables group by table_schema;
#Get Size of all Databases
select table_schema "Database",round(sum(data_length+index_length)/1024/1024/1024,4) "Size (GB)" from information_schema.tables;
#Get Size of Top 15 Tables
select table_schema, table_name "Table Name",round(sum(data_length+index_length)/1024/1024/1024,4) "Size (GB)" from information_schema.tables group by table_schema,table_name ORDER BY round(sum(data_length+index_length)) DESC LIMIT 15;
#Set up Replication
CHANGE MASTER TO MASTER_HOST='ip.ad.re.ss.',MASTER_USER='replication_user',MASTER_PASSWORD='secretpassword', MASTER_LOG_FILE='mysql-master-bin.00001', MASTER_LOG_POS=123456789;
#Bash-Approach for Size of 20 biggest Tables
( mysql_datadir=$(grep datadir /etc/my.cnf | cut -d "=" -f 2) cd $mysql_datadir for frm_file in $(find . -name "*.frm") do tbl_file=${frm_file//.frm/.ibd} table_schema=$(echo $frm_file | cut -d "/" -f 2) table_name=$(echo $frm_file | cut -d "/" -f 3 | cut -d "." -f 1) if [ -f $tbl_file ] then # unpartitioned table file_size=$(du -cb $tbl_file 2> /dev/null | tail -n 1) else # attempt partitioned innodb table tbl_file_partitioned=${frm_file//.frm/#*.ibd} file_size=$(du -cb $tbl_file_partitioned 2> /dev/null | tail -n 1) fi file_size=${file_size//total/} # Replace the below with whatever action you want to take, # for example, push the values into graphite. echo $file_size $table_schema $table_name done ) | sort -k 1 -nr | head -n 20