MySQL服务日志

一、MySQL服务日志介绍

MySQL日志有下面几种：

• 错误日志（Error Log）
• 查询日志（Query Log）
• 二进制日志（Binary Log）

（一）错误日志

记录MySQL服务进程在启动、关闭或运行过程中遇到的错误信息。它的调整在/data/3306/my.cnf文件中：

...
[mysqld_safe]
log-error=/data/3306/mysql_3306.err #错误日志路径
...

（二）查询日志

查询日志分为：

• 普通查询日志（general query log）
• 慢查询日志（slow query log）

1、普通查询日志

记录客户端连接信息和执行的SQL语句信息。对于普通查询日志参数调整查看：

mysql> show variables like 'general_log%';
+------------------+-----------------------------------+
| Variable_name    | Value                             |
+------------------+-----------------------------------+
| general_log      | OFF                               |
| general_log_file | /data/3306/data/hadoop-slave1.log |
+------------------+-----------------------------------+
2 rows in set (0.03 sec)

可以看到普通查询日志处于关闭状态，并且存储的位置，当然你可以通过 set global general_log = ON 进行开启。

2、慢查询日志

记录查询时间超过指定值（long_query_time）的SQL语句。对于普通查询日志参数调整查看：

-- vim /data/3306/my.cnf

[mysqld]
...
long_query_time = 1
log-slow-queries = /data/3306/slow.log
...

（三）二进制日志

记录数据被修改的相关信息。参数调整查看：

-- 查看相关参数
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |    --记录binlog
| sql_log_bin                     | ON    |  --临时不记录binlog
+---------------------------------+-------+
3 rows in set (0.00 sec)


-- 开启binlog vim /data/3306/my.cnf
...
[mysqld]
...
log-bin = /data/3306/mysql-bin
...

二、二进制日志的三种模式

（一）三种模式介绍

binlog日志有三种模式分别为：

• Statement Level模式
• Row Level模式
• Mixed模式

1、Statement Level模式

将每一条修改数据的sql都会记录到master的binlog中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。

优点：解决了Row Level下的缺点，不需要记录每一行的数据变化，从而减少bin-log日志量，节约IO，提高性能。

缺点：由于记录的是执行语句，所以随着新功能的不断增加，MySQL的复制在某些情况下（如：存储过程、trigger调用）会遇到失败。

2、Row Level模式

记录每一行数据的修改形式，然后slave端再对相同的数据进行修改。

优点：bin-log中不记录执行sql语句的上下文相关信息，仅仅记录哪一条数据被修改了以及修改成什么样了，所以Row Level日志清楚的记录数据修改的细节。而且不会出现上面Statement Level模式复制出现问题的情况（如：存储过程、trigger调用）。

缺点：由于记录的日志比较详细，所以会产生大量的日志文件。

3、Mixed模式

该模式是上面两种模式的结合，在Mixed模式下，MySQL会根据执行的sql来区分对待记录日志的形式。

（二）调整binlog日志模式

1、查看binlog日志模式

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

2、调整binlog日志模式方法

• 配置文件修改

-- vim /data/3306/my.cnf

[mysqld]
...
binlog_format = ROW
...

• 在线修改立即生效

-- 设置
mysql> set global binlog_format=ROW;
Query OK, 0 rows affected (0.00 sec)

-- 退出
mysql> quit
Bye

-- 重新登录查看
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

• 不重启修改永久生效

将上面的配置全部设置，配置文件设置和全局变量设置都要设置。

（三）binlog日志模式实践

1、刷新binlog

-- 出现一个新的binlog文件  mysql-bin.000009
 [root@hadoop-slave1 3306]# mysqladmin -uroot -p123456 -S /data/3306/mysql.sock flush-logs

2、操作数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| crm                |
| mysql              |
| performance_schema |
| reptest            |
| test               |
+--------------------+
6 rows in set (0.02 sec)

mysql> use crm;
Database changed

mysql> show tables;
+---------------+
| Tables_in_crm |
+---------------+
| userinfo      |
+---------------+
1 row in set (0.00 sec)

mysql> delete from userinfo;
Query OK, 4 rows affected (0.04 sec)

3、查看binlog日志

-- 查看Row Level下的binlog文件
[root@hadoop-slave1 3306]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000009

-- 具体日志文件
..
D_F
### DELETE FROM `crm`.`userinfo`
### WHERE
###   @1=1
###   @2='张三'
### DELETE FROM `crm`.`userinfo`
### WHERE
###   @1=2
###   @2='李四'
### DELETE FROM `crm`.`userinfo`
### WHERE
###   @1=3
###   @2='王五'
### DELETE FROM `crm`.`userinfo`
### WHERE
###   @1=4
###   @2='赵六'
# at 300
...

可以看到，数据库的更改语句会详细的被记录。