xtrabackup工具-白红宇

xtrabackup工具

阅读量：6171 次

发布时间：2019-06-21

本文共 6746 字，大约阅读时间需要 22 分钟。

实验环境：CentOS7

[root@~ localhost]#yum install percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm #创建备份目录：[root@~ localhost]#mkdir -pv /data/backup#创建可以进行备份的用户，只授予可备份的相关权限：MariaDB [(none)]> CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY '123456';#一定要赋予PROCESS的权限MariaDB [(none)]> grant RELOAD,RELOAD,REPLICATION CLIENT,PROCESS,LOCK TABLES  on *.* to 'back'@'localhost';MariaDB [(none)]> flush privileges;#全量备份：[root@~ localhost]#innobackupex --user back -p 123456 /data/backup/#附：收回权限：MariaDB [(none)]> revoke PROCESS  on *.* from 'back'@'localhost';#附：查看用户所拥有的权限；MariaDB [(none)]>  show grants for 'back'@'localhost';  #附：查看表的存储引擎MariaDB [(none)]> show table status\G;

#全量恢复：

#备份恢复的时候最好关闭二进制日志：

MariaDB [hellodb]> set @@session.sql_log_bin=OFF;

#再另一台服务器上安装：[root@~ localhost]#yum -y install mariadb-server percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm#创建临时还原目录：[root@~ localhost]#mkdir -pv  /data/backup#此时数据库的数据目录为空[root@~ localhost]#cd /var/lib/mysql/[root@mysql localhost]#ls#将备份数据文件发送给此台新服务器：[root@backup localhost]#ls2017-07-14_12-09-21[root@backup localhost]#scp -rp 2017-07-14_12-09-21/ root@172.16.254.47:/data/backup/[root@~ localhost]#cd /data/backup/[root@backup localhost]#ls2017-07-14_12-09-21[root@backup localhost]#cd 2017-07-14_12-09-21/[root@2017-07-14_12-09-21 localhost]#lsbackup-my.cnf  ibdata1  performance_schema  xtrabackup_binlog_info  xtrabackup_infohellodb        mysql    test                xtrabackup_checkpoints  xtrabackup_logfile#在此目录下做数据事务的合并和回滚等操作[root@2017-07-14_12-09-21 localhost]#innobackupex --apply-log ./170714 14:00:48 completed OK![root@2017-07-14_12-09-21 localhost]#lsbackup-my.cnf  ib_logfile1         test                          xtrabackup_infohellodb        ibtmp1              xtrabackup_binlog_info        xtrabackup_logfileibdata1        mysql               xtrabackup_binlog_pos_innodbib_logfile0    performance_schema  xtrabackup_checkpoints#数据的还原[root@2017-07-14_12-09-21 localhost]#innobackupex --copy-back ./170714 14:04:07 completed OK![root@~ localhost]#cd /var/lib/mysql/[root@mysql localhost]#lltotal 40976drwxr-x---. 2 root root      272 Jul 14 14:04 hellodb-rw-r-----. 1 root root 18874368 Jul 14 14:04 ibdata1-rw-r-----. 1 root root  5242880 Jul 14 14:04 ib_logfile0-rw-r-----. 1 root root  5242880 Jul 14 14:04 ib_logfile1-rw-r-----. 1 root root 12582912 Jul 14 14:04 ibtmp1drwxr-x---. 2 root root     4096 Jul 14 14:04 mysqldrwxr-x---. 2 root root     4096 Jul 14 14:04 performance_schemadrwxr-x---. 2 root root       20 Jul 14 14:04 test-rw-r-----. 1 root root       26 Jul 14 14:04 xtrabackup_binlog_pos_innodb-rw-r-----. 1 root root      471 Jul 14 14:04 xtrabackup_info[root@mysql localhost]#chown -R mysql.mysql ./*#启动数据库，检查数据，并做全量备份[root@2017-07-14_12-09-21 localhost]#systemctl start mariadb.service[root@2017-07-14_12-09-21 localhost]#innobackupex -u root  /data/backup/

#经过一段时间的写操作，数据库发生数据变化，然后做增量备份：

#刚做完全量备份的源数据库的信息MariaDB [hellodb]> show master status;+-------------------+----------+--------------+------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-bin.000003 |    18667 #写操作后的二进制日志信息：MariaDB [(none)]> show master status;+-------------------+----------+--------------+------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-bin.000003 |    19150 |     root@mysql localhost]#cd /data/backup/[root@backup localhost]#ls2017-07-14_12-09-21[root@backup localhost]#innobackupex -u back -p 123456 --incremental /data/backup/ --incremental-basedir=/data/backup/2017-07-14_12-09-21/170714 14:37:45 completed OK!#继续写操作，再次进行增量备份，这次增量是基于上一次的增量：[root@backup localhost]#ls2017-07-14_12-09-21  2017-07-14_14-37-28[root@backup localhost]#innobackupex -u back -p 123456 --incremental /data/backup/ --incremental-basedir=/data/backup/2017-07-14_14-37-28/170714 14:52:18 completed OK![root@backup localhost]#ls2017-07-14_12-09-21  2017-07-14_14-37-28  2017-07-14_14-52-08MariaDB [hellodb]> show master status;+-------------------+----------+--------------+------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-bin.000003 |    19261 |#加上二进制日志的备份，此处继续写操作，但不备份，使用二进制进行还原：MariaDB [hellodb]> show master status;+-------------------+----------+--------------+------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-bin.000003 |    19373 #通过下面的文件查看第二次增量备份后，是从哪个日志序列号开始的二进制日志：[root@backup localhost]#cd 2017-07-14_14-52-08/[root@2017-07-14_14-52-08 localhost]#lsbackup-my.cnf  ibdata1.meta        xtrabackup_binlog_info  xtrabackup_logfilehellodb        mysql               xtrabackup_checkpointsibdata1.delta  performance_schema  xtrabackup_info[root@2017-07-14_14-52-08 localhost]#less xtrabackup_binlog_info master-bin.000003       19261 #是从此开始[root@2017-07-14_14-52-08 localhost]#cd /var/lib/mysql/[root@mysql localhost]#lsaria_log.00000001  ibdata1      master-bin.000001  master-bin.index  performance_schemaaria_log_control   ib_logfile0  master-bin.000002  mysqlhellodb            ib_logfile1  master-bin.000003  mysql.sock[root@mysql localhost]#mysqlbinlog -j 19261 master-bin.000003 > /data/backup/binlog-$(date +%F).sql[root@backup localhost]#ls2017-07-14_12-09-21  2017-07-14_14-37-28  2017-07-14_14-52-08  binlog-2017-07-14.sql#接下来在另一数据库做增量恢复和二进制日志的恢复（恢复时数据库是不能启动的）：[root@backup localhost]#scp -rp 2017-07-14_14-37-28/ 2017-07-14_14-52-08/ binlog-2017-07-14.sql  root@172.16.254.47:/data/backup/#先全量做合并，然后其余进行合并；最后回滚[root@backup localhost]#ls2017-07-14_12-09-21  2017-07-14_14-37-28  2017-07-14_14-52-08  binlog-2017-07-14.sql[root@backup localhost]#innobackupex --apply-log --redo-only 2017-07-14_12-09-21170714 15:14:19 completed OK![root@backup localhost]#innobackupex --apply-log --redo-only 2017-07-14_12-09-21  --incremental-dir=2017-07-14_14-37-28170714 15:19:30 completed OK![root@backup localhost]#innobackupex --apply-log --redo-only 2017-07-14_12-09-21  --incremental-dir=2017-07-14_14-52-08170714 15:20:02 completed OK![root@backup localhost]#innobackupex --apply-log 2017-07-14_12-09-21170714 15:25:12 completed OK!#恢复：[root@backup localhost]#innobackupex --copy-back 2017-07-14_12-09-21[root@mysql localhost]#chown -R mysql.mysql *[root@mysql localhost]#systemctl start mariadb.service[root@backup localhost]#ls2017-07-14_12-09-21  2017-07-14_14-37-28  2017-07-14_14-52-08  binlog-2017-07-14.sql[root@backup localhost]#mysql -p < binlog-2017-07-14.sql [root@backup localhost]#mysql -p#检查数据，保证恢复没有问题

转载于:https://www.cnblogs.com/wzhuo/p/7169519.html

你可能感兴趣的文章

Flutter滚动, 中间显示整图, 前后露出部分图

查看>>

Flutter入坑指南：开发环境搭建

查看>>

跨Navigation跳转（类似微信）方案二

查看>>

JavaScript 复习之对象的继承

查看>>

从开源小白到 Apache Member，我的成长之路

查看>>