MySQLBinaryLog, also known as bin-log, is a binary log file generated by mysql execution changes. It has two main functions: * data recovery * master-slave database. Use
MySQL Binary Log, also known as bin-log, is a Binary Log file generated by mysql execution changes. It has two main functions: * data recovery * master-slave database. Use
MySQL Binary Log, also known as bin-log, is a Binary Log file generated by mysql execution changes. It has two main functions:
* Data Recovery
* Master/Slave databases. It is used to add, delete, modify, and maintain synchronization with the master node.
1. Enable binary log
You need to modify the mysql configuration file. The experiment environment in this article is win7. The configuration file is my. ini under the mysql installation directory \ MySQL Server 5.1, and add log_bin = mysql_bin.
Eg:
[Mysqld]
......
Log_bin = mysql_bin
......
Log_bin is the file name of the generated bin-log, and the suffix is 6-digit encoding. Starting from 000001, according to the above configuration, the generated file is:
Mysql_bin.000001
Mysql_bin.000002
......
After saving the configuration, restart the mysql server. Use show variables like '% bin %' to check whether bin-log is enabled,
2. view the generated binary log
Because bin-log is a binary file, it cannot be directly opened in the editor such as Notepad. mysql provides two ways to view it. Before introducing it, we should first add, delete, and modify the database, otherwise, the data in the log is a bit empty.
Create table bin (id int (10) primary key auto_increment, name varchar (255); (I have created a table before the test)
Insert into bin (name) values ('Orange ');
1. use the show binlog events in 'mysql _ bin.000001 'statement on the client to view the results. To make the sorting beautiful, add \ G at the end to make the results horizontally and vertically; statement Terminator.
Eg:
Mysql> show binlog events in 'mysql _ bin.000001 '\ G
...............
* *************************** 3. row ***************************
Log_name: mysql_bin.000001
Pos: 174
Event_type: Intvar
Server_id: 1
End_log_pos: 202
Info: INSERT_ID = 2
* *************************** 4. row ***************************
Log_name: mysql_bin.000001
Pos: 202
Event_type: Query
Server_id: 1
End_log_pos: 304
Info: use 'test'; insert into bin (name) values ('Orange ')
* *************************** 5. row ***************************
...............
Log_name: This log exists in that file. From the above, we can see that both logs exist in the same file as mysql_bin.000001.
Pos: Start position of log in bin-log
Event_type: log Type Information
Server_id: You can view the server_id in the configuration, indicating that the log is generated by that server.
End_log_pos: end position of log in bin-log
Info: some remarks of log, which can intuitively show what operations are performed.
2. using mysqlbinlog, a mysql tool, we need to know where bin-log has a hard disk. By default, Windows 7 contains the C: \ ProgramData \ MySQL Server 5.1 \ data folder ,, if this folder does not exist, you can locate it by using datadir = "C:/ProgramData/MySQL Server 5.1/Data/" in the configuration file. If not, then I will say, "the search functions of various systems are doing well! ". This viewing method is not as beautiful as follows:
C: \ ProgramData \ MySQL Server 5.1 \ data> mysqlbinlog mysql_bin.000001
/*! 40019 SET @ session. max_insert_delayed_threads = 0 */;
/*! 50003 SET @ OLD_COMPLETION_TYPE = @ COMPLETION_TYPE, COMPLETION_TYPE = 0 */;
DELIMITER /*! */;
# At 4
#140215 16:35:56 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.51-community-log created 140215 16:35:56 at startup
ROLLBACK /*! */;
Binlog'
Bytes
AAAAAAAAAAAAAAAAAADsyntQEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*! */;
# At 106
#140215 16:36:51 server id 1 end_log_pos 174 Query thread_id = 2 exec_time = 0 error_code = 0
Set timestamp = 1350290211 /*! */;
SET @ session. pseudo do_thread_id = 2 /*! */;
SET @ session. foreign_key_checks = 1, @ session. SQL _auto_is_null = 1, @ session. unique_checks = 1, @ session. autocommit = 1 /*! */;
SET @ session. SQL _mode = 1344274432 /*! */;
SET @ session. auto_increment_increment = 1, @ session. auto_increment_offset = 1 /*! */;
/*! \ C utf8 *//*! */;
SET @ session. character_set_client = 33, @ session. collation_connection = 33, @ session. collation_server = 33 /*! */;
SET @ session. lc_time_names = 0 /*! */;
SET @ session. collation_database = DEFAULT /*! */;
BEGIN
/*! */;
# At 174
#140215 16:36:51 server id 1 end_log_pos 202 Intvar
SET INSERT_ID = 3 /*! */;
# At 202
#140215 16:36:51 server id 1 end_log_pos 309 Query thread_id = 2 exec_time = 0 error_code = 0
Use test /*! */;
Set timestamp = 1350290211 /*! */;
Insert into bin (name) values ('xishizhaohua ')
/*! */;
# At 309
#140215 16:36:51 server id 1 end_log_pos 336 Xid = 28
COMMIT /*! */;
# At 336
#140215 16:37:25 server id 1 end_log_pos 379 Rotate to mysql_bin.000002 pos: 4
DELIMITER;
# End of log file
ROLLBACK/* added by mysqlbinlog */;
/*! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE */;
Although the layout is messy, we can get more information, such as the timestamp, auto-increment offset, and whether to automatically submit the transaction. For example, it is a part of the extracted data.
