MySQL Incremental recovery

MySQL Incremental recovery process

Create a table for testing

Mysql> CREATE table User (name char (ten), 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)

Inserting data into a table

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 | |+------+------+1 row in Set (0.00 sec)

Back up the database and view the MySQL binary information before backing it up

[[EMAIL PROTECTED] ~]# 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[[email protected] ~]# mysqldump -uroot -p -h 127.0.0.1  --flush-logs git >git.bak.sqlenter password: [[email protected] ~]# 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 

Test: Continue to insert data into table user

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 | |+--------+------+3 rows in Set (0.00 sec)

Update Age Column 19

mysql> update user set age=19; Query OK, 3 rows affected (0.01 sec) rows matched:3 changed:3 warnings:0mysql> select * from user;+--------+------+ | name | Age |+--------+------+|   Lyao | 19 | |   Zhang3 | 19 | |   Li4 | |+--------+------+3 rows in Set (0.00 sec)

At this point, the Age column is all updated, but that's not what we want. Restore with Backup data

mysql> source  git.bak.sql;mysql> select * from user;+------+------+|  name | age  |+------+------+| lyao |   27 |+------+--- ---+1 row in set  (0.00 sec) #还原到备份前的数据了, but we also insert into the 2 data, this will be restored through the binary log # The Log-bin log after the backup is shown above is Mysql-bin.000006[[email protected] ~]# mysqlbinlog  -d git  /data/mysql/data/mysql-bin.000006 >bin.log# found the statement we just inserted in the Bin.log [[email protected] ~]#  egrep  "Zhang3|li4"  bin.log insert into user values (' zhang3 ', ' a ') insert  into user values (' Li4 ', ' #在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)

This article is from the "ly36843" blog, please be sure to keep this source http://ly36843.blog.51cto.com/3120113/1652333

MySQL Incremental recovery