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: ``` 如果觉得我的文章对您有用,请随意赞赏。您的支持将鼓励我继续创作! 赞赏支持