Shulou(Shulou.com)06/01 Report--

The following is mainly about how to use MySQL binary logs. I hope these contents can bring you practical use, which is also the main purpose of my article on how to use MySQL binary logs. All right, don't talk too much nonsense, let's just read the following.

Download the latest version of Navicat for MySQL

Navicat for MySQL is an ideal solution for managing and developing MySQL or MariaDB. Using Navicat for MySQL, you can connect to both MySQL and MariaDB. Navicat for MySQL provides powerful front-end functions and provides an intuitive graphical interface for database management, development and maintenance. For novices and professionals alike, Navicat for MySQL is a powerful tool for managing and developing MySQL or MariaDB.

Recording is about recording what happens in the database. Just as some people may keep personal logs to record what happens in their daily lives, database logs track things such as logins and transactions. More importantly, valid logs should include entries about access control and input validation failures. The only MySQL log enabled by default is the error log (at least on Windows).

The previous article gave an overview of the different log types on MySQL, highlighted some of the most important-- error logs, regular query logs, binary logs, and slow logs-- and detailed the first two. This article will cover binary logging in more detail. The next article will talk about the slow log in the third part.

Statements for binary logging

Binary logs store events that describe database changes, such as table creation operations or table data changes through statements such as INSERT and UPDATE. In addition to using row-based logging, you can save events for statements that may have changed, such as DELETE that do not match any row, for future generations (see below for more information on this content). Therefore, binary logs do not include statements such as SELECT or SHOW that do not modify data. These can be found in the regular query log.

Binary logs have two important purposes:

For replication, the binary log on the primary replication cloud server provides a record of data changes to be sent to the secondary cloud server. In fact, the primary cloud server sends the events contained in its binary log to its secondary cloud server so that they execute the same commands to achieve the same data changes as on the primary cloud server.

Some data recovery operations use binary logs. After the backup is restored, the events in the binary log associated with the backup are re-executed to synchronize the database to the location where the backup occurred.

Despite these very important uses, binary logging is disabled by default because it slightly degrades performance. However, the benefits that binary logs provide when setting up replication and restoring from backup usually outweigh this slight performance loss.

Binary log format

MySQL provides three logging formats for binary logging, each with its own advantages and disadvantages. Unlike other logs, you cannot enable it using a simple ON / OFF switch. Instead, you must explicitly select the binary logging format by launching the MySQL cloud server with "- binlog-format = type". The exact statement of each type is as follows:

Statement-based

Statement-based logging records all SQL statements that make changes to the data or structure of the table. Enable it with-- binlog-format = STATEMENT.

Some non-deterministic statements may not be suitable for replication. If MySQL determines that this is the case, it will issue a warning that "the statement may not be in a safe login statement format."

Line-based

In row-based logging, the primary cloud server writes events to the binary log to indicate how individual table rows are affected. Therefore, the table must always contain a primary key to ensure that rows are effectively identified. You can start it by using-- binlog-format = ROW to tell the cloud server to use row-based logging.

Mixed log

The third option is mixed logging. With this logging format, statement-based logging is used by default, but in some cases, logging mode automatically switches to row-based. To use mixed logging, start MySQL with the option-- binlog-format = MIXED.

Figure 1-binlog_format CVM variables in the Navicat Server Monitor tool

For the above about how to use the MySQL binary log, you do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.