本文发布于Cylon的收藏册,转载请著名原文链接~

错误日志 error log

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

错误日志配置

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

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

启动MySQL命令里加入

 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服务后再查看日志文件,看报有什么错误

    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语句信息。

普通查询日志配置

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

临时生效

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

永久生效

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

日志示例

 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语句

慢查询日志调整

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

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

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

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

开启慢查询

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

慢查询日志切割

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慢查询语句

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

explain语句检查索引执行情况

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日志记录,用于数据库的增量恢复,以及主从复制

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功能

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

mysqlbinlog工具解析binlog日志

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

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

$ cat mysql-bin.000004 
I.5.54-MariaDBlog*ȎX8
      p5ɎXD9std!testB

解析指定库的binlog

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

 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,例如:

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

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

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.

按照位置截取:精准,时间长

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

按时间截取:模糊、不准,会丢失数据

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的模式

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

设置MySQL binlog的格式

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

永久生效

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

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

 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日志记录

 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

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

本文发布于Cylon的收藏册,转载请著名原文链接~

链接:https://www.oomkill.com/2017/05/ch04-mysql-log/

版权:本作品采用「署名-非商业性使用-相同方式共享 4.0 国际」 许可协议进行许可。