溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“mysql互為主從的環境為什么會出現數據不一致”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
m1：
begin;
update t1 set c2='b1' where c1=2;
commit;
m2:
begin;
update t1 set c2='b2' where c1=2;
commit;
m1和m2同時提交，復制不會報錯，但是m1和m2的數據不一致，為什么？
因為sql_thread線程根據主鍵更新數據，不會校驗行數據
如何避免這種問題：
只在單節點進行寫入，如 keepalived+雙主，MGR，PXC如果多節點寫入都有這種問題發生。
例1：
表有主鍵和自增的情況：
root@localhost [testdb]>show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` varchar(10) DEFAULT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
| m1: | m2: |
root@localhost [testdb]>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | | 4 | ccc | | 6 | ddd | | 8 | eee | +----+------+ | root@localhost [testdb]>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | | 4 | ccc | | 6 | ddd | | 8 | eee | +----+------+ |
| root@localhost [testdb]>begin; | root@localhost [testdb]>begin; |
| root@localhost [testdb]>update t1 set c2='b1' where c1=2; | root@localhost [testdb]>update t1 set c2='b2' where c1=2; |
root@localhost [testdb]>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | aaa | | 2 | b1 | | 3 | ccc | | 4 | ccc | | 6 | ddd | | 8 | eee | +----+------+ | root@localhost [testdb]>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | aaa | | 2 | b2 | | 3 | ccc | | 4 | ccc | | 6 | ddd | | 8 | eee | +----+------+ |
| root@localhost [testdb]>commit; | root@localhost [testdb]>commit; |
root@localhost [testdb]>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | aaa | | 2 | b2 | | 3 | ccc | | 4 | ccc | | 6 | ddd | | 8 | eee | +----+------+ | root@localhost [testdb]>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | aaa | | 2 | b1 | | 3 | ccc | | 4 | ccc | | 6 | ddd | | 8 | eee | +----+------+ |
總結：update一條記錄同時提交，有主鍵的情況下，sql_thread是根據主鍵匹配行記錄，不會校驗行數據，所以m1更新了m2中表的記錄，m2更新了m1中表的記錄。
例2：有沒有主鍵同時更新一行數據的情況：
root@localhost [testdb]>show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
| m1 | m2 |
root@localhost [testdb]>select * from t2; +------+------+ | c1 | c2 | +------+------+ | 1 | aaa | | 2 | bbb | +------+------+ | root@localhost [testdb]>select * from t2; +------+------+ | c1 | c2 | +------+------+ | 1 | aaa | | 2 | bbb | +------+------+ |
| root@localhost [testdb]>begin; | root@localhost [testdb]>begin; |
| root@localhost [testdb]>update t2 set c2='b1' where c1=2; | root@localhost [testdb]>update t2 set c2='b2' where c1=2; |
root@localhost [testdb]>select * from t2; +------+------+ | c1 | c2 | +------+------+ | 1 | aaa | | 2 | b1 | +------+------+ | root@localhost [testdb]>select * from t2; +------+------+ | c1 | c2 | +------+------+ | 1 | aaa | | 2 | b2 | +------+------+ |
| root@localhost [testdb]>commit; | root@localhost [testdb]>commit; |
root@localhost [testdb]>select * from t2; +------+------+ | c1 | c2 | +------+------+ | 1 | aaa | | 2 | b1 | +------+------+ | root@localhost [testdb]>select * from t2; +------+------+ | c1 | c2 | +------+------+ | 1 | aaa | | 2 | b2 | +------+------+ |
root@localhost [testdb]>show slave status\G Last_Errno: 1032 Last_Error: Could not execute Update_rows event on table testdb.t2; Can't find record in 't2', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000013, end_log_pos 759 | root@localhost [testdb]>show slave status\G Last_Errno: 1032 Last_Error: Could not execute Update_rows event on table testdb.t2; Can't find record in 't2', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000026, end_log_pos 3064 |
總結：update一條記錄同時提交，有沒有主鍵的情況下，sql_thread是根據全表掃描匹配行記錄，所以m1更新在m2中找不到需要更新的行，報1032錯誤，m2更新在m1中找不到需要更新的行，也報1032錯誤。
“mysql互為主從的環境為什么會出現數據不一致”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
