MySQL主從復(fù)制遇到1590報(bào)錯(cuò)解決分析
故障描述
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: 1590 Last_Error: The incident LOST_EVENTS occured on the master. Message: REVOKE/GRANT failed while granting/revoking privileges in databases. Skip_Counter: 0 Exec_Master_Log_Pos: 12531 Relay_Log_Space: 69304 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_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: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1590 Last_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 requirements mysql> 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)?nbsp;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é)
- 權(quán)限變更操作只處理了一部分并發(fā)生錯(cuò)誤時(shí),會(huì)導(dǎo)致 binlog 寫一條 INCIDENT_EVENT,從而導(dǎo)致主從復(fù)制報(bào)錯(cuò)。
- 在使用密碼復(fù)雜度插件時(shí),使用 CREATE 語句同時(shí)創(chuàng)建兩個(gè)用戶,會(huì)出現(xiàn)一個(gè)用戶創(chuàng)建成功另外一個(gè)用戶創(chuàng)建失敗的情況。
建議
- 使用了密碼復(fù)雜度插件,創(chuàng)建用戶時(shí)一條 CREATE 語句只創(chuàng)建一個(gè)用戶。
- 授權(quán)時(shí)一條 GRANT 語句只對(duì)一個(gè)用戶授權(quán),防止因權(quán)限錯(cuò)誤導(dǎo)致部分授權(quán)成功的問題。
更多技術(shù)文章,請(qǐng)?jiān)L問:http://www.dbjr.com.cn/list/index
關(guān)于 SQLE
愛可生開源社區(qū)的 SQLE 是一款面向數(shù)據(jù)庫使用者和管理者,支持多場(chǎng)景審核,支持標(biāo)準(zhǔn)化上線流程,原生支持 MySQL 審核且數(shù)據(jù)庫類型可擴(kuò)展的 SQL 審核工具。
SQLE 獲取
以上就是MySQL主從復(fù)制遇到1590報(bào)錯(cuò)解決分析的詳細(xì)內(nèi)容,更多關(guān)于MySQL主從復(fù)制1590報(bào)錯(cuò)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
MySQL創(chuàng)建和刪除數(shù)據(jù)庫的命令及相關(guān)PHP腳本的操作方法
這篇文章主要介紹了MySQL創(chuàng)建和刪除數(shù)據(jù)庫的命令及相關(guān)PHP腳本的操作方法,這里主要講述Linux中在mysqladmin下的命令操作,需要的朋友可以參考下2015-11-11

mysql利用mysqlbinlog命令恢復(fù)誤刪除數(shù)據(jù)的實(shí)現(xiàn)

Linux下MySQL安裝配置 MySQL配置參數(shù)詳解

解決windows下mysql8修改my.ini設(shè)置datadir后無法啟動(dòng)問題