MySQL日志记录了MySQL数据库日常操作和错误信息。MySQL有不同类型的日志文件（各自存储了不同类型的日志），从日志当中可以查询到MySQL数据库的运行情况、用户的操作、错误的信息等。

MySQL的日志分为以下四大类：

错误日志：记录mysql服务的启动，运行或停止mysql服务时出现的问题；
查询日志：记录建立的客户端的连接和执行的语句；
二进制日志：记录所有更改数据的语句，可以用于数据的复制；
慢查询日志：记录所有执行的时间超过long_query_time的所有查询或不使用索引的查询；

默认情况下，所有日志创建于MySQL数据目录中，通过刷新日志，可以强制MySQL关闭和重新打开日志文件，Flush logs刷新日志或者执行mysqladmin flush-logs 如果正使用MySQL复制功能，在复制服务器上可以维护更多日志文件，这种日志我们称为接替日志。启动日志功能会降低MySQL数据库的性能。
1）查看系统设置

#查看全局的系统状态
mysql> show global variables\G
mysql> show global variables like '%log%';
#查看当前会话的系统状态
mysql> show session variables\G
mysql> show session variables like '%log%';

若要修改上面查看出来的参数，可以在MySQL的主配置文件中的mysqld字段中写入即可，如：binlog_cache_size = 1M。又或者可以在MySQL数据库中进行临时修改：set global binlog_cache_size = 1048576，这种临时修改在MySQL重启后就会失效。
2）查看运行状态

#查看全局的运行状态=
mysql> show global status\G
#查看当前会话的运行状态
mysql> show session status;
#查看MySQL的版本
[root@mysql ~]# mysql -V
mysql> status;
mysql> select version();

1、错误日志
在mysql数据库中，错误日志功能是默认开启的。默认情况下，错误日志存储在mysql数据库的数据目录中。错误日志文件通常的名称为hostname.err。其中，hostname表示服务器主机名。 错误日志信息可以自己进行配置的，错误日志所记录的信息是可以通过log-error和log-warnings来定义的，其中log-error是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。默认情况下错误日志大概记录以下几个方面的信息：服务器启动和关闭过程中的信息（未必是错误信息，如mysql如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等）、服务器运行过程中的错误信息、事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的信息，MySQL有很多系统变量可以设置，系统变量设置不同，会导致系统运行状态的不同。因此mysql提供两组命令，分别查看系统设置和运行状态。

一般而言，日志级别的定义没有会话变量都只是在全局级别下定义错误日志的状态：

mysql> show global variables like '%log_error%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| binlog_error_action | ABORT_SERVER                    |
| log_error           | /usr/local/mysql/data/mysql.err |
| log_error_verbosity | 3                               |
+---------------------+---------------------------------+
3 rows in set (0.00 sec)

其中 log_error定义为错误日志文件路径 ，log_error_verbosity值得含义如下:

Verbosity	Message
1	Error only
2	Error and warnings
3	Errors, warnings,and notes(default)

错误日志的存放路径在my.cnf的主配置文件中指定，如下：

[root@mysql ~]# cat /etc/my.cnf 
[client]
socket=/usr/local/mysql/mysql.sock
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysql.pid
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/data/mysql.err               # 此行定义错误日志位置

为了方便维护需要，有时候会希望将错误日志中的内容做备份并重新开始记录，这时候就可以利用MySQL 的FLUSH LOGS 命令来告诉MySQL 备份旧日志文件并生成新的日志文件。备份文件名以“.old”结尾。
删除错误日志
在mysql5.5.7之前：数据库管理员可以删除很长时间之前的错误日志，以保证mysql服务器上的硬盘空间。mysql数据库中，可以使用mysqladmin命令开启新的错误日志。mysqladmin命令的语法如下：mysqladmin –u root –p flush-logs也可以登录mysql数据库中使用FLUSH LOGS语句来开启新的错误日志。 在mysql5.5.7之后：服务器将关闭此项功能。只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的，方式如下：

[root@mysql ~]# cd /usr/local/mysql/data/
[root@mysql data]# mv mysql.err{,.old}
[root@mysql data]# mysqladmin -uroot -p flush-logs
Enter password: 
#再次ls查看就会有一个新的mysql.err 文件诞生

2、二进制日志
二进制日志主要记录MySQL数据库的变化，二进制日志以一种有效的格式，并且是事务安全的方式包含更新日志中可用的信息。二进制日志包含了所有更新了数据或者已经潜在更新了数据。还包含关于每个更新数据库的语句的执行时间，它不包含没有修改任何数据的语句。使用二进制日志的主要目的是最大可能地恢复数据库。
1）启动二进制日志（默认情况下二进制日志是关闭的）

[root@mysql ~]# vim /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysql.pid
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/data/mysql.err
port=3306
server_id=1
log-bin=/usr/local/mysql/data/binary_log      #指定二进制日志的路径及名称
expire_logs_days=10          #清除日志的天数
max_binlog_size=100M      #单个日志文件的大小限制，超出会新建一个日志文件
[root@mysql ~]# systemctl restart mysqld
[root@mysql ~]# ll /usr/local/mysql/data/ | grep binary
-rw-r----- 1 mysql mysql      154 May 20 15:57 binary_log.000001
-rw-r----- 1 mysql mysql       40 May 20 15:57 binary_log.index

注：在开启二进制日志时需要在my.cnf中指定server_id，否则回启动失败，日志文件中也只是会报一个警告，而不会报错
登录到数据库中也可以查看到，如下：

2）查看二进制日志
MySQL二进制日志存储了所有的变更信息，MySQL二进制日志经常使用。当MySQL创建二进制日志文件时，首先创建一个以’filename’为名称，以’.index’为后缀的文件；在创建一个以’filename’为名称，以’.000001’为后缀的文件。当MySQL服务重启一次，以’.000001’为后缀的文件会增加一个，并且后缀名加1
递增。如果日志长度超过max_binlog_size的上限，也会创建一个新的日志。 Show binary logs;可以查看当前的二进制日志文件个数及其文件名。
二进制日志并不能直接查看，如果想要查看日志内容，可以通过mysqlbinlog命令查看：

mysql> show binary logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| binary_log.000001 |       154 |
+-------------------+-----------+
1 row in set (0.00 sec)

也可以退出MySQL在命令行使用mysqlbinlog命令查看：

[root@mysql data]# mysqlbinlog binary_log.000001 

3）删除二进制日志
MySQL的二进制文件可以配置自动删除，同时MySQL提供了手动删除二进制文件的方法：
RESET MASTER：删除所有的二进制日志文件；
PURGE MASTER LOGS：只删除部分二进制日志文件；
Reset master：删除所有二进制日志 ；
Purge master logs to ‘二进制名’ ：删除单个二进制日志之前的。

mysql> purge master logs to "binary_log.000003";       #删除...03之前的二进制日志文件
mysql> purge master logs before '20180101';         #删除2018-01-01之前的日志文件

4）通过二进制日志还原MySQL数据
参考博文：mysql备份与恢复
3、事务日志
事务日志（InnoDB特有的日志，因为只有Innodb支持事务）可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的。 如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具有的恢复方式则视存储引擎而定。
1）查看事务日志的定义

mysql> show global variables like 'innodb_lo%';

在上述指令输出的部分内容解释如下：

| innodb_flush_log_at_trx_commit | 1 #在事务提交时innodb是否同步日志从缓冲区到文件
中，当这个值为1（默认值）之时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新，性能会很差造成大量的磁盘I/O但这种方式最安全；如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有缓存的，所以这个写入并不能保证数据已经写入到物理磁盘。设置为0，日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。
注：刷写的概念
刷写其实是两个操作，刷（flush）和写（write），区分这两个概念是很重要的。在大多数的操作系统中，把Innodb的log buffer（内存）写入日志（调用系统调用write），只是简单的把数据移到操作系统缓存中，操作系统缓存同样指的是内存。并没有实际的持久化数据。

所以，通常设为0和2的时候，在崩溃或断电的时候会丢失最后一秒的数据，因为这个时候数据只是存在于操作系统缓存。之所以说“通常”，可能会有丢失不只1秒的数据的情况，比如说执行flush操作的时候阻塞了。
总结
设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2,如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能。

+--------------------------------+----------+
| Variable_name                  | Value    |
+--------------------------------+----------+
| innodb_lock_wait_timeout       | 50       |
| innodb_locks_unsafe_for_binlog | OFF      |
| innodb_log_buffer_size         | 16777216 |
| innodb_log_checksums           | ON       |
| innodb_log_compressed_pages    | ON       |
| innodb_log_file_size           | 50331648 |             #日志文件大小 
| innodb_log_files_in_group      | 2        |             # DB中设置几组事务日志，默认是2 
| innodb_log_group_home_dir      | ./       |         #定义innodb事务日志组的位置,此位置设置默认为MySQL的datadir 
| innodb_log_write_ahead_size    | 8192     |
+--------------------------------+----------+

每个事务日志都是大小为50兆的文件（不同版本的mysql有差异）： 在mysql中默认以ib_logfile0,ib_logfile1名称存在。
4、慢查询日志（slow query log）
顾名思义，慢查询日志中记录的是执行时间较长的query，也就是我们常说的slow query。 慢查询日志采用的是简单的文本格式，可以通过各种文本编辑器查看其中的内容。其中 记录了语句执行的时刻，执行所消耗的时间，执行用户，连接主机等相关信息。 慢查询日志的作用： 慢查询日志是用来记录执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。一般建议开启，它对服务器性能的影响微乎其微，但是可以记录mysql服务器上执行了很长时间的查询语句。可以帮助我们定位性能问题的。MySQL 还提供了专门用来分析满查询日志的工具程序mysqldumpslow，用来帮助数据库管理人员解决可能存在的性能问题。
1）查看慢查询日志的定义
注：在不同的mysql版本中，开启慢查询日志参数不太一样，不过都可以通过 show variables like “%slow%” 和show variables like "%long%"查看出来。

mysql> show global variables like '%slow_query_log%';           #查询慢查询日志是否开启
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /usr/local/mysql/data/mysql-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)
#上面指定的使慢查询日志的记录的位置
mysql> show global variables like '%long%';            # 查看如何定义语句为慢查询语句
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 10000     |
| performance_schema_events_statements_history_long_size   | 10000     |
| performance_schema_events_transactions_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size        | 10000     |
+----------------------------------------------------------+-----------+
5 rows in set (0.00 sec)
#在上面的结果中，long_query_time的值就是慢查询超时时间，
默认为10s，只要执行耗时超过10s的语句就被定义为慢查询语句

2）启动和设置慢查询日志
启动慢查询日志记录：

方法1：通过配置文件my.cnf开启慢查询日志：

[root@mysql data]# vim /etc/my.cnf 
slow_query_log=1             # 1表示开启慢查询
slow_query_log_file=/usr/local/mysql/data/mysql-slow.log   #指定慢查询日志位置
long_query_time=0.0001         #指定超时时间，也就是超过这个时间就会被记录到慢查询日志
slow_launch_time=1        #如果建立线程花费了比这个值更长的时间，slow_launch_threads计数器将增加
[root@mysql ~]# systemctl restart mysqld          #重启服务使配置生效

再次登录数据库查看相关信息，会发现更改已经生效，如下：

方法2：通过登录MySQL服务器直接定义，方式如下：

mysql> set global slow_query_log=1;   
mysql> set global long_query_time=0.0001;

在上面的定义中，global表示全局生效，还有一个选项是session，表示仅在当前会话生效，其区别是，session在退出当前会话后就会被重置，global则是在重启MySQL服务后才会被重置，而方法1中写入配置文件中的方法，则是真正的永久生效。
注意：如果主配置文件中定义了long_query_time的值，并且MySQL命令行中使用set指令又定义了long_query_time的值，则配置文件中定义的值优先生效。
修改后的相关设置如下：

在终端命令行使用mysqldumpslow命令工具查看慢查询日志：
若想要查询到慢查询日志，必须保证两点，首先是将慢查询的超时时间设置的短一些，比如我在上面设置为了0.0001，只要查询的时间超过了这个值，则被定义为慢查询。（为了验证效果，在生产环境中还是要结合实际情况）。第二呢，就是在开启慢查询后还需要执行几条查询语句，以便产生日志记录信息（自己随便查询两句吧）。

mysqldumpslow指令的部分选项解释：

使用选项查看慢查询日志：

[root@mysql data]# mysqldumpslow -t 2 -a -s c mysql-slow.log 
#以次数来排序，查询前两条，并且显示语句的所有内容

5、数据文件
在MySQL 中每一个数据库都会在定义好（或者默认）的数据目录下存在一个以数据库名字命名的文件夹，用来存放该数据库中各种表数据文件。不同的MySQL 存储引擎有各自不同的数据文件。如MyISAM 用“.MYD”作为扩展名，Innodb 用“.ibd”，Archive 用“.arc”，CSV 用“.csv”，等等。

“.frm”文件 与表相关的元数据（meta）信息都存放在“.frm”文件中，包括表结构的定义信息等。不论是什么存储引擎（MySQL常用的两个存储引擎是MyISAM和InnoDB），每一个表都会有一个以表名命名的“.frm”文件。所有的“.frm”文件都存放在所属数据库的文件夹下面。
MyISAM数据库表文件

.MYD文件：表数据文件；.MYI文件：索引文件
“.MYD”文件 “.MYD”文件是MyISAM存储引擎专用，存放MyISAM 表的数据。每一个MyISAM 表都会有一个“.MYD”文件与之对应，同样存放于所属数据库的文件夹下，和“.frm”文件在一起
“.MYI”文件 “.MYI”文件也是专属于MyISAM 存储引擎的，主要存放MyISAM 表的索引相关信息。对于MyISAM 存储来说，可以被cache 的内容主要就是来源于“.MYI”文件中。每
一个MyISAM 表对应一个“.MYI”文件，存放于位置和“.frm”以及“.MYD”一样。

Innodb数据库表文件

InnoDB采用表空间（tablespace）来管理数据，存储表数据和索引。

.ibd文件：单表表空间文件，每个表使用一个表空间文件（file per table），存放用户数据库表数据和索引。
InnoDB共享表空间（即InnoDB文件集，ibfile set）：ibdata1、ibdata2等，存储InnoDB系统信息和用户数据库表数据和索引，所有表共用。
“.ibd”文件和ibdata 文件 这两种文件都是存放Innodb 数据的文件，之所以有两种文件来存放Innodb 的数据（包括索引），是因为Innodb 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件，文件存放在和MyISAM数据相同的位置。
如果选用共享存储表空间来存放数据，则会使用ibdata 文件来存放，所有表共同使用一个
（或者多个，可自行配置）ibdata 文件。 ibdata 文件可以通过innodb_data_home_dir 和innodb_data_file_path两个参数共同配置组成， innodb_data_home_dir 配置数据存放的总目录， 而innodb_data_file_path 配置每一个文件的名称。 innodb_data_file_path 中可以一次配置多个ibdata 文件。文件可以是指定大小，也可以是自动扩展的，但是Innodb 限制了仅仅只有最后一个ibdata 文件能够配置成自动扩展类型。当我们需要添加新的ibdata 文件的时候，只能添加在innodb_data_file_path配置的最后，而且必须重启MySQL 才能完成ibdata 的添加工作。不过如果我们使用独享表空间存储方式的话，就不会有这样的问题
总结
共享表空间以及独占表空间都是针对数据的存储方式而言的。 共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中。 独占表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。其中这个文件包括了单独一个表的数据内容以及索引内容。

两者之间的优缺点

共享表空间：
优点： 可以放表空间分成多个文件存放到各个磁盘上。数据和文件放在一起方便管理。
缺点： 所有的数据和索引存放到一个文件中，多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。
独立表空间：
优点：

每个表都有自已独立的表空间。
每个表的数据和索引都会存在自已的表空间中。
可以实现单表在不同的数据库中移动。
空间可以回收
a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
b)对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
缺点： 单表增加过大，如超过100个G。 相比较之下，使用独占表空间的效率以及性能会更高一点。
1）查看当前数据库的表空间管理类型

mysql> show variables like '%innodb_file_per%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

ON代表独立表空间管理，OFF代表共享表空间管理；（查看单表的表空间管理方式，需要查看每个表是否有单独的数据文件）。
2）修改为Innodb共享表空间

[root@mysql data]# vim /etc/my.cnf 
innodb_file_per_table=0   #0为使用共享表空间，1为独占表空间
innodb_data_file_path=ibdata1:100M:autoextend    #设置一个可自动扩展大小，尺寸为100M的数据文件
innodb_data_home_dir=/usr/local/mysql/data #定义共享表空间默认存放路径
[root@mysql ~]# systemctl restart mysqld    #启动报错了
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

查看日志如下：

注：不同版本的mysql报错略有不同，注意看错误日志的内容。大概意思就是设置的pages页为6400，超过了它的最大值4864，那么算一下，设置的初始大小100M，pages大小就是6400，则表示1M=64pages，它的最大值是4864，也就是说，我们设置初始大小最大可以是4864/64=76M。
再次修改配置文件如下：

[root@mysql ~]# vim /etc/my.cnf        #再次修改主配置文件
innodb_data_file_path=ibdata1:76M:autoextend
#修改初始大小为76M
[root@mysql ~]# systemctl restart mysqld          #再次重启MySQL服务
#同样，如果还是启动失败，则再次查看错误日志，是否pages页大小设置的还是不合理

3）查看修改后数据库的表空间管理类型

MySQL日志记录了MySQL数据库日常操作和错误信息。MySQL有不同类型的日志文件（各自存储了不同类型的日志），从日志当中可以查询到MySQL数据库的运行情况、用户操作、错误信息等，可以为MySQL管理和优化提供必要的信息。对于MySQL的管理工作而言，这些日志文件是不可缺少的。

PART1. 日志简介

MySQL 日志主要分4类，使用这些日志文件，可以查看MySQL内部发生的事情

1）错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题。

2）查询日志：记录建立的客户端连接和执行的语句。

3）二进制日志：记录所有更改数据的语句，可以用于数据复制。

4）慢查询日志：记录所有执行时间超过long_query_time 的所有查询或不使用索引的查询。

默认情况下，所有日志创建于MySQL数据目录中。通过刷新日志，可以强制MySQL关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）。当执行一个FLUSH LOGS语句或执行MySQLadmin flush-logs或MySQLadmin refresh时，将刷新日志。

如果正使用MySQL复制功能，在复制服务器上可以维护更多日志文件，这种日志称为接替日志。

启动日志功能会降低MySQL数据库的性能。例如，在查询非常频繁的MySQL数据库系统中，如果开启了通过查询日志和慢查询日志，MySQL数据库会花费很多时间记录日志。同时，日志会占用大量的磁盘空间。

PART2. 二进制日志

二进制日志主要记录MySQL数据库的变化。二进制日志以一种有效的格式并且是事务安全的方式包含更新日志中可用的所有信息。二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的语句。语句以“事件”的形式保存，描述数据更改。

二进制日志还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果想要记录所有语句（例如，为了识别有问题的查询），需要使用一般查询日志。使用二进制日志的主要目的时最大可能地恢复数据库，因为二进制日志包含备份后进行的所有更新。

1. 启动和设置二进制日志

默认情况下，二进制日志是开启的，可以通过修改MySQL的配置文件来启动和设置二进制日志。

my.ini中[MySQLd]组下面有关于二进制日志的设置：

log-bin [=path/ [filename]]

log-bin定义开启二进制日志；path表明日志文件所在的目录路径；filename指定了日志文件的名称，如文件的全名为filename.000001、filename.000002等，除了上述文件之外，还有一个名为filename.index的文件，文件内容为所有日志的清单，可以使用记事本打开该文件。

expire_logs_days 定义了MySQL清除过期日志的时间，即二进制日志自动删除的天数。默认值为0，表示“没有自动删除”。当MySQL启动或刷新二进制日志时可能删除该文件。

max_binlog_size定义了单个文件的大小限制，如果二进制日志写入的内容大小超出给定值，日志就会发生回滚（关闭当前日志，重新打开一个新的日志文件）。不能将该变量设置为大于1GB或小于4960B，默认是1GB。

如果正在使用大的事务，二进制日志文件大小还可能会超过max_binlog_size 定义的大小。

在my.ini 配置文件中的[MySQLd]组下，添加以下几个参数与参数值：

[mysqld]
log-bin
expire_logs_days = 10
man_binlog_size = 100M

添加完毕之后，关闭并重新启动MySQL服务进程，即可打开二进制日志，然后可以通过show variables 语句来查询日志设置。

通过上面的查询结果可以看出，log_bin变量的值为ON，表明二进制日志已经打开。MySQL重新启动之后，读者可以在自己机器上的MySQL数据文件夹下面看到新生成的文件后缀为.000001和.index的两个文件，文件名称为默认主机名称。

注意：数据库文件最好不要与日志文件放在同一个磁盘上，这样当数据库文件所在的磁盘发生故障时，可以使用日志文件恢复数据。 

2. 查看二进制日志

MySQL二进制日志存储了所有的变更信息，MySQL二进制日志是经常用到的。当MySQL创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一个以“filename”为名称，以“.000001”为后缀的文件。MySQL服务重新启动一次，以“.000001”为后缀的文件会增加一个，并且后缀名加1递增；如果日志长度超过了max_binlog_size的上限（默认是1GB），就会创建一个新的日志文件。

show binary logs语句可以查看当前的二进制日志文件个数及其文件名。MySQL二进制日志并不能直接查看，如果要查看日志内容，可以通过MySQLbinlog命令查看。

 

3. 删除二进制日志

MySQL的二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制文件的方法：RESET MASTER删除所有二进制日志文件；PURGE MASTER LOGS只删除部分二进制日志文件。

1. 使用RESET MASTER语句删除所有二进制日志文件

RESET MASTER;

执行完该语句后，所有二进制日志将被删除，MySQL会重新创建二进制日志，新的日志文件扩展名将重新从000001开始编号。

2. 使用PUGRE MASTER LOGS语句删除指定日志文件

PURGE MASTER LOGS 语法如下：

PURGE [MASTER | BINARY] LOGS TO 'log_name'
PURGE [MASTER | BINARY] LOGS BEFORE 'date'

第一种方法指定文件名，执行该命令将删除文件名编号比指定文件名编号小的所有日志文件。第二种方法指定日期，执行该命令将删除指定日期以前的所有日志文件。

4. 使用二进制日志恢复数据库

如果MySQL服务器启用了二进制日志，在数据库出现意外丢失数据时，可以使用MySQLbinlog工具从指定的时间点开始（例如，最后一次备份）直到现在，或另一个指定的时间点的日志中恢复数据。

要想从二进制日志恢复数据，需要知道当前二进制日志文件的路径和文件名，一般可以从配置文件（my.cnf或者my.ini，文件名取决于MySQL服务器的操作系统）中找到路径。

MySQLbinlog恢复数据的语法如下：

mysqlbinlog [option] filename |mysql -uuser -ppass

option是一些可选的选项，filename是日志文件名。比较重要的两对option参数是--start-date、--stop-date和--start-position、--stop-position。--start-date、--stop-date可以指定恢复数据库的起始时间点和结束时间点。--start-position、--stop-position可以指定恢复数据的开始位置和结束位置。

5. 暂时停止二进制日志功能

如果在MySQL的配置文件中配置启动了二进制日志，MySQL会一直记录二进制日志。修改配置文件，可以停止二进制日志，但是需要重启MySQL数据库。MySQL提供了暂时停止二进制日志的功能。通过SET SQL_LOG_BIN语句可以使用MySQL暂停或者启动二进制日志。

SET sql_log_bin = {0|1}
SET sql_log_bin = 0;--暂停记录二进制日志
SET sql_log_bin = 1;--恢复记录二进制日志

PART3 错误日志

错误日志文件包含了当MySQLd启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。在MySQL中，错误日志也是非常有用的，MySQL会将启动和停止数据库信息以及一些错误信息记录到错误日志文件中。

1. 启动和设置错误日志

在默认情况下，错误日志会记录到数据库的数据目录下。如果没有在配置文件中指定文件名，则文件名默认为hostname.err。例如，MySQL所在的服务器主机名为MySQL-db，记录错误信息的文件名为MySQL-db.err。如果执行了FLUSH LOGS，错误日志会重新加载。

错误日志的启动和停止以及指定日志文件名都可以通过修改my.ini(或者my.cnf)来配置。错误日志的配置项时log-error。在[MySQLd]下配置log-error，则启动错误日志。如果需要指定文件名，则配置项如下：

[mysqld]
log-error=[path / [file_name]]

path为日志文件所在的目录路径，file_name为日志文件名。修改配置项后，需要重启MySQL服务以生效。

2. 查看错误日志

通过错误日志可以监视系统的运行状态，便于及时发现故障、恢复故障。MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看MySQL错误日志。

如果不知道日志文件的存储路径，可以使用SHOW VARIABLES 语句查询错误日志的存储路径。SHOW VARIABLES语句如下：

SHOW VARIABLES LIKE 'log_error';

 

3. 删除错误日志

 MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以直接删除。

对于MySQL5.5.7以前的版本，flush logs可以将错误日志文件重命名为filename.err_old，并创建新的日志文件。但是从MySQL5.5.7开始，flush logs只是重新打开日志文件，并不做日志备份和创建的操作。如果日志文件不存在，MySQL启动并不会自动创建日志文件。flush logs在重新加载日志的时候，如果日志不存在，则会自动创建。所以在删除错误日志之后，如果需要重建日志文件需要在服务器执行如下命令：

mysqladmin -u root -p flush-logs

或者在客户端登录MySQL数据库，执行flush logs语句：

flush logs;

PART4. 通用查询日志

通过查询日志记录MySQL的所有用户操作，包括启动和关闭服务、执行查询和更新语句等。

1. 启动通用查询日志

MySQL服务器默认情况下并没有开启通用查询日志。通过show variables like '%general%'; 语句可以查询当前日志的状态。

开启通用日志的方法：

set @@global.general_log = 1;

 

关闭通用日志：

set @@global.general_log=0;

 2. 查看通用查询日志

通用查询日志中记录了用户的所有操作。通过查看通用查询日志，可以了解用户对MySQL进行的操作。通过查询日志是以文本文件的形式存储在文件系统中的。

 

3. 删除通用查询日志

 通过查询日志是以文本文件的形式存储在文件系统中。通过查询日志记录用户的所有操作，因此在用户查询、更新频繁的情况下，通用查询日志会增长得很快。数据库管理员可以定期删除比较早的通用日志，以节省磁盘空间。本小节将介绍通用日志的删除方法。

可以直接删除日志文件的方式删除通用查询日志。要重新建立新的日志文件，可使用语句MySQLadmin-flush logs。

PART5 慢查询日志

慢查询日志是记录查询时长超过指定时间的日志。慢查询日志主要用来记录执行时间较长的查询语句。通过慢查询日志，可以找出执行时间较长、执行效率较低的语句，然后进行优化。

1. 启动和设置慢查询日志

MySQL中慢查询日志默认是关闭的，可以通过配置文件my.ini 或者my.cnf 中的log-slow-queries 选项打开，也可以在MySQL服务启动的时候使用--log-slow-queries[=file_name]启动慢查询日志。启动慢查询日志时，需要在my.ini 或者 my.cnf 文件中配置long_query_time 选项指定记录阈值，如果某条查询语句的查询时间超过了这个值，这个查询过程将被记录到慢查询日志文件中。

[mysqld]
log-slow-queries[=path/[filename]]
long_query_time=n

在my.ini 或者my.cnf 开启慢查询日志的配置如下：

path 为日志文件所在目录路径，filename 为日志文件名。如果不指定目录和文件名称，默认存储在数据目录中，文件为hostname-slow.log，hostname是MySQL服务器的主机名。参数n 是时间值，单位是秒。如果没有设置long_query_time选项，默认时间为10秒。

2. 查看慢查询日志

MySQL的慢查询日志是以文本形式存储的，可以直接使用文本编辑器查看。在慢查询日志中，记录着执行时间较长的查询语句，用户可以从慢查询日志中获取执行效率较低的查询语句，为查询优化提供重要的依据。

注意：借助慢查询日志分析工具，可以更加方便地分析慢查询语句。比较著名的慢查询工具有MySQL Dump Slow、MySQL SLA、MySQL Log Filter、MyProfi。关于这些慢查询分析工具的用法，可以参考相关软件的帮助文档。

3. 删除慢查询日志

和通用查询日志一样，慢查询日志也可以直接删除。删除后在不重启服务器的情况下，需要执行MySQLadmin -u root-p flush-logs 重新生成日志文件，或者在客户端登录到服务器执行flush logs语句重建日志文件。

PART6 MySQL8.0 的新特性——日志分类更详细

在MySQL8.0版本中，日志分类将更加详细。例如，在错误信息中添加了错误信息编号[MY-010311]和错误所属子系统[Server]。

-- end

前言

MySQL日志记录了MySQL数据库日常操作和错误信息。MySQL有不同类型的日志文件（各自存储了不同类型的日志），从日志当中可以查询到MySQL数据库的运行情况、用户的操作、错误的信息等。

MySQL的日志分为以下四大类：

错误日志：记录mysql服务的启动，运行或停止mysql服务时出现的问题；
查询日志：记录建立的客户端的连接和执行的语句；
二进制日志：记录所有更改数据的语句，可以用于数据的复制；
慢查询日志：记录所有执行的时间超过long_query_time的所有查询或不使用索引的查询；

默认情况下，所有日志创建于MySQL数据目录中，通过刷新日志，可以强制MySQL关闭和重新打开日志文件，Flush logs刷新日志或者执行mysqladmin flush-logs 如果正使用MySQL复制功能，在复制服务器上可以维护更多日志文件，这种日志我们称为接替日志。启动日志功能会降低MySQL数据库的性能。
1）查看系统设置

#查看全局的系统状态
mysql> show global variables\G
mysql> show global variables like ‘%log%’;
#查看当前会话的系统状态
mysql> show session variables\G
mysql> show session variables like ‘%log%’;

若要修改上面查看出来的参数，可以在MySQL的主配置文件中的mysqld字段中写入即可，如：binlog_cache_size = 1M。又或者可以在MySQL数据库中进行临时修改：set global binlog_cache_size = 1048576，这种临时修改在MySQL重启后就会失效。
2）查看运行状态

#查看全局的运行状态=
mysql> show global status\G
#查看当前会话的运行状态
mysql> show session status;
#查看MySQL的版本
[root@mysql ~]# mysql -V
mysql> status;
mysql> select version();

错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何

严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。

错误日志存储在mysql数据库的数据目录中。错误日志文件通常的名称为hostname.err。其中，hostname表示服务器主机名。 错误日志信息可以自己进行配置的，错误日志所记录的信息是可以通过log-error和log-warnings来定义的，其中log-error是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。默认情况下错误日志大概记录以下几个方面的信息：服务器启动和关闭过程中的信息（未必是错误信息，如mysql如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等）、服务器运行过程中的错误信息、事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的信息，MySQL有很多系统变量可以设置，系统变量设置不同，会导致系统运行状态的不同。因此mysql提供两组命令，分别查看系统设置和运行状态。

一般而言，日志级别的定义没有会话变量都只是在全局级别下定义错误日志的状态：

mysql> show global variables like ‘%log_error%’;
±--------------------±--------------------------------+
| Variable_name | Value |
±--------------------±--------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /usr/local/mysql/data/mysql.err |
| log_error_verbosity | 3 |
±--------------------±--------------------------------+
3 rows in set (0.00 sec)

其中 log_error定义为错误日志文件路径 ，log_error_verbosity值得含义如下:

Verbosity Message
1 Error only
2 Error and warnings
3 Errors, warnings,and notes(default)

错误日志的存放路径在my.cnf的主配置文件中指定，如下：

[root@mysql ~]# cat /etc/my.cnf
[client]
socket=/usr/local/mysql/mysql.sock
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysql.pid
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/data/mysql.err # 此行定义错误日志位置

为了方便维护需要，有时候会希望将错误日志中的内容做备份并重新开始记录，这时候就可以利用MySQL 的FLUSH LOGS 命令来告诉MySQL 备份旧日志文件并生成新的日志文件。备份文件名以“.old”结尾。
删除错误日志
在mysql5.5.7之前：数据库管理员可以删除很长时间之前的错误日志，以保证mysql服务器上的硬盘空间。mysql数据库中，可以使用mysqladmin命令开启新的错误日志。mysqladmin命令的语法如下：mysqladmin –u root –p flush-logs也可以登录mysql数据库中使用FLUSH LOGS语句来开启新的错误日志。 在mysql5.5.7之后：服务器将关闭此项功能。只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的，方式如下：

[root@mysql ~]# cd /usr/local/mysql/data/
[root@mysql data]# mv mysql.err{,.old}
[root@mysql data]# mysqladmin -uroot -p flush-logs
Enter password:
#再次ls查看就会有一个新的mysql.err 文件诞生

查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。

慢查询日志

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于

min_examined_row_limit 的所有的SQL语句的日志。long_query_time 默认为 10 秒，最小为 0， 精度可以到微

秒。

慢查询日志中记录的是执行时间较长的query，也就是我们常说的slow query。 慢查询日志采用的是简单的文本格式，可以通过各种文本编辑器查看其中的内容。其中 记录了语句执行的时刻，执行所消耗的时间，执行用户，连接主机等相关信息。 慢查询日志的作用： 慢查询日志是用来记录执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。一般建议开启，它对服务器性能的影响微乎其微，但是可以记录mysql服务器上执行了很长时间的查询语句。可以帮助我们定位性能问题的。MySQL 还提供了专门用来分析满查询日志的工具程序mysqldumpslow，用来帮助数据库管理人员解决可能存在的性能问题。
1）查看慢查询日志的定义
注：在不同的mysql版本中，开启慢查询日志参数不太一样，不过都可以通过 show variables like “%slow%” 和show variables like "%long%"查看出来。

mysql> show global variables like ‘%slow_query_log%’; #查询慢查询日志是否开启
±--------------------±-------------------------------------+
| Variable_name | Value |
±--------------------±-------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/mysql-slow.log |
±--------------------±-------------------------------------+
2 rows in set (0.00 sec)
#上面指定的使慢查询日志的记录的位置
mysql> show global variables like ‘%long%’; # 查看如何定义语句为慢查询语句
±---------------------------------------------------------±----------+
| Variable_name | Value |
±---------------------------------------------------------±----------+
| long_query_time | 10.000000 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_waits_history_long_size | 10000 |
±---------------------------------------------------------±----------+
5 rows in set (0.00 sec)
#在上面的结果中，long_query_time的值就是慢查询超时时间，
默认为10s，只要执行耗时超过10s的语句就被定义为慢查询语句

启动和设置慢查询日志
启动慢查询日志记录：

方法1：通过配置文件my.cnf开启慢查询日志：

[root@mysql data]# vim /etc/my.cnf
slow_query_log=1 # 1表示开启慢查询
slow_query_log_file=/usr/local/mysql/data/mysql-slow.log #指定慢查询日志位置
long_query_time=0.0001 #指定超时时间，也就是超过这个时间就会被记录到慢查询日志
slow_launch_time=1 #如果建立线程花费了比这个值更长的时间，slow_launch_threads计数器将增加
[root@mysql ~]# systemctl restart mysqld #重启服务使配置生效

再次登录数据库查看相关信息，会发现更改已经生效，
方法2：通过登录MySQL服务器直接定义，方式如下：

mysql> set global slow_query_log=1;
mysql> set global long_query_time=0.0001;

在上面的定义中，global表示全局生效，还有一个选项是session，表示仅在当前会话生效，其区别是，session在退出当前会话后就会被重置，global则是在重启MySQL服务后才会被重置，而方法1中写入配置文件中的方法，则是真正的永久生效。
注意：如果主配置文件中定义了long_query_time的值，并且MySQL命令行中使用set指令又定义了long_query_time的值，则配置文件中定义的值优先生效。

在终端命令行使用mysqldumpslow命令工具查看慢查询日志：
若想要查询到慢查询日志，必须保证两点，首先是将慢查询的超时时间设置的短一些，比如我在上面设置为了0.0001，只要查询的时间超过了这个值，则被定义为慢查询。

二进制日志（binlog）

二进制日志（BINLOG）记录了所有的 DDL（data define language数据定义语言）语句和 DML（data manage language数据操纵语言 insert 等）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。

二进制日志主要记录MySQL数据库的变化，二进制日志以一种有效的格式，并且是事务安全的方式包含更新日志中可用的信息。二进制日志包含了所有更新了数据或者已经潜在更新了数据。还包含关于每个更新数据库的语句的执行时间，它不包含没有修改任何数据的语句。使用二进制日志的主要目的是最大可能地恢复数据库。
1）启动二进制日志（默认情况下二进制日志是关闭的）

[root@mysql ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysql.pid
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/data/mysql.err
port=3306
server_id=1
log-bin=/usr/local/mysql/data/binary_log #指定二进制日志的路径及名称
expire_logs_days=10 #清除日志的天数
max_binlog_size=100M #单个日志文件的大小限制，超出会新建一个日志文件
[root@mysql ~]# systemctl restart mysqld
[root@mysql ~]# ll /usr/local/mysql/data/ | grep binary
-rw-r----- 1 mysql mysql 154 May 20 15:57 binary_log.000001
-rw-r----- 1 mysql mysql 40 May 20 15:57 binary_log.index

注：在开启二进制日志时需要在my.cnf中指定server_id，否则回启动失败，日志文件中也只是会报一个警告，而不会报错
登录到数据库中也可以查看到，如下：

2）查看二进制日志
MySQL二进制日志存储了所有的变更信息，MySQL二进制日志经常使用。当MySQL创建二进制日志文件时，首先创建一个以’filename’为名称，以’.index’为后缀的文件；在创建一个以’filename’为名称，以’.000001’为后缀的文件。当MySQL服务重启一次，以’.000001’为后缀的文件会增加一个，并且后缀名加1
递增。如果日志长度超过max_binlog_size的上限，也会创建一个新的日志。 Show binary logs;可以查看当前的二进制日志文件个数及其文件名。
二进制日志并不能直接查看，如果想要查看日志内容，可以通过mysqlbinlog命令查看：

mysql> show binary logs;
±------------------±----------+
| Log_name | File_size |
±------------------±----------+
| binary_log.000001 | 154 |
±------------------±----------+
1 row in set (0.00 sec)

也可以退出MySQL在命令行使用mysqlbinlog命令查看：

[root@mysql data]# mysqlbinlog binary_log.000001

3）删除二进制日志
MySQL的二进制文件可以配置自动删除，同时MySQL提供了手动删除二进制文件的方法：
RESET MASTER：删除所有的二进制日志文件；
PURGE MASTER LOGS：只删除部分二进制日志文件；
Reset master：删除所有二进制日志 ；
Purge master logs to ‘二进制名’ ：删除单个二进制日志之前的。

mysql> purge master logs to “binary_log.000003”; #删除…03之前的二进制日志文件
mysql> purge master logs before ‘20180101’; #删除2018-01-01之前的日志文件

4）通过二进制日志还原MySQL数据
通过MySQL提供的二进制日志来间接实现增量备份。
现在所有对数据库的修改，都将记录mysql-bin.000001文件中，当执行“mysqladmin -u root -p flush-logs”刷新二进制日志后，将会继续生成一个名为mysql-bin.000002的文件，之后所有的更改又将存在mysql-bin.000002文件中，以此类推，每刷新一次，就会生成一个新文件！
录入新的数据，并进行增量备份
[root@mysql /]# mysqladmin -u root -p flush-logs # 刷新日志文件，这样在000002中只有两条数据的操作
Enter password:
[root@mysql /]# cp /usr/local/mysql/logs/mysql-bin.000002 /mysql_bak/ 　＃ 将日志文件复制到备份目录中
[root@mysql /]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -u root -p # 恢复增量备份，–no-defaults 选项必须要有
[root@mysql /]# mysql -u root -p -e ’ select * from test.user_info;’ # 确认，增量备份恢复成功
Enter password:

InnoDB 存储引擎的日志

undo log 和 redo log 其实都不是 MySQL 数据库层面的日志，而是 InnoDB 存储引擎的日志。二者的作用联系紧密，事务的隔离性由锁来实现，原子性、一致性、持久性通过数据库的 redo log 或 undo log 来完成。

redo log 又称为重做日志，用来保证事务的持久性，undo log 用来保证事务的原子性和 MVCC。

redo 记录的是物理上 值的修改 不像 binlog 是记录的是逻辑语句

和大多数关系型数据库一样，InnoDB 记录了对数据文件的物理更改，并保证总是日志先行，也就是所谓的 WAL，即在持久化数据文件前，保证之前的 redo 日志已经写到磁盘。由于 redo log 是顺序整块写入，所以性能要更好。

重做日志两部分组成：一是内存中的重做日志缓冲(redo log buffer)，是易失的；二是重做日志文件(redo log file)，是持久的。redo log 记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。

在一条语句进行执行的时候，InnoDB 引擎会把新记录写到 redo log 日志中，然后更新内存，更新完成后就算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将 redo log 中的内容更新到磁盘中。

实现持久性，避免事务提交之后每次都要刷新到磁盘

回滚日志（undo log）

undo log 有两个作用：提供回滚和多版本并发控制下的读(MVCC)，也即非锁定读

在数据修改的时候，不仅记录了redo，还记录了相对应的 undo，如果因为某些原因导致事务失败或回滚了，可以借助该 undo 进行回滚。

undo log 和 redo log 记录物理日志不一样，它是逻辑日志。可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录。

有时候应用到行版本控制的时候，也是通过 undo log 来实现的：当读取的某一行被其他事务锁定时，它可以从 undo log 中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。