常見(jiàn)的 MySQL 異常及處理方法

在使用 MySQL 的過(guò)程中，我們會(huì)碰到各種各樣的問(wèn)題，如數(shù)據(jù)庫(kù)突然響應(yīng)很慢、表碎片空間占比過(guò)大等。本小節(jié)我們一起來(lái)學(xué)習(xí)一些常見(jiàn)的 MySQL 異常，以及響應(yīng)的處理方法。

1. 主從同步數(shù)據(jù)異常，如何跳過(guò)錯(cuò)誤？

由于種種原因，有時(shí)候從庫(kù)可能會(huì)出現(xiàn)因主從數(shù)據(jù)導(dǎo)致的無(wú)法執(zhí)行的 SQL 語(yǔ)句，那么此時(shí)就要跳過(guò)該SQL 語(yǔ)句了。

1.1 binlog+position 模式

-- 在從庫(kù)上操作
1) 跳過(guò)錯(cuò)誤報(bào)錯(cuò)
mysql> stop slave;
mysql> set global sql_slave_skip_counter =1;
mysql> start slave;

2) 查看同步狀態(tài)
mysql> show slave status\G

1.2 GTID 模式

• 方法一：直接跳過(guò)錯(cuò)誤：

-- 在從庫(kù)上操作
1) 停止slave進(jìn)程
mysql> STOP SLAVE;

2) 設(shè)置事務(wù)號(hào)，事務(wù)號(hào)從Executed_Gtid_Set獲取，在session里設(shè)置gtid_next，即跳過(guò)這個(gè)GTID
如從show slave status拿到Executed_Gtid_Set為xxxx:1-4
mysql> SET @@SESSION.GTID_NEXT= 'xxxx:5'

3) 設(shè)置空事物
mysql> BEGIN; COMMIT;

4) 恢復(fù)事物號(hào)
mysql> SET SESSION GTID_NEXT = AUTOMATIC;

5) 啟動(dòng)slave進(jìn)程
mysql> START SLAVE;

• 方法二：重置master方法跳過(guò)錯(cuò)誤

-- 在從庫(kù)上操作
-- 如從show slave status拿到Executed_Gtid_Set為xxxx:1-4,以下命令表示，忽略xxxx:1-5這個(gè)GTID事務(wù)，下一次事務(wù)接著從 6 這個(gè)GTID開(kāi)始，即可跳過(guò)上述錯(cuò)誤。
1) 停止slave進(jìn)程
mysql> STOP SLAVE;

2) 重置MASTER
mysql> RESET MASTER;

3) 設(shè)置GTID_PURGED
mysql> SET @@GLOBAL.GTID_PURGED ='xxxx:1-5';

4) 啟動(dòng)slave進(jìn)程
mysql> START SLAVE;

2. 如何清理表碎片

當(dāng)某張表刪除完大量數(shù)據(jù)，或 MySQL 運(yùn)行很長(zhǎng)時(shí)間（一般指半年）后，難免會(huì)產(chǎn)生表碎片，這時(shí)就要對(duì)表進(jìn)行碎片清理。

1) 查詢表碎片大?。ㄒ话闼槠食^(guò)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 -- 碎片率超過(guò)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存儲(chǔ)引擎
mysql> OPTIMIZE TABLE tbl_name

-- Innodb存儲(chǔ)引擎
mysql> ALTER TABLE tbl_name ENGINE = Innodb;

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

CPU 使用率突然暴增至 90% 以上，數(shù)據(jù)庫(kù)響應(yīng)非常緩慢，一般都是某個(gè) SQL 出現(xiàn)性能問(wèn)題，耗光 CPU 資源。

1) 定位問(wèn)題sql
-- 找到執(zhí)行時(shí)間很長(zhǎng)，且占用會(huì)話比較多的sql
mysql> show processlist\G
mysql> select * from information_schema.processlist order by info desc\G

2) 分析執(zhí)行計(jì)劃，判斷sql是否存在優(yōu)化空間
mysql> explain select * from xxx where xxx=xxx\G

3) 實(shí)施優(yōu)化措施
這里就比較考驗(yàn)優(yōu)化功力了，根據(jù)不同的問(wèn)題sql，快速判斷需要采取何種優(yōu)化措施。最簡(jiǎn)單的例子，sql的where條件沒(méi)有走索引，而且字段的選擇性比較高，那這時(shí)就要考慮給字段創(chuàng)建相應(yīng)的索引來(lái)解決。

4. 如何處理鎖表問(wèn)題？

當(dāng)發(fā)生死鎖問(wèn)題時(shí)，一般通過(guò) imformation_schema 的表來(lái)查詢相關(guān)的事務(wù)和鎖信息，找到產(chǎn)生死鎖的源頭會(huì)話，將該會(huì)話kill掉。

1) 查找產(chǎn)生死鎖的源頭會(huì)話
--以下sql能看到誰(shuí)阻塞和誰(shuí)在等待，以及等待多久的查詢。
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. 小結(jié)

本小節(jié)主要總結(jié)了一些經(jīng)常碰到的 MySQL 異常及相應(yīng)處理方法，除此之外，在實(shí)際使用過(guò)程當(dāng)中，大家肯定還會(huì)碰到各種各樣的疑難雜癥，經(jīng)常進(jìn)行總結(jié)可以積累豐富的實(shí)戰(zhàn)經(jīng)驗(yàn)。