Mysql-binlog Log Recovery Database

Mysqlbinlog common options include the following:
--start-datetime: Reads from the binary log specify a time equal to the timestamp or later than the local computer
--stop-datetime: Reads from binary log specified less than timestamp or equal to local computer time value is the same as above
--start-position: Starts by reading the specified position event location from the binary log.
--stop-position: Reads the specified position event location from the binary log as an event as of

--database: Specifying the Recovery library

Column 1:

mysqlbinlog--stop-position=11036821--database=rzzl_lease/opt/apps/data/mysqldata/mysql-bin.000322 | mysql- Uroot-pcolumn 2:mysqlbinlog--start-datetime= ' 2016-08-16 14:11:54 ' mysql-bin.000008 | mysql-uroot-p

The Binlog log is used to record all statements that have updated data or that have potentially updated data. Statements are saved as "events," which describe data changes. When we fail the database for some reason, we can use the Binlog log to retrieve it (provided the Binlog is already configured), then we configure

First, open the Mysql-binlog log

In MySQL configuration file my.cnf Add the following configuration

[Mysqld]log-bin=mysql-bin

Restart MySQL

Service mysqld Restart

Second, back up the database

1) First look at the current database situation

Mysql> SELECT * FROM t1;+------+| ID   |+------+|    1 | |    2 |+------+2 rows in Set (0.00 sec)

2) Back up data to/tmp/test.sql

[Email protected] ~]# Whereis mysqldumpmysqldump:/usr/bin/mysqldump/usr/share/man/man1/mysqldump.1.gz[[email Protected] ~]#/usr/bin/mysqldump-uroot-p123456 Test >/tmp/test.sql

Three, then analog error operation (insert 3 data, delete database)

mysql> INSERT INTO T1 values (3); Query OK, 1 row Affected (0.00 sec) mysql> insert into T1 values (4); Query OK, 1 row Affected (0.00 sec) mysql> insert into T1 values (5); Query OK, 1 row Affected (0.00 sec) mysql> flush logs; #关闭当前的二进制日志文件并创建一个新文件, the name of the new binary log file is added 1 to the current binary file number. Query OK, 0 rows affected (0.05 sec)

Mysql> Show Master status;+------------------+----------+--------------+------------------+| File             | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000002 |      106 |              |                  | +------------------+----------+--------------+------------------+1 row in Set (0.01 sec)

Delete data

mysql> truncate t1; Query OK, 0 rows Affected (0.00 sec) mysql> select * from T1; Empty Set (0.01 sec) mysql> Show tables;+----------------+| Tables_in_test |+----------------+| T1             |+----------------+1 row in Set (0.00 sec)

Suddenly the database is corrupted or artificially deleted.

mysql> drop table T1; Query OK, 0 rows Affected (0.00 sec) mysql> Show tables; Empty Set (0.00 sec)

Iv. The database has been completely destroyed at this time

1) Recover data with/tmp/test.sql already backed up

[Email protected]]# mysql-uroot-p123456 test </tmp/test.sql[[email protected]]# mysql-uroot-p123456 test

Mysql> Show tables;+----------------+| Tables_in_test |+----------------+| T1             |+----------------+1 row in Set (0.00 sec) mysql> SELECT * FROM t1;+------+| ID   |+------+|    1 | |    2 |+------+2 rows in Set (0.00 sec)

2) There are three more data not recovered, what to do. Can only be recovered with Bin-log.

[[Email protected]]#/usr/bin/mysqlbinlog--no-defaults/var/lib/mysql/mysql-bin.000001 | More[[email protected]]#/usr/bin/mysqlbinlog--no-defaults/var/lib/mysql/mysql-bin.000001 | /usr/bin/mysql-uroot-p123456 Test

Mysql> SELECT * FROM t1;+------+| ID   |+------+|    1 | |    2 | |    3 | |    4 | |    5 |+------+5 rows in Set (0.00 sec)

3) Successful Recovery

"Summary": MySQL Backup and Bin-log logs

Backup data:

mysqldump-uroot-p123456 test-l-F '/tmp/test.sql '-L: Read lock (can only be read, cannot be updated)-F: Flush logs, you can regenerate new log files, including, of course, Log-bin logs

To view the Binlog log:

Mysql>show Master Status

Back up data before importing:

mysql-uroot-p123456 test-v-F </tmp/test.sql-v View the details of the import-F is when an error is encountered in the middle, you can skip over and continue executing the following statement

To restore the binlog-file binary log file:

Mysqlbinlog--no-defaults Binlog-file | mysql-uroot-p123456

Start recovery from a point (367):

Mysqlbinlog--no-defaults--stop-position= "367" mysql-bin.000001| mysql-uroot-p123456 Test

Check that out first (use more to see)

[Email protected] mysql]#/usr/bin/mysqlbinlog--no-defaults mysql-bin.000002--start-position= "794"--stop-position = "1055" | More

Then restore:

[Email protected] mysql]#/usr/bin/mysqlbinlog--no-defaults mysql-bin.000002--start-position= "794"--stop-position = "1055" | /usr/bin/mysql-uroot-p123456 Test

Reset Binlog Log

mysql> Reset Master; Query OK, 0 rows affected (0.01 sec) mysql> Show Master status;+------------------+----------+--------------+-------- ----------+| File             | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000001 |      106 |              |                  | +------------------+----------+--------------+------------------+

mysql> flush logs; #关闭当前的二进制日志文件并创建一个新文件, the name of the new binary log file is added 1 to the number of the current binary file.

Mysql-binlog Log Recovery Database