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

一、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.logBinFile=/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 $BinFiledo   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    fidoneecho 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数据库全量备份与增量备份简介

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