Example of operating binlog binary log data in Mysql

System environment:

Server System: CentOS 6.5 x86_64
Mysql version: Mysql 5.1

I. binlog introduction

1. binlog, that is, binary log, which records all changes in the database.
2. When the SQL statement of the database is changed, a record will be written at the end of the binlog, and the statement parser will be notified to complete the statement execution.
3. binlog format
1. Based on statements, it cannot be ensured that all statements are successfully executed in the slave database, such as update... Limit 1;
2. Based on rows, each time a row in binlog is initiated, the row-based format will be advantageous when performing a particularly complex update or delete operation.

2. Log on to mysql to view the binlog

1. Only view the content of the first binlog file

Mysql> show binlog events;
2. View the content of the specified binlog file

Mysql> show binlog events in 'binlog. 100 ';
3. View the binlog file currently being written

Mysql> show master status \ G;
4. Obtain the binlog file list

Mysql> show binary logs;

3. Use mysqlbinlog to view

1. Based on start/end time

[Root @ localhost var] #/usr/local/mysql/bin/mysqlbinlog -- no-defaults -- start-datetime = '2017-08-04 00:00:00 '-- stop-datetime =' 2014-08-13 23:59:59 '-d wordpress binlog.000007
2. Based on pos value

[Root @ localhost var] #/usr/local/mysql/bin/mysqlbinlog -- no-defaults -- start-position = 107 -- stop-position = 1000-d wordpress binlog.000007

Note:

1. Do not view the binlog file currently being written.
2. Do not add the-force parameter to force access
3. If the binlog format is in row mode, return the-vv parameter.

4. Use mysqlbinlog to display the binary result of the record and import it to a text file for future recovery.
The detailed process is as follows:
D: \ LAMP \ MYSQL5 \ data> mysqlbinlog -- start-position = 4 -- stop-position = 106 yueliangd
Ao_binglog.000001> c: \ test1.txt
Content of test1.txt:

/*! 40019 SET @ session. max_insert_delayed_threads = 0 */;
/*! 50003 SET @ OLD_COMPLETION_TYPE = @ COMPLETION_TYPE, COMPLETION_TYPE = 0 */;
DELIMITER /*! */;
# At 4
#7122 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.22-rc-community-log created 7122 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK /*! */;
DELIMITER;
# End of log file
ROLLBACK/* added by mysqlbinlog */;
/*! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE */;
Record of the second row:
D: \ LAMP \ MYSQL5 \ data> mysqlbinlog -- start-position = 106 -- stop-position = 134 yuelian
Gdao_binglog.000001> c :\\ test1.txt
The content of test1.txt is as follows:

/*! 40019 SET @ session. max_insert_delayed_threads = 0 */;
/*! 50003 SET @ OLD_COMPLETION_TYPE = @ COMPLETION_TYPE, COMPLETION_TYPE = 0 */;
DELIMITER /*! */;
# At 106
#7122 16:22:36 server id 1 end_log_pos 134 Intvar
SET INSERT_ID = 1 /*! */;
DELIMITER;
# End of log file
ROLLBACK/* added by mysqlbinlog */;
/*! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE */;

Row 3 records:
D: \ LAMP \ MYSQL5 \ data> mysqlbinlog -- start-position = 134 -- stop-position = 254 yuelian
Gdao_binglog.000001> c :\\ test1.txt
Content:
/*! 40019 SET @ session. max_insert_delayed_threads = 0 */;
/*! 50003 SET @ OLD_COMPLETION_TYPE = @ COMPLETION_TYPE, COMPLETION_TYPE = 0 */;
DELIMITER /*! */;
# At 134
#7122 16:55:31 server id 1 end_log_pos 254 Query thread_id = 1 exec_time = 0 error_code = 0
Use test /*! */;
Set timestamp = 1196585731 /*! */;
SET @ session. foreign_key_checks = 1, @ session. SQL _auto_is_null = 1, @ session. unique_checks = 1 /*! */;
SET @ session. SQL _mode = 1344274432 /*! */;
/*! \ C utf8 *//*! */;
SET @ session. character_set_client = 33, @ session. collation_connection = 33, @ session. collation_server = 33 /*! */;
Create table a1 (id int not null auto_increment primary key,
Str varchar (1000) engine = myisam /*! */;
DELIMITER;
# End of log file
ROLLBACK/* added by mysqlbinlog */;
/*! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE */;

/*! 40019 SET @ session. max_insert_delayed_threads = 0 */;

Record of row 4:
D: \ LAMP \ MYSQL5 \ data> mysqlbinlog -- start-position = 254 -- stop-position = 330 yuelian
Gdao_binglog.000001> c :\\ test1.txt
/*! 50003 SET @ OLD_COMPLETION_TYPE = @ COMPLETION_TYPE, COMPLETION_TYPE = 0 */;
DELIMITER /*! */;
# At 254
#7122 16:22:36 server id 1 end_log_pos 330 Query thread_id = 1 exec_time = 0 error_code = 0
Use test /*! */;
Set timestamp = 1196583756 /*! */;
SET @ session. foreign_key_checks = 1, @ session. SQL _auto_is_null = 1, @ session. unique_checks = 1 /*! */;
SET @ session. SQL _mode = 1344274432 /*! */;
/*! \ C utf8 *//*! */;
SET @ session. character_set_client = 33, @ session. collation_connection = 33, @ session. collation_server = 33 /*! */;
Use 'test'; insert into a1 (str) values ('I love you'), ('You love me ')/*! */;
DELIMITER;
# End of log file
ROLLBACK/* added by mysqlbinlog */;
/*! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE */;

5. View these things to restore data, not for fun. Therefore, we want to import the results to MYSQL.

D: \ LAMP \ MYSQL5 \ data> mysqlbinlog -- start-position = 134 -- stop-position = 330 yuelian
Gdao_binglog.000001 | mysql-uroot-p

Or
D: \ LAMP \ MYSQL5 \ data> mysqlbinlog -- start-position = 134 -- stop-position = 330 yuelian
Gdao_binglog.000001> test1.txt
Import data to MYSQL
Mysql> source c :\\ test1.txt
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Charset changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
6. View data:
Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| A1 |
+ ---------------- +
1 row in set (0.01 sec)

Mysql> select * from a1;
+ ---- + ------------- +
| Id | str |
+ ---- + ------------- +
| 1 | I love you |
| 2 | You love me |
+ ---- + ------------- +
2 rows in set (0.00 sec)
 
 
Export a mysqlbinlog file as an SQL file
Cd/usr/local/mysql
./Mysqlbinlog/usr/local/mysql/data/mysql-bin.000001>/opt/001. SQL
Export a mysql-bin.000001 log file to 001. SQL

You can use the -- start-date and -- stop-date options in the mysqlbinlog statement to specify the start and end times of the DATETIME format.
./Mysqlbinlog -- stop-date = "17:41:28"/usr/local/mysql/data/mysql-bin.000002>/opt/004. SQL
Export the log in the mysql-bin.000002 file as of 17:41:28 to 004. SQL
 
. /Mysqlbinlog -- start-date = "17:30:05" -- stop-date = "17:41:28"/usr/local/mysql/data/mysql-bin.000002/usr/local/mysql/data/mysql-bin.0000023 >/opt/004. SQL
---- If multiple binlog files exist, they are separated by spaces and placed in full paths.
 
./Mysqlbinlog -- start-date = "2009-04-10 17:30:05" -- stop-date = "2009-04-10 17:41:28"/usr/local/mysql/data/mysql-bin.000002 | mysql-u root-p123456
Or source/opt/004. SQL
Import the SQL statement from 17:30:05 to 17:41:28 to mysql in the mysql-bin.000002 log file