Mysql Binlog Detailed

MySQL binlog log function is used to record MySQL internal additions and deletions to the MySQL database has updated content records (changes to the database), the database query select or show, etc. will not be binlog log records; Mainly used for master-slave replication and incremental recovery of the database.

MySQL Binlog log must open Log-bin feature to survive Binlog log-rw-rw----1 mysql mysql 669 August 21:29 mysql-bin.000001-rw-rw----1 MySQL mysq L 126 August 22:06 mysql-bin.000002-rw-rw----1 mysql mysql 11799 August 18:17 mysql-bin.000003 1. Open MySQL Log-bin functionEdit MY.CNF configuration file

12	# grep log-bin my.cnflog-bin = /data/3306/mysql-bin

To see if the log is enabled

1	mysql>show variables like ‘log_bin‘;

2. Mysqlbinlog Parsing Tool

The Mysqlbinlog function is to convert the MySQL binlog log into a MySQL statement, which by default Binlog log is a binary file and cannot be viewed directly. Mysqlbinlog parameters

Parameters	Describe
-D	Specify the Binlog of the library
-R	Equivalent to redirect to the specified file
--start-position--stop-position	Accurately parse the Binlog log at the specified location (exact), as long as the--stop-positiion is not received until the end of the Binlog log
--start-datetime--stop-datetime	Resolves the binlog log (blurry, inaccurate) at a specified time, as long as the--stop-datetime is not received until the end of the Binlog log

Note: Myslqlbinlog export binlog, such as using the-d parameter, must use the database when updating data. Example: Parsing the Binlog log of a Ceshi database and writing to a my.sql file

1	#mysqlbinlog -d ceshi mysql-bin.000003 -r my.sql

Use location to accurately parse Binlog logs

1	#mysqlbinlog mysql-bin.000003 --start-position=100  --stop-position=200 -r my.sql

3, MySQL binlog Three kinds of work mode (1) Row levelThe log records the changes in each row of data, and then modifies the same data on the slave side. Advantages: The ability to clearly record the details of each row of data modification disadvantages: too large data volume (2) Statement level (default)Each SQL that is modified data is recorded in the master's bin-log, slave the SQL process resolves the same SQL re-execution benefits sing Woo the original master side: solves the disadvantage of the row level, does not need to record each row of data changes, Reduce Bin-log log volume, save disk IO, improve new performance drawbacks: Prone to master-slave replication inconsistencies (3) Mixed (mixed mode)Combines the benefits of row level and statement level

4, MySQL Enterprise Binlog mode selection

1. Internet companies use MySQL with less functionality (no stored procedures, triggers, functions), choose the default statement level
2. Use MySQL's special features (stored procedures, triggers, functions) to select Mixed mode
3. Use MySQL's special functions (stored procedures, triggers, functions) and want the data to be maximized always choose row mode

5. Set MySQL binlog modeView Mysqlbinlog Mode

12345678910

mysql>show global variables like "binlog%";+-----------------------------------------+-----------+| Variable_name                           | Value     |+-----------------------------------------+-----------+| binlog_cache_size                       | 1048576   || binlog_direct_non_transactional_updates | OFF       || binlog_format                           | STATEMENT |       #系统默认为STATEMENT模式| binlog_stmt_cache_size                  | 32768     |+-----------------------------------------+-----------+4 rows in set (0.00 sec)

Setting Binlog mode in MySQL

1	mysql>setglobal binlog_format=‘ROW‘;

Setting the Binlog mode in the configuration file

123456

#vim my.cnf[mysqld]binlog_format=‘ROW‘#放在mysqld模块下面user    = mysqlport    = 3306socket  = /data/3306/mysql.sock

6. The MySQL service needs to be restarted after the configuration is completed.Parsing the Binlog log in row mode

1	#mysqlbinlog --base64-output="decode-rows" -v mysql-bin.000001

Mysql Binlog Detailed