mysql 自动化数据备份_mysql 自动自动化增量备份及恢复

1.1数据库备份工具

Percona XtraBackup

官方地址：https://www.percona.com/software/mysql-database/percona-xtrabackup

1.2工具的安装

#安装

[root@localhost ~]# cd /opt/

[root@localhost opt]# yum installpercona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm

#如果提示缺失依赖包

[root@localhost opt]# yum -y install perlperl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

#如果报错如下：

postfix-2.10.1-6.el7.x86_64有缺少的需求libmysqlclient.so.18()(64bit)

则需要安装相关依赖，检查系统mysql版本:

[root@localhost opt]# mysql

Welcome to the MySQL monitor.Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.7.15-log MySQL CommunityServer (GPL)

#下载依赖软件包

#解压缩并安装mysql-community-libs-compat-5.7.15-1.el7.x86_64.rpm

[root@localhost opt]# mkdir -p mysql

[root@localhost opt]# cd mysql/

[root@localhost opt]# mvmysql-5.7.15-1.el7.x86_64.rpm-bundle.tar mysql/

[root@localhost mysql]# tar -xvfmysql-5.7.15-1.el7.x86_64.rpm-bundle.tar

[root@localhost mysql]# yum installmysql-community-libs-compat-5.7.15-1.el7.x86_64.rpm

1.3工具的配置

Xtrabackup常用参数

--user=USER指定备份用户，不指定的话为当前系统用户

--password=PASSWD指定备份用户密码

--port=PORT指定数据库端口

--defaults-group=GROUP-NAME在多实例的时候使用

--host=HOST指定备份的主机，可以为远程数据库服务器

--apply-log回滚日志

--database指定需要备份的数据库，多个数据库之间以空格分开

--defaults-file指定mysql的配置文件

--copy-back将备份数据复制回原始位置

--incremental增量备份，后面跟要增量备份的路径

--incremental-basedir=DIRECTORY增量备份时使用指向上一次的增量备份所在的目录

--incremental-dir=DIRECTORY增量备份还原的时候用来合并增量备份到全量，用来指定全备路径

--redo-only对增量备份进行合并

--rsync加快本地文件传输，适用于non-InnoDB数据库引擎。不与--stream共用

--safe-slave-backup

--no-timestamp生成的备份文件不以时间戳为目录.

1.4备份及恢复策略

增量备份从库到其他服务器；

若主库挂而从库正常，则将主线数据请求切到从库，尝试恢复主库；

若主从都挂，则从备份服务器将数据恢复到从库，再将主线数据请求切到从库，之后尝试恢复主库，或者直接启用远程临时库(要考虑集群恢复后远程临时库将变更为真正的数据源，实时数据如何恢复到集群的问题！)；

1.5全量备份及恢复

#全量备份及恢复

[root@localhost opt]# innobackupex--user=root --password=dianzhong /data/backup/full

#上个命令在我的/data/backup/full/目录生成了一个文件夹【2017-10-16_11-57-01】

#一般情况下，这个备份不能用于恢复，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务，此时数据文件处于不一致的状态

#因此，我们现在就是要通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

innobackupex --user=root --password--defaults-file=/data/mysql/my.cnf --apply-log /data/backup/full/2017-10-16_11-57-01

#恢复操作演练

#关掉服务，迁移已有的数据目录

service mysqld stop

mv /data/mysql /data/mysqlold

mkdir -p /data/mysql

#执行innobackupex恢复命令

innobackupex--defaults-file=/etc/my.cnf --user=root --password=dianzhong --copy-back/data/backup/full/2017-10-16_11-57-01

#对新目录执行赋权操作，此操作需在innobackupex恢复命令后

chown -R mysql.mysql /data/mysql

#重启服务，并检查数据是否恢复

service mysqld start

1.6增量备份及恢复

#全量备份

innobackupex--defaults-file=/data/mysql/my.cnf--user=root --password=passwd/data/backup/full

#第一次增量备份

innobackupex --defaults-file=/etc/my.cnf--user=root --password=dianzhong --incremental /data/backup/inc1--incremental-basedir=/data/backup/full/2017-10-16_11-57-01

# --incremental-basedir指的是完全备份所在的目录

#此命令执行结束后，innobackupex命令会在/data/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。

#另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。

#需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

#第二次增量备份

innobackupex --defaults-file=/etc/my.cnf--user=root --password=dianzhong --incremental /data/backup/inc2--incremental-basedir=/data/backup/inc1/2017-10-16_14-52-36

#备份恢复演练

#恢复到第一次备份点

service mysqld stop

mv /data/mysql /data/mysqlold

mkdir -p /data/mysql

innobackupex --apply-log --redo-only/data/backup/full/2017-10-16_11-57-01

innobackupex --apply-log --redo-only/data/backup/full/2017-10-16_11-57-01--incremental-dir=/data/backup/inc1/2017-10-16_14-52-36

innobackupex --defaults-file=/etc/my.cnf--user=root --password=dianzhong --copy-back/data/backup/full/2017-10-16_11-57-01

chown -R mysql:mysql /data/mysql

service mysqld start

#恢复到第二次备份点(比恢复到第一次备份点多一次操作)

servicemysqld stop

mv/data/mysql /data/mysqlold

mkdir -p/data/mysql

innobackupex--apply-log --redo-only /data/backup/full/2017-10-16_11-57-01

innobackupex--apply-log --redo-only /data/backup/full/2017-10-16_11-57-01--incremental-dir=/data/backup/inc1/2017-10-16_14-52-36

innobackupex --apply-log --redo-only/data/backup/full/2017-10-16_11-57-01--incremental-dir=/data/backup/inc2/2017-10-16_14-59-34

innobackupex--defaults-file=/etc/my.cnf --user=root --password=dianzhong --copy-back/data/backup/full/2017-10-16_11-57-01

chown -Rmysql:mysql /data/mysql

servicemysqld start

1.7远程/异地备份

#远程备份的方法except +

scp +md5(except需要单独安装，方法见《expect SCP安装及使用》)

1.创建远程机与源机的互信

#生成密钥对

[root@localhost ~]# ssh-keygen -t rsa

#传送到远程机

[root@localhost ~]# ssh-copy-id -i ~/.ssh/id_rsa.pubroot@192.168.0.200

2.备份文件验证

分别获取本机备份文件及远程机备份文件之后比对md5码

3.限速远程备份方法

#登陆远程机并获取远程机上备份文件MD5码的方法(4个参数：远程机IP、用户名、密钥对文件路径、备份文件路径)

#!/usr/bin/expect

# viscp.exp#免交互登陆脚本

setipaddress [lindex $argv 0]

setusername [lindex $argv 1]

setpassword_id_rsa [lindex $argv 2]

setfilepath [lindex $argv 3]

if {$argc != 4 } {

puts"Usage: expect scp.exp ipaddress username id_rsa filepath"

exit 1

}

spawn scp-i $password_id_rsa -l 300000 -r $filepath $username@$ipaddress:$filepath

expect"100%"

expect eof

4.登陆远程机并获取远程md5码的方法

#!/usr/bin/expect

# vi login.exp#免交互登陆脚本

# expectlogin.exp 192.168.0.200 root ~/.ssh/id_rsa

setipaddress [lindex $argv 0]

setusername [lindex $argv 1]

setpassword_id_rsa [lindex $argv 2]

setfilepath [lindex $argv 3]

if {$argc != 4 } {

puts"Usage: expect login.exp ipaddress username id_rsa filepath"

exit 1

}

settimeout 30

spawn ssh-i $password_id_rsa $username@$ipaddress

#spawnssh $username@$ipaddress

#expect"$username@*"{send "df-h\r"}

expect"$username@*"{send"md5sum $filepath\r"}

expect "$username@*"{send "exit\r"}

5.发送备份成功与否的实时信息

通过webQQ接口实时发送

1.8线上自动化备份策略

1.每天凌晨4点全量备份一次，之后每小时增量备份一次

2.多保留一天的备份数据，以便可以按时间点恢复

1.9在其他机器上恢复数据源

当源数据库短时无法恢复的情况下，可以考虑快速将数据库在另一台远程机上恢复，之后将数据源ip切换即可。

已实验，在数据库基础配置一致的情况下，具体操作过程同1.5、1.6方式一致即可。