Mysql DBA 高级运维学习之路-Mysql数据库中的日志文件

1.错误日志（Error log）

（1）错误日志的介绍

错误日志（error log）:记录MySQL服务进程mysqld在自动/关闭或远行过程中遇到的错误信息

（2）错误日志的设置

在my.cnf配置文件里面查看错误日志log-error

[root@linzhongniao ~]# sed -n '78,79p' /data/3306/my.cnf   
[mysqld_safe]
log-error=/data/3306/mysql_linzhongniao3306.err

2.查询日志（Query Log）

查询日志（query log）:又可分两类

普通查询日志（general query log）：记录客户端连接信息和执行的SQL语句信息。

慢查询日志（slow query log）：记录执行时间超出指定值。（long_query_time）的SQL语句。

2.1 配置普通查询日志

普通查询日志不重要，在工作中是不开启的；我们先登录mysql看一下general log有没有开启

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

通过查询我们得知general_log没有开启，下面我们将general_log开启

mysql> set global general_log=ON;
Query OK, 0 rows affected (0.09 sec)

mysql> show variables like '%general_log%';
+------------------+----------------------------------+
| Variable_name| Value|
+------------------+----------------------------------+
| general_log  | ON   |
| general_log_file | /data/3306/data/linzhongniao.log |
+------------------+----------------------------------+
2 rows in set (0.00 sec)
[root@linzhongniao ~]# ll /data/3306/data/linzhongniao.*
 -rw-rw----. 1 mysql mysql 245 Nov  1 22:13 /data/3306/data/linzhongniao.log

测试general_log作用

general query log：记录客户端连接信息和执行的SQL语句信息：下面我们来验证一下，创建一个库

mysql> create database linzhongniao1;
Query OK, 1 row affected (0.00 sec)

接下来我们查看一下mysql.log普通查询日志

[root@linzhongniao ~]# cat /data/3306/data/linzhongniao.log 
/usr/local/mysql/bin/mysqld, Version: 5.5.32-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /data/3306/mysql.sock
Time Id CommandArgument
181101 22:13:24 8 Query show variables like '%general_log%'
181101 22:15:08 8 Query show databases
181101 22:15:23 8 Query create database linzhongniao1

我们看这里面记录了我刚才创建库的语句。

2.2 慢查询的配置

重要，优化SQL语句做增量备份主从同步都需要

[root@linzhongniao ~]# sed -n '33,35p' /data/3306/my.cnf  
log-slow-queries = /data/3306/slow.log
long_query_time = 1
log_queries_not_using_indexes
[root@linzhongniao ~]# cat /data/3306/slow.log 
/usr/local/mysql/bin/mysqld, Version: 5.5.32-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /data/3306/mysql.sock
Time Id CommandArgument

long_query_time参数多长时间记录慢查询，log-slow-queries慢查询日志文件存放的的位置，log_queries_not_using_indexes参数是将使用索引的语句记录到log里面去。

3.二进制日志（Binary Log）

（1）二进制日志的介绍

二进制日志（binary log）:记录数据被修改的相关信息。

（2）二进制日志的设置

从库提升主库必须开启log-slave-updates

[root@linzhongniao ~]# egrep "\[mysqld]|log-bin|log-sla" /data/3306/my.cnf 
[mysqld]
log-bin = /data/3306/mysqlbin_linzhongniao
log-slave-updates

log_bin是记录binlog，sql_log_bin参数的值为OFF是不更新临时不记录binlog，可以用set global设置，查看没有修改可以退出在登陆查看

mysql> show variables like '%log_bin%';
 +---------------------------------+-------+
| Variable_name   | Value |
 +---------------------------------+-------+
| log_bin | ON|
| log_bin_trust_function_creators | OFF   |
| sql_log_bin | ON|
3 rows in set (0.00 sec)
mysql> set global sql_log_bin=OFF; 
Query OK, 0 rows affected (0.00 sec)

4.binlog日志的三种模式

4.1 Statement Level模式

每一条被修改数据的sql语句都会记录到主服务器(master)的binlog中，从服务器(slave)在主从复制的时候sql进程会解析成和主服务器(master)端执行的相同的sql语句来再次执行。

优点：Stament level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能。因为他只需要记录在Master上所执行的语句的细节，以及执行语句时候的上下文的信息。

缺点：由于他是记录的执行语句，所以为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端执行的时候能够得到和在master端执行时候相同的语句。另外就是，由于MySQL现在发展比较快，很多的新功能不断的加入，使MySQL的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement level下，目前已经发现的就有不少情况会造成MySQL的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现问题，比如：sleep()函数在有些版本中就不能正确复制，在存储过程中使用了last_insert_id()函数，可能会在slave和master上得到不一致的id等等，由于row level是基于每一行来记录的变化，所以不会出现类似的问题。

4.2 Row Level模式

row level模式的日志中会记录每一行数据被修改的形式，然后再对slave端相同的数据进行修改。

优点：在row level模式下，binlog中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了，所以row level的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程，或function,以及trigger的调用和触发无法被正确复制的问题。他能够解决statement level模式解决不了的问题。

缺点：row level模式下，所有的执行的语句当记录到日志中的时候，都将以每行的修改为记录，这样可能会产生大量的日志内容，比如这样的一条update语句：update student set name=’nishishei’where name=’linzhongniao’,执行之后，日志中记录的不是这条update语句而是这条语句所更新的每一条记录的变化情况，这样一条记录就会被更新成很多个事件。自然会导致bin-log日志的存储量增大。

（1）修改为row模式

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

如果查询默认模式没有修改，退出登录查看默认模式就修改了

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

（2）刷新binlog

为了更好的演示我们刷新一下binlog,这时候我们再更新数据的时候就会往新生成的binlog日志文件里面写了。

[root@mysql 3306]# mysqladmin -uroot -p123456 -S /data/3306/mysql.sock flush-logs

（3）登录mysql修改lingzhongniao库的数据

我们将linzhongniao库中student表中的name字段的数据都修改为linzhongniao2

mysql> update student set name='linzhongniao2';
Query OK, 9 rows affected (0.00 sec)
Rows matched: 9  Changed: 9  Warnings: 0

mysql> select * from student;
+----+---------------+
| id | name  |
+----+---------------+
|  1 | linzhongniao2 |
|  3 | linzhongniao2 |
|  5 | linzhongniao2 |
|  6 | linzhongniao2 |
|  8 | linzhongniao2 |
| 10 | linzhongniao2 |
| 11 | linzhongniao2 |
| 13 | linzhongniao2 |
| 15 | linzhongniao2 |
+----+---------------+
9 rows in set (0.00 sec)

（4）查看binlog日志文件

[root@mysql 3306]# mysqlbinlog --base64-output=decode-rows -v  mysql-bin.000026
……省略……
#180214  0:52:30 server id 1  end_log_pos 245 	Table_map: `linzhongniao`.`student` mapped to number 33
#180214  0:52:30 server id 1  end_log_pos 617 	Update_rows: table id 33 flags: STMT_END_F
### UPDATE `linzhongniao`.`student`
### WHERE
###   @1=1
###   @2='linzhongniao1'
### SET
###   @1=1
###   @2='linzhongniao2'
### UPDATE `linzhongniao`.`student`
### WHERE
###   @1=3
###   @2='linzhongniao1'
### SET
###   @1=3
###   @2='linzhongniao2'
### UPDATE `linzhongniao`.`student`
### WHERE
###   @1=5
###   @2='linzhongniao1'
### SET
###   @1=5
###   @2='linzhongniao2'
### UPDATE `linzhongniao`.`student`
### WHERE
###   @1=6
###   @2='linzhongniao1'
### SET
###   @1=6
###   @2='linzhongniao2'
### UPDATE `linzhongniao`.`student`
### WHERE
###   @1=8
###   @2='linzhongniao1'
### SET
###   @1=8
###   @2='linzhongniao2'
### UPDATE `linzhongniao`.`student`
### WHERE
###   @1=10
###   @2='linzhongniao1'
### SET
###   @1=10
###   @2='linzhongniao2'
### UPDATE `linzhongniao`.`student`
### WHERE
###   @1=11
###   @2='linzhongniao1'
### SET
###   @1=11
###   @2='linzhongniao2'
### UPDATE `linzhongniao`.`student`
### WHERE
###   @1=13
###   @2='linzhongniao1'
### SET
###   @1=13
###   @2='linzhongniao2'
### UPDATE `linzhongniao`.`student`
### WHERE
###   @1=15
###   @2='linzhongniao1'
### SET
###   @1=15
###   @2='linzhongniao2'
# at 617
#180214  0:52:30 server id 1  end_log_pos 644 	Xid = 39
COMMIT/*!*/;
DELIMITER ;
# End of log file

我们看上面的内容可以发现row模式记录了每一行记录被修改前后的内容，这样就会导致bin-log日志的存储量就会很大。

4.3 Mixed模式

Mixed模式是前两种模式的混合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来划分记录的日志形式，也就是在Statement和Row两种模式之间的选择一种，新版本中的Statement level 还是和以前一样，仅仅记录执行的语句。而新版本MySQL中对row level 模式也被做了优化，并不是所有的数据都会以row level模式来记录，当遇到表结构变更的时候就会以statement 模式来记录，如果sql语句确实就是update或者delete等语句，那么还是会记录所有行的变更。

5. 调整binlog日志模式方法

5.1 在配置文件中修改的方法

下面是Mysql5.5默认模式也是我们前面主从复制默认的模式

mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

在配置文件中修改的参数如下：

[root@linzhongniao ~]# grep "MIXED" /data/3306/my.cnf  
binlog_format = MIXED

工作中推荐使用混合模式

5.2 在线修改立即生效方法

（1）运行时在线修改，当前会话生效

mysql> set session binlog_formant = 'STATEMENT';
mysql> set session binlog_formant = 'ROW';
mysql> set session binlog_formant = 'MIXED';

（2）全局生效，所有用户都生效：

mysql> set global binlog_format = 'ROW';
mysql> set global binlog_format = 'STATEMENT';
mysql> set global binlog_format = 'MIXED';