Summary of basic operation commands for Binary Log files in MySQL,

Summary of basic operation commands for Binary Log files in MySQL,

MySQL Binary Log, also known as bin-log, is a Binary Log file generated by mysql execution changes. It has two main functions:
* Data reply
* 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 ............... omitted ............... * *************************** 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 ***************************............... omitted ...............

• 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\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 #121015 16:35:56 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.51-community-log created 121015 16:35:56 at startup ROLLBACK/*!*/; BINLOG ' 7Mp7UA8BAAAAZgAAAGoAAAAAAAQANS4xLjUxLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADsyntQEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC '/*!*/; # at 106 #121015 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_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 #121015 16:36:51 server id 1 end_log_pos 202 Intvar SET INSERT_ID=3/*!*/; # at 202 #121015 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 #121015 16:36:51 server id 1 end_log_pos 336 Xid = 28 COMMIT/*!*/; # at 336 #121015 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.

3. Use bin_log to restore Data

(1). The longest time to use is to reply to the data of the specified data end. You can directly restore the data to the database:

 mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 |mysql -uroot -p123456 

It can also be exported as an SQL file and then imported to the database:

  mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 >d:\1.sql   source d:\1.sql 

(2 ). specify the start/end position. From the preceding view, we can know the start and end position of a log, we can specify the reply log from location A to location B during the recovery process. the following two parameters must be specified:

-- Start-positon = "50" // specify to start from position 50 -- stop-postion = "100" // specify to end at position 100

Finally, we will introduce several bin_log operations:
(1) What is the last bin log file in production? The current location

(2) enable new log files. Generally, after the database is backed up

(3). Clear the existing bin-log

4. binary log related variables and parameters

Command Line Parameters

-- Log-bin [= file_name]

Set this parameter to enable binlog and specify the path name.

-- Log-bin-index [= file]

Set this parameter to specify the path and name of the binary index file.

-- Max_binlog_size

Maximum Binlog value. The maximum value and default value are 1 GB. This setting does not strictly control the Binlog size, especially when Binlog is close to the maximum value and a large transaction is encountered,

To ensure transaction integrity, you cannot switch logs. You can only record all SQL statements of the transaction into the current log until the transaction ends.

-- Binlog-do-db = db_name

This parameter indicates that only binary logs of the specified database are logged.

-- Binlog-ignore-db = db_name

This parameter indicates that the binary log of the specified database is not logged.

System Variables

Log_bin

Binlog_cache_size

This parameter indicates the memory size used by binlog. The state variables binlog_cache_use and binlog_cache_disk_use can be used for testing.

Max_binlog_cache_size

This parameter indicates the maximum memory size used by binlog.

Binlog_cache_use

Number of transactions cached using binary logs

Binlog_cache_disk_use

The number of transactions that use binary log cache but exceed the binlog_cache_size value and use temporary files to save the statements in the transaction.

Binlog_do_db

Binlog_ignore_db

Sync_binlog

This parameter directly affects the performance and integrity of mysql.

Sync_binlog = 0:

After the transaction is committed, Mysql only writes data in binlog_cache to the binlog file, but does not execute disks such as fsync. The synchronization command notifies the file system to refresh the cache to the disk, the best performance is to let Filesystem decide when to synchronize data.

Sync_binlog = 0. After n transaction commits, Mysql executes a Disk Synchronization command such as fsync to notify the file system to refresh the Binlog File Cache to the disk.

In Mysql, the default setting is sync_binlog = 0, that is, no mandatory disk refresh command is required. In this case, the performance is the best, but the risk is also the biggest. Once the system Crash is enabled, all Binlog information in the file system cache will be lost.

Articles you may be interested in:

• Mysql displays SQL statements executed by tracking logs
• How to modify the location of a mysql Log File
• Analysis and Induction of deadlock logs caused by MySQL Innodb tables
• How to view MySQL error logs
• Simple sorting of MySQL Log operation commands