mysql调试

观察锁情况：

SELECT * FROM sys.innodb_lock_waits;

SELECT * FROM information_schema.INNODB_TRX;

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

观察主从复制情况：

查看是否启用了GTID：

show variables like '%gtid%'

在maser查看slave情况：

show slave hosts;

在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

--

其他杂项：

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

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