Contents
一.MySQL数据库管理之日志文件介绍
1.mysql工具mysqlbinlog
mysqlbinlog工具的作用是解析mysql的二进制binlog日志内容，把二进制日志解析成可以再mysql数据库执行的SQL语句
1.1 mysql的binlog日志是什么？
mysql数据目录下的如下文件就是mysql的binlog日志
[root@mysql ~]# ls -l /mysqldata/3306/mysql-bin.* -rw-rw----. 1 mysql mysql 1434 Jun 13 07:06 /mysqldata/3306/mysql-bin.000001 -rw-rw----. 1 mysql mysql 622 Jun 13 07:14 /mysqldata/3306/mysql-bin.000002 -rw-rw----. 1 mysql mysql 434 Jun 13 18:37 /mysqldata/3306/mysql-bin.000003 -rw-rw----. 1 mysql mysql 126 Jun 13 18:43 /mysqldata/3306/mysql-bin.000004 -rw-rw----. 1 mysql mysql 2194 Jun 14 03:39 /mysqldata/3306/mysql-bin.000005 -rw-rw----. 1 mysql mysql 483 Jun 14 03:48 /mysqldata/3306/mysql-bin.000006 -rw-rw----. 1 mysql mysql 529082 Jun 14 21:38 /mysqldata/3306/mysql-bin.000007
提示：要打开log-bin功能，才能生成上面文件
1.2 mysql的binlog日志作用是什么？
mysql的binlog日志作用是用来记录mysql内部增删改等对mysql数据库有更新的内容的记录（对数据库的改动），对数据库查询的语句如show，select开头的语句，不会被binlog 日志记录，用于数据库的增量恢复，以及主从复制
1.3 mysqlbinlog工具解析binlog日志实践
默认情况binlog日志是二进制格式的，不能使用查看文本工具的命令查看，例如：cat,vi
[root@mysql ~]# file /mysqldata/3306/mysql-bin.000001 /mysqldata/3306/mysql-bin.000001: MySQL replication log
1.4 解析指定库的binlog日志
范例：利用mysqlbinlog -d参数解析指定库的binlog日志（如果想获取某张表，就从库中grep 表名）
[root@mysql ~]# mysqlbinlog -d john /mysqldata/3306/mysql-bin.000001 -r john.sql (-r 文件名，相当于重定向“>”) [root@mysql ~]# ls -l john.sql -rw-r--r--. 1 root root 1508 Jun 17 01:45 john.sql
结论：mysqlbinlog工具分库导出binlog，如果使用-d参数，那更新数据时，必须有use database，才能分出指定库的binlog，例如：
use john insert into student values(1,’john’)
下面的写法就不行：
insert into john.student values(2,’john’);
按照位置截取：精确
mysqlbinlog mysqlbin.000020 –start-position=365 –stop-position=456 –r pos.sql
按照时间点截取：不准确，模糊，丢数据
mysqlbinlog mysqlbin.000020 –start-datetime=’2018-10-16 16:14:15’ –stop-datetime=’2018-10-16 17:15:15’ –r time.sql
解析：ROW模式binlog日志
mysqlbinlog --base64-output="decode-rows" --verbose mysql-bin.00012
查看binlog日志模式
show variables like '%binlog_format%';
2.错误日志（error log）介绍与调整
1、错误日志（error log）介绍 mysql的错误日志记录mysql服务进程mysqld在启动/关闭或运行过程中遇到的错误信息： 2、错误日志（error log）实践 法一：在配置文件中调整发法：当然可以在启动时加入启动参数 [mysqld_safe] log-error=/data/3306/mysql_log_3306.err 登录MySQL数据库查看 mysql> show variables like '%log_error%'; +---------------+---------------------------+ | Variable_name | Value | +---------------+---------------------------+ | log_error | /data/3306/mysql_3306.err | +---------------+---------------------------+ 1 row in set (0.00 sec) 法二：启动mysql命令里加入： mysqld_safe –defaults-file=/data/3306/my.cnf –log-error=/data/3306/mysql_log.err &
3.普通查询日志（general query log）介绍与调整
生产环境中不用
1、普通查询日志介绍
普通查询日志，记录客户端连接信息和执行的SQL语句信息
2、普通查询日志调整
3、记录增删改查，影响性能开启的话 生产不好用
mysql> show variables like '%general_log%';
+------------------+--------------------------+
| Variable_name | Value |
+------------------+--------------------------+
| general_log | OFF |
| general_log_file | /data/3306/data/node.log |
+------------------+--------------------------+
2 rows in set (0.00 sec)
永久生效
~]# grep ge /data/3306/my.cnf
general_log = on
general_log_file = /data/3306/data/node.log
4.慢查询日志介绍与调整
1、慢查询日志介绍 慢查询日志：记录执行时间超出指定值的SQL语句 2、慢查询日志调整 log_query_time =1 ##看公司的要求需要记录几秒 log-slow-queries = /data/3306/slow.log log_queries_not_using_indexes ##没有使用索引的 3、慢查询日志切割 [root@mysql scripts]# cat /server/scripts/cut_slow.sh #!/bin/sh cd /data/3306 && \ /bin/mv slow.log slow.log.$(date +%F) && \ mysqladmin -uroot -ppcwangjixuan -S /data/3306/mysql.sock flush-logs 定时任务 00 0 * * * /bin/sh /server/scripts/cut_slow.sh &>/dev/null 3、使用工具mysqlsla分析慢查询，定时发送给相关人员信箱 4、使用explain命令优化SQL语句（select语句）的基本流程（上面以提到）
5.二进制日志介绍与调整
1、二进制日志介绍 二进制日志：记录数据被修改的相关信息，用于主从复制及增量恢复 2、二进制日志调整 mysql> show variables like 'log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | ON | 记录binlog开关 | log_bin_trust_function_creators | OFF | +---------------------------------+-------+ 2 rows in set (0.00 sec) 3、临时不记录binlog日志文件，危险命令 mysql> set session sql_log_bin = off; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%log_bin%'; ##有从库不推荐这么玩，一般都使用 +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | ON | | log_bin_trust_function_creators | OFF | | sql_log_bin | OFF | +---------------------------------+-------+ 3 rows in set (0.00 sec)
6 mysql的binlog有三种模式
6.1 row level
日志中会记录成每一行数据被修改的情况，然后在slave端再对相同的数据进行修改
优点：在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录哪一条记录被修改了，修改成什么样了，所以row level的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解，而且不会出现某些特定情况下的存储过程或function，以及trigger的调用和触发无法被正常复制的问题
缺点：row level下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如有这样一条update语句：update product set owner_member_id = ‘b’ where owner_member_id= ’a’,执行之后，日志中记录的不是这条update语句对应额事件（mysql以事件的形式来记录bin-log日志），二十这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的很多个事件，自然，bin-log日志的量就会很大，尤其是当执行alter table之类的语句的时候，产生的日志量是惊人的，因为mysql对于alter table之类的表结构变更语句的处理方式是整个表的每一条记录都需要变动，实际上就是重建了整个表，那么该表的每一条记录都会被记录到日志中
检验ROW模式下binlog日志记录效果
[root@mysql ~]# mysqlbinlog --base64-output="decode-rows" --verbose mysql-bin.000001
6.2 statement level(默认)
每一条会修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行
优点：statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能，因为它只需要记录在master上所执行的语句的细节，以及执行语句时候的上下文的信息
缺点：由于它是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么它还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端执行的时候能够得到和在master端执行时候相同的结果，另外就是，由于mysql现在发展比较快，很多的新功能不断的加入，是mysql的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现，在statement level下，目前已经发现就有不少情况会造成mysql的复制出现问题：主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现
6.3 Mixed
实际上就是前两中模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选择一种，新版本中statement level还是和以前的一样，仅仅记录执行的语句，而新版本的mysql中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更
6.5 企业场景如何选择binlog的模式
1、互联网公司，使用mysql的功能相对少（存储过程、触发器、函数），使用默认的语句模式，statementlevel（默认）
2、公司如果用到使用mysql的特殊功能（存储过程、触发器、函数），则选择Mixed模式
3、公司如果用到使用mysql的特殊功能（存储过程、触发器、函数），又希望数据最大化一致，则选择row模式
6.6 设置mysql binlog的格式
查看当前binlog的模式
mysql> show global variables like 'binlog_format%'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 1 row in set (0.01 sec)
在配置文件修改参数：
[mysqld] log-bin=mysql-bin binlog_format=”STATEMENT” #binlog_format=”ROW” #binlog_format=”MIXED”
运行时在线修改：调整为row模式
mysql> show global variables like 'binlog_format%'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 1 row in set (0.01 sec) mysql> set global binlog_format = ROW; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like 'binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec)
- 转载请注明来源：MySQL数据库管理之日志文件
- 本文永久链接地址：https://www.xionghaier.cn/archives/464.html
该文章由 John 发布
