63. MySQL 异常及处理方法总结

在使用 MySQL 的过程中，我们会碰到各种各样的问题，如数据库突然响应很慢、表碎片空间占比过大等。本小节我们一起来学习一些常见的 MySQL 异常，以及响应的处理方法。

1. 主从同步数据异常，如何跳过错误？

由于种种原因，有时候从库可能会出现因主从数据导致的无法执行的 SQL 语句，那么此时就要跳过该 SQL 语句了。

1.1 binlog+position 模式

--在从库上操作。
1) 跳过错误报错
mysql> stop slave;
mysql> set global sql_slave_skip_counter =1;
mysql> start slave;
2) 查看同步状态

mysql> show slave status\G

1.2 GTID 模式

方法一、直接跳过错误

-- 在从库上操作
1) 停止 slave 进程
mysql> STOP SLAVE;

2) 设置事务号，事务号从 Executed_Gtid_Set 获取，在 session 里设置 gtid_next，即跳过这个 GTID
如从 show slave status 拿到 Executed_Gtid_Set 为 xxxx:1-4
mysql> SET @@SESSION.GTID_NEXT= 'xxxx:5'

3) 设置空事物
mysql> BEGIN; COMMIT;

4) 恢复事物号
mysql> SET SESSION GTID_NEXT = AUTOMATIC;

5) 启动 slave 进程
mysql> START SLAVE;

方法二：重置 master 方法跳过错误

-- 在从库上操作
-- 如从 show slave status 拿到 Executed_Gtid_Set 为 xxxx:1-4,以下命令表示，忽略 xxxx:1-5 这个 GTID 事务，下一次事务接着从 6 这个 GTID 开始，即可跳过上述错误。
1) 停止 slave 进程
mysql> STOP SLAVE;

2) 重置 MASTER
mysql> RESET MASTER;

3) 设置 GTID_PURGED
mysql> SET @@GLOBAL.GTID_PURGED ='xxxx:1-5';

4) 启动 slave 进程
mysql> START SLAVE;

2. 如何清理表碎片

当某张表删除完大量数据，或 MySQL 运行很长时间（一般指半年）后，难免会产生表碎片，这时就要对表进行碎片清理。

1) 查询表碎片大小（一般碎片率超过 30%，可以考虑清理表碎片）
mysql> select t.table_name, t.table_schema, t.ENGINE, t.TABLE_ROWS, ROUND(t.DATA_LENGTH/1024/1024,2) DATA_LENGTH_MB, ROUND(t.INDEX_LENGTH/1024/1024,2) INDEX_LENGTH_MB,
  ROUND((t.DATA_LENGTH+t.INDEX_LENGTH)/1024/1024,2) SUM_LENGTH_MB, ROUND(t.DATA_FREE/1024/1024,2) DATA_FREE_MB, CONCAT(ROUND((t.DATA_FREE / (t.DATA_LENGTH + t.INDEX_LENGTH + t.DATA_FREE)) * 100,2),'%') FRAGMENT_RATE 
  from information_schema.tables t
where 1=1
and t.DATA_FREE != 0
and (t.DATA_FREE / (t.DATA_LENGTH + t.INDEX_LENGTH + t.DATA_FREE)) >= 0.3 -- 碎片率超过 30%
and t.TABLE_ROWS >= 0
and ROUND(t.DATA_FREE/1024/1024,2) >= 100
order by t.DATA_FREE / t.DATA_LENGTH desc

2) 清理表碎片
-- MyISAM 存储引擎
mysql> OPTIMIZE TABLE tbl_name

-- Innodb 存储引擎
mysql> ALTER TABLE tbl_name ENGINE = Innodb;

3. CPU 突然爆表，如何解决？

CPU 使用率突然暴增至 90% 以上，数据库响应非常缓慢，一般都是某个 SQL 出现性能问题，耗光 CPU 资源。

1) 定位问题 sql
-- 找到执行时间很长，且占用会话比较多的 sql
mysql> show processlist\G
mysql> select * from information_schema.processlist order by info desc\G

2) 分析执行计划，判断 sql 是否存在优化空间
mysql> explain select * from xxx where xxx=xxx\G

3) 实施优化措施
这里就比较考验优化功力了，根据不同的问题 sql，快速判断需要采取何种优化措施。最简单的例子，sql 的 where 条件没有走索引，而且字段的选择性比较高，那这时就要考虑给字段创建相应的索引来解决。

4. 如何处理锁表问题？

当发生死锁问题时，一般通过 imformation_schema 的表来查询相关的事务和锁信息，找到产生死锁的源头会话，将该会话 kill 掉。

1) 查找产生死锁的源头会话
--以下 sql 能看到谁阻塞和谁在等待，以及等待多久的查询。
mysql> SELECT
     IFNULL(wt.trx_mysql_thread_id, 1) BLOCKING_THREAD_ID,t.trx_mysql_thread_id WAITING_THREAD_ID, CONCAT(p. USER, '@', p. HOST) USER,
     p.info SQL_TEXT, l.lock_table LOCK_TABLE, l.lock_index LOCKED_INDEX, l.lock_type LOCK_TYPE, l.lock_mode LOCK_MODE,
     CONCAT(FLOOR(HOUR (TIMEDIFF(now(), t.trx_wait_started)) / 24),'day ',MOD (HOUR (TIMEDIFF(now(), t.trx_wait_started)),24),':',
     MINUTE (TIMEDIFF(now(), t.trx_wait_started)),':',SECOND (TIMEDIFF(now(), t.trx_wait_started))) AS WAIT_TIME,
     t.trx_started TRX_STARTED, t.trx_isolation_level TRX_ISOLATION_LEVEL, t.trx_rows_locked TRX_ROWS_LOCKED, t.trx_rows_modified TRX_ROWS_MODIFIED
     FROM INFORMATION_SCHEMA.INNODB_TRX t
     LEFT JOIN information_schema.innodb_lock_waits w ON t.trx_id = w.requesting_trx_id
     LEFT JOIN information_schema.innodb_trx wt ON wt.trx_id = w.blocking_trx_id
     INNER JOIN information_schema.innodb_locks l ON l.lock_trx_id = t.trx_id
     INNER JOIN information_schema. PROCESSLIST p ON t.trx_mysql_thread_id = p.id
     ORDER BY 1\G

5. 小结

本小节主要总结了一些经常碰到的 MySQL 异常及相应处理方法，除此之外，在实际使用过程当中，大家肯定还会碰到各种各样的疑难杂症，经常进行总结可以积累丰富的实战经验。

到这里 MySQL 教程就完结了，错过精彩部分，请移入MySQL 教程栏目进行学习。

以上关于63. MySQL 异常及处理方法总结的文章就介绍到这了，更多相关内容请搜索码云笔记以前的文章或继续浏览下面的相关文章，希望大家以后多多支持码云笔记。