MySQL数据库全量、增量备份与恢复怎么做

MySQL数据库全量备份与恢复步骤：1、创建专用备份文件夹；2、执行全量备份命令；3、执行全量恢复命令。增量备份与恢复步骤：1、查看log_bin是否开启；2、修改/etc/my.cnf；3、增量备份；4、解码查看日志文件内容；5、根据日志文件恢复数据。

一、MySQL数据库全量备份与恢复步骤

1、创建专用备份文件夹

mkdir -p /data/backup

2、执行全量备份命令

/usr/bin/mysqldump -uroot -padmin --lock-all-tables --flush-logs test > /home/backup.sql

如上一段代码所示，其功能是将 test 数据库全量备份。

其中：

• MySQL 用户名为：root
• 密码为：admin
• 备份的文件路径为：/home（当然这个路径也是可以按照个人意愿修改的）
• 备份的文件名为：backup.sql
• 参数 flush-logs：使用一个新的日志文件来记录接下来的日志参数
• 参数 lock-all-tables：锁定所有数据库

3、执行全量恢复命令

执行shell命令：

mysql -h localhost -uroot -padmin < bakdup.sql

或者登录数据库后执行：

mysql> source /home/backup/bakdup.sql

二、MySQL数据库增量备份与恢复步骤

1、查看log_bin是否开启

show variables like '%log_bin%';

如下命令所示，则为未开启：

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

2、修改/etc/my.cnf

修改/etc/my.cnf，在[mysqld]下添加log-bin=mysql-bin，再次查看：

show variables like '%log_bin%';

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+

3、增量备份

增量备份脚本：

#!/bin/bash
#在使用之前，请提前创建以下各个目录
backupDir=/data/backup/daily
#增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
mysqlDir=/var/lib/mysql
#mysql的数据目录
logFile=/data/backup/daily_bak.log
BinFile=/var/lib/mysql/mysql-bin.index
#mysql的index文件路径，放在数据目录下的

mysqladmin -uroot -p123456 flush-logs
#这个是用于产生新的mysql-bin.00000*文件
# wc -l 统计行数
# awk 简单来说awk就是把文件逐行的读入，以空格为默认分隔符将每行切片，切开的部分再进行各种分析处理。
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
for file in `cat $BinFile`
do
   base=`basename $file`
   echo $base
   #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
   NextNum=`expr $NextNum + 1`
   if [ $NextNum -eq $Counter ]
   then
       echo $base skip! >> $logFile
   else
       dest=$backupDir/$base
       if(test -e $dest)
       #test -e用于检测目标文件是否存在，存在就写exist!到$logFile去
       then
           echo $base exist! >> $logFile
       else
           cp $mysqlDir/$base $backupDir
           echo $base copying >> $logFile
        fi
    fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $logFile

新建测试表：

CREATE TABLE `ly_ycasp_qxpt0`.`test`  (
     `id` int NOT NULL AUTO_INCREMENT,
     `name` varchar(255) NULL,
     PRIMARY KEY (`id`)
);

插入三条数据：

insert into test (id, name) values(1, 'test1');
insert into test (id, name) values(2, 'test2');
insert into test (id, name) values(3, 'test3');
insert into test (id, name) values(4, 'test4');

误删id=3和id=4这两条数据：

delete from test where id = 3;
delete from test where id = 4;

继续插入id=5和id=6条数据：

insert into test (id, name) values(5, 'test5');
insert into test (id, name) values(6, 'test6');

增量备份：

#进行增量备份,生成新的备份日志文件，这样旧的日志文件就不会输入新的日志，方便排查问题。
[root@localhost data]# mysqladmin -uroot -padmin flush-logs

查看增量备份文件：

-rw-r-----. 1 mysql mysql      4361 Jan  5 09:50 mysql-bin.000001
-rw-r-----. 1 mysql mysql       156 Jan  5 09:50 mysql-bin.000002

4、解码查看生成的日志文件内容

mysqlbinlog  --no-defaults --base64-output=decode-rows -v mysql-bin.000001  > /opt/bk01.txt

5、根据日志文件恢复数据

在测试库先还原全量数据，然后再根据这里的增量备份，使数据还原。

基于位置恢复：

恢复数据到误操作前一次的指定位置：

mysqlbinlog --s较好-position='操作id'  二进制日志 | mysql -u 用户名 -p 密码

[root@localhost mysql]# mysqlbinlog --s较好-position='3323' mysql-bin.000001 | mysql -uroot -padmin

恢复数据到误操作后一次的指定位置：

mysqlbinlog --start-position='操作id' 二进制日志 | mysql -u 用户名 -p 密码

[root@localhost mysql]# mysqlbinlog --start-position='4078' mysql-bin.000002 | mysql -uroot -padmin

基于时间恢复：

从日志开头截止到某个时间点的恢复：

mysqlbinlog [--no-defaults] --s较好-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u用户名 -p密码

从某个时间点到日志结尾的恢复：

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u用户名 -p密码

从某个时间点到某个时间点的恢复：

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --s较好-
datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u用户名 -p密码

三、MySQL数据库全量备份与增量备份优缺点

1、全量备份

优点：备份与恢复操作简单方便。

缺点：

• 数据存在大量的重复；
• 占用大量的备份空间；
• 备份与恢复时间长。

2、增量备份

优点：没有重复数据，备份量不大，时间短。

缺点：

• 恢复过程需要逐个应用备份文件，增加了恢复时间；
• 当恢复到之前的某个备份时，需要先恢复最后的全量备份，然后再逐个恢复每个增量备份，可能更加繁琐和耗时。

延伸阅读1：MySQL数据库全量备份与增量备份简介

• 全量备份：全量备份是对整个数据库的备份、数据库结构和文件结构的备份。全量备份保存的是备份完成时刻的数据库。全量备份是增量备份的基础。
• 增量备份：备份自上一次备份之后增加或变化的文件或者内容。特点是没有重复数据，备份量不大，时间短，不过恢复麻烦。