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 文件