> Mysql数据库常用sql语句，备查 ``` sql -- 2021/09/30 : create scripts -- check db before plesse must check mysql process and configure file and run os_check_mysql.sh and error_log. commond (eg:ps -ef | grep mysql/ tail -200 /xx/xx/xx.log) -- run commond Reference : mysql -v -v -v -uroot -p123456 -Dmysql < db_check.sql > mysql_2021.log -- db version select version(); -- database info show databases; select * from INFORMATION_SCHEMA.SCHEMATA; -- db user info select distinct concat('user: ''',user,'''@''',host,''';') as 'User Info' from mysql.user; -- db USER PRIVILEGES select * from INFORMATION_SCHEMA.USER_PRIVILEGES; -- db size(MB) select table_schema as 'Database Nmae', round(sum((data_length+index_length)/1024/1024),2) as 'size(MB)' from information_schema.tables where table_schema is not null group by table_schema; -- db error log show global variables like 'log_error'; -- db log info show variables like '%connect%'; show variables like 'character%'; show global variables like '%log_bin%'; -- db schema privileges select *from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES; -- rep info show slave status \G; show slave hosts \G; show processlist; -- mgr SELECT * FROM performance_schema.replication_group_members; -- tbs size(mb) select a.SCHEMA_NAME, a.DEFAULT_CHARACTER_SET_NAME as 'DEFAULT_CHAR',a.DEFAULT_COLLATION_NAME AS 'Default_collName', sum(table_rows) as 'RECORDS', round(sum(data_length)/1024/1024, 2) as 'DATASIZE(MB)', round(sum(index_length)/1024/1024, 2) as 'INDEXSIZE(MB)', round(sum(data_length+index_length)/1024/1024, 2) as 'SUMSIZE(MB)', round(sum(max_data_length)/1024/1024, 2) as 'MAXSIZE(MB)', round(sum(data_free)/1024/1024, 2) as 'FREESPACE(MB)' ,f.filesize_M as 'DISKFILESIZE(MB)' from INFORMATION_SCHEMA.SCHEMATA a left outer join information_schema.tables b on a.SCHEMA_NAME=b.TABLE_SCHEMA left outer join (select substring(b.file_name,3,locate('/',b.file_name,3)-3) as db_name, round(sum(total_extents*extent_size)/1024/1024,2) filesize_M from information_schema.FILES b group by substring(b.file_name,3,locate('/',b.file_name,3)-3)) f on ( a.SCHEMA_NAME= f.db_name) group by a.SCHEMA_NAME, a.DEFAULT_CHARACTER_SET_NAME,a.DEFAULT_COLLATION_NAME,filesize_M order by sum(data_length) desc, sum(index_length) desc; -- top 10 table order by size SELECT table_schema AS 'DB', table_name AS 'TABLENAME', a.TABLE_TYPE, a.`ENGINE`, a.CREATE_TIME, a.UPDATE_TIME, a.TABLE_COLLATION, table_rows AS '记录数', round(a.DATA_LENGTH / 1024 / 1024, 2 ) AS 'DATASIZE(MB)', round( index_length / 1024 / 1024, 2 ) AS 'INDEXSIZE(MB)', round( ( data_length + index_length ) / 1024 / 1024, 2 ) AS 'SUMSIZE(MB)', TRUNCATE( a.DATA_FREE / 1024 / 1024, 2 ) AS 'FREESPACE(MB)', truncate(f.filesize_M,2) AS 'DISKFILESIZE(MB)' FROM information_schema.TABLES a left outer join (select substring(b.file_name,3,locate('/',b.file_name,3)-3) as db_name, substring(b.file_name,locate('/',b.file_name,3)+1,(LENGTH(b.file_name)-locate('/',b.file_name,3)-4)) as tb_name, b.file_name, (total_extents*extent_size)/1024/1024 filesize_M from information_schema.FILES b order by filesize_M desc limit 20 ) f on ( a.TABLE_SCHEMA= f.db_name and a.TABLE_NAME=f.tb_name ) ORDER BY ( data_length + index_length ) DESC LIMIT 10; -- top 10 index order by size select iis.database_name, iis.table_name, iis.index_name, round((iis.stat_value*@@innodb_page_size)/1024/1024, 2) SizeMB, s.NON_UNIQUE, s.INDEX_TYPE, GROUP_CONCAT(s.COLUMN_NAME order by SEQ_IN_INDEX) COLUMN_NAME from (select * from mysql.innodb_index_stats WHERE index_name not in ('PRIMARY','GEN_CLUST_INDEX') and stat_name='size' order by (stat_value*@@innodb_page_size) desc limit 10 ) iis left join INFORMATION_SCHEMA.STATISTICS s on (iis.database_name=s.TABLE_SCHEMA and iis.table_name=s.TABLE_NAME and iis.index_name=s.INDEX_NAME) GROUP BY iis.database_name,iis.TABLE_NAME,iis.INDEX_NAME,(iis.stat_value*@@innodb_page_size),s.NON_UNIQUE,s.INDEX_TYPE order by (stat_value*@@innodb_page_size) desc; -- wait event select event_name, count_star, round(sum_timer_wait/1000000000/1000,2) as 'sum_time_wait(S)' from performance_schema.events_waits_summary_global_by_event_name where count_star > 0 order by sum_timer_wait desc limit 10; -- lock info select * from information_schema.processlist where state like '%lock%'; -- slow log info show variables like '%slow_query_log_file%'; -- Cache_hits show global status like 'QCache_hits'; --show all parameter show variables ```