作者通過一個(gè)主從復(fù)制過程中 1590 的錯(cuò)誤，說明了 MySQL 8.0 在創(chuàng)建用戶授權(quán)過程中的注意事項(xiàng)。

作者：王祥

愛可生 DBA 團(tuán)隊(duì)成員，主要負(fù)責(zé) MySQL 故障處理和性能優(yōu)化。對(duì)技術(shù)執(zhí)著，為客戶負(fù)責(zé)。

本文來源：原創(chuàng)投稿

• 愛可生開源社區(qū)出品，原創(chuàng)內(nèi)容未經(jīng)授權(quán)不得隨意使用，轉(zhuǎn)載請(qǐng)聯(lián)系小編并注明來源。

故障描述

DMP 收到告警：從庫的 SQL 線程停止工作，MySQL 版本為 5.7.32，登錄到從庫查看復(fù)制信息報(bào)錯(cuò)如下：

mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send event……Last_Errno: 1590Last_Error: The incident LOST_EVENTS occured on the master. Message: REVOKE/GRANT failed while granting/revoking privileges in databases.Skip_Counter: 0Exec_Master_Log_Pos: 12531Relay_Log_Space: 69304Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 1590Last_SQL_Error: The incident LOST_EVENTS occured on the master. Message: REVOKE/GRANT failed while granting/revoking privileges in databases. ……

從庫錯(cuò)誤日志信息如下：

[ERROR] Slave SQL for channel '': The incident LOST_EVENTS occured on the master. Message: REVOKE/GRANT failed while granting/revoking privileges in databases. Error_code: 1590[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000003' position 12531.

解析對(duì)應(yīng) Binlog 信息如下：

# Incident: LOST_EVENTS
RELOAD DATABASE; # Shall generate syntax error

主庫錯(cuò)誤信息如下：

[ERROR] REVOKE/GRANT failed while granting/revoking privileges in databases. An incident event has been written to the binary log which will stop the slaves.

客戶反饋執(zhí)行了一些授權(quán)操作然后復(fù)制就出現(xiàn)報(bào)錯(cuò)，執(zhí)行的語句如下：

mysql> create user test@'%',app@'%' identified by 'Root@123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> grant all on test.* to test@'%',app@'%';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

故障分析

根據(jù)以上報(bào)錯(cuò)信息可知：在做權(quán)限變更時(shí)發(fā)生了錯(cuò)誤，主庫在 binlog 里寫一條 INCIDENT_EVENT，備庫在解析到 INCIDENT_EVENT 就直接報(bào)錯(cuò)。

那在什么情況下執(zhí)行授權(quán)語句會(huì)導(dǎo)致主庫在 binlog 寫 INCIDENT_EVENT 呢？

當(dāng)權(quán)限變更操作只處理了一部分并發(fā)生錯(cuò)誤時(shí)，主庫會(huì)在 binlog 里寫一條 INCIDENT_EVENT。

那什么情況下會(huì)發(fā)生權(quán)限變更只處理一部分而有一部分沒處理完呢？

下面舉例說明兩種相關(guān)場(chǎng)景。

MySQL 5.7 的問題

在 MySQL 5.7 里使用 GRANT 語句新建用戶，其中有部分權(quán)限有問題。

使用 GRANT 創(chuàng)建 test 用戶（MySQL 8.0 版本已經(jīng)不支持使用 GRANT 創(chuàng)建用戶）：

mysql> grant select,insert,file on test.* to test@'%' identified by 'Q1w2e3E$';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> select user,host from mysql.user where user='test' and host='%';
+------+--------+
| user | host |
+------+--------+
| test | %    |
+------+--------+
1 row in set (0.00 sec)mysql> show grants for test@'%';
+--------------------------------------------+
| Grants for test@%                 |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' |
+---------------------------------------------+
1 row in set (0.00 sec)

在創(chuàng)建用戶時(shí)對(duì) test 庫授予 SELECT、INSERT、FILE 權(quán)限，因 FILE 權(quán)限不能授予某個(gè)數(shù)據(jù)庫而導(dǎo)致語句執(zhí)行失敗。但最終結(jié)果是：test@'%' 創(chuàng)建成功，授權(quán)部分失敗。從上面的測(cè)試可知，使用 GRANT 創(chuàng)建用戶其實(shí)是分為兩個(gè)步驟：創(chuàng)建用戶和授權(quán)。權(quán)限有問題并不影響用戶的創(chuàng)建，上述語句會(huì)導(dǎo)致主庫在 binlog 寫 INCIDENT_EVENT，從而導(dǎo)致主從復(fù)制報(bào)錯(cuò)。

GRANT 對(duì)兩個(gè)用戶同時(shí)授權(quán)

使用一條 GRANT 語句，同時(shí)給 test@'10.186.63.5' 與 test@'10.186.63.29' 用戶授權(quán)，其中 test@'10.186.63.5' 用戶存在，而 test@'10.186.63.29' 不存在。

mysql> create user test@'10.186.63.5' identified by '123';
Query OK, 0 rows affected (0.00 sec)mysql> grant all on test.* to test@'10.186.63.5',test@'10.186.63.29';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> show grants for test@'10.186.63.5';
+----------------------------------------------------------+
| Grants for test@10.186.63.5                              |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'10.186.63.5'               |
| GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'10.186.63.5' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

根據(jù)上面的實(shí)驗(yàn)可知：test@'10.186.63.5' 用戶存在故授權(quán)成功，而 test@'10.186.63.29' 用戶不存在授權(quán)失敗。上述語句也會(huì)導(dǎo)致主庫在 binlog 寫 INCIDENT_EVENT，從而導(dǎo)致主從復(fù)制報(bào)錯(cuò)。

但以上兩種情況似乎都不符合客戶執(zhí)行語句情況，從報(bào)錯(cuò)來看是因?yàn)槊艽a復(fù)雜度不夠而導(dǎo)致創(chuàng)建用戶失敗了，那到底是什么原因?qū)е聫膸斐霈F(xiàn) 1590 錯(cuò)誤呢？下面我們來看看在使用了密碼復(fù)雜度插件后使用create語句同時(shí)創(chuàng)建兩個(gè)用戶會(huì)有什么問題。

mysql> show global variables like '%validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| query_cache_wlock_invalidate         | OFF    |
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| universe_op   | %         |
| root          | 127.0.0.1 |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)mysql> create user test@'%',app@'%' identified by 'Root@123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirementsmysql> select user,host from mysql.user;（app@'%'創(chuàng)建成功，test@'%'創(chuàng)建失敗）
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| app           | %         |
| universe_op   | %         |
| root          | 127.0.0.1 |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
6 rows in set (0.00 sec)

上述測(cè)試使用 CREATE USER 同時(shí)創(chuàng)建 test@'%'、app@'%'。但因?yàn)槊艽a復(fù)雜度不合符要求而失敗報(bào)錯(cuò)（多次測(cè)試發(fā)現(xiàn)并不是密碼復(fù)雜度不夠，只要同時(shí)創(chuàng)建兩個(gè)用戶都會(huì)報(bào)密碼復(fù)雜度不符合要求。在未使用密碼復(fù)雜度插件時(shí)是可以同時(shí)創(chuàng)建兩個(gè)用戶），正常的話這兩個(gè)用戶應(yīng)該都會(huì)創(chuàng)建失敗。但實(shí)際上 app@'%' 用戶創(chuàng)建成功了。

到這里我們就明白文章開始描述故障的觸發(fā)原因：數(shù)據(jù)庫實(shí)例開啟了密碼復(fù)雜度插件，使用 CREATE USER 同時(shí)創(chuàng)建兩個(gè)用戶，因?yàn)槊艽a復(fù)雜度不符合要求而報(bào)錯(cuò)，但 app@'%' 是已經(jīng)創(chuàng)建了， test@'%' 用戶未創(chuàng)建，緊接著又執(zhí)行了 GRANT 語句給兩個(gè)用戶同時(shí)授權(quán)，此時(shí)因?yàn)?test@'%' 用戶不存在，而導(dǎo)致 GRANT 語句部分執(zhí)行的問題，最終導(dǎo)致了主從復(fù)制報(bào)錯(cuò)。

故障解決

當(dāng)主從復(fù)制出現(xiàn) 1590 報(bào)錯(cuò)時(shí)，可以先解析 binlog，找到 INCIDENT_EVENT（搜索關(guān)鍵字 LOST_EVENTS）和對(duì)應(yīng)的 GTID，然后通過跳過這個(gè) GTID 的方式來恢復(fù)主從復(fù)制。在跳過 GTID 之前還需要先將數(shù)據(jù)補(bǔ)全，因?yàn)橹鲙煊幸粋€(gè)用戶是已經(jīng)授權(quán)成功從庫這部分授權(quán)是沒有執(zhí)行的。具體操作如下（在從庫執(zhí)行）：

mysql>set global super_read_only=0;
mysql>set sql_log_bin=0;
mysql>grant all on test.* to test@'10.186.63.5';

跳過報(bào)錯(cuò)對(duì)應(yīng)的 GTID，具體操作如下：

解析 binlog 找到 INCIDENT_EVENT。

mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000016 > /root/bin.log

跳過 LOST_EVENTS 對(duì)應(yīng)的 GTID（在從庫執(zhí)行）：

stop slave sql_thread;
set gtid_next='9d2e7089-2774-11ee-99d6-02000aba3f05:4198564';
begin;commit;
set gtid_next='automatic';
start slave sql_thread;

總結(jié)

1. 權(quán)限變更操作只處理了一部分并發(fā)生錯(cuò)誤時(shí)，會(huì)導(dǎo)致 binlog 寫一條 INCIDENT_EVENT，從而導(dǎo)致主從復(fù)制報(bào)錯(cuò)。
2. 在使用密碼復(fù)雜度插件時(shí)，使用 CREATE 語句同時(shí)創(chuàng)建兩個(gè)用戶，會(huì)出現(xiàn)一個(gè)用戶創(chuàng)建成功另外一個(gè)用戶創(chuàng)建失敗的情況。

建議

1. 使用了密碼復(fù)雜度插件，創(chuàng)建用戶時(shí)一條 CREATE 語句只創(chuàng)建一個(gè)用戶。
2. 授權(quán)時(shí)一條 GRANT 語句只對(duì)一個(gè)用戶授權(quán)，防止因權(quán)限錯(cuò)誤導(dǎo)致部分授權(quán)成功的問題。 更多技術(shù)文章，請(qǐng)?jiān)L問：https://opensource.actionsky.com/

關(guān)于 SQLE

愛可生開源社區(qū)的 SQLE 是一款面向數(shù)據(jù)庫使用者和管理者，支持多場(chǎng)景審核，支持標(biāo)準(zhǔn)化上線流程，原生支持 MySQL 審核且數(shù)據(jù)庫類型可擴(kuò)展的 SQL 審核工具。

SQLE 獲取

類型	地址
版本庫	https://github.com/actiontech/sqle
文檔	https://actiontech.github.io/sqle-docs/
發(fā)布信息	https://github.com/actiontech/sqle/releases
數(shù)據(jù)審核插件開發(fā)文檔	https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse