Xtrabackup
yum安装Xtrabackup
- 从percona官网查找可用yum源。
https://www.percona.com/doc/percona-xtrabackup/2.2/installation/yum_repo.html
- 安装yum
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
- 安装Xtrabackup和压缩支持
yum install -y percona-xtrabackup-24
yum install -y qpress
- 如果安装中出现缺少libev的问题，是因为epel源的问题。在rpmfind上找到对应版本下载安装即可
http://rpmfind.net/linux/rpm2html/search.php
比如centos7查找
libev-4.15-3.el7.x86_64.rpm
- 如果没有使用yum源从percona官方安装Xtrabackup，则qpress支持可以通过源码编译进行
wget http://www.quicklz.com/qpress-11-source.zip
unzip qpress-11-source
make
cp qpress /usr/local/bin
编译的过程中可能出现(mac必定出现):
qpress.cpp:1039:18: error: use of undeclared identifier 'isatty'
tty_stderr = isatty(fileno(stderr));
^
此时在qpress.h或者qpress.cpp文件中引入unistd.h再次编译:
#include <unistd.h>
Xtrabackup常用参数
--defaults-file
mysql配置文件路径，需要放在第一个参数，优先于其他所有参数
--apply-log
生成一致性快照。如果不指定defaults-file参数，会从backup-my.cnf读取Innodb的相关配置
--redo-only
准备数据，合并事务，但是跳过回滚的事务(skip rollback)。
--copy-back
使用复制的方法恢复数据。
--move-back
使用移动的方法恢复数据(推荐)
--slave-info
备份的服务器是从服务器的时候使用。他会将主服务器信息写入备份记录并且记录change master命令。
--incremental
表示创建的是增量备份。需要制定--incremental-basedir命令告知基础备份的数据位置。
--incremental-basedir
增量备份基础备份位置。
--no-lock
关闭FLUSH TABLES WITH READ LOCK命令。不锁表。
--no-timestamp
不建立时间戳命名的子文件夹，直接备份。
--no-backup-locks
不进行FLUSH TABLES WITH READ LOCK操作。
--decompress
解压缩
--user=name
指定mysql用户名
--host=name
tcp模式下指定远程主机ip或者域名
--port=
tcp模式下指定远程主机端口
--password=name'
mysql密码
--socket=name
mysql的socket连接符文件
--databases=name
指定Xtrabackup需要备份的数据库或者表。
--databases=node_blog novaskii.test novaskii.hels
--compress
压缩备份文件
--compress-threads=
压缩备份线程数量
--parallel=
备份时候使用的线程数
--tables-file=name
要备份的表文件列表。
--tmpdir=name
备份过程中临时文件保存目录
--use-memory=
指定备份最大可用的内存。(e.g. 1MB, 1GB)
使用Xtrabackup进行全库备份
备份
innobackupex --defaults-file=配置文件 --no-timestamp --socket=Socket文件 备份目标目录 --parallel=备份线程数 --use-memory=使用内存 --user=用户名 --password='密码'
innobackupex --defaults-file=my.cnf --no-timestamp --socket=my3306.sock /Users/fangyuan/mysql/3306_bak --parallel=2 --use-memory=512MB --user=root --password='password'
恢复
在恢复之前，首先要停掉现有的mysql，并且做好备份工作。然后合并apply log，恢复文件。
备份完成的目录如下:
fangdeMac:3306_bak fangyuan$ ls -al
total 204848
drwxr-xr-x 15 fangyuan staff 510 8 22 20:13 .
drwxr-xr-x 5 fangyuan staff 170 8 22 20:10 ..
-rw-r----- 1 fangyuan staff 434 8 22 20:13 backup-my.cnf
drwxr-x--- 19 fangyuan staff 646 8 22 20:12 cloak_server
-rw-r----- 1 fangyuan staff 678 8 22 20:13 ib_buffer_pool
-rw-r----- 1 fangyuan staff 104857600 8 22 20:12 ibdata1
drwxr-x--- 77 fangyuan staff 2618 8 22 20:12 mysql
drwxr-x--- 90 fangyuan staff 3060 8 22 20:13 performance_schema
drwxr-x--- 3 fangyuan staff 102 8 22 20:13 student
drwxr-x--- 23 fangyuan staff 782 8 22 20:13 student_db
drwxr-x--- 108 fangyuan staff 3672 8 22 20:13 sys
-rw-r----- 1 fangyuan staff 61 8 22 20:13 xtrabackup_binlog_info
-rw-r----- 1 fangyuan staff 113 8 22 20:13 xtrabackup_checkpoints
-rw-r----- 1 fangyuan staff 631 8 22 20:13 xtrabackup_info
-rw-r----- 1 fangyuan staff 2560 8 22 20:13 xtrabackup_logfile
fangdeMac:3306_bak fangyuan$
首先要合并apply log:
注意检查备份出来的配置文件的完整性
innobackupex --defaults-file=备份出来的配置文件 --apply-log 备份文件目录
innobackupex --defaults-file=backup-my.cnf --apply-log ./
关闭要恢复的服务器:
mysqladmin --defaults-file=3306/my.cnf -S 3306/my3306.sock -u root -p shutdown
恢复数据:
恢复数据有两种方式，复制回去和移动回去，一般建议使用移动回去的方法减少磁盘读写并且速度会比较快，备份数据可以自己在其他时候去复制保存。
注意恢复文件的data目录必须为空
移动：
innobackupex --defaults-file=my.cnf --move-back /Users/fangyuan/mysql/3306_bak/
复制：
innobackupex --defaults-file=my.cnf --move-back /Users/fangyuan/mysql/3306_bak/
重建目录:
fangdeMac:3306 fangyuan$ ls ./../3306_or/
binlog data errlog genlog my.cnf relaylog slowlog tmp
fangdeMac:3306 fangyuan$ ls
data
fangdeMac:3306 fangyuan$ mkdir binlog
fangdeMac:3306 fangyuan$ mkdir errlog
fangdeMac:3306 fangyuan$ mkdir genlog
fangdeMac:3306 fangyuan$ mkdir relaylog
fangdeMac:3306 fangyuan$ mkdir slowlog
fangdeMac:3306 fangyuan$ mkdir tmp
重新启动mysql:
mysqld --defaults-file=my.cnf &
使用Xtrabackup进行增量备份
有些时候我们不能定期做完整的数据库备份，所以会采用全备份+增量备份互补的方法进行。
备份
在进行增量备份之前，需要完成一次全备份。全备份过程略。
我们将第一次全备份的文件备份到文件夹3306bakfull。
创建增量备份1:
innobackupex --defaults-file=my.cnf --socket=my3306.sock --no-timestamp --incremental /Users/fangyuan/mysql/3306_bak_incr01 --incremental-basedir=/Users/fangyuan/mysql/3306_bak_full --user=root --password='password'
创建增量备份2:
innobackupex --defaults-file=my.cnf --socket=my3306.sock --no-timestamp --incremental /Users/fangyuan/mysql/3306_bak_incr02 --incremental-basedir=/Users/fangyuan/mysql/3306_bak_incr01 --user=root --password='password'
恢复
同样的，先停掉服务器。
然后从q备份开始全，逐个进行apply-log，直到合并到需要恢复的增量备份为止。注意此时不要进行提交事务的回滚(redo only)
恢复全备份
innobackupex --apply-log --redo-only ./
恢复增量备份1
innobackupex --apply-log --redo-only ./ --incremental-dir=./../3306_bak_incr01
恢复增量备份2
innobackupex --apply-log --redo-only ./ --incremental-dir=./../3306_bak_incr02
再次备份全备份，提交事务的回滚
innobackupex --defaults-file=my.cnf --apply-log ./
创建目录
mkdir binlog
mkdir errlog
mkdir data
mkdir genlog
mkdir relaylog
mkdir tmp
mkdir slowlog
恢复数据
innobackupex --defaults-file=my.cnf --move-back /Users/fangyuan/mysql/3306_bak_full/
启动mysql
使用Xtrabackup压缩备份
Xtrabackup的compress参数压缩的数据需要使用decompress参数解压
decompress参数依赖于qpress这个工具
备份
备份就是使用了compress参数来实现压缩。而使用compress-threads来实现多线程压缩。
innobackupex --defaults-file=my.cnf --compress --compress-threads=2 --no-timestamp --socket=my3306.sock --user=root --password='password' --parallel=2 --use-memory=512MB ./../compress_bak/
恢复
解压缩使用decompress参数，同样提供了decompress-threads进行多线程的解压。
解压缩文件
innobackupex --decompress --decompress-threads=2 ./
生成一致性快照
innobackupex --defaults-file=./../3306/my.cnf --apply-log ./
恢复数据库
mkdir binlog
mkdir errlog
mkdir data
mkdir genlog
mkdir relaylog
mkdir tmp
mkdir slowlog
恢复数据
innobackupex --defaults-file=my.cnf --move-back /Users/fangyuan/mysql/compress_bak/
Xtrabackup快速建立主从
利用Xtrabackup可以在需要的时候快速将数据库部署到其他服务器上建立主从关系或者将某个数据库单独分离出来放置在别的服务器上使用。
为了启用GTID同步，主库和从库配置中都必须包含以下内容:
gtid_mode=ON
enforce-gtid-consistency
如果是mysql5.6而非mysql5.7，还需要开启如下配置:
log-slave-updates
Master Database:
检查主从复制模式:
show variables like '%gtid%'
创建同步用户:
grant replication slave on *.* to repluser@'172.4.17.%' identified by 'password';
刷新权限:
flush privileges;
备份Mysql:
innobackupex --defaults-file=cnf/3306.cnf --socket=3306/my3306.sock --user=root --no-timestamp back/3306/
压缩并传输到远程服务器:
tar zcvf master.tar.gz back
scp master.tar.gz root@172.4.17.129:~
Slave Server:
恢复Mysql:
innobackupex --defaults-file=/mysql/cnf/3306.cnf --apply-log ./3306
innobackupex --defaults-file=/mysql/cnf/3306.cnf --move-back ./
mysqld --defaults-file=/mysql/cnf/3306.cnf &
进入Mysql执行主从关系:
设置主服务器
change master to master_host="172.4.17.144",master_port=3306,master_user='repluser',master_password='password',master_auto_position=1;
跳过已经执行过的gtid（注意在使用之前gtid_executed必须为空，否则不能执行）
SET @@GLOBAL.GTID_PURGED='f8055cc6-69aa-11e6-83e7-00163e2c627f:1-3';
启动从库
start slave;
检查从库状态
show slave status\G;
检查从库状态结果:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.4.17.144
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: 3306-mysql-bin.000003
Read_Master_Log_Pos: 1768
Relay_Log_File: 3306-relay-bin.000002
Relay_Log_Pos: 1780
Relay_Master_Log_File: 3306-mysql-bin.000003
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: 1768
Relay_Log_Space: 1983
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: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 71493306
Master_UUID: 599aace9-6920-11e6-805f-00163e405856
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 the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 599aace9-6920-11e6-805f-00163e405856:1-3:5-8
Executed_Gtid_Set: 599aace9-6920-11e6-805f-00163e405856:1-8,
f8055cc6-69aa-11e6-83e7-00163e2c627f:1-3
Auto_Position: 1
1 row in set (0.00 sec)
此时已经搭建好了主从同步关系。
错误和解决
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.'
需要设置slave的gtidpurge属性，保证其知道master的gtidexecute值或开始同步的gtid数据
set global GTID_purged='927a597c-6cd2-11e6-b309-3274ad061969:1-3,fcf5cb46-6cce-11e6-a297-ccfa81956745:1-133:138';
ERROR 1840 (HY000): GTID_PURGED can only be set when GTID_EXECUTED is empty.
因为slave被启动过，已经从服务器同步了gtid_execute参数，所以此时不能使用purge参数设置，重置master即可
stop slave;
reset master;
线上机器损坏或者误操作的时候利用Xtrabackup和binlog恢复
先上机器进行误操作或者磁盘损坏的时候，需要进行Xtrabackup+binlog进行线上的数据恢复。
首先备份原始的数据库文件，防止恢复的时候数据库记录被清除,如果没有停机，则刷新binlog文件，flush logs。然后恢复Xtrabackup备份并启动mysql。
mysqladmin -u root -S my3306.sock shutdown
mv 3306 3306_bro
innobackupex --defaults-file=./../3306.cnf --apply-log ./
innobackupex --defaults-file=./../3306.cnf --move-back ./
mysqld --defaults-file=/mysql/cnf/3306.cnf &
进入mysql检查数据:
mysql> select * from test;
+----+----------+
| id | name |
+----+----------+
| 1 | fangyuan |
| 2 | xiaocan |
+----+----------+
2 rows in set (0.00 sec)
mysql>
此时可以发现数据是旧数据，我们需要从binlog中恢复数据。
检查xtrabackupbinlogpos_innodb中binlog所在文件和位置,确定恢复的起始位置。
[root@localhost 3306]# cat xtrabackup_binlog_pos_innodb
3306-mysql-bin.000003 1513
检查原始数据库binlog文件
[root@localhost 3306_bro]# ls
3306-mysql-bin.000001 auto.cnf ib_logfile0 node_blog
3306-mysql-bin.000002 error.log ib_logfile1 novaskii
3306-mysql-bin.000003 ib_buffer_pool ib_logfile2 performance_schema
3306-mysql-bin.index ibdata1 mysql xiaocan
[root@localhost 3306_bro]#
从这里我们可以看出来Xtrabackup的备份binlog到了000003的1513。所以我们从000003的1513这个binlog位置开始往后恢复，直到所有binlog恢复完毕或者恢复到所需要的binlog位置。
如果需要核查binlog，可以用mysqlbinlog导出binlog
导入binlog:
ysqlbinlog 3306-mysql-bin.000003 --set-charset=UTF8 --base64-output=decode-rows -v --start-position=1513 > output.log | mysql -u root -S /mysql/3306/my3306.sock
此时再检查mysql数据库可以发现数据已经恢复一致。