MySQL log

Log type

Log files	Type of information included in the document
Error log	start-up 、 function 、 Problems when stopping
Query log	Record the establishment of client connection and execution statements
Binary log	Record all changed data statements . For recovery and point in time recovery
Slow log	Execution time exceeded long_query_time All queries / Queries that do not use indexes
Transaction log	Record InnoDB The log generated when a transaction is executed by a storage engine that supports transactions

Error log

Record the content

1. Server startup / Information during shutdown
2. Error message during server operation
3. Information generated when event scheduling runs for a time
4. Start the server process on the server to generate information

Definition

In the configuration file (/etc/my.cnf) use log-error[=file_name] Appoint mysql Where to save the error log file
1. There is no log-error, The error log will be output at the terminal
mysql The configuration file

View the current error log file location

mysql> show variables like "%log_error%";
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| binlog_error_action | ABORT_SERVER |
| log_error           | stderr       |
| log_error_verbosity | 3            |
+---------------------+--------------+
3 rows in set (0.00 sec)
 Output at the terminal 

2. There are only... In the configuration file log-error, use host_name.err, And write the log file in the data directory

mysql> show variables like "%log_error%";
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| binlog_error_action | ABORT_SERVER               |
| log_error           | /var/run/mysqld/mysqld.err |
| log_error_verbosity | 3                          |
+---------------------+----------------------------+
3 rows in set (0.00 sec)

3. Only the file name in the configuration file

mysql> show variables like "%log_error%";
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| binlog_error_action | ABORT_SERVER |
| log_error           | ./mysqld.err |
| log_error_verbosity | 3            |
+---------------------+--------------+
3 rows in set (0.00 sec)

Use flush.logs A new error log will be generated

Query log

Related variables

mysql> show variables like "%general_log%";
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)

Temporarily open

mysql> set global general_log=ON;
Query OK, 0 rows affected (0.00 sec)

View query log changes
tail -f Keep reading and displaying the latest content in the file , Monitor file changes , Real time monitoring effect

[root@localhost ~]# tail -f /var/lib/mysql/localhost.log
2022-04-17T01:11:58.715518Z	    2 Query	show tables
2022-04-17T01:12:09.012528Z	    2 Query	select *from score
2022-04-17T01:12:36.501340Z	    2 Query	show variables like "%general_log%"
2022-04-17T01:13:12.130441Z	    2 Query	select * from student
2022-04-17T01:13:27.211315Z	    2 Query	select name from student
2022-04-17T01:13:56.066529Z	    2 Query	set global general_log=OFF
/usr/sbin/mysqld, Version: 5.7.37 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2022-04-17T03:06:19.486324Z	    2 Quit	
2022-04-17T03:09:17.356900Z	    3 Connect	root@localhost on  using Socket
2022-04-17T03:09:17.357146Z	    3 Query	select @@version_comment limit 1
2022-04-17T03:09:30.645102Z	    3 Query	show databases
2022-04-17T03:09:39.951767Z	    3 Query	SELECT DATABASE()
2022-04-17T03:09:39.952130Z	    3 Init DB	homework
2022-04-17T03:09:39.953480Z	    3 Query	show databases
2022-04-17T03:09:39.953902Z	    3 Query	show tables
2022-04-17T03:09:39.954197Z	    3 Field List	score 
2022-04-17T03:09:39.979367Z	    3 Field List	student 
2022-04-17T03:09:47.005675Z	    3 Query	select * from score

Slow query log

Content
Record execution time exceeds long_query_time Query for ;
In profile ：
slow_query_log_file[=file_name],
1. If there is no file_name value , Default hostname , suffix slow.log
2… Only the filename , It's not an absolute path , The file is written to the data directory ;

Related variables

mysql> show variables like "%slow_query_log%";
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | OFF                               |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)
 Default not on , For tuning , Recommended Opening 

Temporarily open

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.01 sec)

Binary log

Content
Record ：insert,update,delete,replace,do,load data,select,truncate table

Turn on binary log

server_id Must be unique , Generally, it is the host IP The last digit of the address .

See what binaries are there

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       620 |
+------------------+-----------+
2 rows in set (0.00 sec)

View the current binary

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      620 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Generate new binaries
1. A new binary file will be generated during manual refresh

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       667 |
| mysql-bin.000003 |       154 |
+------------------+-----------+
3 rows in set (0.00 sec)

2. After restarting the service

[root@localhost ~]# systemctl restart mysqld
mysql> show binary logs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: homework

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       667 |
| mysql-bin.000003 |       177 |
| mysql-bin.000004 |       154 |
+------------------+-----------+
4 rows in set (0.01 sec)

3. achieve max_binlog_size value
stay my.cnf Set the max_binlog_size=200M, Indicates that the maximum size of binary log is 200M, exceed 200M Scroll .

View binary log file contents

[root@localhost ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002

Delete binary

mysql> purge master logs to 'mysql-bin.000002';
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |       667 |
| mysql-bin.000003 |       177 |
| mysql-bin.000004 |       154 |
+------------------+-----------+
3 rows in set (0.00 sec)