Mysql
主-主複製重複密鑰
我已經設置了兩台伺服器主-主複製 mysql ，我設法設置成功，但問題是我得到了重複的密鑰
MASTER 1 配置
/etc/my.cnf
[mysqld] server-id=1 bind-address = 0.0.0.0 log_bin = /var/log/mysql/bin.log log-slave-updates log_bin-index = /var/log/mysql/bin.index log_error = /var/log/mysql/error.log relay_log = /var/log/mysql/relay.log relay-log-info-file = /var/log/mysql/relay.info relay-log-index = /var/log/mysql/relay.index binlog_do_db=database_name replicate-do-db = database_name auto-increment-increment = 10 auto-increment-offset = 1 innodb_file_per_table=1 open_files_limit=7306mysql> 顯示從屬狀態\G
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.1 Master_User: database_name Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000021 Read_Master_Log_Pos: 8385119 Relay_Log_File: relay.000019 Relay_Log_Pos: 79533 Relay_Master_Log_File: bin.000021 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: database_name Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '19882' for key 'PRIMARY'' on query. Default database: 'database_name'. Query: 'INSERT INTO dle_comments (post_id, date, autor, email, text, ip, is_register, approve) values ('353', '2014-06-16 13:04:43', 'ofadoruo', 'ebuxaje@asdfooff.org', 'Peritoneal <a href="//accutane-pills-buy.com/#Accutane-bzakam">Accutane</<br />a> emerge subjective, section state; pattern; <a href="//buycanadapropecia.com/#Propecia-Online-xj5iln">Propeci<br />a Online</a> rhinoscopy, haemorrhage cats, median non-essential Buy Propecia <a href="//cialis-buy-canada.com/#Cialis-20mg-Price-h2sfgx">Gener<br />ic Cialis</a> orthopnoea thrombin widely; mouth, limits <a href="//genericaccutanebuy.com/#Accutane-Online-qk48il">Online<br /> Accutane</a> detachment, supplements triage, kinder Buy Accutane itch <a href="//topamax-buy.com/#Buy-Topamax-Online-t5us55">Topamax Tablet</a> until Skip_Counter: 0 Exec_Master_Log_Pos: 172345 Relay_Log_Space: 8223117 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: 1062 Last_SQL_Error: Error 'Duplicate entry '19882' for key 'PRIMARY'' on query. Default database: 'database_name'. Query: 'INSERT INTO dle_comments (post_id, date, autor, email, text, ip, is_register, approve) values ('353', '2014-06-16 13:04:43', 'ofadoruo', 'ebuxaje@asdfooff.org', 'Peritoneal <a href="//accutane-pills-buy.com/#Accutane-bzakam">Accutane</<br />a> emerge subjective, section state; pattern; <a href="//buycanadapropecia.com/#Propecia-Online-xj5iln">Propeci<br />a Online</a> rhinoscopy, haemorrhage cats, median non-essential Buy Propecia <a href="//cialis-buy-canada.com/#Cialis-20mg-Price-h2sfgx">Gener<br />ic Cialis</a> orthopnoea thrombin widely; mouth, limits <a href="//genericaccutanebuy.com/#Accutane-Online-qk48il">Online<br /> Accutane</a> detachment, supplements triage, kinder Buy Accutane itch <a href="//topamax-buy.com/#Buy-Topamax-Online-t5us55">Topamax Tablet</a> until Replicate_Ignore_Server_Ids: Master_Server_Id: 2 1 row in set (0.00 sec)MASTER 2 配置
/etc/my.cnf
[mysqld] server-id=2 bind-address = 0.0.0.0 log_bin = /var/log/mysql/bin.log log-slave-updates log_bin-index = /var/log/mysql/bin.index log_error = /var/log/mysql/error.log relay_log = /var/log/mysql/relay.log relay-log-info-file = /var/log/mysql/relay.info relay-log-index = /var/log/mysql/relay.index binlog_do_db=database_name replicate-do-db = database_name auto-increment-increment = 10 auto-increment-offset = 2 innodb_file_per_table=1 open_files_limit=7306mysql> 顯示從屬狀態\G
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.2 Master_User: database_name Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000023 Read_Master_Log_Pos: 411543 Relay_Log_File: relay.000029 Relay_Log_Pos: 1378971 Relay_Master_Log_File: bin.000022 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: database_name Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '22091' for key 'PRIMARY'' on query. Default database: 'database_name'. Query: 'INSERT INTO dle_comments (post_id, date, autor, email, text, ip, is_register, approve) values ('353', '2014-06-16 13:46:46', 'hojosisuoiw', 'ogibkpefi@mannbdinfo.org', 'Continuous <a href=\"//20mg-online-levitra.com/#Levitra-rm4nq9\" target=\"_blank\">Levitra 20mg</a> semilaterally Levitra 20 Mg undescended myelofibrosis: earlier prepatellar Levitra 20 Mg <a href=\"//pills-accutane.com/#Accutane-Pills-ks2dm4\" target=\"_blank\">Accutane</a> enlarges unneeded non-paracetamol vomiting boards <a href=\"//buycanadaaccutane.com/#20-Mg-Accutane-fk0jr1\" target=\"_blank\">Accutane Dose</a> post volume; characteristics communicate ingestion, <a href=\"//buyaccutanecanada.com/#Order-Accutane-Online-ncit4x\" target=\"_blank\">Order Accutane Online</a> office Buy Isotretinoin agitated implications hypocaloric doughnut <a href=\"//cialis-generic-20mg.com/#Tadalafil-20mg-nv91qa\" target=\"_bla Skip_Counter: 0 Exec_Master_Log_Pos: 1562757 Relay_Log_Space: 3210092 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: 1062 Last_SQL_Error: Error 'Duplicate entry '22091' for key 'PRIMARY'' on query. Default database: 'database_name'. Query: 'INSERT INTO dle_comments (post_id, date, autor, email, text, ip, is_register, approve) values ('353', '2014-06-16 13:46:46', 'hojosisuoiw', 'ogibkpefi@mannbdinfo.org', 'Continuous <a href=\"//20mg-online-levitra.com/#Levitra-rm4nq9\" target=\"_blank\">Levitra 20mg</a> semilaterally Levitra 20 Mg undescended myelofibrosis: earlier prepatellar Levitra 20 Mg <a href=\"//pills-accutane.com/#Accutane-Pills-ks2dm4\" target=\"_blank\">Accutane</a> enlarges unneeded non-paracetamol vomiting boards <a href=\"//buycanadaaccutane.com/#20-Mg-Accutane-fk0jr1\" target=\"_blank\">Accutane Dose</a> post volume; characteristics communicate ingestion, <a href=\"//buyaccutanecanada.com/#Order-Accutane-Online-ncit4x\" target=\"_blank\">Order Accutane Online</a> office Buy Isotretinoin agitated implications hypocaloric doughnut <a href=\"//cialis-generic-20mg.com/#Tadalafil-20mg-nv91qa\" target=\"_bla Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec)**我的問題是：**如何安全地修復兩個伺服器中的兩個數據庫沒有問題的重複密鑰
**更新：**設置 324234 後，我只在 MASTER 2 中收到以下錯誤
Slave_IO_Running: Yes Slave_SQL_Running: No Last_SQL_Errno: 1690 Last_SQL_Error: Error 'BIGINT UNSIGNED value is out of range in '(`database_name`.`dle_post`.`comm_num` - 1)'' on query. Default database: 'database_name'. Query: 'UPDATE dle_post SET comm_num=comm_num-1 where id='353'' Replicate_Ignore_Server_Ids: Master_Server_Id: 0更新 2：
主控 1 複製到主控 2 但主控 2 在顯示從屬狀態\G 中沒有復製到主控 1
Slave_IO_Running: Yes Slave_SQL_Running: Yes ERROR: No query specified show grants for 'database_name'@'10.0.0.1'; +--------------------------------------------------------------------------------------------------------------------------------------+ | Grants for database_name@10.0.0.1 | +--------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'database_name'@'10.0.0.1' IDENTIFIED BY PASSWORD '*17E4C8685DDE5BE5230C4357C1AFD9674EC6A3B0' | +--------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
如果您想安全地跳過重複鍵錯誤，請將其添加到兩個 Master 上的 my.cnf
[mysqld] slave-skip-errors=1062並重新啟動mysql
請永遠不要跑
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1000;因為這可能會跳過 Slave 應該擁有的真實數據。
更新 2014-06-16 20:51 EDT
請查看您正在複製的查詢
UPDATE dle_post SET comm_num=comm_num-1 where id='353'如果為 0，則
comm_num不能遞減。最小值為 0。因此，不允許從 a中減去。也不允許儲存負數。這解釋了錯誤。0``BIGINT UNSIGNED``BIGINT UNSIGNED``1``0更新 2014-06-16 21:51 EDT
您可以通過將它們設置為 1 萬億來修復所有這些零 comm_nums
UPDATE dle_post SET comm_num = 1000000000000 where comm_num = 0;這只是我的建議。您可以將其設置為您認為合適的任何值。
你應該仔細尋找這些情況。