Focus on WeChat official account [white letter], enter whitek's knowledge sharing planet. 🌍

preface

Last Friday, I interviewed the third side of byte and deeply felt the importance of database knowledge. I also realized that I was weak in my usual study of database. Even after having some internship experience, I still don't have much knowledge of database because of the division of development. I also believe that many people only stay at the stage of eight part essay on MySQL index, log, multi version concurrency control, ACID, etc.

Therefore, I intend to devote myself to learning the knowledge points related to MySQL database in the next period of time, write a series of articles related to MySQL, and cooperate with practical operation. I hope that after this series is completed, I can cover all the important knowledge points of MySQL and directly interview the online interviewer next time.

As the first article in this series, this article will focus on the log file types of MySQL and explain its functions. Combined with a certain practical demonstration, I believe you will have a deeper understanding of MySQL.

Concept of document

Before we start talking about MySQL log files, we should first clarify the concept of files. The MySQL database is stored on the hard disk. The user process communicates with a running MySQL instance process (that is, a running MySQL service is required). Through this service, the MySQL database files on the disk can be operated to achieve the purpose of data access and modification. Therefore, let's take a look at the files.

# In MySQL login status, execute this command to view the location of the main data files in our database
mysql> show variables like 'datadir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+
# After knowing the path, check it on the Linux terminal
sudo ls -lh /usr/local/mysql/data/
# The following is a partial display after executing the command (here are various log files of MySQL, files related to indexes, files of the database you have established, etc. first, there is a concept. You can go back and have a look when reading the various files mentioned below)
-rw-r-----    1 _mysql  _mysql   6.7K  1 19 10:51 binlog.000006
-rw-r-----    1 _mysql  _mysql   179B  1 24 15:04 binlog.000007
-rw-r-----    1 _mysql  _mysql    48B  1 24 15:05 binlog.index
-rw-r-----    1 _mysql  _mysql   4.1K  1 24 15:04 ib_buffer_pool
-rw-r-----    1 _mysql  _mysql    48M  2 26 14:06 ib_logfile0
-rw-r-----    1 _mysql  _mysql    48M 10 11 11:37 ib_logfile1
drwxr-x---    5 _mysql  _mysql   160B 10 13 15:01 blog
-rw-r-----    1 _mysql  _mysql    12M  2 26 14:04 ibdata1
-rw-r-----    1 _mysql  _mysql    12M  1 24 15:05 ibtmp1
-rw-r-----    1 _mysql  _mysql   180B  3  2 17:28 lilithgamesdeMacBook-Pro-42-slow.log
-rw-r-----    1 _mysql  _mysql   249B  3  2 17:55 lilithgamesdeMacBook-Pro-42.log
-rw-r-----    1 _mysql  _mysql    24M  2 26 14:04 mysql.ibd
-rw-r-----    1 _mysql  _mysql    11K  1 24 15:05 mysqld.local.err
-rw-r-----    1 _mysql  _mysql     4B  1 24 15:05 mysqld.local.pid
-rw-r-----    1 _mysql  _mysql    16M  2 26 14:06 undo_001
-rw-r-----    1 _mysql  _mysql    16M  2 26 14:06 undo_002

Parameter file

In MySQL login status, use the show variables command to view all parameters (in the form of key value). These parameters control various status attributes of MySQL. This command is frequently used when we need to obtain various statuses of MySQL.

# Query all parameters
mysql> show variables;
# You can also use the like parameter to specify the parameters to be queried
mysql> show variables like 'warning_count';
# Set the global parameter value in the form of key value pair
mysql> set global Parameter name=Parameter value

log file

error log

The error log records the startup, operation and shutdown of MySQL. For example, MySQL cannot start normally. You can view the error log file. In addition, the error log will record warnings during MySQL operation. You can view these warnings ⚠️ You can optimize the causes of warnings to optimize the database.

# In MySQL login status, use the following command to view the location of error log on the server
mysql> show variables like 'log_error';
+---------------+----------------------------------------+
| Variable_name | Value                                  |
+---------------+----------------------------------------+
| log_error     | /usr/local/mysql/data/mysqld.local.err |
+---------------+----------------------------------------+
# Then check the last 50 lines of the error log through tail -n 50 to locate the error (sudo obtains access rights not in MySQL login status, but in Linux user status)
sudo tail -n 50 /usr/local/mysql/data/mysqld.local.err
# Specific log contents will not be displayed

slow query log

The function of the slow query log is to record all SQL statements whose running time exceeds the set value into the slow query log. By regularly checking the slow query log and locating the slow SQL statement, analyze it to see whether the query is too slow due to the ineffective index and other reasons. The SQL statements whose query is too slow can be optimized.

# When the log execution time exceeds the My SQL log execution threshold, the log will be viewed
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
# Query whether to enable slow query log (closed by default)
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
# Enable slow query log
mysql> set global slow_query_log=on;

One parameter related to slow query log is log_queries_not_using_indexes, after opening. If the running SQL does not use an index, the MySQL database will also record this SQL statement in the slow query log file.

# Query whether to add SQL without index to the slow query log (closed by default)
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
# Opening parameters
mysql> set global log_queries_not_using_indexes=on;

View the path of the slow query log and view the contents of the slow query log through linux commands.

# Make sure you are logged in to MySQL
mysql> show variables like 'slow%';
+---------------------+------------------------------------------------------------+
| Variable_name       | Value                                                      |
+---------------------+------------------------------------------------------------+
| slow_launch_time    | 2                                                          |
| slow_query_log      | ON                                                         |
| slow_query_log_file | /usr/local/mysql/data/lilithgamesdeMacBook-Pro-42-slow.log |
+---------------------+------------------------------------------------------------+
# View the contents of the last 50 pages of the slow query log file on the Linux user terminal (this command is also used when viewing the error log above)
sudo tail -n 50 /usr/local/mysql/data/lilithgamesdeMacBook-Pro-42-slow.log

In addition, MySQL also provides other parameters to control the number of SQL statements that are allowed to be inserted into the slow query log without index per minute (to prevent the slow query log from growing too fast), and provides commands to filter the data in the slow query log, such as displaying the 10 SQL statements with the longest execution time. I won't repeat it here. Let's learn more about it later.

Query log

The query log records the request information of all MySQL databases, such as whether the request has been executed or not. Due to the existence of slow query logs, the use of query logs is generally less dependent, and there will be some performance loss after opening. It is closed by default.

# View the opening status and location of the query log
mysql> show variables like 'general_log%';
+------------------+-------------------------------------------------------+
| Variable_name    | Value                                                 |
+------------------+-------------------------------------------------------+
| general_log      | OFF                                                   |
| general_log_file | /usr/local/mysql/data/lilithgamesdeMacBook-Pro-42.log |
+------------------+-------------------------------------------------------+
# Open query log
mysql> set global general_log = on;
# View the query log file (in Linux user terminal instead of MySQL login status)
sudo tail -n 50 /usr/local/mysql/data/lilithgamesdeMacBook-Pro-42.log

binary log

The binary log records all operations that make changes to the MySQL database (excluding the select and show commands, but these will be recorded in the query log). The main functions of binary log are as follows:

• Data recovery: users can accurately recover data through binary logs.
• Master slave replication: by copying and executing binary logs, one remote MySQL database can be synchronized with another MySQL database that provides data in real time.

# Check the opening status of binary log through the command
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | ON                                 |
| log_bin_basename                | /usr/local/mysql/data/binlog       |
| log_bin_index                   | /usr/local/mysql/data/binlog.index |
| log_bin_trust_function_creators | OFF                                |
| log_bin_use_v1_row_events       | OFF                                |
| sql_log_bin                     | ON                                 |
+---------------------------------+------------------------------------+

Concluding remarks

This article initially introduces the types and functions of log files owned by MySQL. Among them, slow query log and binary log are also the high-frequency focus of the interview. Therefore, it is not enough to grasp the degree explained in this article, In the following articles, I will use examples to describe the practical operation of locating SQL problems in slow query logs (after locating slow SQL, we need to optimize it, so it involves index setting and optimization, which is another important knowledge point ‼️) Data recovery and synchronization with binary logs (also a very important knowledge point) ‼️).

The eight part essay is not the end of learning, but an outline and the starting point of our learning

We have built a spring and autumn recruitment preparation / internal push / chat group. Welcome to join us.

Pay attention to the official account [programmer white] and bring you to a programmer / student party with a bit of trouble.