用binlog做mysql备份_Mysql使用mysqldump和mysqlbinlog进行备份

1. 全量备份和增量备份

备份策略总的来说分为两种：全量备份和增量备份。

全量备份：每次备份都备份当前系统中的所有数据。

增量备份：备份当前时间点的数据与上次备份时间点数据的差异。

2. 使用mysqldump进行全量备份

mysqldump是mysql提供的一种全量备份的工具，下面是一个例子：

mysqldump -uroot -p123456 db1 > db1.sql

上面的命令将db1中的数据导出为db1.sql文件。

如果要备份所有数据库中的数据，可以使用如下命令：

mysqldump -uroot -p123456 --all-databases > all_databases.sql

从sql文件中恢复数据库：

mysql -uroot -p123456

或者登陆mysql后，使用source命令导入：

mysql>source /data/backup/db1.sql

3. 使用mysqlbinlog进行增量备份

MySQL增量备份主要通过二进制日志文件进行增量备份。MySQL数据库会以二进制形式自动把用户对MySQL数据库的操作记录到二进制日志文件，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句执行所消耗的时间，当用户希望恢复的时候可以使用备份文件进行恢复。

记录二进制日志文件最主要的使用场景：

Mysql Replication时，在Master端开启binlog，Master将其记录了数据操作的二进制文件传递给Slave，Slave通过此日志文件进行数据复制，从而实现主从一致。

通过mysqlbinlog工具进行数据恢复

使用mysqlbinlog进行数据恢复前，首先要开启mysql的binlog配置：

log_bin = mysql_bin

server_id = 1

开启log_bin时，server_id也必须指定，否则无法启动mysql服务。

重启mysql服务，即开启了记录二进制日志的功能。

使用以下命令查看log：

image.png

image.png

Binlog文件默认存储在/var/lib/mysql目录下。

4. 备份实践

首先使用mysqldump进行全量备份：

mysqldump -uroot -p123456 --single-transaction --master-data=1 db1 > db1.sql

默认情况下mysqldump会对表加锁，而使用--single-transaction会将备份的操作放在一个事务中进行，从而避免了加表锁。

--master-data=1会在dump出的sql文件中记录Binlog文件和位置。使用--master-data=1参数后sql文件中会出现下面这行：

CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=154;

这意味着当前dump记录到mysql_bin.000001日志文件的154偏移量处。

如果将--master-data选项值设置为2，CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=154;依旧存在，然而会被加上注释。

CHANGE MASTER TO语句主要用于主从同步。当为一个已经存在了数据的mysql server添加slave时，首先要先将数据dump出，然后再将dump出的sql文件导入到Slave中。此后，Slave定时读取Master的Binlog，从而进行同步。因此通过CHANGE MASTER TO语句，能够帮助Slave识别Binlog临界文件，从而确定要复制的Binlog起点。

查看mysql_bin.000001日志文件：

image.png

在将dump的sql导入到数据库中后，我们还需要将此dump之后的数据变更也恢复，此时就需要使用mysqlbinlog工具, 一个例子如下：

mysqlbinlog --start-position=154 --stop-position=719 mysql_bin.000001|mysql -uroot -p123456

上面的语句恢复了偏移量154-719之间的数据操作。mysqlbinlog会停止阅读偏移量大于等于stop-position的event。

mysqlbinlog也可以通过时间点进行恢复：

mysqlbinlog --start-datetime=’ 2017-08-31 00:00:00’ --stop-datetime=’ 2017-08-31 12:00:00’ mysql_bin.000002|mysql -uroot -p123456