MYSQL-07 Log Management

Learning Goals

• MySQL Log
• Binary log
• Error log
• Querying common logs
• Slow query log

MySQL Log

The MySQL logs are divided into four categories, described below:

• Error log: Logs the problem that occurs when the MySQL service starts, runs, or stops.
• Query log: Records client connections and executed statements.
• Binary log: A statement that records all change data and can be used for data replication.
• Slow query log: Records all queries that have been executed for more than long_query_time, or queries that do not use indexes.

Commands to refresh the log: Flush LOGS, mysqladmin flush-logs, mysqladmin refresh.

Enabling logging reduces the performance of the MySQL database and consumes disk space.

Binary log

A binary log is a binary file that is used primarily to log MySQL statements that modify data or that may cause data changes, such as DELETE statements that do not have a matching delete condition, to include all information available in the update log in a transaction-safe manner.

The binary log records all operations that make changes to the MySQL database and records other additional information such as the time of the statement, the length of execution, the operation data, and so on, but it does not record SQL statements such as SELECT, show, etc. that do not modify the data. If all statements are logged, the query log is used.

Binary logs are primarily used for database recovery and master-slave replication, as well as auditing (audit) operations.

Starting and setting up binary logs

Add the following configuration information to the [Mysqld] node of the My.ini configuration file:

[Mysqld]port = 3306; The character set used by the service side defaults to the 8-bit encoded latin1 character set Character-set-server=utf8; Default storage engine Default-storage-engine=innodb that will be used when creating a new table; binary log settings start; Open binary log log-bin; Automatically clears the 10-day overdue log. The MySQL service will be restarted or deleted when the log is refreshed. expire-logs-days=10; Single log file size max_binlog_size=100m; binary log setup ends

　　

Once added, close and restart the MySQL service process to open and view the binary logs.

Confirm that the binary log is open:

In the MySQL data directory, generate a log file with the hostname as the file name:

If you need to modify the storage location and name of the binary log file, modify the value of Log-bin in the My.ini configuration file:

For example:

Log-bin= "D:/logs/binlog"

　　

After restarting the MySQL service, the log is saved in the "D:/logs" directory and the file name is "Binlog".

viewing binary logs

Each time the MySQL service restarts (or executes the SQL command flush LOGS), a file with a. 00000* suffix will be added, or the size of the log file exceeds the size of the Max_bin_log setting (default 1G), and a new file is recreated.

Example 1: View the number of log files and file names in MySQL:

Example 2: Viewing binary logs

On the command line, use the Mysqlbinlog command tool to view.

Mysqlbinlog need to add the parameter "-V", otherwise you cannot see the SQL statement.

Binary log format

• Row-based format row

Due to the large log volume recorded in row format, after MySQL 5.6, the official increased the Binlog_row_image parameter to improve its recording mode.

Value:

Full is the default value, which means that all the contents of a row of records are recorded, whether or not the column is modified.

Minimal records only the columns that have been modified, which can significantly reduce the amount of records.

Noblob with the full type, but columns of that large data type are not logged if the Blob or text type column is not modified.

• Segment-based format statement

Advantage: Record the SQL statement executed by each event, so there is no need to record the specific changes of each row, so the amount of log records is relatively small, saving disk IO and network IO (if only one record is modified or inserted, the row format may be less than the statement format).

Cons: To ensure that these SQL statements can be executed correctly from the library, the context information is logged to ensure consistent behavior during replay. However, using a non-deterministic function such as UUID () may result in inconsistent data between Master and slave.

Format settings:

Delete binary log

Example 1: Delete all binary files

Example 2: Delete a log file that was created at a time greater than rask-pc-bin.000005

Delete the former log file:

Or:

To perform a delete partial log command:

Temporarily start and stop the binary logging feature

Start

Stop it

recovering a database using binary logs

The Mysqlbinlog tool can recover data from a specified point in time.

Mysqlbinlog Recover Data Syntax format:

mysqlbinlog [option] filename | Mysql-uuser-ppassword

　　

Option Optional:

--start-datetime: Specifies the starting point at which to restore the database.

--stop-datetime: Specifies the end point in time at which the database is recovered.

--start-position: The start position of the restore (location in the log).

--stop-position: The end position of the restore.

Example 1: Restore all

Example 2: Revert to the status at 10:30:00 June 17, 2018

Error log

The error log is located by default in the data directory with the file name: Hostname.err. For example:

If you need to modify the default file store path and file name, you need to add the following information to the [Mysqld] node of the My.ini configuration file:

Log-error=[path/[filename]]

viewing log files

View directly using the Text tool.

Delete log files

You can delete a text log file directly. When MySQL is running, deleting the error log file does not automatically create a new log file that needs to be executed on the server side:

Mysqladmin-u Root-ppassword Flush-logs

　　

or execute it in MySQL.

FLUSH LOGS

　　

MySQL reboot will recreate the log file.

General Query Log

The general query log records all user actions for MySQL, including starting and shutting down services, executing queries, and updating statements.

To start and set up a common query log

Versions prior to 5.7

Under the My.ini configuration file [mysqld] node, add:

Log[=path/[filename]]

For example:

; Turn on Universal query log

　　

Version 5.7

; Turn on Universal query log general-log=1general_log_file= "D:/mysql.log"

　　

If you do not specify General_log_file, the query log is saved in the data directory with the file name: Hostname.log.

View the General query log

Use Notepad to view

Deleting and rebuilding a common query log

Locate the query log storage location through the My.ini configuration file and delete it directly from the file management system.

To rebuild the common query log command:

Mysqladmin-flush logs

　　

Slow query log

Slow query logging queries longer than a specified time log. By slowly querying the log, you can find statements that take longer to execute, perform less efficiently, and then optimize for them.

Start and set the slow query log

Add a slow query log configuration under the My.ini configuration file [mysqld] node:

5.6 and Previous versions:

; Turn on slow query log log-slow-queries; Set the query length threshold to 20 seconds (default 10 seconds) long_query_time=20

　　

Version 5.7:

Slow_query_log=on; Set the slow query log storage location slow_query_log_file= ' D:/msql_slow '; Set timeout, default is 10 seconds long_query_time=20

　　

Slow_query_log_file is not set, the log is stored in the data directory with the default file name Hostname-slow.log, for example:

View Slow query log

Use the Text tool to view the log files directly.

Delete Slow query log

Use file system removal directly. Cannot be created automatically after deletion, you need to execute the mysqladmin command or log in to the MySQL server to perform the flush log command:

# system Command mysqladmin-u User-ppassword flush-logs# MySQL EXECUTE statement flush logs;

　　

Comprehensive exercises

Exercise 1:2 in the log synthesis exercise:

1. Starts the binary log, specifying that the binary log name is binlog and the log storage location is D:/logs
2. Verifying the effect of restarting MySQL and flush logs execution statements on binary logs
3. To view binary log files using the Mysqlbinlog command
4. Use binary log files to recover data: Create a Test table T1 (id,name), insert two records into the datasheet, view the log file, find the SQL statement for log file records, delete the T1 table, and restore the T1 table using the Mysqlbinlog command
5. Delete the binary log file. Pauses and starts the binary log file.

Exercise two: Error log synthesis exercises

1. Set up and start the error log file, specifying that the error log file name is ErrLog and the log storage location is D:/logs
2. Use Notepad to view error log files
3. Delete error log file
4. Re-create the log file

Exercise three: General query log synthesis exercise

1. Set and start the Universal query log file, specifying that the universal query log filename is called General_query_log and the log storage location is D:/logs
2. View the General query log
3. Delete a generic query log

Exercise four: Slow query log synthesis exercise

1. Set and start slow query log file, specify slow query log filename called Slow_query_log, log storage location is D:/logs
2. View slow query log files
3. To delete a slow query log file

MYSQL-07 Log Management