Linux数据库备份和恢复

       

      为了保障数据的安全，需要定期对数据进行备份。备份的方式有很多种，效果也不一样。一旦数据库中的数据出现了错误，就需要使用备份好的数据进行还原恢复。从而将损失降到最低。下面我们来了解一下MySQL常见的有三种备份恢复方式：

1、利用Mysqldump+二进制日志实现备份
2、利用LVM快照+二进制日志实现备份
3、使用Xtrabackup备份

今天我们主要来了解学习一下使用Xtrabackup进行数据库的增量备份及还原。

1.完全备份

[root@contos7 ~]#mkdir /mysql/backup/{full,inc{1,2}}
[root@contos7 ~]#innobackupex /mysql/backup/full
xtrabackup: recognized server arguments: --datadir=/mysql/data --log_bin=/mysql/logbin --innodb_file_per_table=1 
xtrabackup: recognized client arguments: --datadir=/mysql/data --log_bin=/mysql/logbin --innodb_file_per_table=1 
181013 15:33:45 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

 

2.第一次修改数据及第一次增量

[root@contos7 ~]#innobackupex --incremental /mysql/backup/inc1 --incremental-basedir=/mysql/backup/full/2018-10-12_11-14-47/
xtrabackup: recognized server arguments: --datadir=/mysql/data --log_bin=/mysql/logbin --innodb_file_per_table=1 
xtrabackup: recognized client arguments: --datadir=/mysql/data --log_bin=/mysql/logbin --innodb_file_per_table=1 
181013 15:36:07 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

 

3.第二次修改数据及第二次增量

[root@contos7 ~]# innobackupex --incremental /mysql/backup/inc2 --incremental-basedir=/mysql/backup/inc1/2018-10-12_11-16-04/.
xtrabackup: recognized server arguments: --datadir=/mysql/data --log_bin=/mysql/logbin --innodb_file_per_table=1 
xtrabackup: recognized client arguments: --datadir=/mysql/data --log_bin=/mysql/logbin --innodb_file_per_table=1 
181013 15:38:27 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

 

4.对数据库进行破坏

略

 

5.清空数据停服务

[root@contos7 ~]# systemctl stop mariadb.service
[root@contos7 ~]# rm /mysql/data/* -rf

 

6.预处理数据库

[root@contos7 ~]# innobackupex --apply-log --redo-only /backup/full/2018-10-12_11-14-47/
[root@contos7 ~]# innobackupex --apply-log --redo-only /backup/full/2018-10-12_11-14-47/ --incremental-dir=/backup/inc1/2018-10-12_11-16-04/
[root@contos7 ~]# innobackupex --apply-log  /backup/full/2018-10-12_11-14-47/ --incremental-dir=/backup/inc2/2018-10-12_11-17-00/

 

7.复制数据

[root@contos7 ~]# innobackupex --copy-back /backup/full/2018-10-12_11-14-47/

 

8.修改权限和启动服务

[root@contos7 ~]# chown -R mysql.mysql /mysql/data
[root@contos7 ~]# systemctl restart mariadb.service

 

9.通过二进制日志回恢复recovery，先读取最后一次增量备份目录下的xtrabackup_binlog_info，获取需要恢复的二进制日志的起始位置

[root@contos7 ~]# mysqlbinlog --start-position=8057 /mysql/logbin/mysql-bin.000003 > /root/bin.sql
[root@contos7 ~]# mysql < /root/bin.sql