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;- 查询非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;- 通过ID查询对应的SQL语句
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.processlist
where 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.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
);- 检查会话阻塞
检查
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;- 存储引擎信息
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;- 查询表字符集
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;- 查询行格式为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';- 页压缩查询
页压缩的表数据文件，在某些文件系统下，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=%');- 检查注释信息
如果存在注释乱码的问题，可能会影响到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';- 检查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);- 运行参数查询
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.statistics
where 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 not in ('sys','mysql','information_schema','performance_schema');- 触发器信息查询
select trigger_schema,trigger_name,definer from information_schema.triggers where trigger_schema not in ('sys','mysql','information_schema','performance_schema');- 存储过程信息查询
select routine_schema,routine_name,routine_type,definer from information_schema.routines where routine_schema not in ('sys','mysql','information_schema','performance_schema');- event查询
select event_schema,event_name,definer from information_schema.events where event_schema not in ('sys','mysql','information_schema','performance_schema');- 表信息查询
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;- 分区表
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;- 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');- 数据库存在没有主键业务表
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'
);- 查询主键自增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;- 导出到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=指定路径 时，指定路径有导出权限，其他目录不能导出- 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;- 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- 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- 查询 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 >=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行- 删除 binlog
删除所有binlog
purge binary logs;
###删除指定名称之前的所有文件(不包括此文件)
purge binary logs to 'mysql-bin.000023';
###删除指定时间之前的所有文件
purge binary logs before '2023-11-11 12:00:00';- 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是前后
- 高可用查询
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.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;- 统计某类文件大小
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 Sleep
pager less取消过滤
pager
微信
支付宝本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可。