☰

my.cnf (主)

tag-icon • tag-icon mysql debian database-replication percona
my.cnf (主)

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通过以下方式获取第二个 uid

gtid=$(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

答案1

我在这里找到了解决方案 https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/setting_up_replication.html

我错过了xtrabackup --move-back命令

相关内容