mysql备份与恢复

mysql备份与恢复

1.XtraBackup安装
1.1.编译安装
1.1.1.安装依赖
[root@yzx ~]#yum -y install libaio-devel ncurses-devel bzip2-devel libxml2-devel libgcrypt-devel gcc* vim-* libcurl-devel python-sphinx curl perl-Digest-MD5
[root@yzx ~]# cd /opt/
1.1.2.下载boost
[root@yzx ~]#cd /home/mysql/xtrabackup/
[root@yzx ~]# wget http://jenkins.percona.com/downloads/boost/boost_1_59_0.tar.gz
1.1.3.下载安装libev
[root@yzx ~]#wget http://dist.schmorp.de/libev/libev-4.15.tar.gz
[root@yzx ~]# tar -zxf libev-4.15.tar.gz
[root@yzx ~]# cd libev-4.15
[root@yzx ~]# ./configure
[root@yzx ~]# make
[root@yzx ~]# make install
1.1.4.下载安装percona-xtrabackup
[root@yzx ~]#cd opt
[root@yzx opt]#wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.20/source/tarball/percona-xtrabackup-2.4.20.tar.gz
[root@yzx opt]# tar zxvf percona-xtrabackup-2.4.20.tar.gz
[root@yzx opt]# cdpercona-xtrabackup-2.4.20.tar.gz
[root@yzx percona-xtrabackup-2.4.20.tar.gz]# cmake -DBUILD_CONFIG=xtrabackup_release -DWITH_BOOST=/home/mysql/xtrabackup/
[root@yzx percona-xtrabackup-2.4.20.tar.gz]# make -j4
[root@yzx percona-xtrabackup-2.4.20.tar.gz]# make DESTDIR=/home/mysql/xtrabackup/ install #指定安装到/home/mysql/xtrabackup/
[root@yzx ~]# cd /home/mysql/
[root@yzx mysql]# chown -R mysql:mysql xtrabackup/
[root@yzx ~]#cd /home/mysql/xtrabackup/usr/local/xtrabackup/bin
[root@yzx ~]#cp -rp * /usr/bin
1.2.XtraBackup rpm包安装
[root@yzx ~]# cd /opt/
[root@yzx ~]# wget https://www.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.14/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm
[root@yzx opt]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
[root@yzx opt]# yum -y install rsync perl l perl-Digest-MD5
[root@yzx opt]# rpm -ivh percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm

参数说明:
–apply-log-only：prepare备份的时候只执行redo阶段，用于增量备份。
–backup：创建备份并且放入–targent-dir目录中
–close-files：不保持文件打开状态，xtrabackup打开表空间的时候通常不会关闭文件句柄，目的是为了正确处理DDL操作。如果表空间数量非常巨大并且不适合任何限制，一旦文件不在被访问的时候这个选项可以关闭文件句柄.打开这个选项会产生不一致的备份。
–compact：创建一份没有辅助索引的紧凑备份
–compress：压缩所有输出数据，包括事务日志文件和元数据文件，通过指定的压缩算法，目前唯一支持的算法是quicklz.结果文件是qpress归档格式，每个xtrabackup创建的*.qp文件都可以通过qpress程序提取或者解压缩
–compress-chunk-size=#：压缩线程工作buffer的字节大小，默认是64K
–compress-threads=#：xtrabackup进行并行数据压缩时的worker线程的数量，该选项默认值是1，并行压缩（‘compress-threads’）可以和并行文件拷贝(‘parallel’)一起使用。例如:'–parallel=4 --compress --compress-threads=2’会创建4个IO线程读取数据并通过管道传送给2个压缩线程。
–create-ib-logfile：这个选项目前还没有实现，目前创建Innodb事务日志，你还是需要prepare两次。
–datadir=DIRECTORY：backup的源目录，mysql实例的数据目录。从my.cnf中读取，或者命令行指定。
–defaults-extra-file=[MY.CNF]：在global files文件之后读取，必须在命令行的第一选项位置指定。
–defaults-file=[MY.CNF]：唯一从给定文件读取默认选项，必须是个真实文件，必须在命令行第一个选项位置指定。
–defaults-group=GROUP-NAME：从配置文件读取的组，innobakcupex多个实例部署时使用。
–export：为导出的表创建必要的文件
–extra-lsndir=DIRECTORY：(for --bakcup):在指定目录创建一份xtrabakcup_checkpoints文件的额外的备份。
–incremental-basedir=DIRECTORY：创建一份增量备份时，这个目录是增量别分的一份包含了full bakcup的Base数据集。
–incremental-dir=DIRECTORY：prepare增量备份的时候，增量备份在DIRECTORY结合full backup创建出一份新的full backup。
–incremental-force-scan：创建一份增量备份时，强制扫描所有增在备份中的数据页即使完全改变的page bitmap数据可用。
–incremetal-lsn=LSN：创建增量备份的时候指定lsn。
–innodb-log-arch-dir：指定包含归档日志的目录。只能和xtrabackup --prepare选项一起使用。
–innodb-miscellaneous：从My.cnf文件读取的一组Innodb选项。以便xtrabackup以同样的配置启动内置的Innodb。通常不需要显示指定。
–log-copy-interval=#：这个选项指定了log拷贝线程check的时间间隔（默认1秒）。
–log-stream：xtrabakcup不拷贝数据文件，将事务日志内容重定向到标准输出直到–suspend-at-end文件被删除。这个选项自动开启–suspend-at-end。
–no-defaults：不从任何选项文件中读取任何默认选项,必须在命令行第一个选项。
–databases=#：指定了需要备份的数据库和表。
–database-file=#：指定包含数据库和表的文件格式为databasename1.tablename1为一个元素，一个元素一行。
–parallel=#：指定备份时拷贝多个数据文件并发的进程数，默认值为1。
–prepare：xtrabackup在一份通过–backup生成的备份执行还原操作，以便准备使用。
–print-default：打印程序参数列表并退出，必须放在命令行首位。
–print-param：使xtrabackup打印参数用来将数据文件拷贝到datadir并还原它们。
–rebuild_indexes：在apply事务日志之后重建innodb辅助索引，只有和–prepare一起才生效。
–rebuild_threads=#：在紧凑备份重建辅助索引的线程数，只有和–prepare和rebuild-index一起才生效。
–stats：xtrabakcup扫描指定数据文件并打印出索引统计。
–stream=name：将所有备份文件以指定格式流向标准输出，目前支持的格式有xbstream和tar。
–suspend-at-end：使xtrabackup在–target-dir目录中生成xtrabakcup_suspended文件。在拷贝数据文件之后xtrabackup不是退出而是继续拷贝日志文件并且等待知道xtrabakcup_suspended文件被删除。这项可以使xtrabackup和其他程序协同工作。
–tables=name：正则表达式匹配database.tablename。备份匹配的表。
–tables-file=name：指定文件，一个表名一行。
–target-dir=DIRECTORY：指定backup的目的地，如果目录不存在，xtrabakcup会创建。如果目录存在且为空则成功。不会覆盖已存在的文件。
–throttle=#：指定每秒操作读写对的数量。
–tmpdir=name：当使用–print-param指定的时候打印出正确的tmpdir参数。
–to-archived-lsn=LSN：指定prepare备份时apply事务日志的LSN，只能和xtarbackup --prepare选项一起用。
–user-memory = #：通过–prepare prepare备份时候分配多大内存，目的像innodb_buffer_pool_size。默认值100M如果你有足够大的内存。1-2G是推荐值，支持各种单位(1MB,1M,1GB,1G)。
–version：打印xtrabackup版本并退出。
–xbstream：支持同时压缩和流式化。需要客服传统归档tar,cpio和其他不允许动态streaming生成的文件的限制，例如动态压缩文件，xbstream超越其他传统流式/归档格式的的优点是，并发stream多个文件并且更紧凑的数据存储（所以可以和–parallel选项选项一起使用xbstream格式进行streaming）。

1.3.备份用户授权
授权限给’xtrabackup’用户进行备份，如下命令创建此类用户：
[root@yzx opt]# su - mysql
[mysql@yzx ~]$ mysql -uroot -p
mysql> CREATE USER ‘xtrabackup’@‘localhost’ IDENTIFIED BY ‘123456’;　　#创建用户
mysql> GRANT SELECT, INSERT, CREATE, RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT ON . TO ‘xtrabackup’@‘localhost’ ;　　#授权刷新、锁定表、用户查看服务器状态
mysql> FLUSH PRIVILEGES;　　#刷新授权表

2.全量备份
2.1.全量备份
[mysql@yzx ~]$innobackupex --defaults-file=/home/mysql/my.cnf --user xtrabackup --password 123456 --no-timestamp /home/backup/week1/sun >/home/backup/week1/sun.txt 2>&1
2.2.准备数据校验环境

[mysql@yzx ~]$ cd /home/mysql/data/
[mysql@yzx data]$ rm -rf *

3.全量数据恢复
3.1.恢复一致性
[mysql@yzx ~]$ innobackupex --defaults-file=/home/mysql/my.cnf --user root --use-memory=4G --apply-log --redo-only /home/backup/week1/sun #回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
3.2.恢复data
[mysql@yzx ~]$ innobackupex --defaults-file=/home/mysql/my.cnf --user root --use-memory=4G --copy-back /home/backup/week1/sun #将恢复的数据拷贝回data
3.3.文件授权
[mysql@yzx ~]$ cd /home/mysql/data/
[mysql@yzx data]$ chown mysql.mysql -R * && chmod 770 -R * #数据文件授权
3.4.binlog回滚
[mysql@yzx ~]$ cd /home/backup/week1/sun #开始对全备后的操作进行recovery操作
[mysql@yzx sun]$ cat xtrabackup_info #查看对应binlog日志和position_id

[mysql@yzx sun]$ time /home/mysql/bin/mysqlbinlog /home/mysql/mysql-bin.000001 --start-position=1741 --stop-datetime=‘2020-09-26 02:30:00’|/home/mysql/bin/mysql -u root -p #通过mysql-bin.000001从posid:1741开始恢复至指定的时间点(2020-09-26 02:30:00)
[mysql@yzx data]$ cd /home/mysql
3.5.数据校验
[mysql@yzx ~]$ sh stop.sh #重启mysql
[mysql@yzx ~]$ sh start.sh

数据全量恢复完成！

4.增量备份
4.1.增量备份
[mysql@yzx ~]$ innobackupex --defaults-file=/home/mysql/my.cnf --user xtrabackup --password 123456 --no-timestamp --incremental-basedir=/home/backup/week1/sun --incremental /home/backup/week1/1 >/home/backup/week1/1.txt 2>&1
4.2.准备数据校验环境
备份后插入一条数据，最后校验通过binlog日志恢复到指定时间点

[mysql@yzx ~]$ cd /home/mysql/data/ && rm -rf *

5.增量数据恢复
开始基于全量+增量+binlog日志还原数据库：
5.1.恢复全量备份一致性
[mysql@yzx ~]$ innobackupex --defaults-file=/home/mysql/my.cnf --user root --use-memory=4G --apply-log --redo-only /home/backup/week1/sun
5.2.追加增量备份
[mysql@yzx ~]$ innobackupex --defaults-file=/home/mysql/my.cnf --user root --apply-log -use-memory=4G /home/backup/week1/sun --incremental-dir=/home/backup/week1/1
5.3.恢复增量备份一致性
[mysql@yzx ~]$ innobackupex --defaults-file=/home/mysql/my.cnf --user root --apply-log --use-memory=4G /home/backup/week1/sun
5.4.清空数据文件
[mysql@yzx ~]$ cd /home/mysql/data/ && rm -rf *
5.5.还原数据文件
[mysql@yzx ~]$ innobackupex --defaults-file=/home/mysql/my.cnf --user root --use-memory=4G --copy-back /home/backup/week1/sun
5.6.文件授权
[mysql@yzx ~]$ cd /home/mysql/data/ && chown mysql.mysql -R * && chmod 770 -R *
5.7.binlog回滚
[mysql@yzx ~]$ cat /home/backup/week1/sun/xtrabackup_info #查看对应binlog日志和position_id

[mysql@yzx ~]$ time /home/mysql/bin/mysqlbinlog /home/backup/mysql-bin.000001 --start-position=154 --stop-datetime=‘2020-09-28 12:30:00’|/home/mysql/bin/mysql -u root -p #基于binlog实现redolog的recovery操作

5.8.数据校验
[mysql@yzx data]$ cd /home/mysql
[mysql@yzx ~]$ sh stop.sh #重启mysql
[mysql@yzx ~]$ sh start.sh

恢复成功，上图‘wangwu’是增量备份以后新增的数据，通过binlog实现了恢复数据库到指定时间点！
6.异机binlog恢复
异机binlog恢复前提条件是：两个数据库关系为主从关系，主库有binlog日志，没备份，从库有备份但是缺失从库的binlog日志文件。找到从库备份后的position_id，然后在从主库的binlog日志中开始恢复数据。
这个方法对一般很简单的流线型业务逻辑可以和快的找到对应position_id执行恢复。对业务逻辑复杂和dml频繁并有大量的回滚操作的，因为一个事务的dml操作会涉及到前后很多值的改变，要配合业务逻辑很熟悉的开发才能找到对应的position_id。
6.1.数据恢复增量
数据恢复至增量，查看增备时记录的position_id和binlog end_time
[root@yzx backup]#cat /home/backup/week1/sun/xtrabackup_info

6.2.解析异机binlog日志
[root@yzx backup]# /home/mysql/bin/mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -vv /home/backup/mysql-bin.000001 > 001.sql
6.3.查找end_time
在001.sql 查找end_time临近的position_id

6.4.开始执行恢复
从at 开始实验下面的命令
[root@yzx backup]# time /home/mysql/bin/mysqlbinlog /home/backup/mysql-bin.000001 --start-position=290 --stop-datetime=‘2020-09-27 14:30:00’ |/home/mysql/bin/mysql -u root -p’pwd’

###出现下面错误时候：
ERROR 1062 (23000) at line 2521: Duplicate entry ‘zhangsan’ for key ‘PRIMARY’
在 001.sql 里搜索到相关的 posation 比如是：xxxxxxx 继续上面的重复工作即可

[root@yzx backup]# time /home/mysql/bin/mysqlbinlog /home/full/mysqlbin-log.006921 --start-position=xxxx --stop-datetime=‘2020-09-17 02:30:00’ |/home/mysql/bin/mysql -u root -p’pwd’

###中间可能出现冲突ID 按上面的方式找到记录 找到这个记录insert 后的 postion 继续即可，
7.Binlog2sql数据回退闪回
7.1.安装git
yum install git
7.2.获取binlog2sql原码
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
7.3.安装pip
wget https://bootstrap.pypa.io/get-pip.py
python get-pip.py
7.4.安装binlog2sql依赖
pip install -r requirements.txt
7.5.用户授权
CREATE USER ‘root’@‘127.0.0.1’ IDENTIFIED BY ‘pwd’;
GRANT ALL PRIVILEGES ON . TO ‘root’@‘127.0.0.1’ WITH GRANT OPTION;
7.6.解析binlog日志
注意：此命令需要在mysqlbin-log.000008同级目录下执行
python /home/binlog2sql/binlog2sql/binlog2sql.py -h127.0.0.1 -uroot -p"" -P3306 --start-file mysqlbin-log.000008 --start-datetime “2020-09-27 12:08:12” --stop-datetime “2020-09-27 12:30:00” > /home/log2sql/mysqlbin-log.000008.sql
执行完后得到了从mysqlbin-log.000098解析出来的反向sql语句 /home/log2sql/mysqlbin-log.000008.sql
7.7.执行方向sql恢复数据
登入mysql后source /home/log2sql/mysqlbin-log.000008.sql 开始执行数据回退。
8.总结
xtrabackup每次做完恢复后必须要执行一次全量备份。
Binlog2sql数据闪回方式在针对闪回时间段内的dml操作很少的情况恢复时间短效率高，但是Binlog2sql不支持中文和bit类型闪回，如有，过程会报 too long data错误，另外数据安全性和数据一致性相对低一些。
xtrabackup恢复在对大体量数据库恢复耗时还是很长，恢复数据安全可靠，如恢复数据时间段内没有频繁的dml操作优先考虑Binlog2sql闪回。
在面对非常重要的数据恢复的时候首选还是xtrabackup，不管数据体量多大，xtrabackup严格按照mysql内部机制进行数据还原，恢复过程安全，数据一致性强，操作人员不易对数据做修改。
数据备份重于一切！