Mysql

主-主複製重複密鑰

  • June 17, 2014

我已經設置了兩台伺服器主-主複製 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=7306

mysql> 顯示從屬狀態\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=7306

mysql> 顯示從屬狀態\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;

這只是我的建議。您可以將其設置為您認為合適的任何值。

你應該仔細尋找這些情況。

引用自:https://dba.stackexchange.com/questions/68317