Basic operation commands for binary log binary log files in Mysql summary _mysql

MySQL Binary log is often said to Bin-log, is the MySQL implementation of changes generated by the binary log file, its main role is two:
* Data reply
* Master-slave database. For the slave end to perform additions and deletions, keep sync with master.

1. Turn on binary log function

Need to modify the MySQL configuration file, the experimental environment of this article is win7, the configuration file for MySQL installation directory \mysql Server 5.1 under the My.ini, add a sentence Log_bin = Mysql_bin can

  eg: 
  [mysqld] ... 
    Log_bin = Mysql_bin ... 
  Log_bin is the generated bin-log file name, suffix is a 6-digit encoding, starting from 000001, according to the above configuration, the resulting file is: 
    mysql_bin.000001 
    mysql_bin.000002 
    ...... 

Configure the server to restart MySQL after the save, with show variables like '%bin% ' to see if the Bin-log is open, as shown in figure:

2. View the resulting binary log

Bin-log because it is binary file, not through Notepad, such as editor directly open the view, MySQL provides two ways to view, in the introduction, we first to the database to delete and modify the operation, otherwise log inside the data is a bit empty.

 Create TABLE bin (id int (a) primary key auto_increment,name varchar (255));

(I have built the table before the test)

 Insert into Bin (name) values (' orange '); 

1. In the client use show Binlog events in ' mysql_bin.000001 ' statement to view, in order to sort beautiful, you can add \g at the end of the result of the horizontal vertical, at the end without the addition of the statement terminator.
eg

Mysql> Show Binlog The events in the ' mysql_bin.000001 ' \g ..... 
omit to do ....... 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 
  ' test '; insert Into bin (name) values (' Orange ') 
*************************** 5. Row *************************** 
............... Omitted............... 

• Log_name: This log exists in that file, from which you can see that all 2 logs exist in the mysql_bin.000001 file.
• Pos:log the start position in the Bin-log
• Type information for Event_type:log
• SERVER_ID: You can view the server_id in the configuration, indicating that the log is the server that generated
• The end position of the end_log_pos:log in the Bin-log
• Info:log Some of the memo information, you can visually see what the operation

2. Mysqlbinlog with MySQL's own tools, this is where we need to know where the bin-log exists, win7 default exists under C:\ProgramData\MySQL\MySQL Server 5.1\data folder, If this folder is not available, we can locate it through the   datadir= "C:/programdata/mysql/mysql Server 5.1/data/" in the configuration file, if not, then I will say "the search function of each system is doing well!" ”。 This way of viewing is not that beautiful, the following

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 start:binlog v 4, Server v 5.1.51-community-log created 121015 16:35 
: At startup rollback/*!*/; 
Binlog ' 7MP7UA8BAAAAZGAAAGOAAAAAAAQANS4XLJUXLWNVBW11BML0ES1SB2CAAAAAAAAAAAAAAAAAAAAA 
AAAAAAAAAAAAAAAAAADSYNTQEZGNAAGAEGAEBAQEEGAAUWAEGGGAAAAICAGC '/*!*/; # at #121015 16:36:51 Server ID 1 end_log_pos 174 Query thread_id=2 exec_time=0 error_code=0 SET timestamp=1350290 
211/*!*/; 
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 intvar SET insert_id=3/*!*/; 
# at the #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 = commit/*!*/; 
# at 336 #121015 16:37:25 Server ID 1 end_log_pos 379 Rotate to mysql_bin.000002 pos:4; 
# End of log file ROLLBACK/* Added by Mysqlbinlog * *; 

 /*!50003 SET completion_type= @OLD_COMPLETION_TYPE * *;

Although the layout is a bit messy, we can get more information from the graph, such as timestamp, the offset of the increment, the automatic submission of the transaction and so on. The following figure is the part that is extracted from.

3. Recovery of data using Bin_log

(1). The longest use is to reply to the data at the specified end of the data, can be directly restored 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 a SQL file and then imported into 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 above view generated binary log we can know the beginning of a log to the end of the position, we can in the process of recovery to specify the response from a to B position log. You need to specify the following two parameters:

 --start-positon= "50"//specified from 50-bit start 
 --stop-postion= "100"//specified to 100-bit end 

Finally, we introduce a few Bin_log operations:
(1). Production look at the last bin log file is that, now position

(2). Enable new log files, typically after the database is backed up and executed

(3). Empty the existing Bin-log

4.binary log related variables and parameters

Command line arguments

--log-bin [=file_name]

Setting this parameter indicates that the Binlog feature is enabled and the path name is established.

--log-bin-index[=file]

Setting this parameter is the path and name of the specified binary index file.

--max_binlog_size

Binlog maximum, the maximum and default value is 1GB, this setting does not strictly control the size of the binlog, especially when Binlog is closer to the maximum and encounters a larger transaction,

In order to ensure the integrity of the transaction, it is not possible to do a toggle log action, only all the SQL of the transaction can be recorded into the current log until the transaction ends.

--binlog-do-db=db_name

This parameter indicates that only the binary log of the specified database is 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 represents the amount of memory used by Binlog and can be helped by state variables Binlog_cache_use and Binlog_cache_disk_use.

Max_binlog_cache_size

This parameter represents the maximum size of memory used by Binlog

Binlog_cache_use

Number of transactions using binary log caching

Binlog_cache_disk_use

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

binlog_do_db

binlog_ignore_db

Sync_binlog

This parameter directly affects MySQL's performance and integrity.

Sync_binlog=0:

When a transaction is committed, MySQL simply writes the data in the Binlog_cache to the Binlog file, but does not perform a disk such as Fsync, which notifies the file system to flush the cache to disk and lets filesystem decide when to synchronize, which is the best performance.

Sync_binlog=0, after the n transaction commits, MySQL performs a disk synchronization instruction such as Fsync, notifying the file system to flush the Binlog file cache to disk.

The default setting in

  MySQL is sync_binlog=0, which does not make any mandatory disk refresh instructions, at which point performance is best, but the risk is greatest. Once the system is crash, all binlog information in the file system cache will be lost.