Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is MySQL binlog". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is MySQL binlog".

Introduction to 1.binlog

Binlog is binary log, a binary log file. It records all DDL and DML statements executed in the database (except data query statements select, show, etc.), records them as events and saves them in binary files.

Binlog has two main application scenarios, one is for replication, master passes its binary log to slaves to achieve the purpose of master-slave data consistency. Second, for data recovery, for example, after restoring a backup, you can re-execute the newly generated binlog after the backup to keep the database up-to-date. In addition to these two main uses, binlog can be used for data exchange between heterogeneous systems. Binlog completely preserves the front and back records of a record, and you can use DTS service to extract MySQL data to the underlying data platform in a quasi-real-time way, such as HBase, Hive, Spark, etc., to get through OLTP and OLAP.

You can choose three modes for binlog logs, namely STATEMENT, ROW and MIXED. Here is a brief introduction to these three modes:

STATEMENT: based on the replication of SQL statements, each sql statement that modifies the data is recorded in binlog. The number of binlog logs generated in this mode is relatively small, but it may lead to inconsistency between master and slave data.

ROW: row-based replication does not record every SQL statement that is executed, only which piece of data has been modified and what it looks like before and after the modification. The number of binlog logs generated in this mode will be relatively large, but the advantage is that the details of each row of data modification will be clearly recorded, and the master-slave replication will not make an error.

Mixed: mixed mode replication, which is a mixture of the above two modes. General replication uses STATEMENT mode to save binlog, and for operations that cannot be replicated in STATEMENT mode, use ROW mode to save binlog,MySQL. Log preservation method is selected according to the SQL statement executed.

The binlog mode defaults to STATEMENT before MySQL 5.7.7, and to ROW in later versions. ROW mode is recommended here because ROW mode is more secure and can clearly record the details of each row of data changes.

2.binlog related parameters

Binlog is not enabled by default, but in general, it is recommended to add the log-bin parameter to the configuration file to enable binlog during initialization.

# add log-bin configuration to the configuration file [mysqld] log-bin = binlog# do not specify a path under the data directory by default, or you can specify a path [mysqld] log-bin = / data/mysql/logs/binlog# to check whether the database has binlogshow variables like 'log_bin%' enabled.

After enabling binlog, you need to pay attention to some parameters related to binlog, which are briefly described below:

Binlog_format

Set the binlog mode. It is recommended to set it to ROW.

Binlog_do_db

This parameter means that only the binary logs of the specified database are recorded, all are recorded by default, and changes are not recommended in general.

Binlog_ignore_db

This parameter means that the binary log of the specified database is not logged. As above, it is not explicitly specified.

Expire_logs_days

This parameter controls the number of days that binary log files are retained. The default value is 0, which means that it is not automatically deleted and can be set to 0room99. It can be set according to the actual situation, such as keeping it for 15 or 30 days. The MySQL8.0 version can be replaced with the binlog_expire_logs_seconds parameter.

Max_binlog_size

Controls the size of a single binary log, and the switch action is performed when the current log file size exceeds this variable. The maximum and default value of this parameter is 1GB, which does not strictly control the size of the Binlog, especially when the Binlog is close to the maximum and encounters a larger transaction, in order to ensure the integrity of the transaction, it is impossible to switch the log, so all SQL of the transaction can only be recorded in the current log until the end of the transaction. In general, the default value can be taken.

Log_bin_trust_function_creators

This parameter is enabled when binary logging is enabled. It controls whether the creator of the stored function can be trusted and does not create a stored function that writes to the binary log that causes unsafe events. If set to 0 (the default), users must not create or modify stored functions unless they have SUPER privileges other than CREATE ROUTINE or ALTER ROUTINE privileges. It is recommended to set it to 1.

Sync_binlog

Controls how often the MySQL server synchronizes binary logs to disk, with a default value of 1.

Set to 0, which means that MySQL does not control the refresh of binlog, and the file system controls the refresh of its cache.

Set to 1, which means that MySQL will brush binlog down every time a transaction commits. This is the safest setting, but it may have a negative impact on performance due to the increase in disk writes.

Set to n, where n is a value other than 0 or 1, and after n transaction commits, Mysql will execute a disk synchronization instruction such as fsync to flush the Binlog file cache to disk.

It is recommended to set to 1, which can be adjusted as appropriate for performance reasons.

There are also many SQL related to binlog operation and management. Here are some commonly used statements:

SQL statement meaning show master status View the numbered name of the latest binlog log and the location where the last event ends show binary logs view the list and size of all binlog logs currently reserved flush logs refresh binlog At this point, a newly numbered binlog log file purge binary logs before '2020-07-01 12 binlog log before the specified time will be manually cleaned. Purge binary logs to' binlog.000012' will clear the log before the specified log file, reset master to empty all binlog logs, carefully use show binlog events in 'binlog.000030' to view the specified binlog log eventshow binlog events in' binlog.000030' from 931 from the specified location View the specified binlog log show binlog events in 'binlog.000030' from 931 limit 2 start from the specified location, view the specified binlog log, and limit the number of enent queries to 3. 0. Parsing binlog content

As mentioned earlier, all changes to the database are recorded in binglog. However, binlog is a binary file and cannot be viewed directly. If you want to observe it more intuitively, you need to use the mysqlbinlog command tool. The following mainly describes how to use mysqlbinlog to parse the contents of the binlog log.

For the smooth development of the story, we first switch to binlog, then create test libraries, test tables, insert data, and update data. These pre-operations are not shown for the time being. Let's take a look at how to parse and view the generated binlog content:

# this resolution is based on the MySQL8.0 version, and gtid has been enabled for the instance. The mode is ROW [root@centos logs] # mysqlbinlog-- no-defaults-- base64-output=decode-rows-vv binlog.000013 * . # 200708 16:52:09 server id 1003306 end_log_pos 1049 CRC32 0xbcf3de39 Query thread_id=85 exec_time=0 error_code=0 Xid = 1514use `bindb` / *! * /; SET timestamppacks 1594198329; SET timestampplates default sets @ @ session.session. CREATE TABLE `bin_ tb` (`stu_ id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-increment primary key', `stu_ id` int (11) NOT NULL COMMENT 'student ID', `stu_ name` varchar (20) DEFAULT NULL COMMENT 'student name', `create_ time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time', `update_ time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time', PRIMARY KEY (`increment_ id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' test binlogbadge # at 1049 October 200708 16:52:45 server id 1003306 end_log_pos 1128 CRC32 0xf19ea0a9 GTID last_committed=2 sequence_number=3 rbr_only=yes original_committed_timestamp=1594198365741300 immediate_commit_timestamp=1594198365741300 transactioncodes accounting 468 MB / 50718 SET TRANSACTION ISOLATION LEVEL READ com ITTEDAccording to Universe accounts / # original_commit_timestamp=1594198365741300 (2020-07-08 16 immediate_commit_timestamp=1594198365741300 52CST 45.741300 CST) # immediate_commit_timestamp=1594198365741300 / *! 80001 SET @ @ session.session SET @ @ SESSION.GTID_NEXT= '0032d819-2d32-11ea-91b5-5254002ae61fpur24883According to server id # at 1128200708 16:52:45 server id 1003306 end_log_pos 1204 CRC32 0x5b4b03db Query thread_id=85 exec_time=0 error_code=0SET timespace 1594198365 # at 1204bin 200708 16:52:45 server id 1003306 end_log_pos 1268 CRC32 0xd4755d50 Table_map: `bindb`.`bin _ tb` mapped to number 13examples at 1268bin 200708 16:52:45 server id 1003306 end_log_pos 1486 CRC32 0x274cf734 Write_rows: table id flags: STMT_END_F### INSERT INTO `bindb`.`bin _ tb` # SET### @ 1 / * INT meta=0 nullable=0 is_null=0 * / # # @ 2bin 1001 / * INT meta=0 nullable=0 is_null=0 * / # # @ 3 examples from1'/ * VARSTRING (60) meta=60 nullable=1 is_null=0 * / # @ 4 "1594198365 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * / # # @ 5" 1594198365 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * / # INSERT INTO `bindb`.bin _ tb` # SET### @ 1room2 / * INT meta=0 nullable=0 is_null=0 * / # # @ 2room1002 / * INT meta=0 nullable=0 is_null=0 * / # # @ 3='dfsfd '/ * VARSTRING (60) meta=60 nullable=1 is_null=0 * / # # @ 4 "1594198365 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * / # # @ 5" 1594198365 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * /. # at 1486 / 200708 16:52:45 server id 1003306 end_log_pos 1517 CRC32 0x0437e777 Xid = 1515COMMIT ... # at 1596 200708 16:54:35 server id 1003306 end_log_pos 1681 CRC32 0x111539b6 Query thread_id=85 exec_time=0 error_code=0SET timestamp 1594198475 Compact # at 1681bin 200708 16:54:35 server id 1003306 end_log_pos 1745 CRC32 0x6f0664ee Table_map: `bindb`.`bin _ tb` mapped to number 13examples at 174500708 16:54:35 server id 1003306 end_log_pos 1939 CRC32 0xfafe7ae8 Update_rows: table id 139flags: STMT_END_F### UPDATE `bindb`.`bin _ tb` # WHERE### @ 1q5 / * INT meta=0 nullable=0 is_null=0 * / # # @ 2bin 1005 / * INT meta=0 nullable=0 is_null=0 * / # # @ 3roomdsfsdg'/ * VARSTRING (60) meta=60 nullable=1 is_null=0 * / # # @ 4room1594198365 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * / # @ 5" 1594198365 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * / # SET### @ 12005 / * INT meta=0 nullable=0 is_null=0 * / # # @ 2mm 1005 / * INT meta=0 nullable=0 is_null=0 * / # # @ 3=NULL / * VARSTRING (60) meta=60 nullable=1 is_null=1 * / # @ 4room1594198365 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * / # # @ 5room1594198475 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * / # UPDATE `bindb`.bin _ tb` # WHERE### @ 1room6 / * INT meta=0 nullable=0 is_null=0 * / # @ 2room1006 / * INT meta=0 nullable=0 is_null=0 * / # # @ 3room1594198365 / TIMESTAMP (0) meta=0 nullable=0 is_null=0 * / # @ 5' 1594198365 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * / # SET### @ 1' 6 / * INT meta=0 nullable=0 is_null=0 * / # # @ 2' 1006 / * INT meta=0 nullable=0 is_null=0 * / # # @ 3=NULL / * VARSTRING (60) meta=60 nullable=1 is_null=1 * / # # @ 4' 1594198365 / * TIMESTAMP (0) meta=0 nullable=0 is_null=0 * / # # @ 5' 1594198475 / * TIMESTAMP ( 0) meta=0 nullable=0 is_null=0 * /... # at 1939 September 200708 16:54:35 server id 1003306 end_log_pos 1970 CRC32 0x632a82b7 Xid = 1516COMMITAG SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER; # End of log filewise / 50003 SET completion implementation types of OLDPCE operations @ @ SESSION.PSEUDO_SLAVE_MODE=0*/;# can be seen that the changes generated by each sql execution are recorded in detail in binlog and include system values such as execution time, pos site, server_id and so on.

There are many tricks for using mysqlbinlog tools, such as parsing only operations on a library or operations within a certain period of time, etc. Simply share a few commonly used sentences, more operations can refer to the official documentation.

Mysqlbinlog-no-defaults-base64-output=decode-rows-vv binlog.000013 > / tmp/bin13.sql

Import the parsed SQL into the file

Mysqlbinlog-no-defaults-base64-output=decode-rows-vv-database=testdb binlog.000013

Parse only the operation of a library

Mysqlbinlog-no-defaults-base64-output=decode-rows-vv-start-datetime= "2020-01-11 01:00:00"-stop-datetime= "2020-01-11 23:59:00" binlog.000008

Parse operations within a specified period of time

Mysqlbinlog-no-defaults-base64-output=decode-rows-vv-start-position=204136360-stop-position=204136499 binlog.000008

Parse the operation within the specified pos site

Mysqlbinlog-no-defaults-start-position=204136360-stop-position=204136499 binlog.000008 | mysql-uroot-pxxxx testdb

Resumes operations between specified points in the specified library

Thank you for your reading, the above is the content of "what is MySQL binlog", after the study of this article, I believe you have a deeper understanding of what is MySQL binlog, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.