MySQL数据库常用的41个脚本，速来下载！

show processlist;select * from information_schema.processlist limit 10;select user,SUBSTRING_INDEX(host,':',1) as ip,db,count(*) as num from information_schema.processlist group by user,ip,db order by num desc;

select id,USER,HOST,DB,COMMAND,TIME,STATE from information_schema.processlist where COMMAND!='Sleep';select id,USER,HOST,DB,COMMAND,TIME,STATE,info from information_schema.processlist where COMMAND!='Sleep'\G;

select info from information_schema.processlist where id=XXX\G;

select now();select host,user,Command,Time,State,info from information_schema.processlist where COMMAND !='Sleep' and user not in ('repl','system user','event_scheduler','root') ORDER BY time desc ;

select concat('kill ',id,';'),host,user,command,time,state,info from information_schema.processlistwhere command !='Sleep' and user not in('repl','system user','event_scheduler')order by time desc limit 10;

update performance_schema.setup_instruments set enabled='YES' where name like '%lock%';

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;show status like '%key_read%';show status like 'Qcache%';
select table_schema,table_name,table_type from information_schema.tableswhere table_schema not in ('information_schema','mysql','performance_schema','sys')and table_name not in(select table_namefrom (select table_name,index_namefrom information_schema.statisticswhere table_schema not in ('information_schema','mysql','performance_schema','sys')group by table_name,index_name) tab_ind_colsgroup by table_name);

show processlist;show full processlist;select id,user,host,db,command,time,state,info from information_schema.processlist order by time;

select * from information_schema.innodb_trx\G;

select * from information_schema.innodb_locks;

select * from information_schema.innodb_lock_waits;

select * from performance_schema.setup_instruments where name like '%lock%';select * from performance_schema.metadata_locks;

select * from performance_schema.metadata_locks\G;

select * from performance_schema.events_statements_current where thread_id=XXX\G;

select name,thread_id,processlist_id ,thread_os_id from performance_schema.threads where thread_id in (XXX);

select    locked_schema,    locked_table,    locked_type,    waiting_processlist_id,    waiting_age,    waiting_query,    waiting_state,    blocking_processlist_id,    blocking_age,    substring_index(sql_text,"transaction_begin;" ,-1) as blocking_query,    sql_kill_blocking_connectionfrom    (        select            b.owner_thread_id as granted_thread_id,            a.object_schema as locked_schema,            a.object_name as locked_table,            "metadata lock" as locked_type,            c.processlist_id as waiting_processlist_id,            c.processlist_time as waiting_age,            c.processlist_info as waiting_query,            c.processlist_state as waiting_state,            d.processlist_id as blocking_processlist_id,            d.processlist_time as blocking_age,            d.processlist_info as blocking_query,            concat('kill ', d.processlist_id) as sql_kill_blocking_connection        from            performance_schema.metadata_locks a        join performance_schema.metadata_locks b on a.object_schema = b.object_schema        and a.object_name = b.object_name        and a.lock_status = 'pending'        and b.lock_status = 'granted'        and a.owner_thread_id <> b.owner_thread_id        and a.lock_type = 'exclusive'        join performance_schema.threads c on a.owner_thread_id = c.thread_id        join performance_schema.threads d on b.owner_thread_id = d.thread_id    ) t1,    (        select            thread_id,            group_concat(   case when event_name = 'statement/sql/begin' then "transaction_begin" else sql_text end order by event_id separator ";" ) as sql_text        from           performance_schema.events_statements_history        group by thread_id    ) t2where    t1.granted_thread_id = t2.thread_id \G;

show engine innodb status\G;

select table_schema as DB_NAME,concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as DB_DATA_SIZE,concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') as DB_INDEX_SIZE,concat(round(sum((DATA_LENGTH + INDEX_LENGTH)/1024/1024),2),'MB') as DB_TOTAL_SIZE from information_schema.TABLES group by table_schema order by sum(DATA_LENGTH + INDEX_LENGTH) desc ;

select TABLE_NAME,DATA_LENGTH/1024/1024 as TAB_SIZE,INDEX_LENGTH/1024/1024 as INDEX_SIZE,(DATA_LENGTH + INDEX_LENGTH)/1024/1024 as TOTAL_SIZE from information_schema.TABLES where table_schema='OSCHECK' AND TABLE_NAME IN ('cjc01','cjc02');

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as DB_DATA_SIZE,concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') as DB_INDEX_SIZE,concat(round(sum((DATA_LENGTH + INDEX_LENGTH)/1024/1024),2),'MB') as DB_TOTAL_SIZE from information_schema.TABLES;

SELECTT.TABLE_SCHEMA,T.TABLE_NAME,T.ENGINE,CCSA.character_set_nameFROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSAWHERE CCSA.collation_name = T.table_collation ANDT.table_schema in ('xxx') ORDER BY 1,2;

selectconcat ('alter table ',a.TABLE_SCHEMA,'.',a.TABLE_NAME,' ROW_FORMAT=DYNAMIC;') row_changefrominformation_schema.tables a,(select TABLE_SCHEMA,TABLE_NAME,count(*) from COLUMNS group by TABLE_SCHEMA,TABLE_NAME having count(*)>20) bwherea.TABLE_SCHEMA=b.TABLE_SCHEMA anda.TABLE_NAME=b.TABLE_NAME andROW_FORMAT ='Compact';

SELECT TABLE_NAME, TABLE_SCHEMA, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';

SELECT concat(TABLE_SCHEMA,"/",TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';

SELECT NAME, FILE_SIZE, ALLOCATED_SIZE,(FILE_SIZE-ALLOCATED_SIZE)/1024/1024/1024 "GB" FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME in (SELECT concat(TABLE_SCHEMA,"/",TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%');

SELECT SUM(FILE_SIZE-ALLOCATED_SIZE)/1024/1024/1024 "GB" FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME in (SELECT concat(TABLE_SCHEMA,"/",TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%');

SELECT table_schema,table_name,column_name,hex(column_comment),column_comment FROM information_schema.columns WHERE length(column_comment)<>char_length(column_comment) ORDER BY table_schema,table_name;select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_COMMENT,LENGTH(COLUMN_COMMENT) from information_schema.COLUMNS WHERE LENGTH(COLUMN_COMMENT)>0;select TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT,length(TABLE_COMMENT) from information_schema.tables where LENGTH(TABLE_COMMENT)>0 AND TABLE_COMMENT !='VIEW';

SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERENAME NOT LIKE '%#%' AND NAME NOT LIKE '%@%' AND NAME NOT LIKE '%FTS_000000%' ANDNAME NOT IN (SELECT CONCAT(TABLE_SCHEMA,'/',TABLE_NAME) NAME FROM INFORMATION_SCHEMA.TABLES);

select * from performance_schema.global_status where variable_name in('Bytes_received','Bytes_sent','Connections','Locked_connects','Max_used_connections','Aborted_clients','Aborted_connects','Threads_cached','Threads_connected','Threads_created','Threads_running','Key_blocks_not_flushed','Key_blocks_unused','Key_blocks_used','Table_locks_immediate','Table_locks_waited','Key_read_requests','Key_reads','Qcache_free_blocks','Qcache_free_memory','Qcache_hits','Qcache_inserts','Qcache_lowmem_prunes','Qcache_not_cached','Qcache_queries_in_cache','Qcache_total_blocks','Created_tmp_disk_tables','Created_tmp_files','Created_tmp_tables','Slow_launch_threads','Slow_queries');

select * from performance_schema.global_variables where variable_name in('skip_name_resolve','skip_external_locking','lower_case_table_names','event_scheduler','max_connections','max_user_connections','wait_timeout','interactive_timeout','max_allowed_packet','binlog_format','expire_logs_days','innodb_buffer_pool_size','innodb_buffer_pool_instances','innodb_flush_log_at_trx_commit','innodb_lock_wait_timeout','gtid_mode','enforce_gtid_consistency','auto_increment_increment','auto_increment_offset');

select user,host,authentication_string,password_expired,password_last_changed,password_lifetime,account_locked from mysql.user;

select table_schema,table_type,count(*) from information_schema.tables group by table_schema,table_type;select table_schema,table_name,count(*) from information_schema.partitions where partition_name is not null group by table_schema,table_name;

select table_schema,count(*)from information_schema.statisticswhere table_schema not in ('information_schema','mysql','performance_schema','sys')group by table_schema;

select table_schema,table_name,definer from information_schema.views where table_schema notin ('sys','mysql','information_schema','performance_schema');

select trigger_schema,trigger_name,definer from information_schema.triggers where trigger_schema notin ('sys','mysql','information_schema','performance_schema');

select routine_schema,routine_name,routine_type,definer from information_schema.routines where routine_schema notin ('sys','mysql','information_schema','performance_schema');

select event_schema,event_name,definer from information_schema.events where event_schema notin ('sys','mysql','information_schema','performance_schema');

select table_schema,table_name,table_type,table_rows from information_schema.tables where table_schema notin ('sys','mysql','information_schema','performance_schema') order by table_rows desc limit 10;

select table_schema,table_name,count(*) from information_schema.partitions where partition_name is not null group by table_schema,table_name;select table_schema,table_name,max(partition_name) from information_schema.partitions where partition_name is not null group by table_schema,table_name;

show variables like 'event_scheduler';select event_schema,event_name,definer from information_schema.events where event_schema not in ('sys','mysql','information_schema','performance_schema');

SELECTt1.table_schema,t1.table_nameFROMinformation_schema.TABLES t1LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMAAND t1.table_name = t2.TABLE_NAMEAND t2.CONSTRAINT_NAME IN ('PRIMARY')WHEREt2.table_name IS NULLAND t1.table_type = 'BASE TABLE'AND t1.TABLE_SCHEMA NOT IN ('information_schema','performance_schema','mysql','sys');

SELECTt.TABLE_NAME,c.COLUMN_NAME,ts.AUTO_INCREMENTFROMINFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,information_schema.TABLES AS ts,information_schema.KEY_COLUMN_USAGE AS cWHEREt.TABLE_NAME = ts.TABLE_NAMEAND ts.TABLE_NAME = c.TABLE_NAMEAND t.TABLE_SCHEMA = 'XXX'AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'ORDER BY ts.`AUTO_INCREMENT` DESC;

select ......INTO OUTFILE '/home/mysql/xxxxx.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'from tab where ...;

secure-file-priv=''和secure-file-priv=时，无限制，任何目录都可以导出secure-file-priv=NULL时，无权限，任何目录都不能导出secure-file-priv=指定路径 时，指定路径有导出权限，其他目录不能导出

LOAD DATA INFILE '/db/mysqldata/3306/file/t1a.csv'INTO TABLE t2FIELDS TERMINATED BY ','ENCLOSED BY '"'LINES TERMINATED BY '\n'IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '/db/mysqldata/3306/file/t1a.csv'INTO TABLE t2FIELDS TERMINATED BY ','ENCLOSED BY '"'LINES TERMINATED BY '\n'IGNORE 1 LINES;

帮助信息mysqldump --help

全库备份mysqldump -uroot -p --all-databases --hex-blob --single-transaction --set-gtid-purged=OFF --master-data=2 --flush-logs --routines --triggers --events --extended-insert=TRUE --net-buffer-length=16777216 --max-allowed-packet=67108864 > /cjc/mysqldata/bak/mysql_bak1.sql8.0.33 基于 GTID 全备mysqldump -uroot -p --all-databases --hex-blob --source-data=2 --single-transaction --routines --triggers --events --set-gtid-purged=COMMENTED --flush-logs --flush-privileges --extended-insert=TRUE --net-buffer-length=16777216 --max-allowed-packet=128M > /cjc/mysqldata/dbtmpfile/mysql_master_bak_20241025.sql指定数据库备份mysqldump -uroot -p cjc --hex-blob --single-transaction --set-gtid-purged=OFF --master-data=2 --flush-logs --routines --triggers --events --extended-insert=TRUE --net-buffer-length=16777216 --max-allowed-packet=67108864 > /cjc/mysqldata/bak/mysql_bak2.sql指定表备份mysqldump -uroot -p cjc t2 t3 --hex-blob --single-transaction --set-gtid-purged=OFF --master-data=2 --flush-logs --extended-insert=TRUE --net-buffer-length=16777216 --max-allowed-packet=67108864 > /cjc/mysqldata/bak/mysql_bak3.sql只备份表结构mysqldump -uroot -p cjc t3 --no-data --hex-blob --single-transaction --set-gtid-purged=OFF --master-data=2 --flush-logs --extended-insert=TRUE --net-buffer-length=16777216 --max-allowed-packet=67108864 > /cjc/mysqldata/bak/mysql_bak4.sql只备份表数据mysqldump -uroot -p cjc t3 --no-create-info --hex-blob --single-transaction --set-gtid-purged=OFF --master-data=2 --flush-logs --extended-insert=TRUE --net-buffer-length=16777216 --max-allowed-packet=67108864 > /cjc/mysqldata/bak/mysql_bak4.sql用户备份mysqlpump -udumper -p --set-gtid-purged=OFF --exclude-databases=% --users --exclude-users=root,mysql.session,mysql.sys,monitor,dumper,eisoo,repluser,dbatmp> /mysqldata/back/06_mysql_bak6.sql指定库备份:---导出单个数据库，不指定 –databases参数，不会生成create database 和use dbname语句。mysqldump -u用户名 -p 数据库名 --extended-insert=TRUE --hex-blob --quick --master-data=2 --single-transaction --routines --triggers --events --set-gtid-purged=OFF --flush-logs > /备份路径/mysql_xxxbak.sql

cat mysql_tps_qps_detail.sh #!/bin/bashfunction check_tps_qps {date +%F_%T >> mysql_tps_qps_detail.logmysqladmin -uroot -p'******' extended-status -i1 -c2|awk 'BEGIN{local_switch=0;print "QPS Commit Rollback TPS delete insert select update Threads_con Threads_run \n------------------------------------------------------- "}$2 ~ /Queries$/ {q=$4-lq;lq=$4;}$2 ~ /Com_commit$/ {c=$4-lc;lc=$4;}$2 ~ /Com_rollback$/ {r=$4-lr;lr=$4;}$2 ~ /Innodb_rows_deleted$/ {deleted=$4-ldeleted;ldeleted=$4;}$2 ~ /Innodb_rows_inserted$/ {inserted=$4-linserted;linserted=$4;}$2 ~ /Innodb_rows_read$/ {read=$4-lread;lread=$4;}$2 ~ /Innodb_rows_updated$/ {updated=$4-lupdated;lupdated=$4;}$2 ~ /Threads_connected$/ {tc=$4;}$2 ~ /Threads_running$/ {tr=$4;if(local_switch==0){local_switch=1; count=0}else {if(count>10){count=0;print "------------------------------------------------------- \n QPS Commit Rollback TPS delete insert select update Threads_con Threads_run \n------------------------------------------------------- ";}else{count+=1;printf "%-6d %-8d %-7d %-8d %-6d %-8d %-7d %-8d %-10d %d \n", q,c,r,c+r,deleted,inserted,read,updated,tc,tr;} }}' >> mysql_tps_qps_detail.log}for i in {1..10}docheck_tps_qpsdone

show binary logs;show master status;show binlog events;show binlog events in 'mysql-bin.00000x';show binlog events in 'mysql-bin.00000x' from 100;  ---pos >=100show binlog events in 'mysql-bin.00000x' from 100 limit 10; ---pos >=100 and 显示 10行show binlog events in 'mysql-bin.00000x' from 100 limit 10,3; ---pos >=100 and 后面 第 10 行开始，显示2行

删除所有binlogpurge binary logs;###删除指定名称之前的所有文件(不包括此文件)purge binary logs to 'mysql-bin.000023';###删除指定时间之前的所有文件purge binary logs before '2023-11-11 12:00:00';

mysqlbinlog --no-defaults --base64-output=decode-rows --verbose -vvv mysql-relay-bin.000199 > 19.logmysqlbinlog --no-defaults --base64-output=decode-rows --verbose -vvv mysql-bin.000001 --start_position=101432xxx |moremysqlbinlog --no-defaults --base64-output=decode-rows --verbose -vvv mysql-relay-bin.000200 |grep -C 5 '关键字'|more

show slave status\G;show slave hosts\G;show master status\G;show variables like '%semi%';show global status like '%semi%';

create user repl@'IP' identified  with mysql_native_password by '******';grant replication slave on *.*  to repl@'IP';

#MySQL 8.0CHANGE REPLICATION SOURCE TO SOURCE_HOST = 'IP', SOURCE_PORT = 3306, SOURCE_USER = 'repl', SOURCE_PASSWORD = '******', SOURCE_AUTO_POSITION = 1,SOURCE_CONNECT_RETRY=10;
CHANGE REPLICATION SOURCE TO SOURCE_HOST='source_host_name', SOURCE_USER='replication_user_name', SOURCE_PASSWORD='replication_password', SOURCE_LOG_FILE='recorded_log_file_name', SOURCE_LOG_POS=recorded_log_position,SOURCE_CONNECT_RETRY=10;
#MySQL 5.7CHANGE MASTER TOMASTER_HOST='IP',MASTER_USER='repl',MASTER_PASSWORD='******',MASTER_PORT=3306,MASTER_AUTO_POSITION=1,MASTER_CONNECT_RETRY=10;
CHANGE MASTER TOMASTER_HOST='IP',MASTER_USER='repl',MASTER_PASSWORD='******',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',    MASTER_LOG_POS=154,  MASTER_CONNECT_RETRY=10;

set gtid_next='******-3788-11ef-a068-******:83';begin;commit;set gtid_next=automatic;start slave;

ls mysql-relay-bin.* | xargs du -s | awk '{sum += $1} END {print sum/1024"M"}'ls mysql-bin.* | xargs du -s | awk '{sum += $1} END {print sum/1024"M"}'

mysql -uroot -p  --tee=/cjc/dbtmpfile/20231111/mysql_20231111.log 

show processlist;show full processlist;

pager grep -v Sleeppager less

pager