Mysql
無法從 Percona 備份(從伺服器)恢復主/從複製
從
master
使用 Percona xtrabackup 創建的備份恢復從屬在主 binlog 上設置為在 14 天后過期,並且此備份不到 24 小時
my.cnf(主)
expire_logs_days = 14
恢復腳本(從屬)
service mysql stop rm -r /var/lib/mysql/* # backup is unzipped into /var/lib/mysql xtrabackup --prepare --target-dir=/var/lib/mysql chown -R mysql.mysql /var/lib/mysql service mysql start # get gtid line=$(head -n 1 "/var/lib/mysql/xtrabackup_binlog_info") gtid=$(echo $line | awk -F '[ ,]' '{print $3}') mysql -u root -pPASS -e "reset master; SET GLOBAL gtid_purged='$gtid'; CHANGE MASTER TO MASTER_HOST='$master_host', MASTER_USER='repl', MASTER_PASSWORD='$master_pass', MASTER_AUTO_POSITION=1; start slave" mysql -u root -pPASS -e "show slave status\G;"
地位
*************************** 1. row *************************** Slave_IO_State: Master_Host: server.com Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: rep-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: mydb Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 154 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID sets and the missing purged transactions are too long to print in this message. For more information, please see the master's error log or the manual for GTID_SUBTRACT.' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: fe51e8df-b7c4-11e9-be21-4061862b8d34 Master_Info_File: /mnt/HC_Volume_8355755/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 210211 10:50:32 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 3ccc2392-45ed-11e7-bc98-4061862b8d34:1-35942477, 94bb04d3-6c4e-11eb-8631-96000080ccbb:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
/var/lib/mysql/xtrabackup_binlog_info
mysql-bin.000025 217141189 3ccc2392-45ed-11e7-bc98-4061862b8d34:1-35942477, fe51e8df-b7c4-11e9-be21-4061862b8d34:1-25059775
gtid
從文件中提取的值:3ccc2392-45ed-11e7-bc98-4061862b8d34:1-35942477
更新
還嘗試
/var/lib/mysql/xtrabackup_binlog_info
通過執行來獲取第二個 uidgtid=$(tail -n 1 "$mysql_dir/xtrabackup_binlog_info")
沒有任何效果..同樣的錯誤
更新 2
show master status
在主返回3ccc2392-45ed-11e7-bc98-4061862b8d34:1-35942477, fe51e8df-b7c4-11e9-be21-4061862b8d34:1-25203198
我在這裡找到了解決方案 https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/setting_up_replication.html
我錯過了
xtrabackup --move-back
命令