MySQL5.7基于xtrabackup不停服搭建mysql主从

作者: sysit 分类: d 发表于 2019-11-14 129人围观

## 1. 场景说明

在一个基于GTID的MySQL主从模式中，主服务器因为一些原因宕机，造成MySQL主服务数据盘故障，无法恢复。将MySQL从服务器提升为主服务器之后，需要为该MySQL新建一台从服务器，但是原从服务器的binlog日志已经被删掉，因此搭建基于GTID的主从模式，无法设置master_position=1使得主从模式正常运行。

## 2. 工具
xtrabackup——该工具主要是用于不停服热备，并记录binlog以及replication相关信息。

## 3. 操作步骤
### 3.1 将MySQL从服务器提升为主服务器

* 检查master状态。

```
mysql> show master status\G;
*************************** 1. row ***************************
             File: node03-bin.000065
         Position: 516157809
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 54f3c2a4-6a52-11e9-9570-74a4b5008b02:1-1051894,
ad7e9c52-6a44-11e9-bce5-4ea055d3b6f3:1-129436176
1 row in set (0.00 sec)

```

* replication用户和权限

```
mysql> show grants for repl@'%';
+---------------------------------------------------------------------------------+
| Grants for repl@%                                                               |
+---------------------------------------------------------------------------------+
| GRANT RELOAD, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

### 3.2 准备一台全新的MySQL服务器

参见CentOS7.3 离线安装MySQL5.7，注意gtid的配置

### 3.3 利用xtrabackup将数据备份，并传输到从服务器

* 软件安装
```
wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.16/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.16-1.el7.x86_64.rpm

yum localinstall percona-xtrabackup-24-2.4.16-1.el7.x86_64.rpm 
```

* 备份全量数据

``` bash
innobackupex --defaults-file=/etc/my.cnf \
-S /var/lib/mysql/mysql.sock \
--user=root --password='password' \
--parallel=200 --throttle=2000 /data/mysqlbackup/
```

* 拷贝到从服务器

```
scp -r /data/mysqlbackup/2019-11-14_16-57-43 node02:/data/mysqlbackup/
```

### 3.4 从服务器恢复数据

* 正常情况下同步数据报错

```
mysql> change master to
    -> master_host='10.200.100.13',
    -> master_port=3306,
    -> master_user='repl',
    -> master_password='password',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.200.100.13
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: node02-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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.'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 12
                  Master_UUID: 54f3c2a4-6a52-11e9-9570-74a4b5008b02
             Master_Info_File: mysql.slave_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: 191114 17:09:02
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
```

* 恢复备份的数据

```
[root@node02 ~]# systemctl stop mysqld

#配置文件中的数据目录必须为空
[root@node02 ~]# rm -rf /var/lib/mysql/*
[root@node02 ~]# innobackupex --apply-log --use-memory=64G /data/mysqlbackup/2019-11-14_16-57-43/

[root@node02 ~]# innobackupex --defaults-file=/etc/my.cnf --move-back /data/mysqlbackup/2019-11-14_16-57-43/

[root@node02 ~]# chown -R mysql:mysql /var/lib/mysql
[root@node02 ~]# systemctl restart mysqld  
```

* 配置从节点

```
# 查询binlog_info

[root@node02 ~]# cat /data/mysqlbackup/2019-11-14_16-57-43/xtrabackup_binlog_info 
node03-bin.000065       521508352       54f3c2a4-6a52-11e9-9570-74a4b5008b02:1-1063376,
ad7e9c52-6a44-11e9-bce5-4ea055d3b6f3:1-129436176

# 54f3c2a4-6a52-11e9-9570-74a4b5008b02:1-1063376,ad7e9c52-6a44-11e9-bce5-4ea055d3b6f3:1-129436176就是需要purged的gtid，这个值也可能是只有一个。

# 配置slave

mysql> reset master；

mysql> set global gtid_purged='54f3c2a4-6a52-11e9-9570-74a4b5008b02:1-1063376,ad7e9c52-6a44-11e9-bce5-4ea055d3b6f3:1-129436176';

mysql> CHANGE MASTER TO MASTER_HOST='10.50.100.13', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_AUTO_POSITION=1;

mysql> start slave;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.50.100.13
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: node03-bin.000065
          Read_Master_Log_Pos: 531875937
               Relay_Log_File: node02-relay-bin.000003
                Relay_Log_Pos: 8237237
        Relay_Master_Log_File: node03-bin.000065
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 529930194
              Relay_Log_Space: 10183365
              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: 539
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 12
                  Master_UUID: 54f3c2a4-6a52-11e9-9570-74a4b5008b02
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Waiting for Slave Worker to release partition
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 54f3c2a4-6a52-11e9-9570-74a4b5008b02:1063377-1084358
            Executed_Gtid_Set: 54f3c2a4-6a52-11e9-9570-74a4b5008b02:1-1080494,
ad7e9c52-6a44-11e9-bce5-4ea055d3b6f3:1-129436176
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
```

如果觉得我的文章对您有用，请随意赞赏。您的支持将鼓励我继续创作！