Sample code sharing that explains the details of the MySQL logging system

This article summarizes and introduces the detailed information of the log system in MySQL. It is very detailed. Friends in need can refer to it.

Everyone who has done large-scale systems knows this. , the role of logs should not be underestimated. Often in the middle and later stages of a project, optimization and upgrading decisions are made based on logs. So when learning MySQL, of course you can’t miss the logging part. The optimizations discussed in our interviews are all derived from logs. Systematically studying mysql logs can help us accurately locate problems and improve our work level. In addition, the following series of logs will focus on the operation and maintenance of DBA, systematically understand the configuration of all aspects of MySQL, know yourself and the enemy, and make MySQL a convenient data warehouse for you.

1. MySQL’s Log Class Type

By default, all MySQL logs are stored in the root directory of the database in the form of files:

[root@roverliang data]# pwd
/usr/local/webserver/extend_lib/mysql/data
[root@roverliang data]# ls
auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mytest performance_schema roverliang roverliang.err roverliang.pid test

The log types of MySQL are as follows:

1. Error log (error), MySQL service instance startup, running or stopping and other related information.
2. General query log (general), all SQL statements or MySQL commands run by the MySQL service instance.
3. Binary log (binary), all update statements executed on the database, excluding select and show statements.
4. Slow query log (slow), SQL statements whose execution time exceeds the long_query_time setting value, or SQL statements that do not use indexes.

2. MySQL log cache

A high-speed, stable, and reliable system, in which cache must play a vital role. MySQL log processing also uses a caching mechanism. MySQL logs are initially stored in the memory of the MySQL server. If the specified storage capacity is exceeded, the logs in the memory are written (or flushed) to external memory, and are permanently stored on the hard disk in the form of database tables or files.

3. MySQL error log (error log)

MySQL’s error log mainly records detailed information about each start and stop of the MySQL service instance, as well as the running process of the MySQL instance. Warnings or error messages generated. Unlike other logs, MySQL's error log must be turned on and cannot be turned off.

By default, the file name of the error log is: hostname.err. However, the error log does not record all error information. Only critical errors (critical) that occur during the running of the MySQL service instance will be recorded.

mysql> show variables like 'log_error'\G
*************************** 1. row ***************************
Variable_name: log_error
Value: /usr/local/webserver/extend_lib/mysql/data/roverliang.err
1 row in set (0.02 sec)

4. MySQL general query log (general log)

MySQL general query log records all operations of the MySQL service instance, such as select, update, insert, delete, etc. operation, regardless of whether the operation was performed successfully. There is also information about the connection and disconnection between the MySQL client and the MySQL server, whether the connection is successful or failed. There are three parameters related to MySQL general query log.

[]()general_log

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log  | OFF  |
+---------------+-------+
1 row in set (0.01 sec)

You can turn on the general query log by setting @@global.general_log = 1.

mysql> set @@global.general_log =1;
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log  | ON  |
+---------------+-------+

But modifying MySQL's variables in this way will only take effect while the current MySQL instance is running. Once MySQL is restarted, it will be restored to the default state. The way to make it permanent is to modify the my.cnf file of mysql. Add after Configuration file:

general_log = 1 
general_log_file

Once the general query log is turned on, the MySQL service instance will automatically create a general query log file. The general_log_file parameter sets the physical location of the general query log file. As follows:

mysql> show variables like 'general_log_file';
+------------------+-----------------------------------------------------------+
| Variable_name  | Value                           |
+------------------+-----------------------------------------------------------+
| general_log_file | /usr/local/webserver/extend_lib/mysql/data/roverliang.log |
+------------------+-----------------------------------------------------------+

Note: Since the ordinary query log records almost all operations of MySQL, for database servers with frequent data access, if the ordinary query log of MySQL is turned on, the performance of the database will be greatly reduced. Therefore, it is recommended to turn off the normal query log. Only during special periods, if you need to track some special query logs, you can temporarily open the ordinary query log.

log_output

The log_output parameter sets the contents of the normal query log and the slow query log to be stored in the database table. You can use set @@global.log_output='table' to store normal query logs and slow query logs in the general table and slow_log table in the mysql system database. It is worth noting that the storage engine of these two tables is CSV. After that, you can use SQL statements when viewing the new ordinary query log content;

set @@global.log_output = 'table';
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output  | TABLE |
+---------------+-------+

5. MySQL slow query log (slow log)

Questions related to slow query logs. During the interview, the interviewer is very fond of talking about this issue. In the past, I could only talk about the MySQL master-slave architecture and optimize MySQL from all aspects, but I didn't really understand how to enable slow queries and related configurations.

使用MySQL慢查询日志可以有效的跟踪 执行时间过长 或者 没有使用索引的查询语句。这种包括select 语句，update语句，delete语句，以及insert语句，为优化查询提供帮助。与普通查询日志不同的另一个区别在于，慢查询日志只包含成功执行过的查询语句。与MySQL慢查询日志有关的参数有5个。

1、slow_query_log

slow_query_log 设置慢查询日志是否开启。

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF  |
+----------------+-------+

2、slow_query_log_file

慢查询日志一旦开启，MySQL实例将自动创建慢查询日志文件。slowquerylog_file 所指定的文件，存放慢查询日志内容。 修改方法和上文所示的一致。直接到my.cnf 文件中进行编辑。

3、long_query_time

long_query_time 设置了慢查询的时间阈值。默认阈值是10s。

4、log_quries_not_using_indexes

log_quries_not_using_indexes 是否将不使用索引的查询语句记录到慢查询日志中，无论查询速度有多快。

mysql> set @@global.log_queries_not_using_indexes=1;

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name         | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON  |
+-------------------------------+-------+

5、log_output

设置了普通查询日志以及慢查询日志的输出形式，值有两个file、table;

六、MySQL慢查询日志查看

log_output参数可以设置慢查询日志的输出形式。默认为FILE,可以设置为TABLE;

mysql> desc mysql.slow_log;
+----------------+---------------------+
| Field     | Type        |
+----------------+---------------------+
| start_time   | timestamp      |
| user_host   | mediumtext     |
| query_time   | time        |
| lock_time   | time        |
| rows_sent   | int(11)       |
| rows_examined | int(11)       |
| db       | varchar(512)    |
| last_insert_id | int(11)       |
| insert_id   | int(11)       |
| server_id   | int(10) unsigned  |
| sql_text    | mediumtext     |
| thread_id   | bigint(21) unsigned |
+----------------+---------------------+

其中: lock_time表示该SQL执行时被锁阻塞的时间。 rows_send表示执行SQL后返回的内容行数。 rows_examined表示该SQL执行时实际扫描的记录条数。

但是使用TABLE来存储慢查询日志并不常见，业务量较大的情况下，对于系统的主服务会有影响。我们可以使用FILE 的方式来进行日志存储。安装MySQL的时候在MySQL的bin目录下已经默认安装了mysqldumpslow.pl工具来进行慢查询的日志分析。 window下使用这个工具，可能需要折腾些配置，这不在本文的介绍范围内，学系统服务，还是移步linux吧。 linux下的命令以及工具都可以使用 命令本身 + --help 的选项来查看帮助文档。

-s 表示按照何种方式排序

子选项: c、t、l、r

c : SQL执行的次数
t : 执行时间
l : 锁等待时间
r : 返回数据条数
at、al、ar 是对应 t l r 的平均值。 -t :表示返回前 N 条记录。

-g： grep 缩写。包含模糊匹配

常用法如下：

//返回访问次数最多的20条SQL语句
./mysqldumpslow -s c -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

//返回return记录数最多的20条SQL语句
./mysqldumpslow -s r -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

//返回含有like的SQL语句
./mysqldumpslow -g 'like' 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

七、二进制日志（binary）

二进制日志和前面提到的几种日志不同，二进制不能直接通过cat或者less 文本查看器查看。需要借助专业的工具。二进制日志主要记录数据库的变化情况，因此可以用作主从库的同步。内容主要包括数据库所有的更新操作，use语句、insert语句、delete语句、update语句、create语句、alter语句、drop语句。用一句更简洁易懂的话概括就是：所有涉及数据变动的操作，都要记录进二进制日志中。

启动二进制日志 使用 show variables like 'log_bin'\G 来查看二进制日志是否开启。

mysql> show variables like 'log_bin'\G
*************************** 1. row ***************************
Variable_name: log_bin
    Value: OFF
1 row in set (0.00 sec)

mysql> set @@global.log_bin=1;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
mysql>

看到log_bin 默认是不开启的，并且是个只读的变量，需要在my.cnf中配置，然后重启MySQL。 service mysql restart 重启MySQL后，在data目录会发现生成了一个1.000001的文件。实际上每次MySQL重启，在目录下都会生成一个这样的文件，文件名依次递增。此外，MySQL还会在该目录下创建一个二进制日志的索引文件，可以通过命令show variables like 'log_bin_index'\G来查看索引文件的位置，然后使用cat命令看下。会发现，里面记录着二进制文件的相对路径。

查看二进制日志 可以使用MySQL 中自带的工具。具体位置在mysql的bin目录下。 mysqlbinlog命令的常用选项：

-s                          以精简的方式显示日志内容
-v                          以详细的方式显示日志内容
-d=数据库名                  只显示指定数据库的日志内容
-o=n                        忽略日志中前n行MySQL命令
-r=file                    将指定内容写入指定文件

--start-datetime 
                            显示指定时间范围内的日志内容
--stop-datetime        

--start-position       
                            显示指定位置间隔内的日志内容
--stop-position    

获取当前使用的二进制日志文件

mysql> show master status;
+----------+----------+--------------+------------------+-------------------+
| File   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------+----------+--------------+------------------+-------------------+
| 1.000002 |   120 |       |         |          |
+----------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

使用二进制日志恢复数据

语法很简单：

mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p

mysqlbinlog 之后可以跟 --start-datetime 、--stop-datetime 、start-position 、stop-position 等参数。

--start-datetime 、--stop-datetime 这两个参数可以基于时间点进行数据恢复；

start-position 、stop-position 可以进行操作点更加细化的进行数据恢复；

MySQL二进制日志相关参数

mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name              | Value        |
+-----------------------------------------+----------------------+
| binlog_cache_size            | 32768        |
| binlog_checksum             | CRC32        |
| binlog_direct_non_transactional_updates | OFF         |
| binlog_error_action           | IGNORE_ERROR     |
| binlog_format              | STATEMENT      |
| binlog_gtid_simple_recovery       | OFF         |
| binlog_max_flush_queue_time       | 0          |
| binlog_order_commits          | ON          |
| binlog_row_image            | FULL         |
| binlog_rows_query_log_events      | OFF         |
| binlog_stmt_cache_size         | 32768        |
| binlogging_impossible_mode       | IGNORE_ERROR     |
| innodb_api_enable_binlog        | OFF         |
| innodb_locks_unsafe_for_binlog     | OFF         |
| max_binlog_cache_size          | 18446744073709547520 |
| max_binlog_size             | 1073741824      |
| max_binlog_stmt_cache_size       | 18446744073709547520 |
| simplified_binlog_gtid_recovery     | OFF         |
| sync_binlog               | 0          |
+-----------------------------------------+----------------------+

max_binlog_size

maxbinlogsize 单个二进制日志文件的大小。如果超过该值，则生成新的文件，后缀名+1；

binlog_cache_size

binlogcachesize 内存中存放二进制日志的缓存大小

sync_binlog

sync_binlog 缓存中写入几次二进制日志，开始同步刷新到外存（硬盘）中。

log_slave_updates

logslvaeupdates 用于主从复制

二进制日志的清理

原则上要先将准备清理的日志通过物理备份的方式备份到其他存储设备上，永久留存。 然后推荐使用下面两种风险较小的清理方式：

第一种：

purge master logs before '2017-02-16 00:00:00';

第二种：

直接在MySQL的配置文件my.cnf 中设置expire_logs_days参数来设置二进制文件的过期天数，过期的二进制文件将会被自动删除。建议在删除前另启一个周期计划任务，定期去备份二进制任务。免得一些数据若干天才发现出现了差错，而二进制日志被自动删除了。

expire_logs_days=90

八 、InnoDB 事务日志

MySQL会最大程度的利用缓存，从而提高数据的访问效率。那么换一句话来说，任何高性能的系统都必须利用到缓存，从各个层面来讲，缓存都发挥了巨大的作用。再上升到一个高度提炼一下：缓存和队列是实现高性能的必走之路。那么对于数据库来说这个却是个很棘手的问题，要保证数据更高效率的读取和存储，所以要利用到缓存。但是要保证数据的一致性，则必须保证所有的数据都必须准确无误的存储到数据库中，及时发生意外，也要保证数据可恢复。我们知道InnoDB是一个事务安全的存储引擎，而一致性是事务ACID中的一个重要特性。InnoDB存储引擎主要是通过InnoDB事务日志实现数据一致性的，InnoDB事务日志包括重做（redo）日志，以及回滚（undo）日志。

InnoDB事务日志与前面提高的日志不同，InnoDB事务日志由InnoDB存储引擎自行维护，而且内容不能被数据库管理员读取。

重做日志（redo）

重做日志主要是记录已经全部完成的事务,即执行了commit的日志，在默认情况下重做日志的值记录在iblogfile0 以及iblogfile1重做日志中。

[root@roverliang data]# pwd
/usr/local/webserver/mysql/data
[root@roverliang data]# ls ib*
ibdata1 ib_logfile0 ib_logfile1

回滚日志（undo）

回滚日志主要记录已经部分完成并且写入硬盘的未完成事务，默认情况情况下，回滚日志的信息记录在表空间文件，共享表空间文件ibdata1或者独享表空间未见ibd中。

由上图我们可以知道，回滚日志默认是记录在ibdta1中的。我的mysql系统版本为：5.6.24.

Checkpoint机制

MySQL服务器崩溃后，重新启动MySQL服务时，由于重做日志（redo）与回滚日志（undo）日志的存在，InnoDB通过回滚日志（undo）日志将所有已部分完成并写入硬盘的未完成事务进行回滚操作（rollback）。然后将重做日志（undo）日志中的事务全部重新执行一遍即可恢复所有的数据。但是数据量过大，为了缩短恢复的时间InnoDB引入了Checkpoint 机制。

脏页（dirty page）

当事务需要修改某条记录是，InnoDB首先将该数据所在的数据块从外存中读取到硬盘中，事务提交后，InnoDB修改数据页中的记录，这时缓存的数据页已经和外存中的数据块已经不一样了，此时缓存中的数据页称为脏页（dirty page）,脏页刷新到外存中，变为干净页（clean page）。

备注：一个内存页默认为4K，或者4K的倍数。你可以把内存想象成一本可以擦洗的书，每次MySQL读数据的时候，向内存申请几张干净的书页，然后书写上去。当数据刷新到硬盘之后，这些数据页马上被擦除，供其他程序使用。

日志序列号（log sequence number）

日志序列号（LSN）是日志空间中每条日志的结束点，用字节偏移量表示，在Checkpoin和恢复时使用。

Checkpoint 机制原理 假设在某个时间点，所有的脏页（dirty page）都被刷新到了硬盘上，这个时间点之前的所有重做日志（redo）就不需要重做了.系统就就下这个时间点重做日志的结尾位置作为Checkpoint,Checkpoint之前的重做日志也就不需要再重做了，可以放心的删除掉。为了更好的利用重做日志（redo）的空间,InnoDb采用轮循的策略使用重做日志空间，因此InnoDB的重做日志文件至少为2个。通过Checkpoint 机制，通过重做日志（redo）将数据库崩溃时已经完成但还没有来得及将缓存中已经修改但还未完全写入外存的事务进行重做（undo）操作，即可保证数据的一致性，也可以缩短恢复时间。

InnoDB重做日志(redo)的参数

innodb_log_buffer_size: 设置了重做日志缓存的大小。
innodb_log_files_in_group : 设置了日志文件组中重做日志（redo）日志的数量。
innodb_log_file_size: 设置了重做日志文件的大小，文件越大，恢复起来耗时越长。
innodb_mirrored_log_groups: 重做日志镜像文件组数量，只能设置为1.
innodb_log_group_home_dir： 设置日志文件组存放的目录，默认在数据库根目录下。

InnoDB回滚日志（undo）的参数

innodb_undo_directory: 设置回滚日志存放的目录。
innodb_undo_logs :设置回滚日志的回滚段大小，默认为128k
innodb_undo_tablespace:设置了回滚日志由多少个回滚日志文件组成，默认为0.
Warning 特别注意：安装MySQL后需要在my.cnf中设置回滚日志的参数，如果创建数据库以后再设置回滚日志的参数，MySQL就会报错，并且回滚日志建好后，就不能再次修改或者增加。

九 、日志文件备份

备份的时候可以使用flush logs，关闭当前的所有日志文件，然后产生新的日志文件。关闭日志文件后，可以采用物理方式备份。 另外flush logs可以添加具体的日志类型：

flush error logs
flush general logs
flush binary logs
flush slow logs

The above is the detailed content of Sample code sharing that explains the details of the MySQL logging system. For more information, please follow other related articles on the PHP Chinese website!