MySQL's binlog detailed

MySQL's binlog detailed

What is Binlog
The Binlog log is used to record all statements that have updated data or that have potentially updated data (for example, a delete that does not match any row). Statements are saved as "events," which describe data changes.

Binlog effect
Because of the binlog of the data update, it can be used for real-time backup, with Master/slave replication

and Binlog related parameters

Log_bin
Setting this parameter means enabling the Binlog feature and specifying the path name
Log_bin_index
Set this parameter to specify the path and name of the binary index file
binlog_do_db
This parameter indicates that only the binary log of the specified database is logged
binlog_ignore_db
This parameter indicates that the binary log of the specified database is not logged
Max_binlog_cache_size
This parameter represents the maximum size of memory used by Binlog
Binlog_cache_size
This parameter represents the memory size used by Binlog, which can be helped by state variables Binlog_cache_use and Binlog_cache_disk_use.
Binlog_cache_use: Number of transactions using binary log cache
Binlog_cache_disk_use: The number of transactions using the binary log cache but exceeding the Binlog_cache_size value and using temporary files to hold statements in the transaction

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 the Binlog is closer to the maximum and encounters a relatively large transaction, in order to ensure the integrity of the transaction, it is impossible to do the switch log action, All SQL for the transaction can only be logged into the current log until the end of the transaction

Sync_binlog
This parameter directly affects MySQL's performance and integrity

Sync_binlog=0:
When a transaction commits, MySQL simply writes the data in Binlog_cache to the Binlog file, but does not perform a disk synchronization instruction such as Fsync to notify the filesystem to flush the cache to disk, and let filesystem decide when to synchronize, which is the best performance.

Sync_binlog=n, after committing n transactions, MySQL performs a disk synchronization instruction such as Fsync, and the gay file system flushes the Binlog file cache to disk.

The default setting in MySQL is sync_binlog=0, i.e. without any mandatory disk refresh instructions, performance is the best, but the risk is greatest. Once the system is stretched crash, all binlog information in the file system cache is lost

Deletion of Binlog
Binlog Delete can be deleted manually or deleted automatically.

Automatically delete Binlog
Automatic MySQL deletion via Binlog parameter (expire_logs_days) binlog
Mysql> show binary logs;
Mysql> Show variables like ' expire_logs_days ';
mysql> set global expire_logs_days=3;

Delete Binlog manually
mysql> Reset Master; Delete Master's Binlog
mysql> Reset Slave; To delete a slave trunk log
Mysql> purge master logs before ' 2012-03-30 17:20:00 '; Deletes the Binlog log file from the previous log index for the specified date
Mysql> purge master logs to ' binlog.000002 '; Delete Binlog log files in the log index of the specified log file
or directly remove it directly from the operating system command

Mysql> set sql_log_bin=1/0; If the user has super privileges, you can enable or disable the Binlog record for the current session

Mysql> show master logs; View the Binlog log for Master
Mysql> show binary logs; View the Binlog log for Master
Mysql> Show master status; Used to provide status information for the master binary log file
Mysql> show Slave hosts; Displays a list of currently registered slave. Slave that do not start with the--report-host=slave_name option are not displayed in this list

Binglog's view
You can view the contents of Binlog through the Mysqlbinlog command
[Email protected] ~]# mysqlbinlog/home/mysql/binlog/binlog.000003 | More

/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;
DELIMITER/*!*/;
# at 4
#120330 16:51:46 Server ID 1 end_log_pos 98 start:binlog v 4, Server v 5.0.45-log created 120330 1
6:51:46
# Warning:this Binlog is not closed properly. Most probably mysqld crashed writing it.
# at 196
#120330 17:54:15 Server ID 1 end_log_pos 294 Query thread_id=3 exec_time=2 error_code=0
SET timestamp=1333101255/*!*/;
INSERT INTO TT7 select * from tt7/*!*/;
# at 294
#120330 17:54:46 Server ID 1 end_log_pos 388 Query thread_id=3 exec_time=28 error_code=0
SET timestamp=1333101286/*!*/;
ALTER TABLE TT7 engine=innodb/*!*/;

Parsing the Binlog format

Position
Location in the file, "at 196" indicates the starting point of "event", starting with the 196th byte; "End_log_pos 294" description ends with No. 294 byte

Time stamp
Timestamp of event occurrence: "120330 17:54:46"

Event Execution Time
Time spent on event execution: "Exec_time=28"

Error code
The error code is: "Error_code=0"

The identity of the server
Identity ID of the server: "Server ID 1"

--------End---------

MySQL Binlog (GO)