Mysql数据库常用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