MySQL的日志基础知识及基本操作

一、错误日志：Error Log

1、简介

错误日志是记录MySQL服务进程mysqld在启动/关闭或运行过程中遇到的错误信息。错误日志功能是默认开启的。并且，错误日志无法被禁止。默认情况下，错误日志存储在mysql数据库的数据文件中。错误日志文件通常的名称为hostname.err。其中，hostname表示服务器主机名。

错误日志所记录的信息是可以通过log-error和log-warnings来定义的，其中log-error是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。

2、log-error参数配置

vi /data/3306/my.cnf

[mysqld_safe]

log-error=/data/3306/mysql_oldboy3306.err

二、查询日志：Query Log

1、简介

(1)普通查询日志(General Query Log)

记录客户端连接信息和执行的SQL语句信息。默认情况下查询日志是关闭的。由于查询日志会用户的所有操作，其中还包含增删查改等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响mysql的性能的。如若不是为了调试数据库的目的建议不要开启查询日志。

(2)慢查询日志(Slow Query Log)

慢查询日志是用来记录执行时间超过指定时间(Long_Query_Time)的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。一般建议开启，它对服务器性能的影响微乎其微，但是可以记录mysql服务器上执行了很长时间的查询语句。可以帮助我们定位性能问题的。

(3)查看与log相关的参数

show variables like '%_log%';

+-----------------------------------------+-------------------------------+

|Variable_name |Value |

+-----------------------------------------+-------------------------------+

| back_log | 600 |

|binlog_cache_size |1048576 |

|binlog_direct_non_transactional_updates | OFF |

|binlog_format |STATEMENT |

|binlog_stmt_cache_size |32768 |

|expire_logs_days |7 |

| general_log | OFF |

|general_log_file |/data/3306/data/3306.log |

|innodb_flush_log_at_trx_commit |2 |

|innodb_locks_unsafe_for_binlog |OFF |

|innodb_log_buffer_size |2097152 |

|innodb_log_file_size |4194304 |

|innodb_log_files_in_group |3 |

|innodb_log_group_home_dir |./ |

|innodb_mirrored_log_groups |1 |

|max_binlog_cache_size |1048576 |

|max_binlog_size | 2097152 |

|max_binlog_stmt_cache_size |18446744073709547520 |

|max_relay_log_size |0 |

| relay_log | /data/3306/relay-bin |

|relay_log_index| |

|relay_log_info_file |/data/3306/relay-log.info |

|relay_log_purge |ON |

| relay_log_recovery | OFF |

|relay_log_space_limit |0 |

|slow_query_log |OFF |

|slow_query_log_file | /data/3306/data/3306-slow.log |

|sql_log_bin |ON |

|sql_log_off |OFF |

|sync_binlog |0 |

|sync_relay_log |0 |

|sync_relay_log_info |0 |

+-----------------------------------------+-------------------------------+

2、设置普通查询日志

(1)开启普通查询日志

set global general_log = ON;

(2)查看参数是否生效

show variables like '%general_log%';

+------------------+--------------------------+

|Variable_name | Value |

+------------------+--------------------------+

|general_log | ON |

|general_log_file | /data/3306/data/3306.log |

+------------------+--------------------------+

(3)查看日志内容

a、执行SQL语句

select * frommysql.user;

create databaseddd;

b、查看日志内容

cat/data/3306/data/3306.log

/application/mysql-5.5.32/bin/mysqld,Version: 5.5.32-log (Source distribution). started with:

Tcp port:3306 Unix socket: /data/3306/mysql.sock

Time Id Command Argument

17042513:38:20 9 Query show variables like '%general_log%'

17042513:41:55 9 Query select * from mysql.user

17042513:45:16 9 Query create database ddd

17042513:45:17 9 Quit

17042513:45:21 10 Connect root@localhost on

10 Query select @@version_comment limit 1

(4)关闭普通查询日志

set global general_log = OFF;

3、设置慢查询日志

(1)在配置文件my.cnf中加入慢查询参数

vi/data/3306/my.cnf

[mysqld_safe]

long_query_time= 1

log-slow-queries= /data/3306/slow.log

log_queries_not_using_indexes

(2)查看参数

show variableslike 'lo%';

+---------------------------------+---------------------------------+

|Variable_name |Value |

+---------------------------------+---------------------------------+

|local_infile | ON |

|lock_wait_timeout |31536000 |

|locked_in_memory |OFF |

| log | OFF |

| log_bin | ON |

|log_bin_trust_function_creators | OFF |

| log_error |/data/3306/mysql_oldboy3306.err |

|log_output |FILE |

| log_queries_not_using_indexes | OFF |

|log_slave_updates |OFF |

|log_slow_queries |OFF |

|log_warnings | 1 |

|long_query_time |1.000000 |

|low_priority_updates |OFF |

|lower_case_file_system |OFF |

|lower_case_table_names | 1 |

+---------------------------------+---------------------------------+

三、二进制日志：Binary Log

1、简介

记录数据被修改的相关信息，由参数log-bin指定位置和文件名。

2、查看binlog参数

show variables like '%log_bin';

+---------------+-------+

| Variable_name| Value |

+---------------+-------+

| log_bin | ON| #记录binlog日志

|sql_log_bin | ON |#临时在binlog不记录sql语句的日志

+---------------+-------+

3、binlog日志的三种级别

(1)Statement Level(SQL语句级别，默认)

a、作用

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

b、优点

不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

c、缺点

由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).

使用以下函数的语句也无法被复制：

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁

(2)Row Level(行级模式)

a、作用

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

b、优点

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

c、缺点

所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

(3)Mixed Level(混合模式，官方推荐)

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

4、调整binlog日志的模式

(1)设置方法

a、方法一：修改配置文件my.cnf

vi /data/3306/my.cnf

binlog_format="STATEMENT"

#binlog_format="ROW"

#binlog_format="MIXED"

b、方法二：在线修改

(i)运行时生效

set session binlog_format = 'STATEMENT';

set session binlog_format = 'ROW';

set session binlog_format = 'MIXED';

(ii)全局生效

set global binlog_format = 'STATEMENT';

set global binlog_format = 'ROW';

set global binlog_format = 'MIXED';

(2)查看当前binlog日志模式(默认为STATEMENT)

show variables like '%binlog_format%';

+---------------+-----------+

| Variable_name | Value |

+---------------+-----------+

| binlog_format | STATEMENT |

+---------------+-----------+

(3)在线设置为Row Level模式

a、设置

set global binlog_format = 'ROW';

b、查看修改后的结果，发现没有改变

show variables like '%binlog_format%';

+---------------+-----------+

| Variable_name| Value |

+---------------+-----------+

| binlog_format| STATEMENT |

+---------------+-----------+

c、退出，重进

quit;

mysql -uroot -p'123456' -S /data/3306/mysql.sock

d、再次查询

show variables like '%binlog_format%';

+---------------+-------+

| Variable_name| Value |

+---------------+-------+

| binlog_format| ROW |

+---------------+-------+

(4)查看Row Level模式效果

a、刷新日志，切换binlog

flush logs;

b、更新表中数据

update student set name='cc' where id=3;

c、查看binlog内容

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000005

# at 224

#170425 19:46:00。。。 Table_map:`test`.`student`mapped to number 35

#170425 19:46:00。。。 Update_rows:table id 35 flags: STMT_END_F

### UPDATE`test`.`student`

### WHERE

### @1=3

### @2='c'

### SET

### @1=3

### @2='cc'

# at 269

#170425 19:46:00server id 1 end_log_pos 296 Xid = 68

COMMIT/*!*/;

d、查看binlog中的ROW Level内容，必须用--base64-output=decode-rows -v才会正确显示