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

isgood

1.错误日志（Error log）
　　（1）错误日志的介绍
　　错误日志（error log）:记录MySQL服务进程mysqld在自动/关闭或远行过程中遇到的错误信息
　　（2）错误日志的调整
　　在my.cnf配置文件里面查看错误日志log-error
　　

[root@mysql ~]# sed -n '78,80p' /data/3306/my.cnf　　
[mysqld_safe]
　　
log-error=/data/3306/mysql_zbf3306.err
　　
pid-file=/data/3306/mysqld.pid
　　

2.查询日志（Query Log）
　　（1）查询日志的介绍
　　查询日志（query log）:又可分两类
　　普通查询日志（general query log）：记录客户端连接信息和执行的SQL语句信息。
　　慢查询日志（slow query log）：记录执行时间超出指定值。（long_query_time）的SQL语句。
　　（2）普通查询日志的调整（general query log）
　　普通查询日志不重要，在工作中是不开的
　　a.我们先登录mysql看一下general log有没有开启
　　

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

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

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

　　c.查看general_log是否开启
　　

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

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

mysql> create database nn;　　
Query OK, 1 row affected (0.01 sec)
　　

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

[root@mysql ~]# cat /data/3306/data/mysql.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>　　
180213  1:11:05    25 Query create database nn
　　

　　我们看这里面记录了我刚才创建库的语句。
　　（3）慢查询的调整（重要，优化SQL语句做增量备份主从同步都需要）
　　

[root@mysql ~]# sed -n "33,35p" /data/3306/my.cnf　　
long_query_time = 1
　　
#log-slow-queries = /data/3306/slow.log
　　
#log_queries_not_using_indexes
　　

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

3.二进制日志（Binary Log）
　　（1）二进制日志的介绍
　　二进制日志（binary log）:记录数据被修改的相关信息。
　　（2）二进制日志的调整
　　

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

　　log_bin是记录binlog
　　sql_log_bin临时不记录binlog

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模式
　　日志中会记录成每一行数据被修改的形式，然后再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日志的存储量就会很大。

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@mysql ~]# sed -n "39,42p" /data/3306/my.cnf　　
log-bin = /data/3306/mysql-bin
　　
#binlog_format = "STATEMENT"
　　
#binlog_format = "ROW"
　　
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';
　　

　　（3）实战演示全局修改binlog日志模式以及row的原理演示
　　a.修改为row模式
　　

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

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

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

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

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

　　c.登录mysql修改linzhongniao库的数据
　　我们将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;
　　
+----+---------------+

　　
|>　　
+----+---------------+
　　
|  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)
　　

　　d.查看binlog日志文件
　　

[root@mysql 3306]# mysqlbinlog --base64-output=decode-rows -v  mysql-bin.000026　　
……省略……

　　
#180214  0:52:30 server>
　　
#180214  0:52:30 server>　　
### 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>　　
COMMIT/*!*/;
　　
DELIMITER ;
　　
# End of log file
　　

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