Linux运维 | mysql数据库的备份与恢复

特点

• 备份的数据全面而且最为完整
• 数据量大的时候备份时间长
• 备份数据会存在大量重复信息

增量备份

增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。

特点

• 与全量备份相比，没有重复的备份数据
• 备份数据量不大，备份恢复的时间短
• 数据恢复麻烦，必须具有上一次全备份和所有增量备份磁带，并且需要按照全量备份到增量备份的顺序反推进行恢复

差异备份

差异备份是指在一次全备份后到进行差异备份的这段时间内，对那些增加或者修改文件的备份。

特点

• 备份时间短，节省磁盘空间
• 恢复所需的磁带少，恢复时间短

比较
数据备份的数据量（大>>小）：
全量备份>差异备份>增量备份

数据的恢复时间（快>>慢）：
全量备份->差异备份->增量备份

Mysql数据库备份工具

mysqldump工具

mysqldump是一款mysql逻辑备份的工具，它将数据库里面的对象（表）导出作为SQL脚本文件。它是mysql备份常用的备份方法，适合于不同版本mysql之间的升级、迁移等，不过在数据库比较大的时候，效率不高。

下面是mysqldump命令的常见用法：

# 备份单个的数据库，如备份db1这个数据库
mysqldump -uroot -p123456 db1 > /backup/db1_`date +%F`.sql

# 备份所有数据库，-A参数
mysqldump -uroot -p123456 -A > /backup/all_db.sql

# 备份远程主机的数据库，-h指定IP地址，-P(大写P)指定端口
mysqldump -uroot -p123456 -h192.168.30.4 -P3306 db1 > /backup/db1.sql

# 只备份表结构，不备份数据，使用-d参数
mysqldump -uroot -p123456 -d db1 > /backup/db1.sql

# 只备份数据，不备份表结构，使用-t参数
mysqldump -uroot -p123456 -t db1 > /backup/db1.sql 

# 备份指定的多个库，-B参数
mysqldump -uroot -p123456 -B db1 db2 db3 > /backup/db123.sql

# 备份指定的表，如备份db1库里面的stu表
mysqldump -uroot -p123456 db1 stu > /backup/db1_stu.sql

# 备份多个表
mysqldump -uroot -p123456 库1 表1 表2 表3... > db_tables.sql

# 恢复数据
mysqldump -uroot -p123456 dbname < xxx.sql
#或者
mysqldump -uroot -p123456 < xxx.sql

其他参数：

参数	说明
-R, --routines	备份存储过程和函数数据
--triggers	备份触发器数据
--master-data={1或者2}	告诉你备份后时刻的binlog位置，如果等于1，则将其打印为CHANGE MASTER命令; 如果等于2，那么该命令将以注释符号为前缀。
--single-transaction	对innodb引擎进行热备

一条完整的备份语句（适用innodb引擎）：

mysqldump -A -R --triggers --master-data=2 --single-transaction | gzip >/backup/all_$(date +%F).sql.gz

适用多引擎混合的一条完整备份语句：

mysqldump -A -R --triggers --master-data=2 |gzip >/backup/all_$(date +%F).sql.gz

备份脚本
日常可以根据需求进行备份工作，常见是使用shell脚本结合任务计划，下面是一个mysql数据库备份脚本示例，mysql_backup.sh脚本内容如下：

#!/bin/bash 
bakdir=/data/backup # 定义备份文件存放目录
d=`date +%F` # 以日期为命名格式

# 备份三个数据库bbs,blog,db2
for db in bbs blog db2
do
    mysqldump -uroot -p123456 $db > $bakdir/$db\_$d.sql
done
cd $bakdir
# 对备份文件进行压缩
gzip *_$d.sql
# 对备份时间超过一个月的文件进行删除
find ./ -name "*.gz" -mtime +30 |xargs rm

添加任务计划：

[root@moli_linux1 data]$ chmod +x /usr/local/sbin/mysql_backup.sh
[root@moli_linux1 data]$ crontab  -e
# 添加内容
30 3 * * * /bin/bash /usr/local/sbin/mysql_backup.sh >/tmp/mysql_backup.log 2>/tmp/mysql_backup.log
# 任务计划内容是：
# 每天凌晨3点30分执行备份脚本，并将正确与错误输出写进mysql_backup.log文件

xtrabackup备份工具

Xtrabackup是由percona开源的免费数据库热备份软件，它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份,也支持MyISAM表和服务器的其他部分。

mysqldump对于导出几个G的数据库或几个表，还是不错的，速度并不慢。一旦数据量达到几十上百G，
无论是对原库的压力还是导出的性能，mysqldump就力不从心了。Percona-Xtrabackup备份工具，
是实现MySQL在线热备工作的不二选择，可进行全量、增量、单表备份和还原。

xtrabackup的安装

环境说明：Centos Linux release 7.5.1804 (Core) IP=192.168.30.3

安装xtrabackup2.4版本：

# 添加yum源
wget -O /etc/yum.repos.d/epel.repo Http://mirrors.aliyun.com/repo/epel-6.repo
# 安装依赖包
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
# 下载rpm包
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
# 安装
yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

Xtrabackup安装完成后有4个可执行文件，其中2个比较重要的备份工具是innobackupex、xtrabackup:

1. .xtrabackup只能备份InnoDB和XtraDB 两种数据表(在2.4版本之后支持备份MyISAM)
2. .innobackupex是一个封装xtrabackup的Perl脚本，支持同时备份innodb和myisam，但在对myisam备份时需要加一个全局的读锁

innobackupex的全量备份

用innobackupex做全量备份的命令是：

innobackupex --defaults-file=/etc/my.cnf  --host=192.168.30.3  --port=3306 
--user=root --passWord=123456  /data/backup/mysql

说明：

• --defaults-file指定mysql的配置文件，要从配置文件找到datadir的路径；
• --host指定IP, --port指定端口；
• --user指定用户，--password指定用户密码；
• /data/backup/mysql是备份文件所在的目录，使用绝对路径。

备份的数据会存放在指定的目录里，并且自动生成一个以当前日期、时间为名字的目录，例如2019-06-01_15-05-31。

[root@moli_linux1 mysql]$ pwd
/data/backup/mysql
[root@moli_linux1 mysql]$ ll
总用量 0
drwxr-x--- 2 root root   6 6月   1 15:03 2019-06-01_15-03-22
drwxr-x--- 6 root root 188 6月   1 15:05 2019-06-01_15-05-31
[root@moli_linux1 mysql]# 

关于备份用户

使用xtrabackup备份可以直接使用root用户，但是生产环境中是很少见的不符合安全规则，因此可以创建一个备份用户进行数据备份的工作，这个备份用户拥有reload, lock tables, replication client, process, super等权限。

mysql  -uroot  -p123456
> grant reload,lock tables,replication client on *.* to "backupuser"@"localhost"  identified  by  "123456";
> flush  privileges;

创建后，后续的备份工作都使用这个备份用户，mysqldump也可以类似地使用非root用户。

innobackupex全量备份的恢复

innobackupex全量备份的恢复:

# 1.预备恢复：
innobackupex --apply-log /data/backup/mysql/2019-06-01_15-05-31 --user-memroy=2G

# 2.停止MySQL/MariaDB，将datadir目录里面的数据清空（或者挪走）
/etc/init.d/mysql stop
mv /data/mysql /data/mysql_bak

# 3.恢复
innobackupex --defaults-file=/etc/my.cnf  --copy-back  /data/backup/mysql/2019-06-01_15-05-31

# 4.改权限，这个/data/mysql是my.cnf里datadir的目录
chown -R mysql:mysql /data/msyql

#5.启动MySQL/MariaDB
/etc/init.d/mysql start

innobackupex的增量备份

innobackupex --host=192.168.30.3  --port=3306 --user=bakuser --password=your_pass 
 --incremental /data/backup/mysql --incremental-basedir=/data/backup/mysql/last-backup-file

说明：

• --incremental ：表示本次备份是一个增量备份（若针对的上次备份为一个全量备份，这里也可以认为是个差异备份）
• --incremental-basedir：指定本次增量备份针对的哪一个备份（可以是上个增量，也可以是上个全量） 增量备份可以一环扣一环，比如可以周一做一个全量备份，然后周二做周一的增量，周三做周二的增量...

innobackupex增量备份的恢复
准备阶段：
第一步：停止数据库服务
第二步：删除或者备份datadir里面的数据
第三步：准备全量备份恢复：

innobackupex --apply-log --redo-only  /PATH/TO/全量备份目录

第四步：准备增量备份恢复1：

innobackupex --apply-log --redo-only  /PATH/TO/全量备份目录 --incremental-dir=/PATH/TO/第一次增量备份目录

第五步：准备增量备份恢复2：

innobackupex --apply-log --redo-only /PATH/TO/全量备份目录  --incremental-dir=/PATH/TO/第二次增量备份目录

......
第N步：准备最后一个增量备份恢复：

innobackupex --apply-log /PATH/TO/全量备份目录  --incremental-dir=/PATH/TO/最后一次增量备份目录

准备阶段的最后一步，再次准备全量：

innobackupex --apply-log   /PATH/TO/全量备份目录   #（这次不用加--redo-only了）

恢复阶段：

1. innobackupex --copy-back /PATH/TO/全量备份目录
2. 改权限：chown -R mysql:mysql /PATH/TO/datadir
3. 启动MySQL/MariaDB

说明：--redo-only：表示进行准备（应用日志）工作时，只进行redo操作，只会重做已提交但未应用的事务，不会回滚未提交的事务。原因是后面还有个增量备份，未提交的可能在后面增量备份时进行提交。需要注意的是，最后一个增量备份不需要加这个选型。

mariadb10.3.x以上版本进行备份恢复

MariaDB10.3.x及以上的版本用Percona XtraBackup工具会有问题。
原因可能是MariaDB10.3以上版本的redo日志格式和之前不同了。
解决方案是，使用mariabackup，它是MariaDB提供的一个开源工具，用于对InnoDB，Aria和MyISAM表进行物理在线备份。
这个工具是基于Percona的XtraBackup（版本2.3.8）的解决方案：

全量备份：
mariabackup --defaults-file=/etc/my.cnf --backup --user=xxx --password=xxx --target-dir /data/backup/2019-06-01

全量恢复：
停止数据库
清空datadir
mariabackup --prepare --target-dir /data/backup/2019-06-01/ 
mariabackup --copy-back --target-dir /data/backup/2019-06-01/
改属主、属组
启动

增量备份：
mariabackup --defaults-file=/etc/my.cnf --backup --user=xxx --password=xxx --target-dir /data/backup/2019-06-01
第一个增量：mariabackup --backup --user=xxx --password=xxx  --target-dir /data/backup/2019-06-01_inc1 --incremental-basedir /data/backup/2019-06-01
第二个增量：mariabackup --backup --user=xxx --password=xxx  --target-dir /data/backup/2019-06-01_inc2 --incremental-basedir /data/backup/2019-06-01_inc1

增量恢复：
先准备全备
mariabackup --prepare --target-dir /data/backup/2019-06-01 --apply-log-only
准备第一个增量：
cd /data/backup
mariabackup --prepare --target-dir ./2019-06-01 --incremental-dir ./2019-02-21_inc1 --apply-log-only
准备第二个增量：
mariabackup --prepare --target-dir .2019-06-01 --incremental-dir ./2019-02-21_inc2 --apply-log-only
恢复：
mariabackup --copy-back  --target-dir ./2019-06-01

参考资料

• 惨绿少年. Mysql 备份恢复与xtrabackup备份.
• zhangguangzhi. innobackex工具备份mysql数据.
• Percona XtraBackup. 官方文档.