MySQL备份工具之mysqlbinlog

2019独角兽企业重金招聘Python工程师标准>>>

前言

增量备份 是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。这种备份方式最显著的优点就是：没有重复的备份数据，因此备份的数据量不大，备份所需的时间很短。但增量备份的数据恢复是比较麻烦的。您必须具有上一次全备份和所有增量备份磁带（一旦丢失或损坏其中的一个增量，就会造成恢复的失败），并且它们必须沿着从全备份到依次增量备份的时间顺序逐个反推恢复，因此这就极大地延长了恢复时间。

 

 

一、binlog简介

binlog日志由配置文件的 log-bin 选项负责启用，MySQL服务器将在数据根目录创建两个新文 件XXX-bin.001和xxx-bin.index，若配置选项没有给出文件名，Mysql将使用主机名称命名这两个文件，其中.index文件包含一份全体日志文件的清单。  

 

Mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件，而不会记录 SELECT和没有实际更新的UPDATE语句。

当MySQL数据库停止或重启时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的binlog日志文件，文件序号递增，此外，如果日志文件超过max_binlog_size系统变量配置的上限时，也会生成新的日志文件。

 

 

二、binlog日志操作

 

1、修改 MySQL 的配置文件my.cnf 如下：

[mysqld] 

log-bin = mysql-bin 

binlog_format = row

 

其中：log-bin若不显示指定存储目录，则默认存储在mysql的data目录下 

 

2、一些常用操作

mysql> show master logs;  #查看数据库所有日志文件。 

mysql> show binlog events in 'mysql-bin.000007';  #查看当前使用的binlog文件信息。 

mysql> show binlog events in 'mysql-bin.000016';  #查看指定的binlog文件信息。 

mysql> flush logs;  #将内存中log日志写磁盘，保存在当前binlog文件中，并产生一个新的binlog日志文件。 

mysql> flush logs; reset master;  #删除所有二进制日志，并重新（mysql-bin.000001）开始记录。

 

三、MySQL备份实例（全备 + 基于 binlog的增备）

示例采用小数据量进行模拟，包含一份全备及两份增备，主要演示下备份还原过程，工程中可根据数据实际情况进行备份还原策略调整。

1、查看当前数据库binlog文件

·         通过mysql客户端查看:

mysql> show master logs; 

+------------------+-----------+ 

| Log_name         | File_size | 

+------------------+-----------+ 

| mysql-bin.000001 |       107 | 

+------------------+-----------+

·       通过linux命令行直接查看（mysql数据目录data）

[root@localhost data]# ll -h 

total 5.1G 

-rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ibdata1 

-rw-rw---- 1 mysql mysql 2.0G Jul 18 14:12 ibdata2 

-rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ib_logfile0 

-rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ib_logfile1 

drwxr-xr-x 2 mysql mysql 4.0K Jul 18 13:52 mysql 

-rw-rw---- 1 mysql mysql  107 Jul 23 13:29 mysql-bin.000001 

-rw-rw---- 1 mysql mysql   19 Jul 23 13:29 mysql-bin.index 

srwxrwxrwx 1 mysql mysql    0 Jul 18 14:14 mysql.sock 

drwx------ 2 mysql mysql 4.0K Jul 18 14:01 performance_schema 

-rw-rw---- 1 mysql mysql  483 Jul 23 13:29 R820-08.err 

-rw-rw---- 1 mysql mysql    5 Jul 18 14:14 R820-08.pid 

drwx------ 2 mysql mysql   19 Jul 22 23:15 test 

 目前只有一个binlog文件mysql-bin.000001。

 

3.2 准备全量数据

mysql> create database backup_full; 

mysql> create table full (c1 int(10), c2 varchar(20)) engine=innodb; 

mysql> insert into full values (1, 'full1'),(2, 'full2'),(3, 'full3'),(4, 'full4'),  (5, 'full5'),(6, 'full6'),(7, 'full7'),(8, 'full8'),(9, 'full9'),(10, 'full10');

 

3.3 将全量数据进行备份

步骤如下：

a.备份前需要将数据库加读锁，防止数据在备份时写入。  

mysql> flush tables with read lock; 

b.通过命令flush logs;将log日志刷盘，写入当前binlog（mysql-bin.000001），在生成一个新的binlog（mysql-bin.000002）为增备做准备。 

mysql> flush logs;

c.进行数据备份。在linux命令行下执行：

[root]mysqldump  -u用户名 -p密码 -hIP地址 -P端口 数据库名 > /tmp/backup_full.sql 

[root]mysqldump  -uroot -pjesse -h127.0.0.1 -P3355 buckup_full > /tmp/backup_full.sql

d.解除表锁。 

mysql> unlock tables;

至此全量备份全部结束，将全量数据文件buckup_full.sql保存即可。数据库再有新的数据更新会记录在新的binlog（mysql-bin.000002）里面。

 

3.4 准备第一份增量数据

mysql> create database backup_increment; 

mysql> use backup_increment; 

mysql> create table increment (c1 int(10), c2 varchar(20)) engine=innodb; 

mysql> insert into increment values (11, 'increment1'),(12, 'increment2'),(13, 'increment3'),(14, 'increment4'),(15, 'increment5');

 

3.5 将第一份增量数据进行备份

步骤如下：

a.备份前需要将数据库加读锁，防止数据在备份时写入。

mysql> flush tables with read lock;

b.通过命令flush logs;将log日志刷盘，写入当前binlog（mysql-bin.000002），在生成一个新的binlog（mysql-bin.000003）为下次增备做准备。 

mysql> flush logs;

c.将binlog第一个增备文件mysql-bin.000002直接复制保存即可。        也可以将二进制文件导出到文本文件保存，在linux命令行下执行

[root ]mysqlbinlog  mysql-bin.000002 > /tmp/increment1.txt

d.解除表锁。 

mysql> unlock tables;

至此第一个增量备份全部结束，将增量binlog文件mysql-bin.000002或者有binlog文件导出的文本文件/tmp/increment1.txt保存即可。数据库再有新的数据更新会记录在新的binlog（mysql-bin.000003）里面。

 

3.6 准备第二份增量数据

mysql> use backup_increment; 

mysql> insert into increment values (16, 'increment16'),(17, 'increment17'),(18, 'increment18'),(19, 'increment19'),(20, 'increment20');

 

3.7 将第二份增量数据进行备份

步骤如下：

同上 

四、mysql还原实例分析（全备还原+基于binlog的增备还原）

模拟数据库故障，即删除全备数据及增备数据库。

mysql> drop table backup_full.full; 

mysql> drop database backup_increment;

此时数据库数据被清空。

mysql> show databases; 

+--------------------+ 

| Database           | 

+--------------------+ 

| information_schema | 

| backup_full        | 

| mysql              | 

| performance_schema | 

| test               | 

+--------------------+ 

   5 rows in set (0.00 sec) 



mysql> select * from backup_full.full; 

ERROR 1146 (42S02): Table 'backup_full.full' doesn't exist 

mysql> select * from backup_increment.increment; 

ERROR 1146 (42S02): Table 'backup_increment.increment' doesn't exist

 

4.1 还原全备数据

·          方法：直接还原数据文件。

mysql -u用户名 -p密码  -hIP地址    -P端口 数据库名     < /tmp/buckup_full.sql 

mysql -uroot   -pjesse -h127.0.0.1 -P3355 backup_full  < /tmp/buckup_full.sql 

mysql> select * from backup_full.full; 

+------+--------+ 

| c1   | c2     | 

+------+--------+ 

|    1 | full1  | 

|    2 | full2  | 

|    3 | full3  | 

|    4 | full4  | 

|    5 | full5  | 

|    6 | full6  | 

|    7 | full7  | 

|    8 | full8  | 

|    9 | full9  | 

|   10 | full10 | 

+------+--------+

 

4.2 还原第一个增备文件

·         方法：通过binlog直接还原

在linux命令行下执行：

[root]mysqlbinlog  binlog文件名 | mysql -u用户名 -p密码 -hIP地址 -P端口 

[root]mysqlbinlog  mysql-bin.000002 | mysql -uroot -pjesse -h127.0.0.1 -P3355 

查看数据：

mysql> select * from backup_increment.increment; 

+------+------------+ 

| c1   | c2         | 

+------+------------+ 

|   11 | increment1 | 

|   12 | increment2 | 

|   13 | increment3 | 

|   14 | increment4 | 

|   15 | increment5 | 

+------+------------+

 第一份增量数据还原成功！

4.3 还原第二个增备文件（方法同上）

在linux命令行下执行：

[root]mysqlbinlog  binlog文件名 | mysql -u用户名 -p密码 -hIP地址 -P端口 

[root]mysqlbinlog  mysql-bin.000003 | mysql -uroot -pjesse -h127.0.0.1 -P3355 

查看数据：

mysql> select * from backup_increment.increment; 

+------+-------------+ 

| c1   | c2          | 

+------+-------------+ 

|   11 | increment1  | 

|   12 | increment2  | 

|   13 | increment3  | 

|   14 | increment4  | 

|   15 | increment5  | 

|   16 | increment16 | 

|   17 | increment17 | 

|   18 | increment18 | 

|   19 | increment19 | 

|   20 | increment20 | 

+------+-------------+

 至此数据全部还原成功！

 

4.4 按时间点和位置点恢复

 

按位置点信息恢复

[root@bogon ~]# mysqlbinlog --start-position=259 --stop-position=561 /data/mysql/mysql3306/logs/mysql-bin.000003 |mysql -uroot -p123456

 

按时间点恢复

[root@bogon ~]# mysqlbinlog --start-datetime="2016-11-18 6:10:34" --stop-datetime="2016-11-18 6:11:13" /data/mysql/mysql3306/logs/mysql-bin.000003 |mysql -uroot -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

 

 

参考文档：

http://www.cnblogs.com/luoyx/p/5896419.html

http://blog.csdn.net/jesseyoung/article/details/41211841

为了方便大家交流，本人开通了微信公众号，和QQ群291519319。喜欢技术的一起来交流吧

转载于:https://my.oschina.net/u/3023401/blog/808174