MySQL学习笔记十:日志管理

时间:2023-02-09 16:04:35

1.错误日志文件

MySQL的错误日志文件记录错误信息,还会记录mysqld进程的关闭和启动的信息,但也不是什么错误信息都会记录,只记录mysqld进程运行过程中发生的关键性错误。

错误日志的启动,可以在启动mysqld进程时,通过log-error选项来指定错误日志文件名和存放位置,或者my.ini配置文件中配置log-error参数,如果没有指定文件名的话,会自动生成一个[hostname].err文件保存在{datadir}文件夹下。

在删除错误日志后,会把错误日志重建到[hostname].err.old的文件夹下。删除日志可以使用使用flush logs。

2.慢查询日志,有利于SQL语句调优

慢查询日志会记录所有查询语句执行时间大于系统变量long_query_time的值。默认情况下,慢查询日志功能是被禁止的。

mysql> show variables like 'slow%';
+---------------------+--------------------+
| Variable_name | Value |
+---------------------+--------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | LENOVO-PC-slow.log |
+---------------------+--------------------+

要开启慢查询日志功能,可以在my.ini配置文件中配置以下语句,或直接在客户端设置

slow-query-log=1  --0 关闭
slow_query_log_file="LENOVO-PC-slow.log"
long_query_time=10 --慢查询语句执行时间阀值 set global slow_query_log = on --开启
set global slow_query_log = off --禁止

要查看一个较大的慢查询日志,可以使用mysqldumpslow命令,或者第三方工具mysqlsla。

3.普通查询日志

普通查询日志(Qeneral Query Log),会记录mysqld进程几乎所有的操作,包括客户端的SQL语句查询,数据库和对象的管理操作等等。

普通查询日志的开启或关闭,可以在客户端进行,亦可以在配置文件中设置。

mysql> set global general_log=1;
Query OK, 0 rows affected (0.03 sec) mysql> show variables like 'general%';
+------------------+---------------+
| Variable_name | Value |
+------------------+---------------+
| general_log | ON |
| general_log_file | LENOVO-PC.log |
+------------------+---------------+
2 rows in set (0.00 sec)

如果要在某个会话开启/禁用普通查询日志,可以使用sql_log_off变量来设置。

set sql_log_off = N  --1(on) 开启  0(off) 关闭

在mysql5.6版本之前,普通日志会记录用户更新密码的信息,会有安全隐患,不过在5.6版本在记录用户相关信息时会自动重写,将涉及密码的部分加密后再写入。

4.查询日志配置

设置查询日志的输出位置,可以在mysql服务启动时通过--log-output参数指定,也可以在服务运行时设置,如下所示

D:\MySQL\MySQL Server 5.5\bin> mysqld --log-output=Table,File --general_log
--log-output参数值有三个选项,Table,File,None
--Table,即将日志记录到日志表中,相对应的有general_log和slow_log表
--File,将日志记录到文件中
--None,不输出日志

启用普通查询日志和慢查询日志,并输出到表

--log-output=TABLE --general_log --slow_log

在服务运行中设置,主要有log_output,general_log,slow_query_log,sql_log_off等系统变量

mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | NONE |
+---------------+-------+ mysql> set global log_output='TABLE';
Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

5.二进制日志文件

二进制日志记录数据库的修改事件,通过二进制日志,能够实现两个非常重要的功能,复制和恢复。

二进制日志的开启,方法1:在配置文件中开启  2:在服务启动时,使用--log-bin参数开启

[mysqld]
log-bin=mysql-bin D:\MySQL\MySQL Server 5.5\bin> mysqld --log-bin=mysql-bin

每次开启mysql服务时,都会新建一个二进制日志文件,其命名格式为[=base_name]-bin.000001,单个日志文件大小不是无限的,有max_binlog_size进行控制

mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

开启二进制功能后,也可以通过sql_bin_log变量或者my.ini的binlog-do-db,binlog-ignore-db选项来限制某些语句或者数据库的修改生成二进制日志。

#需要备份数据,多个写多行
binlog-do-db=test
#不需要备份的数据库,多个写多行
binlog-ignore-db=mysql mysql> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec) mysql> set sql_log_bin=0

查看二进制日志文件,使用mysqlbinlog命令

PS D:\ProgramData\MySQL\MySQL Server 5.5\data> mysqlbinlog .\mysql-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150126 17:25:25 server id 3306 end_log_pos 107 Start: binlog v 4, ser
t startup
ROLLBACK/*!*/;
BINLOG '

查看二进制日志文件并用于数据库恢复

D:\ProgramData\MySQL\MySQL Server 5.5\data> mysqlbinlog .\mysql-bin.000001 | mysql -u root -p

默认情况下,二进制日志不是同步刷新到磁盘的,这就会有丢失数据的隐患,为此mysql提供了一个sync_binlog的系统变量,用来设置二进制日志同步到磁盘的频率。

mysql> set global sync_binlog = x
--当x=0时,二进制日志不与磁盘同步,即mysql不做同步刷新指令将binlog_cache中信息到磁盘 --当X=N时,当进行N次事务提交,并写入二进制日志,mysql将binlog_cache中的信息写入到磁盘;N为1时,系统最安全但性能损耗最大,因为当设置为1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响。从以往经验和相关测试来看,对于高并发事务的系统来说,“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。