Mysql增量恢复必备条件

1.开启mysql log-bin日志功能

MySQL数据库开启了log-bin 参数记录binlog日志功能下：

小结：增量恢复的条件：

存在一份全备加上全备之后的时刻到出问题时刻的所有增量binlog文件备份。

创建模拟环境

[root@mysql-db02 3306]# grep log-bin /data/3306/my.cnf

log-bin = /data/3306/mysql-bin

[root@mysql-db02 3306]#

mkdir -p /server/backup/

备份全量数据数据

[root@mysql-db02 backup]# mysqldump -S /data/3306/mysql.sock -B -F -R -x --master-data=http://www.mamicode.com/2

test |gzip >/server/backup/test_$(date +%F).sql.gz

(X与x区别：X为导出xml格式文件  x锁表，笔者在这坑了很久，告请大家值得注意）

[root@mysql-db02 backup]# ls

test_2017-05-23.sql.gz

[root@mysql-db02 backup]#

插入新增数据数据 

mysql> select *  from cc;

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

| id | name |

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

|  1 | cc1  |

|  2 | cc2  |

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

2 rows in set (0.00 sec)

mysql> insert into cc values(3,‘xuexue‘);

Query OK, 1 row affected (0.00 sec)

mysql> insert into cc values(4,‘chenpeng‘);

Query OK, 1 row affected (0.00 sec)

mysql> insert into cc values(5,‘zhangya‘);

Query OK, 1 row affected (0.00 sec)

mysql> select *  from cc;

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

| id | name     |

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

|  1 | cc1      |

|  2 | cc2      |

|  3 | xuexue   |

|  4 | chenpeng |

|  5 | zhangya  |

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

5 rows in set (0.00 sec)

mysql> quit

模拟更改时间删除数据

[root@mysql-db02 backup]# date -s ‘2017/5/23 10:00:00‘

Tue May 23 10:00:00 CST 2017

[root@mysql-db02 backup]# mysql -S /data/3306/mysql.sock

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 23

Server version: 5.6.34-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> drop database test;

Query OK, 1 row affected (0.01 sec)

mysql> show  databases;

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

| Database           |

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

| information_schema |

| cc2                |

| mysql              |

| performance_schema |

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

4 rows in set (0.00 sec)

mysql> quit

发现错误查看binlog文件 如果不知binlog文件是哪个 可采取以下措施

[root@mysql-db02 backup]# gzip -d test_2017-05-23.sql.gz

[root@mysql-db02 backup]# grep CHANGE test_2017-05-23.sql.gz

[root@mysql-db02 backup]# grep CHANGE test_2017-05-23.sql

-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000027‘, MASTER_LOG_POS=120;

[root@mysql-db02 backup]#

故障之后一定要将binlog拿走 要不会写入增量备份的sql

增量备份条件需要全备与增备 条件具备才可以玩 将增量备份拷入backup目录下:

[root@mysql-db02 backup]# cp /data/3306/mysql-bin.000027 .

[root@mysql-db02 backup]# ls

mysql-bin.000027  test_2017-05-23.sql  

由于数据库没了  新生成的binlog也不会写入  可以对新生成的binlog不进行关注

生成binlog备份 注意binlog顺序

[root@mysql-db02 backup]# mysqlbinlog -d test mysql-bin.000027 >27bin.sql （如有很多可以列在后面，太多的话可以采用匹配的方式）

[root@mysql-db02 backup]# ls

27bin.sql         test_2017-05-23.sql

mysql-bin.000027  test_2017-05-24.sql.gz

[root@mysql-db02 backup]#

[root@mysql-db02 backup]# vim 27bin.sql

[root@mysql-db02 backup]#

删除里面drop语句 如果不删除  又恢复到删除前状态

恢复过程

先恢复全备

mysql -uroot -poldboy123 -S /data/3306/mysql.sock <test_2017-05-23.sql

mysql> select *  from cc;

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

| id | name |

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

|  1 | cc1  |

|  2 | cc2  |

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

2 rows in set (0.00 sec)

再恢复增倍

mysql -uroot -poldboy123 -S /data/3306/mysql.sock <mysql-bin.000027

mysql> select *  from cc;

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

| id | name     |

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

|  1 | cc1      |

|  2 | cc2      |

|  3 | xuexue   |

|  4 | chenpeng |

|  5 | zhangya  |

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

5 rows in set (0.00 sec)

至此增量备份完成

Mysqldump重要参数！

-B 导出建表语句 备份多个库

-A 备份所有的数据库

--master-data=http://www.mamicode.com/{1|2} 添加一条语句，change master

刷新binlog

-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000010‘, MASTER_LOG_POS=1137;

single-transaction 在备份时间 会增加一个新的进程  将备份期间写入库的内容放到新日志里，备份结束后再写入

innodb的引擎支持

本文出自 “cc” 博客，谢绝转载！

mysqldunp增量恢复过程及详解