MySQL数据库中根据日志文件的不同,可分为以下几种,下面将分别介绍。
一 错误日志
1 描述
记录MySQL的启动、停止信息以及在MySQL运行过程中的错误信息,类似于Oracle数据库的alert log,默认是开启。错误日志文件一般存放在数据目录下,以error.log作为文件名的结尾。
错误日志不光记录着是错误的信息,在MySQL 5.7初始化数据库中,加上--initialize参数,会生成一个临时的数据库初始密码,记录在log-error中。
2 参数
[mysqld]中的参数:
1)log_err(默认开启)
log_error=[path/[file_name]],如果不指定文件名,则默认hostname.err,修改后重启生效;
mysql> show variables like 'log_error';
+---------------+-------------------------+
| Variable_name | Value |
+---------------+-------------------------+
| log_error | ./strong.oracle.com.err |
+---------------+-------------------------+
1 row in set (0.02 sec)
2)log_warnings
该参数在5.7.2之后被不建议使用,取而代之的是log_error_verbosity;
3)log_error_verbosity
该参数有以下几种值选项,分别为:
1:仅仅记录错误;
2:记录错误和警告;
3:默认值,记录错误、警告和普通日志;
3 试验
1)查看错误日志
mysql> show variables like 'log_error';
+---------------+-------------------------+
| Variable_name | Value |
+---------------+-------------------------+
| log_error | ./strong.oracle.com.err |
+---------------+-------------------------+
1 row in set (0.01 sec)
2)错误日志维护
可以直接删除错误日志文件,删除后,错误日志不会自动重建,需要flush logs或者重启数据库。
[root@strong mydb]# ll strong.oracle.com.err
-rw-r-----. 1 mysql mysql 79058 May 11 10:14 strong.oracle.com.err
[root@strong mydb]# rm strong.oracle.com.err
rm: remove regular file `strong.oracle.com.err'? y
[root@strong mydb]# ll strong.oracle.com.err
ls: cannot access strong.oracle.com.err: No such file or directory
[root@strong mydb]# mysqladmin -uroot -pmysql -S /u01/data/mydb/3306.sock flush-logs;
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@strong mydb]# ll strong.oracle.com.err
-rw-r-----. 1 mysql mysql 0 May 11 10:39 strong.oracle.com.err
注:也可以使用mysql> flush logs;重建错误日志。
二 常规日志
1 描述
由log moudle写入,记录MySQL的所有用户操作,包括启动和关闭服务、执行查询和更新语句等。包括错误的记录,mysqld的所有动作,默认关闭。
平时不开启常规日志,出现问题时候再开启,防止大量日志充满空间。
2 参数
[mysqld]中的参数:
1)general_log
默认关闭,{0-关闭,1-开启},可以在线打开和关闭;
2)general_log_file
指定日志文件名称和路径;
3)log_output
该参数有如下选项,分别为:
TABLE:便于统计写入一个CSV引擎的表mysql.general_log中;
FIFLE:默认值,存储在文件;
TABLE,FILE:同时存放在表和文件中;
NONE:不存放日志;注:如果log_output=NONE,但是general_log为1,则默认不写常规日志;
4)sql_log_off
默认值为OFF,设置该值为ON,则不写常规日志;
3 试验
1)查看常规日志设置
mysql> show variables like 'general%';
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| general_log | OFF |
| general_log_file | /u01/data/mydb/strong.log |
+------------------+---------------------------+
2 rows in set (0.01 sec)
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.02 sec)
2)打开常规日志
mysql> set global general_log='ON';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'general%';
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| general_log | ON |
| general_log_file | /u01/data/mydb/strong.log |
+------------------+---------------------------+
2 rows in set (0.02 sec)
3)查看常规日志内容
[root@strong mydb]# more strong.log
/usr/local/mysql/bin/mysqld, Version: 5.7.25 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /u01/data/mydb/3306.sock
Time Id Command Argument
2019-05-11T02:57:09.481561Z 2 Query show variables like 'general%'
2019-05-11T02:58:34.571239Z 2 Query show databases
2019-05-11T02:58:48.788272Z 2 Query SELECT DATABASE()
2019-05-11T02:58:48.788990Z 2 Init DB mysql
2019-05-11T02:58:52.107128Z 2 Query show tables
4)常规日志维护
[root@strong mydb]# rm strong.log
rm: remove regular file `strong.log'? y
[root@strong mydb]# mysqladmin -uroot -pmysql -S /u01/data/mydb/3306.sock flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@strong mydb]# ls strong.log
strong.log
注:如果设置为常规日志存储在TABLE,也可从表general_log查看常规日志。
三 慢查询日志
1 描述
MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阈值(long_query_time、min_examined_row_limit、log_slow_admin_statements和log_queries_not_using_indexes)的语句,默认关闭。
注意:慢查询日志不会计算语句在开始执行之前等待锁的时间(initial slow query),mysqld只会从语句等待的锁释放掉,并开始执行开始计算时间(执行开始后的锁定时间会记录)。所以慢查询日志中的语句顺序和语句的发出顺序或者general log的顺序不同。
2 参数
[mysqld]中的参数:
1)slow_query_log
默认关闭,{0-关闭,1-开启},可以在线打开和关闭;
2)slow_query_log_file
指定慢查询日志的名称和位置;
3)long_query_time
设置SQL执行的阈值;
4)log_output
该参数有如下选项,分别为:
TABLE:便于统计写入一个CSV引擎的表mysql.slow_log中;
FIFLE:默认值,存储在文件;
TABLE,FILE:同时存放在表和文件中;
NONE:不存放日志;注:如果log_output=NONE,但是slow_query_log为1,则默认不写慢查询日志;
5)log_queries_not_using_indexes
默认值为OFF,记录没有用索引的查询;
6)log_throttle_queries_not_using_indexes
记录没有使用索引查询的语句条数在一分钟内记录的条数;
注:如何消除上线后未使用索引查询的隐患,线上开启5秒即可。
7)min_examined_row_limit
要检查的行数大于等于N时才记录为慢查询,前提是必须满足long_query_time和log_queries_not_using_indexes约束;
8)log_slow_admin_statements
管理语句是否记录,比如:ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE和REPAIR TABLE。
9)log_slow_slave_statements
从库的语句执行是否记录;
3 试验
1)开启慢查询
mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)
2)查看慢查询日志
注:慢查询日志可以使用percona-toolkit工具的pt-query-digest查看。
3)慢查询日志维护
[root@strong mydb]# rm strong-slow.log
rm: remove regular file `strong-slow.log'? y
[root@strong mydb]# ls strong-slow.log
ls: cannot access strong-slow.log: No such file or directory
[root@strong mydb]# mysqladmin -uroot -pmysql -S /u01/data/mydb/3306.sock flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@strong mydb]# ls strong-slow.log
strong-slow.log
四 二进制日志
1 描述
以“事件”的形式保存,描述数据的更改,包含关于每个更新数据库的语句的执行时间。
二进制日志的主要作用:
主从复制:在主服务器上把所有修改数据的操作记录到binlog中,通过网络发送给从服务器,从而达到主从同步;
恢复操作:数据可以通过binlog日志,使用mysqlbinlog命令,实现基于时间点和位置的恢复操作;
二进制日志记录在语句或事务完成之后立即执行(而不是在任何锁释放或任何提交完成之前写入),这确保日志以提交顺序记录。
对于不支持事务的表,执行之后马上会被记录到二进制日志中去;
对于未提交的事务中,更改事务表(例如InnoDB表)的所有更新(UPDATE、DELETE或INSERT)将被缓存,直到服务器收到COMMIT语句,在这一点上,mysqld在执行COMMIT之前将整个事务写入二进制日志;
对非事务性表的修改无法回滚。如果回滚的事务包括对非事务性表的修改,则会在最后使用ROLLBACK语句记录整个事务,以确保对这些表的修改进行复制。注:binlog只记录完整事务。
启用二进制日志选项数据库性能会降低1%,但保障数据库完整性,对于重要数据库值得以性能换完整。
2 参数
[mysqld]中的参数:
1)log_bin
默认关闭,{0-关闭,1-开启},不可以在线打开和关闭;
2)log_bin_basename
不指定路径则为hostname-bin.00000N;
3)log_bin_index
不指定路径则为hostname-bin.index;
注:文件路径需要在log_bin中设置,而不是log_bin_basename,所有的日志都建议自己起名字,不要用默认。
4)binlog_cache_size
内存中缓存二进制的缓冲区大小,所有未提交的事务会记录到一个缓存中,等待事务提交时,直接将缓存中的binlog写入二进制日志文件,该缓存大小由该参数决定,默认大小为32K,并且binlog_cache_size是基于会话的,也就是当一个线程要开始一个事务时,MySQL会自动分配一个该值大小的缓存,如果一个语句大于这个值,线程将打开一个临时文件来存储事务,当线程结束时,临时文件被删除。
注:对于大的事务,二进制日志会超过max_binlog_size设定的值,但事务仅仅写入一个二进制日志。设置此值需小心,如果设置太小就使用磁盘上的临时文件来记录,可通过show global status命令查看binlog_cache_use和binlog_cache_disk_use的使用情况,来判断当前的binlog_cache_size的设置是否合适。
binlog_cache_use状态变量显示用于存储语句的此缓冲区(可能还有一个临时文件)的事务数;
binlog_cache_disk_use状态变量显示有多少这些事务实际上必须使用临时文件;
这两个变量可用于将binlog_cache_size调整到足够大的值,以避免使用临时文件。
5)max_binlog_size
二进制日志的大小,该参数指定了单个binlog的最大值,如果超过该值就会自动生成新的binlog文件(重启MySQL实例也会生成新的binlog);
6)max_binlog_cache_size
限制用于缓存多语句事务的总大小,如果一个事务大于这个值,它将失败并回滚;
7)sync_binlog
{0,1}二进制内容从缓冲区落入磁盘的时机。
5.7.7以后,sync_binlog参数被默认设置为1,减少了binlog中的不一致可能性。
8)expire_logs_days
日期过期时间,一定要和备份相结合;
9)binlog_format
binlog模式,有以下几种:
基于语句:当时语句的原样写入;
基于行:只记录受影响的行;
混合模式:默认用语句模式,遇到下面请看用基于行模式:
uuid();
UDF;
temporary;
rows_found/row_count;
user()/current_user();
LOAD_FILE();
MySQL中的复制功能最初是基于从主库到从库的SQL语句的传输,称为基于语句的日志记录,可以使用--binlog-format=STATEMENT启动服务器使用此格式;
在基于行的日志记录中,主库将事件写入二进制日志,记录表中各个行如何被改变,因此,重要的是,表始终使用主键来确保可以有效的识别行,可以使用--binlog-format=ROW启动基于行的日志记录;
注:在MySQL 5.7.7之前,基于语句的日志记录格式是默认的,在MySQL 5.7.7及更高的版本中,基于行的记录格式是默认的。
使用混合日志记录(MIXED),默认使用基于语句的日志记录,但是如下所述,在某些情况下,记录模式将自动切换到基于行的记录,可以使用--binlog-format=MIXED启动基于混合模式的日志记录。
使用基于语句的二进制日志记录,主机将执行的查询写入二进制日志,这是一种非常快速、紧凑、高效的记录方法,在大多数情况下没有问题,然而,如果查询的设计方式使得数据修改是非确定性的,主从库的数据可能会变得不同。
3 试验
1)查看二进制日志配置
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.01 sec)
2)开启二进制日志
在my.cnf配置文件增加下面两行记录,保存然后重启MySQL实例:
log_bin=/u01/data/mydb/binlog/mysql-bin
log_bin_index=/u01/data/mydb/binlog/mysql-bin.index
3)查看二进制日志的格式
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.02 sec)
注:修改二进制日志格式需要super权限;
4)查看二进制日志内容
可使用mysqlbinlog工具:
审计SQL语句和数据库的更改;
定点恢复;
5)查看二进制日志
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
6)切换二进制日志
mysql> flush logs;
Query OK, 0 rows affected (0.14 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 154 |
+------------------+-----------+
2 rows in set (0.00 sec)
7)清理所有二进制日志,并从新开始记录二进制日志
mysql> reset master;
Query OK, 0 rows affected (0.13 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
8)清理指定编号之前的二进制日志
mysql> purge binary logs to 'mysql-bin.000004';
Query OK, 0 rows affected (0.04 sec)
9)清理指定时间之前的二进制日志
mysql> purge binary logs before '2019-05-11 17:38:56';
Query OK, 0 rows affected (0.16 sec)
注:不建议手动rm删除二进制日志,会造成index文件不一致。
五 中继日志
主从复制中,从服务器上一个很重要的文件,从服务器IO线程将主服务器的二进制日志读取过来并记录到从服务器本地文件(relay log)中,然后从服务器上的SQL线程会读取中继日志的内容并应用到从服务器。