增量备份
增量备份的特点：
优点：没有重复数据，备份量不大，时间短。
缺点：需要上次完全备份及完全备份之后所有的增量备份才能恢复，
MySQL没有提供直接的增量备份方法，但是可以通过mysql的二进制文件（binarylogs）间接实现增量备份。二进制日志对备份的意义如下：
（1）二进制日志文件保存了所有更新或者可能更新数据库的操作。
（2）二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件。
（3）只需要定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。
首先在进行增量备份之前需要查看一下配置文件，查看 log_bin 是否开启，因为要做增量备份首先要开启 log_bin 。首先，进入到 myslq 命令行，输入如下命令：
mysql> 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 |
+---------------------------------+-------+修改 MySQL 配置项到如下代码段：vim /etc/mysql/mysql.conf.d/mysqld.cnf
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#binlog setting，开启增量备份的关键
log-bin=/var/lib/mysql/mysql-bin
server-id=123454修改之后，重启 mysql 服务，输入：
mysql> 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 |
+---------------------------------+--------------------------------+好了，做好了充足的准备，那我们就开始学习增量备份了。
查看当前使用的 mysql_bin.000*** 日志文件，
mysql>show master status;状态如下：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000015 | 610 | | | |
+------------------+----------+--------------+------------------+-------------------+当前正在记录日志的文件名为 mysql-bin.000015 。
当前数据库中有如下数据：
mysql> select * from users;
+-------+------+----+
| name | sex | id |
+-------+------+----+
| zone | 0 | 1 |
| zone1 | 1 | 2 |
| zone2 | 0 | 3 |
+-------+------+----+我们插入一条数据：
mysql> insert into `zone`.`users` ( `name`, `sex`, `id`) values ( 'zone3', '0', '4');查看效果：
mysql> select * from users;
+-------+------+----+
| name | sex | id |
+-------+------+----+
| zone | 0 | 1 |
| zone1 | 1 | 2 |
| zone2 | 0 | 3 |
| zone3 | 0 | 4 |
+-------+------+----+我们执行如下命令，使用新的日志文件：
mysqladmin -uroot -p123456 flush-logs日志文件从 mysql-bin.000015 变为 mysql-bin.000016，而 mysql-bin.000015 则记录着刚刚 insert 命令的日志。上句代码的效果如下：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+那么到现在为止，其实已经完成了增量备份了。
恢复增量备份
那么现在将刚刚插入的数据删除，效果如下：
delete from `zone`.`users` where `id`='4'
mysql> select * from users;
+-------+------+----+
| name | sex | id |
+-------+------+----+
| zone | 0 | 1 |
| zone1 | 1 | 2 |
| zone2 | 0 | 3 |
+-------+------+----+那么现在就是重点时间了，从 mysql-bin.000015 中恢复数据：
mysqlbinlog /var/lib/mysql/mysql-bin.000015 | mysql -uroot -p123456 zone;上一句代码指定了，需要恢复的 mysql_bin 文件，指定了用户名：root 、密码：123456 、数据库名：zone。效果如下：
mysql> select * from users;
+-------+------+----+
| name | sex | id |
+-------+------+----+
| zone | 0 | 1 |
| zone1 | 1 | 2 |
| zone2 | 0 | 3 |
| zone3 | 0 | 4 |
+-------+------+----+OK，整一个增量备份的操作流程都在这里了，那么我们如何将它写成脚本文件呢，代码如下：
#!/bin/bash
#在使用之前，请提前创建以下各个目录
backupDir=/usr/local/work/backup/daily
#增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
mysqlDir=/var/lib/mysql
#mysql的数据目录
logFile=/usr/local/work/backup/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
#NODE_ENV=$backUpFolder@$backUpFileName /root/node/v8.11.3/bin/node /usr/local/upload.js定时备份
输入如下命令，进入定时任务编辑界面：
crontab -e添加如下命令，其意思为：每分钟执行一次备份脚本，crontab 的具体规则就另外写文了，与本文主题不太相关。
* * * * * sh /usr/your/path/mysqlbackup.sh关于 crontab 的介绍，查看我的其它推文
说明
恢复的顺序，要先恢复最先生成的二进制文件，然后依次执行
基于时间点恢复
通过查看二进制日志文件： 180903 15:31:56 错误操作时间点 --stop-datetime
[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2018-09-03 15:31:56' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p通过查看二进制日志文件：180903 15:32:10 下一个正确操作的时间点 --start-datetime
[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2018-09-03 15:32:10' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p基于位置点恢复
通过查看二进制日志文件： At 833 上一次可以被正确执行的位置 --stop–position
[root@localhost data]# mysqlbinlog --no-defaults --stop-position='833' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p通过查看二进制日志文件：At 930 下一次可以被正确执行的位置 --start–position
[root@localhost data]# mysqlbinlog --no-defaults --start-position='930' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p下面是binlog日志恢复中一些常用的参数
–start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
–stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
–start-position：从二进制日志中读取指定position 事件位置作为开始。
–stop-position：从二进制日志中读取指定position 事件位置作为事件截至
恢复指定库的数据 -d
—database=db_name，-d db_name
只列出该数据库的条目(只用本地日志)。仅仅恢复指定数据库的数据，如下面的，仅仅恢复数据库lvnian的数据而已，其他库的数据不会恢复
mysqlbinlog -d lvnian /var/log/mysql-bin.000001 | mysql -uroot -p显示二进制文件
show binlog events用于在二进制日志中显示事件。如果未指定’log_name’，则显示第一个二进制日志。
mysql> show variables like 'version';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.6.12-log |
+---------------+------------+
mysql> show binary logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| APP01bin.000001 | 120 |
+-----------------+-----------+a、只查看第一个binlog文件的内容(show binlog events)
mysql> use replication;
Database changed
mysql> select * from tb;
+------+-------+
| id | val |
+------+-------+
| 1 | robin |
+------+-------+
mysql> insert into tb values(2,'jack');
Query OK, 1 row affected (0.02 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb values(3,'fred');
Query OK, 1 row affected (0.00 sec)
mysql> show binary logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| APP01bin.000001 | 409 |
| APP01bin.000002 | 363 |
+-----------------+-----------+
mysql> show binlog events;
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| APP01bin.000001 | 4 | Format_desc | 11 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |
| APP01bin.000001 | 120 | Query | 11 | 213 | BEGIN |
| APP01bin.000001 | 213 | Query | 11 | 332 | use `replication`; insert into tb values(2,'jack') |
| APP01bin.000001 | 332 | Xid | 11 | 363 | COMMIT /* xid=382 */ |
| APP01bin.000001 | 363 | Rotate | 11 | 409 | APP01bin.000002;pos=4 |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
-- 在上面的结果中第3行可以看到我们执行的SQL语句，第4行为自动提交
-- Author : Leshamib、查看指定binlog文件的内容(show binlog events in ‘binname.xxxxx’)
mysql> show binlog events in 'APP01bin.000002';
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| APP01bin.000002 | 4 | Format_desc | 11 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |
| APP01bin.000002 | 120 | Query | 11 | 213 | BEGIN |
| APP01bin.000002 | 213 | Query | 11 | 332 | use `replication`; insert into tb values(3,'fred') |
| APP01bin.000002 | 332 | Xid | 11 | 363 | COMMIT /* xid=394 */ |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+c、查看当前正在写入的binlog文件(show master status\G)
mysql> show master status\G
*************************** 1. row ***************************
File: APP01bin.000002
Position: 363
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)d、获取指定位置binlog的内容(show binlog events from)
mysql> show binlog events from 213;
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+
| APP01bin.000001 | 213 | Query | 11 | 332 | use `replication`; insert into tb values(2,'jack') |
| APP01bin.000001 | 332 | Xid | 11 | 363 | COMMIT /* xid=382 */ |
| APP01bin.000001 | 363 | Rotate | 11 | 409 | APP01bin.000002;pos=4 |
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+演示mysqlbinlog方式提取binlog
a、提取指定的binlog日志
# mysqlbinlog /opt/data/APP01bin.000001
# mysqlbinlog /opt/data/APP01bin.000001|grep insert
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
insert into tb values(2,'jack')
b、提取指定position位置的binlog日志
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001
c、提取指定position位置的binlog日志并输出到压缩文件
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 |gzip >extra_01.sql.gz
d、提取指定position位置的binlog日志导入数据库
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 | mysql -uroot -p
e、提取指定开始时间的binlog并输出到日志文件
# mysqlbinlog --start-datetime="2014-12-15 20:15:23" /opt/data/APP01bin.000002 --result-file=extra02.sql
f、提取指定位置的多个binlog日志文件
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 /opt/data/APP01bin.000002|more
g、提取指定数据库binlog并转换字符集到UTF8
# mysqlbinlog --database=test --set-charset=utf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 >test.sql
h、远程提取日志，指定结束时间
# mysqlbinlog -urobin -p -h192.168.1.116 -P3306 --stop-datetime="2014-12-15 20:30:23" --read-from-remote-server mysql-bin.000033 |more
i、远程提取使用row格式的binlog日志并输出到本地文件
# mysqlbinlog -urobin -p -P3606 -h192.168.1.177 --read-from-remote-server -vv inst3606bin.000005 >row.sql本篇总
结：
- 使用分割日志的方式进行增量备份。
- 增量恢复需要根据日志文件的时间先后，逐个执行。
- 使用基于时间和位置的方式进行恢复，可以更精确地恢复数据。
- 大企业应每周做一次全备，每天做一次增量备份；中小企业应每天进行一次全备。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
