ch04 - MySQL数据库服务日志类型

错误日志 error log

MySQL错误日志记录MySQL服务进程mysqld在启动/关闭或运行过程中遇到的错误信息

错误日志配置

在配置文件中调整方法，当然可以在启动时加入启动参数

bash

1
2
[mysqld_safe]
log-error=/data/3306/mysql_3306.err

启动MySQL命令里加入

bash

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
/app/mysql/bin/mysqld_safe \
	--defaults-file=/data/3306/my.cnf \
	--log-error=/data/3306/mysql_3306.err

MariaDB> show variables like "%log_error%";
+-------------------+---------------------------+
| Variable_name 	| Value		                |
+-------------------+---------------------------+
| log_error  	 	| /data/3306/mysql_3306.err |
+-------------------+---------------------------+

遇到数据库启动不了

先把日志文件备份并清空启动一下mysql服务后再查看日志文件，看报有什么错误
bash
1 2 3
InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory
然后查看mysql3306目录下文件权限

普通查询日志 general query log

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

普通查询日志配置

bash

1
2
3
4
5
6
7
MariaDB> show variables like "%general_log%";
+------------------+-----------+
| Variable_name    | Value     |
+------------------+-----------+
| general_log      | OFF       |
| general_log_file | lnmp.log  |
+------------------+-----------+

临时生效

bash

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
MariaDB> set global general_log_file = '/data/3306/log/mysql_query.log';
MariaDB> set global general_log='on';

MariaDB> show variables like '%general_log%';                           
+-------------------+----------------------------------+
| Variable_name  	| Value               			   |
+-------------------+----------------------------------+
| general_log    	| ON            	               |
| general_log_file  | /data/3306/log/mysql_query.log   |
+-------------------+----------------------------------+

永久生效

bash

1
2
3
$ grep general /data/3306/my.cnf  
general_log=on
general_log_file=/data/3306/log/mysql_query.log

日志示例

bash

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
cat log/mysql_query.log 
/app/mysql/bin/mysqld, Version: 5.5.54-MariaDB (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /data/3306/mysql.sock
Time                 	Id	 Command    Argument
170130  8:55:03      	1 	 Connect   root@localhost as anonymous on 
 					    1 	 Query     select @@version_comment limit 1
170130  8:55:18      	1 	 Query     show variabales
170130  8:55:44      	2 	 Connect   root@localhost as anonymous on 
                      	2 	 Query     KILL QUERY 1
                      	2 	 Quit

慢查询日志 slow query log

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

慢查询日志调整

bash

1
2
3
long_query_time=1
log-slow-queries=/data/3306/log/mysql_slow.log
log_queries_not_using_indexes

慢查询的设置，对于数据库SQL的优化非常重要

bash

1
2
3
4
$ egrep que ../my.cnf   
long_query_time = 2
log-slow-queries = /data/3306/log/slow.log
log_queries_not_using_indexes

利用慢查询进行优化解决方案

开启慢查询

bash

1
2
3
long_query_time = 2
log-slow-queries = /data/3306/log/slow.log
log_queries_not_using_indexes

慢查询日志切割

bash

1
2
3
4
5
6
#!/bin/sh
cd /data/3306/ &&\
/bin/mv slow.log slow.log.$(date +%F)&&\
mysqladmin -uroot -p111 -S /data/3306/mysql.sock flush-log

00 00 * * * /bin/sh /server/scripts/cut_slow_log.sh &> /dev/null

使用mysqlsla分析慢查询，定时发给相关人员信箱

使用explain优化SQL语句（select）

抓SQL慢查询语句

bash

1
2
show full processlist;
mysql -uroot -p111 -S /data/3306/mysql.sock -e "show full processlist;"|egrep -vi "sleep"

explain语句检查索引执行情况

bash

1
2
explain select * from test where name='123'\G;
explain select SQL_NO_CACHE * from test where name='123'\G

对需要建立索引的条件列建立索引

大表不能高峰期建立索引，300w记录

分析慢查询的工具mysqlsla

二进制日志 binary log

二进制日志 (binary log)：用来记录mysql内部增删改等对mysql数据库有更新的内容的记录（对数据库的改动），对数据库查询的语句如：show、select开头的语句，不会被binlog日志记录，用于数据库的增量恢复，以及主从复制

bash

1
2
3
4
5
6
$ ls
mysql-bin.000002  
mysql-bin.000004  
mysql-bin.000001  
mysql-bin.000003  
mysql-bin.index

面试题：在MySQL数据库中，关于binlog日志，下列说法正确的是 ___ ?( A )
A. 依靠足够长度的binlog日志和定期的全备，我们可以恢复任何时间点的单表数据。
B. 以mysql主从同步为例，binlog中会记录主数据库进行的所有操作。
C. 以mysql主从同步为例，binlog中会记录主数据库进行的所有查询操作。
D. binlog通过cat和vi无法查看，但可以通过gedit查看

开启mysql的binlog功能

bash

1
2
3
log-bin=/data/3306/mysql-bin
max_binlog_cache_size = 5M
max_binlog_size = 20M

mysqlbinlog工具解析binlog日志

默认情况binlog日志是二进制格式的，不能使用查看文本工具的命令查看，例如 cat vi等

bash

1
2
3
4
5
6
$ file mysql-bin.000004
mysql-bin.000004: MySQL replication log

$ cat mysql-bin.000004 
I.5.54-MariaDBlog*ȎX8
      p5ɎXD9std!testB

解析指定库的binlog

范例：利用 mysqlbinlog -d 参数解析指定库的binlog日志

bash

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
$ cat -n 10 1.sql 
29  # at 313  # <=对应的位置
# #170130 13:03:49 2017年01月30日 13:03:49秒 对应的时间
# end_log_pos 结束的位置，对应下面开始的位置
    30  #170130 13:03:49 server id 1  end_log_pos 341   Intvar
    31  SET INSERT_ID=3000001/*!*/;
    32  # at 341
    33  #170130 13:03:49 server id 1  end_log_pos 543   Query   thread_id=1     exec_time=0     error_code=0
    34  use `test`/*!*/;
    35  SET TIMESTAMP=1485752629/*!*/;
    36  insert into test1(num1,num2,num3) values( NAME_CONST('rand_num1',3127167), NAME_CONST('rand_num2',3952885), NAME_CONST('rand_num3',382922))

结论：mysqlbinlog 工具分库导出 binlog，如果使用 -d 参数，那更新数据时，必须有use database，才能分出指定库的binlog，例如：

bash

1
2
use test;
insert into test values (1,'test');

官方资料：mysqlbinlog — Utility for Processing Binary Log Files

bash

1
2
 --database=db_name, -d db_name
This option causes mysqlbinlog to output entries from the binary log (local log only) that occur while db_name is been selected as the default database by USE.

按照位置截取：精准，时间长

bash

1
2
3
4
5
6
7
mysqlbinlog mysqlbin.000020 --start-position=200 --stop-position=450 -r pos.sql

# 指定开始位置，不指定结束位置。结束位置为文件结尾
mysqlbinlog mysqlbin.000020 --start-position=200  -r pos.sql

# 指定结束位置，不指定开始位置，开始位置为文件开头
mysqlbinlog mysqlbin.000020 --stop-position=450 -r pos.sql

按时间截取：模糊、不准，会丢失数据

bash

1
2
3
4
mysqlbinlog mysql-bin.0020 \
	--start-datatime='1912-10-10 10:10:10' \
	--stop-datetime='2015-10-10 10:10:10' \
	-r time.sql

mysqlbinlog命令

把 binlog 解析为sql语句（包含位置和时间点）
-d 参数根据指定库拆分binlog（拆分单表binlog可通过SQL关键字过滤）
通过位置参数截取部分binlog：--start-position=111 --stop-position=500，精确定位取部分内容。
通过时间参数截取部分binglog：--start-datetime='2016-10-10 10:10:10'
-r fileName，相当于重定向 “>”
解析 row 级别 binlog 日志方法 mysqlbinlog --base64-output=decode-rows -v mysql-bin.000004

binlog三种模式

Row Level

不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题
缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

Statement Level

每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).

Mixed Level

是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用 row 格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

生产环境如何选择binlog的模式

互联网公司，使用MySQL的功能相对少（存储过程、触发器、函数），选择默认的语句模式。
公司如果用到使用MySQL的特殊功能（存储过程、触发器、函数）。则选择Mixed。
公司如果公道使用MySQL的特殊功能（存储过程、触发器、函数），又希望数据最大化一致，此时最好用row模式

设置MySQL binlog的格式

bash

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
MariaDB >show global variables like '%binlog_format%'; 
+---------------+-----------+
| Variable_name | Value 	|
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

MariaDB >set global binlog_format='row';

MariaDB >show global variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

永久生效

bash

1
2
3
4
log-bin=mysql-bin
binlog_format=STATEMENT
binlog_format=MIXED
binlog_format=ROW

测试ROW模式下的binlog记录日志情况

bash

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
  22 # at 366
  23 # at 1377
  24 # at 2392
  25 # at 3410
  26 # at 4420
  27 # at 5428
  28 #170202  6:29:13 server id 1  end_log_pos 366   Table_map: `test`.`test1` mapped to number 35
  25 # at 3410
  26 # at 4420
  27 # at 5428
  28 #170202  6:29:13 server id 1  end_log_pos 366   Table_map: `test`.`test1` mapped to number 35
  29 #170202  6:29:13 server id 1  end_log_pos 1377  Update_rows: table id 35
  30 #170202  6:29:13 server id 1  end_log_pos 2392  Update_rows: table id 35
  31 #170202  6:29:13 server id 1  end_log_pos 3410  Update_rows: table id 35
  32 #170202  6:29:13 server id 1  end_log_pos 4420  Update_rows: table id 35
  33 #170202  6:29:13 server id 1  end_log_pos 5428  Update_rows: table id 35
  34 #170202  6:29:13 server id 1  end_log_pos 6002  Update_rows: table id 35 flags: STMT_END_F
  35 ### UPDATE `test`.`test1`
  36 ### WHERE
  37 ###   @1=1
  38 ###   @2='3941282'
  39 ###   @3='3149717'
  40 ###   @4='3924740'
  41 ### SET
  42 ###   @1=1
  43 ###   @2='3941282'
  44 ###   @3='test'
  45 ###   @4='3924740'
  46 ### UPDATE `test`.`test1`
  47 ### WHERE
  48 ###   @1=2
  49 ###   @2='174549'
  50 ###   @3='9098525'
  51 ###   @4='4968976'
  52 ### SET
  53 ###   @1=2
  54 ###   @2='174549'
  55 ###   @3='test'
  56 ###   @4='4968976'
  57 ### UPDATE `test`.`test1`
  58 ### WHERE
  59 ###   @1=3
  60 ###   @2='7549308'
  61 ###   @3='2839610'
  62 ###   @4='1550126'
  63 ### SET
  64 ###   @1=3
  65 ###   @2='7549308'
  66 ###   @3='test'
  67 ###   @4='1550126' 

statement日志记录

bash

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# at 313
#170202  6:36:27 server id 1  end_log_pos 403 	Query	thread_id=1	exec_time=0	error_code=0
use `test`/*!*/;
SET TIMESTAMP=1485988587/*!*/;
update test1 set num3='aa1'
/*!*/;
# at 403
#170202  6:36:27 server id 1  end_log_pos 430 	Xid = 11
COMMIT/*!*/;
DELIMITER ;
# End of log file

1197

bash

1
ERROR 1197 (HY000) at line 4: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

原因：row 格式的 binlog 的特点：在 row 模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。所以会造成binlog cache因为过小而中断。

解决设置大的cache

错误日志 error log#

普通查询日志 general query log#

慢查询日志 slow query log#

利用慢查询进行优化解决方案#

使用explain优化SQL语句（select）#

二进制日志 binary log#

mysqlbinlog命令#

binlog三种模式#

设置MySQL binlog的格式#

相关阅读

错误日志 error log

普通查询日志 general query log

慢查询日志 slow query log

利用慢查询进行优化解决方案

使用explain优化SQL语句（select）

二进制日志 binary log

mysqlbinlog命令

binlog三种模式

设置MySQL binlog的格式