mysql增量恢复

gerr3

mysql增量恢复过程

创建一张用于测试的表

1 2 3 4 5 6 7 8 9 10 11

mysql> create table user (name char(10),age int(3)); Query OK, 0 rows affected (0.07 sec) mysql> desc user; +-------+----------+------+-----+---------+-------+ | Field | Type     | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | name  | char(10) | YES  |     | NULL    |       | | age   | int(3)   | YES  |     | NULL    |       | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec)

往表中插入数据

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

mysql> select database(); +------------+ | database() | +------------+ | git        | +------------+ 1 row in set (0.00 sec) mysql> insert into user('lyao','27'); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +------+------+ | name | age  | +------+------+ | lyao |   27 | +------+------+ 1 row in set (0.00 sec)

备份数据库,备份前查看mysql的二进制文件信息

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

[iyunv@mysql-m ~]# ll /data/mysql/data/ |grep mysql-bin -rw-rw---- 1 mysql mysql      302 May 18 11:10 mysql-bin.000001 -rw-rw---- 1 mysql mysql      448 May 18 11:20 mysql-bin.000002 -rw-rw---- 1 mysql mysql      221 May 18 11:21 mysql-bin.000003 -rw-rw---- 1 mysql mysql      150 May 18 11:21 mysql-bin.000004 -rw-rw---- 1 mysql mysql      716 May 18 12:47 mysql-bin.000005 -rw-rw---- 1 mysql mysql       95 May 18 11:21 mysql-bin.index [iyunv@mysql-m ~]# mysqldump -uroot -p -h 127.0.0.1 --flush-logs git >git.bak.sql Enter password: [iyunv@mysql-m ~]# ll /data/mysql/data/ |grep mysql-bin -rw-rw---- 1 mysql mysql      302 May 18 11:10 mysql-bin.000001 -rw-rw---- 1 mysql mysql      448 May 18 11:20 mysql-bin.000002 -rw-rw---- 1 mysql mysql      221 May 18 11:21 mysql-bin.000003 -rw-rw---- 1 mysql mysql      150 May 18 11:21 mysql-bin.000004 -rw-rw---- 1 mysql mysql      759 May 18 12:52 mysql-bin.000005 -rw-rw---- 1 mysql mysql      107 May 18 12:52 mysql-bin.000006 -rw-rw---- 1 mysql mysql      114 May 18 12:52 mysql-bin.index

测试：往表user中继续insert数据

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

mysql> insert into user values('zhang3','34'); Query OK, 1 row affected (0.01 sec) mysql> insert into user values('li4','34'); Query OK, 1 row affected (0.00 sec) mysql> select *  from user; +--------+------+ | name   | age  | +--------+------+ | lyao   |   27 | | zhang3 |   34 | | li4    |   34 | +--------+------+ 3 rows in set (0.00 sec)

更新age列为19

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

mysql> update user set age=19; Query OK, 3 rows affected (0.01 sec) Rows matched: 3  Changed: 3  Warnings: 0 mysql> select * from user; +--------+------+ | name   | age  | +--------+------+ | lyao   |   19 | | zhang3 |   19 | | li4    |   19 | +--------+------+ 3 rows in set (0.00 sec)

此时看到，age列全部更新了，但是这不是我们想要的。通过备份数据还原

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

mysql> source  git.bak.sql; mysql> select * from user; +------+------+ | name | age  | +------+------+ | lyao |   27 | +------+------+ 1 row in set (0.00 sec) #还原到备份前的数据了，但是我们后来还insert进去2条数据的，这个要通过二进制日志恢复了 #上文中显示备份后的log-bin日志是mysql-bin.000006 [iyunv@mysql-m ~]# mysqlbinlog  -d git /data/mysql/data/mysql-bin.000006 >bin.log #在bin.log中找到我们刚才插入的语句 [iyunv@mysql-m ~]# egrep "zhang3|li4" bin.log insert into user values('zhang3','34') insert into user values('li4','34') #在mysql中插入这2条语句即可 mysql> select * from user; +------+------+ | name | age  | +------+------+ | lyao |   27 | +------+------+ 1 row in set (0.00 sec) mysql> insert into user values('zhang3','34'); Query OK, 1 row affected (0.02 sec) mysql> insert into user values('li4','34'); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +--------+------+ | name   | age  | +--------+------+ | lyao   |   27 | | zhang3 |   34 | | li4    |   34 | +--------+------+ 3 rows in set (0.00 sec)