mysql调试

原创已于 2025-12-14 16:13:38 修改 · 633 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#数据库 #mysql

于 2021-11-24 12:35:17 首次发布

数据库技术同时被 2 个专栏收录

100 篇文章

订阅专栏

开发工具

77 篇文章

订阅专栏

观察锁情况：

**SELECT * FROM sys.innodb_lock_waits;**

**SELECT * FROM information_schema.INNODB_TRX;**

通过INNODB_TRX表的trx_mysql_thread_id字段查询事务在执行的sql：

SELECT * FROM information_schema.processlist WHERE ID='trx_mysql_thread_id';

通过trx_mysql_thread_id字段也可以KILL线程。

SHOW STATUS LIKE '%innodb_row_lock%';

InnoDB_row_lock_current_waits：当前正在等待锁定的数量。
InnoDB_row_lock_time：从系统启动到现在锁定总时间长度。
InnoDB_row_lock_time_avg：每次等待所花平均时间。
InnoDB_row_lock_time_max：从系统启动到现在等待最长的一次所花时间。
InnoDB_row_lock_waits：系统启动后到现在总共等待的次数。

show engine InnoDB status;

模拟长事务：

insert into taaa (`key`,`value`) select 'kxxx','vxxx' from taaa where id=xxx and not sleep(10) limit 1;

观察索引使用情况：

show status like 'Handler_read%';

1. Handler_read_key：值越大越好，代表基于索引的查询较多。
2. Handler_read_first，Handler_read_last，Handler_read_next，Handler_read_prev：都会利用索引，但查询是否高效还需要结合其它Handler_read值来判断。
3. Handler_read_rnd：不宜过大。
4. Handler_read_rnd_next：不宜过大，过大的话，代表全表扫描过多，要引起足够的警惕。

观察慢查询情况：

show variables like '%query%';

开启慢查询日志：

SET GLOBAL slow_query_log = 'ON';

设置慢查询阈值（新开连接才能看到效果）：

SET GLOBAL long_query_time = 0.2;

借助工具统计慢查询日志（不同版本日志格式有变化，需要使用对应版本的工具）：

mysqldumpslow -s at -t 3 myslow.log

观察二进制日志情况：

查看二进制日志格式：

show variables like '%binlog_format%'

查看二进制日志路径：

show variables like '%log_bin%'

查看当前的二进制文件列表：

show binary logs;

查看某个二进制文件里的事件：

show binlog events in 'binlog.000006' limit 300000,100

查看binlog里的GTID事务和sql：

mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.0000* | grep -30 "bdc4:40839'"

MySQL 8.0.20+开启binlog压缩：

select version(); -- version should be equal to or greater than 8.0.20
show variables like 'binlog_transaction%';
SET GLOBAL binlog_transaction_compression = ON;
SET GLOBAL binlog_transaction_compression_level_zstd = 10; -- compression effort, from 1 (the lowest effort) to 22 (the highest effort)

观察主从复制情况：

查看是否启用了GTID：

show variables like '%gtid%'

GTID模式下从库跳过单个错误事务：

stop slave;
set gtid_next='${Master_UUID}:${error_transaction_id}';
begin;commit;
set gtid_next='automatic';
start slave;

GTID模式下从库忽略整段事务集合：

stop slave;
set global gtid_purged='${Master_UUID}:${error_transaction_id1}-${error_transaction_id2},${Master_UUID}:${error_transaction_id3}'
start slave;

GTID模式下从库清空mysql.gtid_executed表：

select * from mysql.gtid_executed;
reset master;
select * from mysql.gtid_executed;

GTID模式下查看复制进度：

mysql> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
                                      CHANNEL_NAME: 
                                        GROUP_NAME: 
                                       SOURCE_UUID: 6fc327dc-676a-11f0-9db4-ba6f8943bdc4
                                         THREAD_ID: 952
                                     SERVICE_STATE: ON
                         COUNT_RECEIVED_HEARTBEATS: 3
                          LAST_HEARTBEAT_TIMESTAMP: 2025-08-15 19:37:29.169577
                          RECEIVED_TRANSACTION_SET: 6fc327dc-676a-11f0-9db4-ba6f8943bdc4:1-40772
                                 LAST_ERROR_NUMBER: 0
                                LAST_ERROR_MESSAGE: 
                              LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                           LAST_QUEUED_TRANSACTION: 6fc327dc-676a-11f0-9db4-ba6f8943bdc4:40772
 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2025-08-15 19:36:57.510409
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2025-08-15 19:36:57.510409
     LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2025-08-15 19:36:59.300280
       LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2025-08-15 19:36:59.300301
                              QUEUEING_TRANSACTION: 
    QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
   QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
1 row in set (0.00 sec)

解决从库复制报错cannot be converted from type xx to type xx, Error_code: MY-013146：

SET GLOBAL slave_type_conversions = 'ALL_NON_LOSSY';

[mysqld]
slave_type_conversions=ALL_NON_LOSSY

在maser查看slave情况：

show slave hosts;

清空show slave hosts：

停止主从同步，然后在原主库执行：

reset slave all;

在slave查看同步情况：

show slave status;

Slave_IO_State：从库的I/O线程状态。
Slave_IO_Running：I/O线程是否正在运行。
Slave_SQL_Running：SQL线程是否正在运行。
Seconds_Behind_Master：从库相对于主库的延迟时间，单位为秒。

备份恢复：

备份时同时获取同步点：

从主库备份：
mysqldump -h10.10.10.202 -P3306 -u"root" -p"123456" --databases dbname --single-transaction --master-data=2 > dump-master.sql

从从库备份：
mysqldump -h10.10.10.206 -P3306 -u"root" -p"123456" --databases dbname --single-transaction --dump-slave=2 > dump-slave.sql

GTID场景的备份：

备份单个数据库，忽略gtid范围（恢复时不产生binlog）：
mysqldump -h127.0.0.1 -P3306 -u"root" -p"123456" --databases dbname --single-transaction --set-gtid-purged=OFF > dump-no-gtid-range.sql

备份全部数据库，包含gtid范围（恢复时不产生binlog）：
mysqldump -h127.0.0.1 -P3306 -u"root" -p"123456" --all-databases --single-transaction --master-data=2 --set-gtid-purged=ON --triggers --routines --events > dump-with-gtid-range.sql

单个数据库：

全部数据库：

其他杂项：

查看某个表占用的磁盘空间：

select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables where table_schema='dbname' and table_name='tablename';

某个账号当前正在运行的sql语句：

select * from information_schema.processlist where user='someone';

某个ip当前的连接：

select * from information_schema.processlist where host like '10.10.10.123%';

服务器的超时设置：

show variables like  '%timeout%';

SHOW GLOBAL VARIABLES LIKE '%timeout%';

-- SET GLOBAL wait_timeout=600;

查看mysql隔离级别：

show variables like '%tx_isolation%';

kill掉sleep超过100s的连接：

-- 将结果复制出来，粘贴执行
SELECT CONCAT('KILL ', id, ';') AS kill_statement FROM information_schema.processlist WHERE command='Sleep' AND time>100;

忘记密码或无权访问的临时应急解决办法：

在/etc/my.cnf的mysqld配置段增加一行配置：

[mysqld]
skip-grant-tables

如果需要远端登录，注释掉mysqld里的配置项：

[mysqld]
#bind-address=127.0.0.1

重启mysqld。

使用unix socket文件登录，执行以下sql：

-- 查看用户
select * from mysql.user where user='root'\G
-- 新建用户（如果没有用户）
create user 'root'@'localhost' identified with 'mysql_native_password' by 'MYSQL_NEW_PASS';
create user 'root'@'%' identified with 'mysql_native_password' by 'MYSQL_NEW_PASS';
-- 重设密码（如果已有用户）
alter user 'root'@'localhost' identified with 'mysql_native_password' by 'MYSQL_NEW_PASS';
alter user 'root'@'%' identified with 'mysql_native_password' by 'MYSQL_NEW_PASS';
-- 赋权（只是举例，并不安全）
grant all privileges on *.* to 'root'@'localhost';
grant all privileges on *.* to 'root'@'%';
flush privileges;

--end--