Welcome to pay attention to github.com/hsfxuebao, I hope to help you, if you think it can trouble to click on the Star ha

When we talked about database transactions, we talked about two types of logs: redo logs and rollback logs. For online database application system, suddenly encountered database downtime how to do? In this case, locating the cause of the outage is critical. We can view the error log of the database. Logs record diagnostic information during database operation, including errors, warnings, and comments. For example, the log shows that the SQL operation in a connection has an infinite loop, causing insufficient memory and being terminated by the system. Once the cause was identified, it was easy to deal with and the system was quickly back up and running.

In addition to error detection, logs play an irreplaceable role in data replication, data recovery, operation auditing, and ensuring data permanence and consistency.

Never underestimate a log. The answers to many seemingly strange questions are often hidden in the logs. In many cases, it is only by looking at the logs that the cause of the problem is discovered and the problem is truly resolved. So, be sure to learn to check the log, develop the habit of checking the log, to improve your database application development ability is crucial.

Address: MySQL8.0 website log dev.mysql.com/doc/refman/…

1. Logs supported by MySQL

1.1 Log Types

MySQL has different types of log files for storing different types of logs, including binary logs, error logs, general query logs, and slow query logs.

MySQL 8 has added two new types of logging support: relay logging and data definition statement logging.

Using these log files, you can see what is happening inside MySQL.

The six types of logs are as follows:

• Slow query log: Records all queries whose execution time exceeds long_query_time, so that we can optimize the query.

• Generic direct query log: It records the start and end times of all connections, as well as all instructions sent to the database server. It is very helpful for us to recover actual scenarios of operations, find problems, and even audit database operations.

• Error log: Records the problems that occur when the MySQL service is started, running, or stopped. This helps you learn about the server status and maintain the server.

• Binary log: A statement that records all changed data. It can be used for data synchronization between the primary and secondary servers and for data recovery without loss in the event of a server failure.

• Relay log: An intermediate file used by the slave server to store the binary log content of the master server in the master server architecture. The secondary server synchronizes operations on the primary server by reading the contents of the trunk log.

• Data definition statement logging: Records metadata operations performed by data definition statements.

All logs except binary logs are text files. By default, all logs are created in the MySQL data directory.

1.2 Disadvantages of logs

• The logging function degrades the performance of the MySQL database. For example, on a MySQL database system where queries are very frequent, if generic query logging and slow query logging are enabled, the MySQL database will spend a lot of time logging.

• Logs take up a lot of disk space. For a database with a large number of users and frequent operations, the storage space required by log files is larger than that required by database files.

2. Slow query log

Locating slow SQL: Logs are slowly queried

3. General Query Log

The general query log records all user operations, including starting and stopping the MySQL service, connection start time and end time of all users, and all SQL commands sent to the MySQL database server.

When our data is abnormal, viewing the general query log and the specific scenario during the restore operation can help us locate the problem accurately.

3.1 Problem Scenario

In the e-commerce system, after purchasing goods and using wechat payment, it is found that the record of the payment center has not been added. At this time, the user uses Alipay to pay again, and the problem of repeated payment will occur. However, when you query the data in the database, only one record will be found. The result is that there is only one payment record, but the user made two payments.

We checked the system carefully and found no data problems because the user and order numbers as well as the third party serial numbers were correct. But the user did pay twice, and at this point we thought of checking the common query log to see what happened that day.

After checking, I found that: At 2pm on January 1st, after the user finished using wechat payment, but due to network failure, the payment center did not receive the callback notice of wechat payment in time, so no data was written at that time. After 2 PM on January 1, the user used Alipay to pay again. At this time, the record was updated to the payment center. At 9 PM on January 1st, the wechat callback notice came, but the payment center already had alipay’s record, so it could only cover the record.

Duplicate payments due to network problems. As for the solutions to the problem, there are many, omitted here.

You can see that the generic query log helps us understand when the operation occurred and the details of the operation, which is critical to finding out why the exception occurred.

3.2 Viewing the Current Status

mysql> SHOW VARIABLES LIKE '%general%'; +------------------+------------------------------+ | Variable_name | Value | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | general_log | OFF | # general query log closed | general_log_file | / var/lib/mysql/test01 log | # test01 is the name of general query log file. The log + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.03 SEC)Copy the code

Note 1: The value of the system variable general_log is OFF, that is, the general query log is disabled. In MySQL, this parameter is turned off by default. Once logging is enabled, MySQL records all connection starts and stops and associated SQL operations, which consumes system resources and occupies disk space. We can manually change the value of the variable to enable logging when needed.

Note 2: The name of the common query log file is test01.log. The storage path is /var/ib/mysql.default is also the data path. So we know where to view the contents of the common query log.

3.3 Startup Logs

3.3.1 Permanent mode:

Modify the my.cnf or my.ini configuration file to set. Add the log option to the [mysqld] group and restart the MySQL service. The format is as follows:

[mysqld] general_log=ON general_log_file=[path[filename]] # directory where log files are stored. Filename indicates the log filenameCopy the code

If the directory and file name are not specified, the general query logs are stored in hostname.log in the MySQL data directory by default. Hostname indicates the hostname.

3.3.2 Mode 2: Temporary mode:

SET GLOBAL general_log=on; SET GLOBAL general_log_file= 'path/filename'; # set the location of the log fileCopy the code

The SQL command to close the operation is as follows:

SET GLOBAL general_log=off; Turn off the generic query logCopy the code

View the situation after setting:

SHOW VARIABLES LIKE 'general_log%';
Copy the code

3.4 Viewing Logs

Generic query logs are stored on the file system as text files that can be opened directly using a text editor. The general query log content is different for each MySQL server.

• In Windows, text file viewer is used.
• In Linux, you can use the visual interface (VI) tool or gedit tool.
• On the Mac OS, you can use a tool such as text file viewer or VI to view files.

SHOW VARIABLES LIKE ‘general_log%’; You can see the location of the common query log in the results.

/usr/sbin/mysqld, Version: 8.0.26 (MySQL Community server-gpl). Started with: Tcp port: 3306 Unix socket: The/var/lib/mysql/mysql. The sock Time Id Command Argument T07:2022-01-04 44:58. 10 052890 z Query SHOW VARIABLES LIKE '% general %' 2022-01-04T07:45:15.666672z 10 Query SHOW VARIABLES LIKE 'general_log%' 2022-01-04T07:45:28.970765z 10 Query Select * From student 2022-01-04T07:47:38.706804z 11 Connect root@localhost on using Socket 2022-01-04T07:47:38.707435z 11 Query Select @@version_comment limit 1 2022-01-04T07:48:21.384886z 12 Connect [email protected] on using TCP/IP 2022-01-04T07:48:21.385253z 12 Query SET NAMES UTf8 2022-01-04T07:48:21.385640z 12 Query USE 'atguigu12' 2022-01-04T07:48:21.386179z 12 Query SHOW FULL TABLES WHERE Table_Type! ='VIEW' 2022-01-04T07:48:23.901778z 13 Connect [email protected] on using TCP/IP 2022-01-04T07:48:23.902128z 13 Query SET NAMES UTf8 2022-01-04T07:48:23.905179z 13 Query USE 'atguigu' 2022-01-04T07:48:23.905825z 13 Query SHOW FULL TABLES WHERE Table_Type ! ='VIEW' 2022-01-04T07:48:32.163833z 14 Connect [email protected] on using TCP/IP 2022-01-04T07:48:32.164451z 14 Query SET NAMES UTf8 2022-01-04T07:48:32.164840z 14 Query USE 'atguigu' 2022-01-04T07:48:40.006687z 14 Query select * from accountCopy the code

In the general query log, we can clearly see when the new client login database, the SQL operation done after login, which data table is targeted and other information.

3.5 Stopping Logs

3.5.1 Mode 1: Permanent Mode:

Modify the my. CNF or my.ini file to set general_log under the [mysqld] group to OFF or comment out the general_log entry. After the modification is saved, restart the MySQL service to take effect.

Example 1:

[mysqld]
general_log=OFF
Copy the code

Example 2:

[mysqld]
#general_log=ON
Copy the code

3.5.2 Mode 2: Temporary mode

MySQL > disable log query with SET

SET GLOBAL general_log=off;
Copy the code

Query common logs:

SHOW VARIABLES LIKE 'general_log%';
Copy the code

3.6 Deleting or Refreshing logs

If the data is used very frequently, the generic query log can take up a very large amount of disk space on the server.

The data administrator can delete query logs from a long time ago to save hard disk space on the MySQL server.

Manually deleting files:

SHOW VARIABLES LIKE 'general_log%';
Copy the code

As you can see, the default directory for the generic query log is the MySQL data directory. Manually delete the common query log test01.log from this directory.

Run the following command to generate the query log file:

SQL > create log file in MySQL database The general log function must be enabled.

mysqladmin -uroot -p flush-logs
Copy the code

4. Error log

Error logs record the time when the MySQL server starts, stops, and diagnoses during the starting, running, and stopping of the system, including errors, warnings, and prompts.

Error logs help you view the running status of the system, helping you discover and rectify faults in a timely manner. If the MySQL service is abnormal, the error log is the first choice to discover and resolve the problem.

4.1 Startup Logs

Error logging is enabled by default in the MySQL database. Also, error logging cannot be disabled.

By default, error logs are stored in the MySQL database data folder named mysqld.log (Linux) or hostname.err (MAC) by default.

To specify a file name, perform the following configuration in my. CNF or my.ini:

[mysqld] log-error=[path/[filename]] #path indicates the directory where the log file resides, and filename indicates the log filenameCopy the code

After modifying the configuration item, restart the MySQL service for the modification to take effect.

4.2 Viewing Logs

MySQL error logs are stored as text files and can be viewed directly using a text editor.

Query the path for storing error logs.

mysql> SHOW VARIABLES LIKE 'log_err%'; +----------------------------+----------------------------------------+ | Variable_name | Value | +----------------------------+----------------------------------------+ | log_error | /var/log/mysqld.log | | log_error_services | log_filter_internal; log_sink_internal | | log_error_suppression_list | | | log_error_verbosity | 2 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 4 rows in the set (0.01 SEC)Copy the code

The error log file is mysqld.log, located in the default MySQL data directory.

Let’s look at the contents of the error log.

2822-01-84T08:44:58.307689zg[System][my-010116][Server]/usr/sbin/mysq1d(mysq1d8.0.26) starting as process 1347 2022-01-04T08:44:58.324902z 1 [System][my-013576][InnoDB]InnoDB initialization has started. 2022-01-04T08:44:58.615451z 1 [System][my-013577][InnoDB] Initialization has ended. 2022-01-04T08:44:58.850032z 0 [Warning][MY-013746][Server]A deprecated TLS version TLSv1 is enabled for channel mysql_mainCopy the code

As you can see, the error log file records the server startup time, the storage engine InnoDB startup and stop time, etc. The initial database password generated during initialization is also recorded in error.log.

4.3 Deleting or Refreshing logs

For error logs from a long time ago, it is not possible for the database administrator to view these error logs. They can be removed to ensure disk space on the MySQL server.

MySQL error logs are stored as text files in the file system and can be deleted directly.

• Step 1 (Mode 1) : Delete operation

rm -f /var/lib/mysql/mysqld.log
Copy the code

After an error log file is deleted in the running state, MySQL does not automatically create a log file.

• Step 1 (method 2) : Rename the file

mv /var/log/mysqld.log /var/log/mysqld.log.old
Copy the code

• Step 2: Rebuild the log

mysqladmin -u root -p flush-logs
Copy the code

Error may occur:

[root@atguigu01 log]# mysqladmin -uroot -p flush-logs
Enter password:
mysqladmin: refresh failed; error: 'Could not open file '/var/log/mysqld.log' for
error logging.'
Copy the code

Tips:

Supplementary operations:

install -omysql -gmysql -mo644 /dev/null /var/log/mysqld.log
Copy the code

F1ush-1ogs command operation:

• MySQL5.5.7 or earlier,flush-logsRename the error log file tofilename.. err_oldAnd create a new log file.
• From MySQL5.5.7To start,Flush-logs only reopen log files, and do not back up or create logs.
• If the log file does not exist, MySQL starts or executesflush-logsA new log file is automatically created. Recreate the error log with a size of 0 bytes.

4.4 New MySQL8.0 features

Error logging improvements in MySQL8.0. The error log in MySQL8.0 can be interpreted as a new log, in which a wide range of criticisms from the community are accepted and a new log is generated based on these comments and suggestions.

Here are some comments from the community:

• By default, the content is too verbose
• Useful information was omitted
• It is difficult to filter certain information
• No subsystem source identified with error message
• There is no error code, and parsing the message requires error identification
• Boot messages may be lost
• Fixed format

In response to these comments, MySQL made the following changes:

• A component architecture is adopted to implement log writing and filtering functions through different components
• All information written to the error log has a unique error code starting at 10000
• A new message class, System, was added for non-error but server state change events that are always visible in the error log
• Added additional additional information, such as the version of the shutdown, who initiated the shutdown, and so on
• Two filtering methods, Interna and Dragnet
• Three write forms, classical, JSoN, and SyseventLog

Summary: Typically, administrators do not need to view error logs. However, when an exception occurs on the MySQL server, the administrator can find the time and cause of the exception from the error log and rectify the exception based on the information.

5. Bin log

Binlog is an important log in MySQL. It is often encountered during daily development, operation and maintenance. A binary log file is also called a update log. It records all statements of database update events such as DDL and DML executed by the database, but does not include statements (such as data query statements SELECT, show, and so on) that do not modify any data.

If you want to log all statements (for example, to identify problematic queries), you need to use generic query logging.

Binlog is used in the following scenarios:

• First, it is used for data verification. If the MySQL database stops unexpectedly, you can check the operations performed by the user and the changes made to the database server file through the binary log file, and then restore the database server according to the records in the binary log file.

• Second, it is used for data replication. Due to the continuity and timeliness of logs, master transfers its binary logs to Slaves to achieve the purpose of master-slave data consistency.

It can be said that the data backup, master/slave, master/master, and master/slave of MySQL database are all dependent on binlog. Binlog is required to synchronize data and ensure data consistency.

5.1 Viewing the Default Settings

Check whether logging binary is enabled: In MySQL8, binary files are enabled by default.

mysql> show variables like '%log_bin%'; +---------------------------------+----------------------------------+ | Variable_name | Value | +---------------------------------+----------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/binlog | | log_bin_index | /var/lib/mysql/binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 6 rows in the set (0.00 SEC)Copy the code

• Log_bin_basename: indicates the basic file name of binlog logs. Each file is identified by an identifier

• Log_bin_index: is the index file of the binlog file. This file manages the directories of all binlog files

• Log_bin_trust_function_creatbrs: Limits stored procedures, which we have already covered, because an important function of binary logs is for master/slave replication, and storage functions can cause data inconsistency between master and slave. Therefore, the creation, modification, and invocation of storage functions must be restricted after binary logging is enabled

• Log_bin_use_v1_row_events This read-only system variable is deprecated. ON means to use the version 1 binary log line, and OFF means to use the version 2 binary log line (the default for MySQL5.6 is 2).

5.2 Setting Log Parameters

5.2.1 Mode 1: Permanent Mode:

MySQL my.cnf/my.ini file to set binary log parameters:

Log-bin =atguigu-bin binlog_expire_logs_seconds=600 max_binlog_size=100MCopy the code

Tip:

1. log-bin=mysq-bin# Enable logging (host needs to enable), thismysql-binYou can also customize the path, such as /home/www/mysql_bin_log/mysql-bin
2. Binlog_expire_logs_seconds’ : This parameter controls the retention duration of binary log files, in seconds. The default value is 2592000 30 days –144004 hours: 864001 days. 2592003 days;
3. max_binlog_size: Controls the size of a single binary log file. If the size of the current log file exceeds this variable, the switch is performed. The parameters of theThe maximum and default value is 1GB, the setting andThe size of the binlog cannot be strictly controlled, especially when the Binlog is close to the maximum value and a large transaction is encountered, to ensure the integrity of the transaction, it may not switch the log, but only record all the SQL of the transaction into the current log until the end of the transaction. Generally, the default value can be used.

Restart the MySQL service and query binary log information. The result is as follows:

mysql> show variables like '%log_bin%'; +---------------------------------+----------------------------------+ | Variable_name | Value | +---------------------------------+----------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/atguigu-bin | | log_bin_index | /var/lib/mysql/atguigu-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 6 rows in the set (0.00 SEC)Copy the code

Set the bin-log directory with folders

If you want to change the directory and name of the log file, change the log_bin parameter in my.cnf or my.ini as follows:

[mysqld]
log-bin="/var/lib/mysql/binlog/test-bin"
Copy the code

Note: The new folder needs to use mysql user, use the following command.

chown -R -v mysql:mysql binlog
Copy the code

After the MySQL service is restarted, the new binary log file will appear under the var/lib/mysql.binlog folder:

The database file should not be on the same disk as the log file! This allows you to use log files to recover data when the disk on which the database files reside fails.

5.2.2 Mode 2: Temporary Mode:

If you don’t want to set binary logging by modifying the configuration file and restarting it, you can also use the following command. Note that in mysql8 there are only session level Settings, not global level Settings.

Mysql > set global sql_log_bin=0; ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION Variable and can't be used with SET GLOBAL # SESSION level mysql> SET SQL_log_bin =0; Query OK, 0 rows affected (0.01 SEC)Copy the code

5.3 Viewing Logs

When MySQL creates a binary log file, create a file with filename and suffix.index, and then create a file with filename and suffix.000001.

After the MySQL service is restarted, a file with the suffix.000001 is added. The file name extension increases by 1. That is, the number of log files is the same as the number of MySQL service startup times. If the log length exceeds the max_binlog_size limit (the default is 1GB), a new log file is created.

View the current binary log file list and size. The instructions are as follows:

mysql> SHOW BINARY LOGS; +--------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +--------------------+-----------+-----------+ | atguigu-bin.000001 | 156 | No | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 1 row in the data set (0.02 seconds)Copy the code

All changes to the database are recorded in binglog. However, binlog is a binary file and cannot be viewed directly. For a more intuitive view, use the mysqlBinlog command tool. The command is as follows: Before viewing execution, execute an sQL statement as follows

Update student set name=' c 'where id=1;Copy the code

Start viewing the binlog

mysqlbinlog "/var/1ib/mysql/binlog/test-bin.000002"
Copy the code

As you can see, this is a simple log file, which records some user operations. There is no SQL sentence in this file, because the content after the binlog keyword is the encoded binary log. Here an UPDATE statement contains the following events:

• The Query event is responsible for starting a transaction.
• The Table_map event is responsible for mapping the required tables
• The Update_rows event is responsible for writing data
• The Xid event is responsible for ending the transaction

The following command represents the row events in pseudo-SQL

mysqlbinlog -v "/var/lib/mysql/binlog/atguigu-bin.000002" #220105 9:16:37 server id 1 end_log_pos 324 CRC32 0x6b31978b Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1641345397/*! * /. SET @@session.pseudo_thread_id=10/*! * /. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*! * /. SET @@session.sql_mode=1168113696/*! * /. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*! * /. / *! \C utf8mb3 *//*! * /. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collatio n_server=255/*! * /. SET @@session.lc_time_names=0/*! * /. SET @@session.collation_database=DEFAULT/*! * /. / *! 80011 SET @@session.default_collation_for_utf8mb4=255*//*! * /. BEGIN /*! * /. # at 324 #220105 9:16:37 server id 1 end_log_pos 391 CRC32 0x74f89890 Table_map: `atguigu14`.`student` mapped to number 85 # at 391 #220105 9:16:37 server id 1 end_log_pos 470 CRC32 0xc9920491 Update_rows: table id 85 flags: STMT_END_F BINLOG ' dfHUYRMBAAAAQwAAAIcBAAAAAFUAAAAAAAEACWF0Z3VpZ3UxNAAHc3R1ZGVudAADAw8PBDwAHgAG AQEAAgEhkJj4dA== dfHUYR8BAAAATwAAANYBAAAAAFUAAAAAAAEAAgAD//8AAQAAAAblvKDkuIkG5LiA54+tAAEAAAAL 5byg5LiJX2JhY2sG5LiA54+tkQSSyQ== '/*! * /. # # # UPDATE ` atguigu `. ` student ` # # # WHERE 1 = 1 # # # @ # # # @ 2 = 'zhang' # # # @ 3 = 'class' # # # # # @ # SET 1 = 1 # # # @ 2 = 'zhang SAN _back' # # # @ 3 = 'class' # at 470 #220105 9:16:37 server id 1 end_log_pos 501 CRC32 0xca01d30f Xid = 15 COMMIT/*! * /.Copy the code

The previous command also displays the statement in binlog format; use the following command to not display it

mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/test-bin.000002" #220105 9:16:37 server id 1 end_log_pos 324 CRC32 0x6b31978b Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1641345397/*! * /. SET @@session.pseudo_thread_id=10/*! * /. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*! * /. SET @@session.sql_mode=1168113696/*! * /. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*! * /. / *! \C utf8mb3 *//*! * /. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collatio n_server=255/*! * /. SET @@session.lc_time_names=0/*! * /. SET @@session.collation_database=DEFAULT/*! * /. / *! 80011 SET @@session.default_collation_for_utf8mb4=255*//*! * /. BEGIN /*! * /. # at 324 #220105 9:16:37 server id 1 end_log_pos 391 CRC32 0x74f89890 Table_map: `atguigu14`.`student` mapped to number 85 # at 391 #220105 9:16:37 server id 1 end_log_pos 470 CRC32 0xc9920491 Update_rows: table id 85 flags: STMT_END_F # # # UPDATE ` atguigu14 `. ` student ` # # # WHERE 1 = 1 # # # @ # # # @ 2 = 'zhang' # # # @ 3 = 'class' # # # # # @ # SET 1 = 1 # # # @ 2 = 'zhang SAN _back' ### @3=' class 1 '# at 497 #220105 9:16:37 server id 1 end_log_pos 501crc32 0xCA01d30f Xid = 15Copy the code

There are many other tricks to using the mysqlBinlog tool, such as resolving only operations to a particular library or within a certain period of time. Simply share a few common statements, more operations can refer to the official documentation.

Mysqlbinlog --no-defaults --help # mysqlbinlog --no-defaults --base64-output=decode -- rows-vv Atguigu - bin. 000002 | tail - 100 # according to the position for mysqlbinlog -- no - defaults -- base64 - output = decode, rows, and vv atguigu-bin.000002 |grep -A 20 '4939002'Copy the code

The above method reads the full text of the binlog, it is not easy to distinguish and view the pos point information, the following is a more convenient query command:

Mysql > show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset], row_count];Copy the code

• IN 'log_name': Specifies the name of the binlog file to be queried.
• FROM pos: specifies the starting point of pos (if not specified, the starting point of pos in the entire file)
• LIMIT [offset]: offset (0 if not specified)
• row_count : Query total number of rows (if not specified, all rows)

mysql> show binlog events in 'atguigu-bin.000002'; +--------------------+-----+----------------+-----------+-------------+--------------- --------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +--------------------+-----+----------------+-----------+-------------+--------------- --------------------------------------------------------------+ | atguigu-bin.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.26, Binlog ver: 4 | | atguigu-bin.000002 | 125 | Previous_gtids | 1 | 156 | | | atguigu-bin.000002 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | atguigu-bin.000002 | 235 | Query | 1 | 324 | BEGIN | | atguigu-bin.000002 | 324  | Table_map | 1 | 391 | table_id: 85 (atguigu14.student) | | atguigu-bin.000002 | 391 | Update_rows | 1 | 470 | table_id: 85 flags: STMT_END_F | | atguigu-bin.000002 | 470 | Xid | 1 | 501 | COMMIT /* xid=15 */ | | atguigu-bin.000002 | 501 | Anonymous_Gtid | 1 | 578 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | atguigu-bin.000002 | 578 | Query | 1 | 721 | use `atguigu14`; create table test(id int, title varchar(100)) /* xid=19 */ | | atguigu-bin.000002 | 721 | Anonymous_Gtid | 1 | 800 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | atguigu-bin.000002 | 800 | Query | 1 | 880 | BEGIN | | atguigu-bin.000002 | 880 | Table_map | 1 | 943 | table_id: 89 (atguigu14.test) | | atguigu-bin.000002 | 943 | Write_rows | 1 | 992 | table_id: 89 flags: STMT_END_F | | atguigu-bin.000002 | 992 | Xid | 1 | 1023 | COMMIT /* xid=21 */ +--------------------+-----+----------------+-----------+-------------+--------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 14 rows in the data set (0.02 seconds)Copy the code

So all of this stuff that we’ve talked about is based on the default binlog format, binlog view

mysql> show variables like 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in dataset (0.02 seconds)Copy the code

In addition, there are two formats of binlog: Statement and Mixed

• Statement: Each SQL Statement that modifies data is recorded in the bin log. Advantages: No need to record every row change, reduce the amount of binlog, save 10, improve performance.

• Row: MySQL 5.1.5 has just started to support Row level replication. It does not record information about the context of SQL statements, but only which records were modified.

  Advantages: Row level logs clearly record the details of each row. And there are no specific cases where stored procedures, or functions, or trigger calls and triggers cannot be copied correctly.

• Mixed: Starting with version 5.8, MySQL offers a Mixed format, which is essentially a combination of Statement and Row. The details will be explained in the next chapter.

5.4 Using Logs to Restore Data

If binary logging is enabled on the MySQL server, in the event of an unexpected loss of data from the database, you can use the MySQLbinlog tool to recover data from the logs from a specified point in time (for example, the last backup) up to now or another specified point in time.

Mysqlbinlog restores data using the following syntax:

Mysqlbinlog [option] filename | mysql - uuser - ppass;Copy the code

The command can be interpreted as follows: use the mysqlbinlog command to read the contents of filename, and then use the mysql command to restore the contents to the database.

• Filename: indicates the log filename.
• Option: Optional. The two important pairs of option parameters are -start-date and -stop-date, and –start-position and -stop-position₀
  • -start-date and -stop-date: specify the start time and end time of database recovery.
  • -start-position and -stop-position: Specifies the start position and end position of the data to be restored.

Note: When the mysqlbinlog command is used to restore the data, the data with a small number must be restored first. For example, atguigu-bin.000001 must be restored before atguigu-bin.000002.

5.5 Deleting Binary Logs

MySQL binaries can be configured for automatic deletion, and MySQL also provides a safe way to manually delete binaries. PURGE MASTER LOGS deletes only a specified portion of binary log files, and RESET MASTER deletes all binary log files.

Details are as follows:

PURGE MASTER LOGS: PURGE MASTER LOGS

PURGE MASTER LOGS syntax is as follows:

PURGE {MASTER | BINARY} LOGS TO 'the specified log file name' PURGE {MASTER | BINARY} LOGS BEFORE 'date specifiedCopy the code

For example, use the PURGE MASTER LOGS statement to delete all LOGS created earlier than binlog.000005. (1) Restart MySQL several times to generate multiple log files. The list of binary log files is then displayed with the SHOW statement

SHOW BINARY LOGS;
Copy the code

(2) Execute the PURGE MASTER LOGS statement to delete all LOGS created earlier than binlog.000005

PURGE MASTER LOGS TO "binlog.000005";
Copy the code

(3) Display the binary log file list

SHOW BINARY LOGS;
Copy the code

All log files older than binlog.000005 have been deleted.

For example, use the PURGE MASTER LOGSi statement to delete all log files created before October 25, 2020. The procedure is as follows: (1) Display the binary log file list

SHOW BINARY LOGS;
Copy the code

(2) Run the mysqlbinlog command to view the contents of binary log file binlog.0000o5

mysqlbinlog --no-defaults "/var/lib/mysql/binlog/atguigu-bin.000005"
Copy the code

The result shows that 20220105 is the log creation time, that is, January 05, 2022. (3) Use the PURGE MASTER LOGS statement to delete all log files created before January 5, 2022

PURGE MASTER LOGS before "20220105
Copy the code

(4) Display the binary log file list

SHOW BINARY LOGS;
Copy the code

All binary log files before January 5th, 2022 have been deleted. The last log file has not been deleted because it is currently in use and has not recorded the last time, so it has not been deleted.

2. RESET MASTER: Deletes all binary log files

Use the RESET MASTER statement to clear all binlog logs. MySQL will re-create the binary file with the new log file extension name numbered from 000001. Careful! For example, use the RESET MASTERi statement to delete all log files.

(1) Restart the MySQL service several times and run the SHOW statement to display the binary log file list.

SHOW BINARY LOGS;
Copy the code

(2) Run the RESET MASTER statement to delete all log files

RESET MASTER;
Copy the code

After the statement is executed, all original binary logs are deleted.

5.6 Other Scenarios

Binary logs can be used to restore the database without loss by backing up the database and storing incremental information in binary logs. However, in a scenario where there is a large amount of data and a large number of databases and tables (such as the application of separate databases and tables), data recovery using binary logs can be very challenging because the start and end locations are not easy to manage

In this case, an effective solution is to configure a master/slave database server, or even a master/slave architecture, to synchronize the contents of binary log files to the slave database server through the relay log. This can effectively avoid data anomalies caused by database failures and other problems.

6. Binlogs

6.1 Write Mechanism

When a transaction is executed, the log is written to the binlog cache first, and when a transaction is committed, the binlog cache is written to the binlog file. (redo log)

Since the binlog of a transaction cannot be broken apart, no matter how big the transaction is, it must be written at once, so the system allocates a block of memory for each thread as the binlog cache.

The binlog_cache_size parameter can be used to control the size of the binlog cache for a single thread. If the size of the cache exceeds this parameter, it must be temporarily saved to a disk (Swap).

In the figure above, write indicates that the log is written to the page cache of the file system, but the data is not persisted to the disk. Therefore, fSYC in the figure above is the operation that persists the data to the disk

The timing of write and fsync can be controlled by the sync_binlog parameter, which defaults to 0. If the value is 0, only write is committed for each transaction. The system determines when to execute fsync. Although the performance is improved, the binglog in the page cache is lost when the machine is down.

The diagram below:

For security, set it to 1 to indicate that fsync is performed every time a transaction is committed, just like the redo log flush process.

Finally, there is a compromise that can be set to N(N>1), which means that each committed transaction is written, but N transactions are accumulated before fsync.

In IO bottleneck scenarios, setting sync_binlog to a large value can improve performance. Similarly, if the machine goes down, the binlog of the last N transactions will be lost.

6.2 Comparison between Binlog and Redolog

• Redo log is a physical log of what was changed on a data page. InnoDB storage engine layer generates the redo log.

• The binlog is a logical log that records the original logic of the statement. It is similar to “add 1 to the C field in the row ID=2” and belongs to the MySQL Server layer.

• Although they both fall under the category of persistence guarantees, the emphasis is different.

  • redo logInnoDB storage engine has crash recovery capability.
  • binlogThis ensures the data consistency of MySQL cluster architecture.

6.3 Two-phase Commit

Redo log and binlog are recorded during the execution of an update statement. Redo log is written continuously during the execution of a transaction, while binlog is written only when a transaction is committed. Therefore, redo log and binlog write at different times.

What can I do if the logic between redo log and binlog is inconsistent? SQL statement update T set c = 1 where ID =2

What happens if an exception occurs during binlog writing after redo log writing?

There is no modification record in the binlog. As a result, when the database is restored using the binlig log, the value of c in the restored line is 0, whereas the value of C in the original library was 1 due to the redo log.

To solve the logical consistency problem between two logs, InnoDB storage engine uses a two-phase commit scheme. The principle is simple: Split redo log writing into two steps prepare and commit.

useAfter two phases of submissionIf an exception occurs while writing to the binlog, it does not affect. When MySQL restores data from the redo log, the redo log is in the prepare state and there is no corresponding bin log. Therefore, the transaction is rolled back.

If the redo log is successfully written, the redo log is committed in the commit phase. If the redo log fails, the rollback is performed

In another scenario, if an exception occurs during the redo log commit phase, will the transaction be rolled back?

MySQL does not roll back the transaction. The redo log executes the logic outlined above. Although the redo log is in the prepare state, the binlog is found by the transaction ID, so MySQL considers it complete and commits the transaction recovery data.

7. Relay log

7.1 introduction

Relay logs exist only on slave servers in the master slave architecture. To be consistent with the primary server, the secondary server reads binary logs from the primary server and writes the read information to the local log file, which is called the trunk log file. Then, the secondary server reads the relay logs and updates the data of the secondary server according to the content of the relay logs to complete data synchronization between the primary and secondary servers.

After the primary and secondary servers are set up, the trunk logs are saved in the data directory of the secondary server by default.

The file name is in the format of secondary server name-relay-bin. Serial number. Relay logs also have an index file: the slave server name -relaybin.index, which is used to locate the relay logs currently in use.

7.2 Viewing Trunk Logs

Relay logs are in the same format as binary logs and can be viewed using the mysqlBinlog tool. Here is a snippet of the relay log:

SET TIMESTAMP=1618558728/*! * /. BEGIN /*! * /. # at 950 #210416 15:38:48 server id 1 end_log_pos 832 CRC32 0xcc16d651 Table_map: `atguigu`.`test` mapped to number 91 # at 1000 #210416 15:38:48 server id 1 end_log_pos 872 CRC32 0x07e4047c Delete_rows: table id 91 flags: STMT_END_F -- server id 1 is primary server, Meaning is the primary server delete a row BINLOG 'CD95YBMBAAAAMgAAAEADAAAAAFsAAAAAAAEABGRlbW8ABHRlc3QAAQMAAQEBAFHWFsw = CD95YCABAAAAKAAAAGgDAAAAAFsAAAAAAAEAAgAB/wABAAAAfATkBw== '/*! * /. # at 1040Copy the code

Table atguigu.test (” server id 1 “); atguigu.test (” server id 1 “);

Table atguigu.test number 91, log position 832; Delete the record whose number is 91 and log position is 872.Copy the code

7.3 Typical Restoration Errors

If the secondary server goes down, sometimes you have to reinstall the operating system to recover, which may cause your server name to be different from the previous one. The relay log contains the name of the slave server.

In this case, you may not be able to read data from the relay log when you restore the slave server, thinking that the log file is corrupted, but in fact the name is wrong.

The solution is simply to change the name of the secondary server back to the previous name.

Refer to the article

MySQL from the beginning to the master of MySQL Technology Insider: InnoDB Storage Engine (Version 2) how MySQL is running From the root to understand MySQL database Index Design and Optimization