SHARE
TWEET

Untitled

a guest May 22nd, 2017 56 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #Is autoincrement-Max-Value near?  
  2. 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';
  3. #Which account do not close the connection correct?
  4. 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;
  5. #Which host has the most connections open at the moment?   
  6. select * from hosts order by CURRENT_CONNECTIONS DESC LIMIT 10;
  7. #Who killed the Radio Star?
  8. 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;
  9. #Show ALTER TABLE-Statements to convert Tables to InnoDB   
  10. select concat('alter table ',TABLE_SCHEMA ,'.',table_name,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables where table_type='BASE TABLE' and engine = 'InnoDB';
  11. #Get all Not-InnoDB-Tables 
  12. 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';
  13. #Get all Tables using Fulltext-Index   
  14. select TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE INDEX_TYPE='FULLTEXT';
  15. #Get all Tables without Primary Key
  16. 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';
  17. #Get Size of each Database 
  18. select table_schema "Database",round(sum(data_length+index_length)/1024/1024/1024,4) "Size (GB)" from information_schema.tables group by table_schema;
  19. #Get Size of all Databases 
  20. select table_schema "Database",round(sum(data_length+index_length)/1024/1024/1024,4) "Size (GB)" from information_schema.tables;
  21. #Get Size of Top 15 Tables 
  22. 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;
  23. #Set up Replication
  24. 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;
  25. #Bash-Approach for Size of 20 biggest Tables   
  26. ( 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
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top