MySQL数据库备份还原（基于binlog的增量备份）

一、简介

1、增量备份

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

      假如我们有一个数据库，有20G的数据，每天会增加10M的数据，数据库每天都要全量备份一次，这样的话服务器的压力比较大，因此我们只需要备份增加的这部分数据，这样减少服务器的负担。

2、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日志操作

2.1、开启binlog日志

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

1 2 3

[mysqld]  log-bin = mysql-bin  binlog_format = row

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

    binlog_format的几种格式：（STATEMENT，ROW和MIXED），

 STATEMENT：基于SQL语句的复制(statement-based replication, SBR)    

ROW：基于行的复制(row-based replication, RBR)    

MIXED：混合模式复制(mixed-based replication, MBR)

    启动后会产生mysql-bin.*这样的文件，每启动一次，就会增加一个或者多个。

 

1 2 3 4

[root@localhost data] # ls -l| grep mysql-bin  02.-rw-rw----. 1 mysql mysql       107 Jul  5 11:19 mysql-bin.000001  03.-rw-rw----. 1 mysql mysql        19 Jul  5 11:19 mysql-bin.index  04.[root@localhost data] #

• 查看binlog开启情况

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> show variables  like  'log_bin%' ;  02.+ ---------------------------------+-------+  03.| Variable_name                   | Value |  04.+ ---------------------------------+-------+  05.| log_bin                         |  ON     |  06.| log_bin_trust_function_creators |  OFF    |  07.+ ---------------------------------+-------+  08.mysql> show variables  like  'binlog%' ;  09.+ -----------------------------------------+-------+  10.| Variable_name                           | Value |  11.+ -----------------------------------------+-------+  12.| binlog_cache_size                       | 32768 |  13.| binlog_direct_non_transactional_updates |  OFF    |  14.| binlog_format                           | ROW |  15.| binlog_stmt_cache_size                  | 32768 |  16.+ -----------------------------------------+-------+

 

 

2.2、查看binlog日志内容

1 2	[root@localhost data] # mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 ;  02.mysqlbinlog: unknown variable  'default-character-set=utf8'

 这里我们碰到了mysqlbinlog的一个bug，解决方法有两个：

• 方法一：使用--no-defaults选项

 

1	[root@localhost data] # mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001

 

• 方法二：将my.cnf中[client]选项组中default-character-set=utf8选项临时屏蔽掉（该选项即时生效，不用重启数据库）,使用完mysqlbinlog命令时在恢复。因为使用mysqlbinlog工具查看二进制日志时会重新读取的mysql的配置文件my.cnf(windows下是my.ini),而不是服务器已经加载进内存的配置文件。

 

1 2 3 4 5 6 7 8 9

# at 188  #140705 11:23:55 server id 1  end_log_pos 271   Query   thread_id=1 exec_time=0 error_code=0  SET TIMESTAMP=1404573835/*!*/;  create database  test  /*!*/;  DELIMITER ;  # End of log file  ROLLBACK /* added by mysqlbinlog */;  /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

包含以下要素：

    Position:位于文件中的位置，即第一行的（#at 4）和第二行的（log_pos 4），说明该事件记录从文件第4字节开始。      

    Timestamp:事件发生的时间戳，即第二行的（#070813 14:16:36）    

    Exec_time:事件的执行花费时间    

    Error_code:错误码     

    Type 事件类型：     

           Master ID:创建二进制事件的主机服务器ID     

           Master Pos:事件在原始二进制文件中的位置 

           Flags:标志信息

 

2.3 一些常用操作

1 2 3 4 5

mysql> show master logs;  #查看数据库所有日志文件。  mysql> show binlog events \g;  #查看当前使用的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的增备）

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

3.1 查看当前数据库binlog文件

• 通过mysql客户端查看:

1 2 3 4 5 6

mysql> show master logs;  02.+ ------------------+-----------+  03.| Log_name         | File_size |  04.+ ------------------+-----------+  05.| mysql-bin.000001 |       107 |  06.+ ------------------+-----------+

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14

[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 准备全量数据

 

1 2 3 4

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.备份前需要将数据库加读锁，防止数据在备份时写入。   

1	mysql> flush tables  with  read  lock;

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

1	mysql> flush logs;

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

1 2	[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.解除表锁。 

1	mysql> unlock tables;

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

 

3.4 准备第一份增量数据

 

1 2 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.备份前需要将数据库加读锁，防止数据在备份时写入。

1	mysql> flush tables  with  read  lock;

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

1	mysql> flush logs;

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

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

    d.解除表锁。 

1	mysql> unlock tables;

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

 

3.6 准备第二份增量数据

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

 

 

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

步骤如下：

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

1	mysql> flush tables  with  read  lock;

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

1	mysql> flush logs;

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

1	[root]mysqlbinlog  mysql-bin.000003 > /tmp/increment2.txt

    d.解除表锁。 

1	mysql> unlock tables;

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

 

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

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

1 2	mysql>  drop  table  backup_full. full ;  mysql>  drop  database  backup_increment;

此时数据库数据被清空。

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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 还原全备数据

•  方法1：进入数据库，通过source

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

mysql> use backup_full;  mysql> source /tmp/backup_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 |  + ------+--------+

 全量数据还原成功。

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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 还原第一个增备文件

•  方法一：通过文本文件还原

 

1 2 3 4 5 6 7 8 9 10 11

mysql> source /tmp/increment1.txt;  mysql>  select  *  from  backup_increment.increment;  + ------+------------+  | c1   | c2         |  + ------+------------+  |   11 | increment1 |  |   12 | increment2 |  |   13 | increment3 |  |   14 | increment4 |  |   15 | increment5 |  + ------+------------+

 

• 方法二：通过binlog直接还原

在linux命令行下执行：

1 2	[root]mysqlbinlog  binlog文件名 | mysql -u用户名 -p密码 -hIP地址 -P端口  [root]mysqlbinlog  mysql-bin.000002 | mysql -uroot -pjesse -h127.0.0.1 -P3355

 查看数据：

1 2 3 4 5 6 7 8 9 10

mysql>  select  *  from  backup_increment.increment;  + ------+------------+  | c1   | c2         |  + ------+------------+  |   11 | increment1 |  |   12 | increment2 |  |   13 | increment3 |  |   14 | increment4 |  |   15 | increment5 |  + ------+------------+

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

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

在linux命令行下执行：

1 2	[root]mysqlbinlog  binlog文件名 | mysql -u用户名 -p密码 -hIP地址 -P端口  [root]mysqlbinlog  mysql-bin.000003 | mysql -uroot -pjesse -h127.0.0.1 -P3355

 查看数据：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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 |  + ------+-------------+

 至此数据全部还原成功！

 以上内容来自 http://blog.csdn.net/jesseyoung/article/details/37106035 ，感谢作者的贡献 ！！