Ubunt16.04通过xtabackup备份后重建从库

序

这篇文章完整讲述Ubuntu16.04环境下如何安装xtrabackup，然后利用innobackupex进行备份，再搭建从库；

安装Xtrabackup

官网下载地址：https://www.percona.com/downloads/XtraBackup/LATEST/

建议不要使用apt-get install xtrabackup安装，可能你机器上的源导致安装的版本较低。

官网安装指引：https://www.percona.com/doc/percona-xtrabackup/LATEST/installation/apt_repo.html

官网安装说的比较清晰，我这里不细说，大概写下我安装的流程：

root@xx:/data/tmp#lsb_release -sc xenial root@xx:/data/tmp#wget https://repo.percona.com/apt/percona-release_0.1-4.xenial_all.deb root@xx:/data/tmp#dpkg -i percona-release_0.1-4.xenial_all.deb root@xx:/data/tmp#vi /etc/apt/sources.list root@xx:/data/tmp#dpkg -i percona-release_0.1-4.xenial_all.deb root@xx:/data/tmp#apt-get update root@xx:/data/tmp#apt-get install percona-xtrabackup-24 root@xx:/data/tmp# innobackupex -version innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7) root@xx:/data/tmp#innobackupex --help Open source backup tool for InnoDB and XtraDB Copyright (C) 2009-2015 Percona LLC and/or its affiliates. Portions Copyright (C) 2000, 2011, MySQL AB & Innobase Oy. All Rights Reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation version 2 of the License. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more details. You can download full text of the license on http://www.gnu.org/licenses/gpl-2.0.txt innobackupex - Non-blocking backup tool for InnoDB, XtraDB and HailDB databases

备份主库

主库全库备份

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user root --socket=/data/log/mysql.sock --password 123456  /data/xtrabackup/ &  备份完成后，会在/data/xtrabackup/目录下生成一个带日期带文件夹

主库备份事务提交

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user root --socket=/data/log/mysql.sock --password 123456 --apply-log  /data/xtrabackup/2017-08-01_10-47-22/ & ##preparing，undo撤销未提交的事务，重放redo log

（如果只想备份一个库，加“--include=navy”指定库名即可， “--databases=navy”不好使，被坑过。）

传输备份文件

可以使用SCP方式传输过去，我的备份较大，打包后传输到要搭建从库的机器的。

rsync -av 172.28.29.152::xtrabackup/2017-08-01_10-47-22/ /data/mysql/ &

或者scp -r -P

从库搭建

启动从库

将scp传输过来的文件，拷贝到mysql的数据目录，比如我的是/data1/mysql/data/下。将文件名改名。由于备份的时候文件名是以日期格式命名的，我们实际应用中是我是以实例端口号命名的，所以我改成了3308:

mv 2017-07-04_10-20-55 3308

拷贝原来的my.cnf文件，放到你启动的目录下，我是放到数据目录下的。

cp ./3308_bak/my.cnf ./3308/(3308_bak/my.cnf是我原来使用的配置文件，这里直接拷贝无需做配置修改)

启动从库：

/usr/local/mysql/bin/mysqld_safe --defaults-file=/data1/mysql/data/3308/my.cnf --user=mysql &

登陆应该没问题了；

搭建GTID的主从复制

innobackupex备份完成后，备份目录有一个xtrabackup_info的文件记录了主库的一些基本信息，包括GTID信息：

root@xx:/data/tmp/xx/2017-08-01_10-47-22# cat xtrabackup_info uuid = 0e2c0188-7664-11e7-8677-1418773c7c83 name = tool_name = innobackupex tool_command = --defaults-file=/$yourpath/my.cnf --user=root --password=... --socket=/$path/mysql.sock /yourpath/ tool_version = 2.4.8 ibbackup_version = 2.4.8 server_version = 5.7.17-log start_time = 2017-08-01 10:47:22 end_time = 2017-08-01 10:49:36 lock_time = 0 binlog_pos = filename 'mysql-bin.000096', position '665480224', GTID of the last change '2199b808-1dc9-11e7-97a0-1418773c7c83:1-980313' innodb_from_lsn = 0 innodb_to_lsn = 333932354788 partial = N incremental = N format = file compact = N compressed = N encrypted = N

登陆从库，开始重建主从GTID复制

mysql> show master status; +------------------+----------+--------------+------------------+-----------------------------------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                             | +------------------+----------+--------------+------------------+-----------------------------------------------+ | mysql-bin.000001 |      154 |              |                  | 2199b808-1dc9-11e7-97a0-1418773c7c83:1-939574 | +------------------+----------+--------------+------------------+-----------------------------------------------+ 1 row in set (0.00 sec) #查看slave已执行的gtid是否为空，为空，需要检查。备份还原是否有误。如果无误，需要执行reset MASTER;进行情况，否则无法设置gtid。 mysql> reset master; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 |      154 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; Query OK, 0 rows affected (0.00 sec) mysql> SET @@SESSION.SQL_LOG_BIN= 0; Query OK, 0 rows affected (0.00 sec) mysql> SET @@GLOBAL.GTID_PURGED='2199b808-1dc9-11e7-97a0-1418773c7c83:1-980313'; Query OK, 0 rows affected (0.00 sec) mysql> change master to master_host='192.xx.xx.xx',master_port=3308,master_user='rep_user',master_password='xxxxxx',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: Waiting for master to send event                   Master_Host: 192.xx.xx.xx                   Master_User: rep_user                   Master_Port: 3308                 Connect_Retry: 60               Master_Log_File: mysql-bin.000096           Read_Master_Log_Pos: 665522338                Relay_Log_File: relay-bin.000002                 Relay_Log_Pos: 42528         Relay_Master_Log_File: mysql-bin.000096              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB: ....