Mysql
MySql 複製失敗
我有一個 mysql（版本 14.14 Distrib 5.7.29，適用於 Linux (x86_64)）主 -> 輔助複製設置。最初複製工作成功，直到我們不得不重新啟動主數據庫。從那時起，複製就失敗了。我們曾嘗試重新啟用複制幾次，但幾分鐘後複製再次失敗。
my.cnf 文件 -> 主要
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M log-bin=mysql-bin server-id=1 datadir=/mysqldb/datadir/mysql socket=/mysqldb/datadir/mysql/mysql.sock log-error=/var/log/mysql/mysql_error.log log-syslog=ON max_connections=1000 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [client] port=3306 socket=/mysqldb/datadir/mysql/mysql.sockmy.cnf 文件 -> 輔助
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M server-id=2 datadir=/mysqldb/datadir/mysql socket=/mysqldb/datadir/mysql/mysql.sock log-error=/var/log/mysql/mysql_error.log log-syslog=ON max_connections=1000 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [client] port=3306 socket=/mysqldb/datadir/mysql/mysql.sock顯示狀態 slave\G -> 次要的
Slave_IO_State: Waiting for master to send event Master_Host: <ip-address> Master_User: <username> Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 336473455 Relay_Log_File: avt-mysql-sc-prod-2-relay-bin.000002 Relay_Log_Pos: 187164 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1032 Last_Error: Could not execute Delete_rows event on table store_metadata.access_token; Can't find record in 'access_token', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000003, end_log_pos 336136737 Skip_Counter: 0 Exec_Master_Log_Pos: 336136333 Relay_Log_Space: 524507 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: 1032 Last_SQL_Error: Could not execute Delete_rows event on table store_metadata.access_token; Can't find record in 'access_token', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000003, end_log_pos 336136737 Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 7c367a1b-de48-11e9-a49d-42010a164351 Master_Info_File: /mysqldb/datadir/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 200122 17:37:02 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)然後我們嘗試通過以下方式重新啟用複制：
stop slave; CHANGE MASTER TO MASTER_HOST='<ip-address>', MASTER_USER='<username>', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=336562088; start slave;在哪裡
MASTER_LOG_POS=;來自
show master status\G File: mysql-bin.000003 Position: 336562088幾分鐘後複製失敗 error_log -> 輔助
2020-01-22T17:32:45.562685Z 1524 [Note] Slave I/O thread for channel '': connected to master 'replica1@<ip-address>:3306',replication started in log 'mysql-bin.000003' at position 335949489 2020-01-22T17:37:02.294105Z 1525 [ERROR] Slave SQL for channel '': Could not execute Delete_rows event on table store_metadata.access_token; Can't find record in 'access_token', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000003, end_log_pos 336136737, Error_code: 1032 2020-01-22T17:37:02.294180Z 1525 [Warning] Slave: Can't find record in 'access_token' Error_code: 1032 2020-01-22T17:37:02.294193Z 1525 [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 336136333.我還注意到主數據庫上出現以下連接錯誤。但是，一旦複製失敗，就會發生這種情況。或者可能是連接終止，因此複製失敗。
mysqld.log -> 主
2020-01-22T21:10:13.065441Z 697 [Note] Start binlog_dump to master_thread_id(697) slave_server(2), pos(mysql-bin.000003, 347432570) 2020-01-22T21:53:31.113366Z 678 [Note] Aborted connection 678 to db: 'store_metadata' user: 'avtadmin' host: '<hostname>-f6297368e00118f33f8e74e464.interna' (Got an error reading communication packets)附加測試資訊
在奴隸
stop slave; drop database; create database;在大師
flush tables with read lock; show master status\G mysqldump -h <ip-address> --single-transaction --max_allowed_packet=1024m --databases store_metadata> /tmp/mysqlsnapshot-replication.sql unlock tables;在奴隸
mysql -h<ip-address> -u <username> -p store_metadata < "/tmp/mysqlsnapshot-replication.sql" show databases; use store_metadata; show tables;啟用複制
CHANGE MASTER TO MASTER_HOST=‘<ip-address>’, MASTER_USER=‘<username>’, MASTER_PASSWORD=‘<password>’, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=364656552; start slave; show slave status\G tail -f /var/log/mysqld.log在大師
show master status\G tail -f /var/log/mysqld.log最初一切正常。然後我將一個新數據庫添加到主數據庫，但該數據庫從未復製到輔助數據庫。然後我刪除了主數據庫上的新數據庫，並再次破壞了複製，錯誤是輔助數據庫上不存在新數據庫。對此有什麼想法嗎？我應該重新啟動主數據庫以便創建新的二進制日誌嗎？
謝謝你。
基本上，這意味著您的從站現在與主站已過時，並且在從站完全同步之前，它將一直沒有數據。您可能必須從頭開始一個新的從屬伺服器，並將其與主伺服器的目前狀態同步。
之前已經在這裡回答過：
https://forums.mysql.com/read.php?26,367180,367785#msg-367785
除非您進行了某種更改，否則在重新啟動 Slave 時無需採取任何措施——不
CHANGE MASTER，不RESET。最多，您可能需要START SLAVE.你的設置似乎被水洗了。所以從頭開始重建奴隸。
CHANGE MASTER然後用很多參數做一個單一的初始化它。