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

小周的数据库进阶之路 2025-04-14

206

1.连接信息查询

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;

2.查询非Sleep会话

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;

3.通过ID查询对应的SQL语句

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

4.运行进程查询

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 ;

5.批量杀会话

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

6.锁信息查询

启动锁监控

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.tables
where table_schema not in ('information_schema','mysql','performance_schema','sys')
and table_name not in
(
select table_name
from (
select table_name,index_name
from information_schema.statistics
where table_schema not in ('information_schema','mysql','performance_schema','sys')
group by table_name,index_name) tab_ind_cols
group by table_name
);

7.检查会话阻塞

检查

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;

检查 metadata lock

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

查看锁源头

select * from performance_schema.metadata_locks\G;

查看锁源头最近一次执行的SQL

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

查询thread_id和processlist_id对应关系

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_connection
from
    (
        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
    ) t2
where
    t1.granted_thread_id = t2.thread_id \G;

8.存储引擎信息

show engine innodb status\G;

9.数据库大小查询

按库

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;

10.查询表字符集

SELECT
T.TABLE_SCHEMA,T.TABLE_NAME,T.ENGINE,CCSA.character_set_name
FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation AND
T.table_schema in ('xxx') ORDER BY 1,2;

11.查询行格式为Compact，并且列数>20的表

select
concat ('alter table ',a.TABLE_SCHEMA,'.',a.TABLE_NAME,' ROW_FORMAT=DYNAMIC;') row_change
from
information_schema.tables a,(select TABLE_SCHEMA,TABLE_NAME,count(*) from COLUMNS group by TABLE_SCHEMA,TABLE_NAME having count(*)>20) b
where
a.TABLE_SCHEMA=b.TABLE_SCHEMA and
a.TABLE_NAME=b.TABLE_NAME and
ROW_FORMAT ='Compact';

12.页压缩查询

页压缩的表数据文件，在某些文件系统下，cp后，会导致表放大，占用更多的磁盘空间

查询哪些表配置了页压缩属性

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

拼接出表空间格式名称，例如:cjc/t1

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

计算cp复制额外需要的空间

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=%');

13.检查注释信息

如果存在注释乱码的问题，可能会影响到MySQL升级。

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';

14.检查MySQL库中，frm和ibd不匹配的表

查询比较耗时

5.7

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

15.运行参数查询

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'
);

16.全局参数查询

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'
);

17.用户信息查询

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

18.对象信息查询

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;

19.统计信息查询

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

20.视图信息查询

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

21.触发器信息查询

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

22.存储过程信息查询

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

23.event查询

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

24.表信息查询

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

25.分区表

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;

26.events查询

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');

27.数据库存在没有主键业务表

SELECT
t1.table_schema,
t1.table_name
FROM
information_schema.TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
t2.table_name IS NULL
AND t1.table_type = 'BASE TABLE'
AND t1.TABLE_SCHEMA NOT IN (
'information_schema',
'performance_schema',
'mysql',
'sys'
);

28.查询主键自增SQL

SELECT
t.TABLE_NAME,
c.COLUMN_NAME,
ts.AUTO_INCREMENT
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
information_schema.TABLES AS ts,
information_schema.KEY_COLUMN_USAGE AS c
WHERE
t.TABLE_NAME = ts.TABLE_NAME
AND ts.TABLE_NAME = c.TABLE_NAME
AND t.TABLE_SCHEMA = 'XXX'
AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY ts.`AUTO_INCREMENT` DESC;

29.导出到CSV

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=指定路径 时，指定路径有导出权限，其他目录不能导出

30.LOAD 导入

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

普通用户

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

31.mysqldump常用语句

mysqldump备份参考语句：

帮助信息
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.sql
8.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

32.tps,qps查询

cat mysql_tps_qps_detail.sh 
#!/bin/bash
function check_tps_qps {
date +%F_%T >> mysql_tps_qps_detail.log
mysqladmin -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}
do
check_tps_qps
done

33.查询 binlog

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 >=100
show 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行

34.删除 binlog

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

35.mysqlbinlog

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

其中：grep -C 中的C表示前后

B是前面 A是后面 C是前后

16.高可用查询

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

37.主从配置

主从:

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

#MySQL 8.0
CHANGE 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.7
CHANGE MASTER TO
MASTER_HOST='IP',
MASTER_USER='repl',
MASTER_PASSWORD='******',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_CONNECT_RETRY=10;


CHANGE MASTER TO
MASTER_HOST='IP',
MASTER_USER='repl',
MASTER_PASSWORD='******',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',    
MASTER_LOG_POS=154,  
MASTER_CONNECT_RETRY=10;

38.跳过单个事务

也就是将执行失败的事务，通过begin;commit;的方式替换为一个空事务。

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

39.统计某类文件大小

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"}'

40.输出执行结果

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

41.设置过滤

show processlist;
show full processlist;

过滤信息

pager grep -v Sleep
pager less

取消过滤

pager

###chenjuchao 20250325###

欢迎关注我的公众号《IT小Chen》

文章转载自小周的数据库进阶之路，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

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

评论