Summary of some basic commands for MySQL logging operations

MySQL log mainly includes: error log, query log, slow query log, transaction log, base 2 log;

Logs are an important part of the mysql database. The log file records the changes that occurred during the mysql database run. That is, it is used to record the client connection status of the mysql database, the execution of the SQL statement and error information, etc. When the database is accidentally corrupted, the log can be used to see what went wrong with the file, and the log file can be used for data recovery.

The error log

In the mysql database, error logging is enabled by default. Also, error logging cannot be prohibited. By default, the error log is stored in a data file in the mysql database. The error log file is usually named hostname.err. Where hostname represents the server host name.

Error log information can be configured by itself. The information recorded in the error log can be defined by ES19en-ES20en and ES21en-ES22en, where ES23en-ES24en defines whether to enable the error log and the storage location of the error log, and ES25en-ES26en defines whether to also define the warning information to the error log. By default error log records about the following information: the information in the server startup and shutdown process, is not necessarily an error message, such as how mysql startup InnoDB tablespace file, how to initialize its own storage engines, etc.), the server is running in the process of error information, event scheduler run 1 when the event information, when to start the server from the server process to produce information.

Let's define the functions of the mysql error log:

In general, log-level definitions without callback variables are defined only at the global level.

Whether logging is enabled

mysql>show variables like 'log_bin';

How do I know the current log

mysql> show master status;

Base 2 log file See base 2 log file with mysqlbinlog

shell>mysqlbinlog mail-bin.000001

or

shell>mysqlbinlog mail-bin.000001 | tail

Note: The log directory, if not specified, defaults to the datadir configuration directory and views the configuration directory through my.ini
Use a similar command under Windows.

Manually specify in version 5.6 and above. 5.6 The following version defaults to file_name as $datadir/ ES55en-ES56en
The base 2 log is used to record all statements that change data. Mainly used for replication and just-in-time point recovery.
The tool to view the base 2 log is: mysqlbinlog
The base 2 log contains all statements that have updated data or that have potentially updated data (for example, 1 DELETE that does not match any rows). Statement is saved as an "event" that describes data changes. The base 2 log also contains information about the execution time of each statement that updates the database. It does not contain statements that do not modify any data.
The primary purpose of base 2 logging is to update the database as much as possible during recovery (just-in-time recovery) in the event of a database failure, because base 2 logging contains all updates made after the backup. Base 2 logging is also used to record on the master replication server all statements that will be sent to the slave server.
Is the base 2 log the statement executed or the result data executed?
The first case:
Add a table with 100,000 rows of data, and now execute the following statement to increase the value of the amount field by 1000 from the original:

UPDATE sales.january SET amount=amount+1000 ; 

At this point, the log will be very large if you want to record the resulting data after execution.
Therefore, the execution statement should be logged in this case. This approach is called statement based base 2 logging.
Case 2:
What if the current time is inserted into a field? As follows:

INSERT INTO tb SET Birthdate=CURRENT_TIME();

The statement cannot be recorded at this point because the execution results are different at different times. This is the value that should be logged for the 1 row, which is the base 2 log based on the row (row).
In some cases, a combination of two approaches may be used, which is called a mixed base 2 log.
Base 2 Logging time:
By default, the base 2 log is not synchronized with the hard disk every time a write is made. So if the operating system or machine (not just the MySQL server) crashes, it is possible that the last statement in the base 2 log is missing. To prevent this, you can use the sync_binlog global variable (1 is the safest value, but also the slowest) to keep the 2-base log in sync with the hard disk after every 2-base log write to N.
Updates to non-transactional tables are saved to the base 2 log immediately after execution. For a transaction table, such as BDB or InnoDB, all updates that change the table (UPDATE, DELETE, or INSERT) are cached until the server receives the COMMIT statement. At this point, mysqld writes the entire transaction to the base 2 log before executing COMMIT. When the thread processing the transaction starts, it allocates binlog_cache_size size of memory for the buffered query. If the statement is greater than this value, the thread opens the temporary file to save the transaction. The temporary file is deleted after the thread ends.

Log recovery: (Database backup time :2013-02-30 10:10:10 Data error 1 moment before :2013-02-30 10:10:10)
Use the ES101en.exe tool
(1) Open cmd and enter the log directory
(2) Restore the backup database
(3) Re-execute the log from the beginning of the backup database to 1 minute before the error.
Example 1: Restore by time period

mysqlbinlog --start-datetime="2013-02-30 10:10:10" --stop-datetime="2013-02-30 10:10:10" log.00001 | mysql -uroot -p123456

Since it was found in the test that time was used for recovery, it is not accurate to restore sql during this period, which is hereby marked (to be studied).
Example 2: Restore with the log position (the log must be turned on to determine where to start restoring the log and where the log was before the error)
(A):

mysqlbinlog log.00001 >F:log.sql

-- Import base 2 file ES119en.00001 into file log log.sql
(B): Open the ES124en.sql log file to determine the recovery point
(C):

mysqlbinlog --start-position="5230766" --stop-position="5231104" PC-201304011235-bin.000001 | mysql -uroot -p111111

Note: | must be followed by mysql information to re-execute this section of the log between points