mysql日志管理


mysql的日志分为如下几类:

1.错误日志

1.1记录服务器启动和关闭过程中的信息

1.2记录服务器运行过程中的错误信息.

1.3记录事件调度器运行一个事件时产生的信息.

1.4在从服务器上启动从服务器进程时产生的信息.

| log_error                               | /usr/local/mysql/data/idc131.err      |错误日志位置

| log_warnings                            | 1                                     |将警告日志记入错误日志

2.一般查询日志

mysql> show global variables like 'general%log%';

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

| Variable_name    | Value                            |

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

| general_log      | OFF                              |是否启用一般查询日志

| general_log_file | /usr/local/mysql/data/idc131.log |一般查询日志的位置

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

2 rows in set (0.00 sec)

默认值取决于mysqld在启动时是否使用了--general_log的功能,如果定义,则输出位置由--log-output定义,若该值定义为none,则即使启用了该功能,也不记录任何信息.

mysql> show global variables like 'log';

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

| Variable_name | Value |

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

| log           | OFF   |是否记录所有语句的操作于一般查询日志中,默认关闭,mysql5.6废弃该参数.

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

1 row in set (0.00 sec)

mysql> show variables like 'log_output';

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

| Variable_name | Value |

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

| log_output    | FILE  |

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

1 row in set (0.00 sec)

用于定义一般查询日志或慢查询日志的输出方式,有table,file,none,也可以是table和file的组合,用逗号隔开.

默认为table,如果组合中出现none,则其他的设定都失效,无论是否启用日志功能,都将不记录任何日志信息.

3.慢查询日志

mysql> show variables like 'log_queries_not_using_indexes';

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

| Variable_name                 | Value |

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

| log_queries_not_using_indexes | OFF   |

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

1 row in set (0.00 sec)

是否将未使用索引的查询操作记录到慢查询日志中.

mysql> show global variables like 'long_query%';

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

| Variable_name   | Value     |

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

| long_query_time | 10.000000 |

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

1 row in set (0.01 sec)

超过该值定义的时间长度的查询,均为慢查询语句.

mysql> show global variables like 'log_slow_queries';

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

| Variable_name    | Value |

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

| log_slow_queries | OFF   |

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

1 row in set (0.00 sec)

是否启用慢查询,mysql5.6中该参数修改为slow_query_log.

mysql> show global variables like 'slow_query%';

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

| Variable_name       | Value                                 |

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

| slow_query_log      | OFF                                   |

| slow_query_log_file | /usr/local/mysql/data/idc131-slow.log |

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

2 rows in set (0.00 sec)

4.二进制日志:记录任何引起或可能引起数据库变化的语句,用于复制或即时点恢复.


mysql> show global variables like 'log_bin';

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

| Variable_name | Value |

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

| log_bin       | ON    |

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

1 row in set (0.00 sec)

是否启用二进制日志,如果mysqld设置了--log_bin选项,则值默认为on,否则为off.

mysql> show global variables like 'binlog_format';

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

| Variable_name | Value |

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

| binlog_format | MIXED |

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

1 row in set (0.00 sec)

二进制日志的格式,有row,statement,mixed,默认为statement,若设置该值,但未启用二进制日志,则启动过程中会产生警告信息.

mysql> show global variables like 'expir%';

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

| Variable_name    | Value |

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

| expire_logs_days | 0     |

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

1 row in set (0.00 sec)

设定二进制日志的过期天数,超出此天数的二进制日志将被自动删除.

默认为0,表示不启用自动删除功能,如果启动,作用范围发生在mysql启动或flush日志的时候,作用范围是全局.

mysql> show global variables like 'log_bin_trust_function_creators';

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

| Variable_name                   | Value |

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

| log_bin_trust_function_creators | OFF   |

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

1 row in set (0.00 sec)

该参数仅在启用二进制日志时有效,用于控制创建存储函数时,如果会导致不安全的日志记录二进制日志,是否禁止创建存储函数.

默认为0,表示用户除了具有create routine和alter routine权限外,还应该具有super权限,否则将禁止创建存储函数.

还要求在创建函数时必须为其使用deterministic属性,再不然就是附带read sql data或no sql属性.

其值为1时,不启用这些限制.

mysql> show global variables like 'max_binlog_size';

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

| Variable_name   | Value      |

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

| max_binlog_size | 1073741824 |

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

1 row in set (0.00 sec)

二进制日志文件的上限,单位为字节,最小为4k,最大为1G,默认为1G.

某事物产生的日志仅能写入一个二进制日志文件.因此,实际上的二进制日志可能


mysql> show global variables like 'max_binlog_cache%';

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

| Variable_name         | Value                |

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

| max_binlog_cache_size | 18446744073709547520 |

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

1 row in set (0.00 sec)

二进制日志缓存空间大小,5.5.9后的版本中仅应用为事物缓存,其上限由max_binlog_stmt_cache_size决定.

mysql> show global variables like 'max_binlog_stmt%';

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

| Variable_name              | Value                |

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

| max_binlog_stmt_cache_size | 18446744073709547520 |

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

1 row in set (0.00 sec)


mysql> show global variables like 'sql_log%';

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

| Variable_name | Value |

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

| sql_log_bin   | ON    |是否将二进制日志的信息记录到日志文件.

| sql_log_off   | OFF   |是否禁止一般查询类的信息记录到查询日志文件,默认不禁止.

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

2 rows in set (0.00 sec)

mysql> show global variables like 'sync_binlog%';

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

| Variable_name | Value |

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

| sync_binlog   | 0     |

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

1 row in set (0.00 sec)

设置多久同步一次二进制日志到磁盘中,0表示不同步,N表示对二进制日志每多少次写操作后,同步一次.

当autocommit为1的时候,每条语句执行都会引起日志同步,否则每个事务的提交都会引起日志同步.

二进制日志事件由产生时间和事件相对位置来确定.

二进制日志由索引文件盒二进制日志组成,即由mysql-bin.00000N和mysql-bin.index文件组成.

查看当前正在使用的二进制日志文件:

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000014 |      107 |              |                  |

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

1 row in set (0.00 sec)

查看从二进制日志文件中的事件信息:

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000014 |      107 |              |                  |

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

1 row in set (0.00 sec)


mysql> create table mytab5 select * from mytab1;

Query OK, 6 rows affected (0.28 sec)

Records: 6  Duplicates: 0  Warnings: 0


mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000014 |      210 |              |                  |

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

1 row in set (0.00 sec)


mysql> show binlog events in 'mysql-bin.000014';

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

| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                 |

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

| mysql-bin.000014 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.38-log, Binlog ver: 4                |

| mysql-bin.000014 | 107 | Query       |         1 |         210 | use `mydb`; create table mytab5 select * from mytab1 |

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

2 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000014' from 107;

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

| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                                                 |

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

| mysql-bin.000014 | 107 | Query      |         1 |         210 | use `mydb`; create table mytab5 select * from mytab1 |

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

1 row in set (0.00 sec)


mysql> help show binlog events;

Name: 'SHOW BINLOG EVENTS'

Description:

Syntax:

SHOW BINLOG EVENTS

  [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]


Shows the events in the binary log. If you do not specify 'log_name',

the first binary log is displayed.


URL: http://dev.mysql.com/doc/refman/5.5/en/show-binlog-events.html

显示mysql-bin.000014二进制文件中107位置后面的3个偏移事件:

mysql> show binlog events in 'mysql-bin.000014' from 107 limit 1,3;

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

| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                           |

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

| mysql-bin.000014 | 210 | Query      |         1 |         278 | BEGIN                          |

| mysql-bin.000014 | 278 | Table_map  |         1 |         330 | table_id: 33 (mydb.mytab1)     |

| mysql-bin.000014 | 330 | Write_rows |         1 |         385 | table_id: 33 flags: STMT_END_F |

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

3 rows in set (0.00 sec)

使用mysqlbinlog命令显示mysql-bin.000014中的所有信息:

[root@idc131 data]# mysqlbinlog  mysql-bin.000014

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#140803 23:15:16 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.38-log created 140803 23:15:16 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

BFLeUw8BAAAAZwAAAGsAAAABAAQANS41LjM4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAEUt5TEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

'/*!*/;

# at 107

#140807  6:08:30 server id 1  end_log_pos 210   Query   thread_id=24    exec_time=0     error_code=0

use `mydb`/*!*/;

SET TIMESTAMP=1407362910/*!*/;

SET @@session.pseudo_thread_id=24/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=0/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C gbk *//*!*/;

SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

create table mytab5 select * from mytab1

/*!*/;

# at 210

#140807  6:13:36 server id 1  end_log_pos 278   Query   thread_id=24    exec_time=0     error_code=0

SET TIMESTAMP=1407363216/*!*/;

BEGIN

/*!*/;

# at 278

# at 330

#140807  6:13:36 server id 1  end_log_pos 330   Table_map: `mydb`.`mytab1` mapped to number 33

#140807  6:13:36 server id 1  end_log_pos 385   Write_rows: table id 33 flags: STMT_END_F


BINLOG '

kKjiUxMBAAAANAAAAEoBAAAAACEAAAAAAAEABG15ZGIABm15dGFiMQAEAw8PAQQ8ADwAAA==

kKjiUxcBAAAANwAAAIEBAAAAACEAAAAAAAEABP/wCAAAAANCQkICQjEC8AkAAAADQ0NCAkMxAw==

'/*!*/;

# at 385

#140807  6:13:36 server id 1  end_log_pos 412   Xid = 59

COMMIT/*!*/;

# at 412

#140807  6:13:45 server id 1  end_log_pos 480   Query   thread_id=24    exec_time=0     error_code=0

SET TIMESTAMP=1407363225/*!*/;

BEGIN

/*!*/;

# at 480

# at 532

#140807  6:13:45 server id 1  end_log_pos 532   Table_map: `mydb`.`mytab1` mapped to number 33

#140807  6:13:45 server id 1  end_log_pos 587   Write_rows: table id 33 flags: STMT_END_F


BINLOG '

majiUxMBAAAANAAAABQCAAAAACEAAAAAAAEABG15ZGIABm15dGFiMQAEAw8PAQQ8ADwAAA==

majiUxcBAAAANwAAAEsCAAAAACEAAAAAAAEABP/wCwAAAANFRUUCRTEF8AwAAAADRkZGAkY1Bg==

'/*!*/;

# at 587

#140807  6:13:45 server id 1  end_log_pos 614   Xid = 60

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

使用mysqlbinlog获取mysql-bin.000014中从587到826的日志信息:

[root@idc131 data]# mysqlbinlog --start-position=587 --stop-position=826  mysql-bin.000014

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#140803 23:15:16 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.38-log created 140803 23:15:16 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

BFLeUw8BAAAAZwAAAGsAAAABAAQANS41LjM4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAEUt5TEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

'/*!*/;

# at 587

#140807  6:13:45 server id 1  end_log_pos 614   Xid = 60

COMMIT/*!*/;

# at 614

#140807  6:33:04 server id 1  end_log_pos 682   Query   thread_id=25    exec_time=0     error_code=0

SET TIMESTAMP=1407364384/*!*/;

SET @@session.pseudo_thread_id=25/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=0/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C gbk *//*!*/;

SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 682

#140807  6:33:04 server id 1  end_log_pos 710   Intvar

SET INSERT_ID=14/*!*/;

# at 710

#140807  6:33:04 server id 1  end_log_pos 826   Query   thread_id=25    exec_time=0     error_code=0

use `mydb`/*!*/;

SET TIMESTAMP=1407364384/*!*/;

insert into mytab1(name,class,age)values('UU','U1',8)

/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@idc131 data]# 

使用mysqlbinlog显示2个时间段间的日志信息:

[root@idc131 data]# mysqlbinlog --start-datetime='2014-08-07 06:33:15' --stop-datetime='2014-08-07 06:33:18'  mysql-bin.000014

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#140803 23:15:16 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.38-log created 140803 23:15:16 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

BFLeUw8BAAAAZwAAAGsAAAABAAQANS41LjM4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAEUt5TEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

'/*!*/;

# at 853

#140807  6:33:16 server id 1  end_log_pos 921   Query   thread_id=25    exec_time=0     error_code=0

SET TIMESTAMP=1407364396/*!*/;

SET @@session.pseudo_thread_id=25/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=0/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C gbk *//*!*/;

SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 921

#140807  6:33:16 server id 1  end_log_pos 949   Intvar

SET INSERT_ID=15/*!*/;

# at 949

#140807  6:33:16 server id 1  end_log_pos 1066  Query   thread_id=25    exec_time=0     error_code=0

use `mydb`/*!*/;

SET TIMESTAMP=1407364396/*!*/;

insert into mytab1(name,class,age)values('OO','U2',88)

/*!*/;

# at 1066

#140807  6:33:16 server id 1  end_log_pos 1093  Xid = 77

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@idc131 data]# 

滚动二进制日志:

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000014 |     1093 |              |                  |

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

1 row in set (0.00 sec)


mysql> flush logs;

Query OK, 0 rows affected (0.20 sec)


mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000015 |      107 |              |                  |

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

1 row in set (0.00 sec)

重启mysql后,自动滚动日志.

[root@idc131 ~]# mysql -e 'show master status'

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000015 |      107 |              |                  |

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

[root@idc131 ~]# service mysqld restart

Shutting down MySQL. SUCCESS! 

Starting MySQL... SUCCESS! 

[root@idc131 ~]# mysql -e 'show master status'

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000016 |      107 |              |                  |

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

清理二进制日志方法:

mysql> show binary logs;

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

| Log_name         | File_size |

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

| mysql-bin.000001 |       107 |

| mysql-bin.000002 |       107 |

| mysql-bin.000003 |     27690 |

| mysql-bin.000004 |   1070318 |

| mysql-bin.000005 |       301 |

| mysql-bin.000006 |      1345 |

| mysql-bin.000007 |       126 |

| mysql-bin.000008 |      4063 |

| mysql-bin.000009 |      3787 |

| mysql-bin.000010 |       362 |

| mysql-bin.000011 |       126 |

| mysql-bin.000012 |       126 |

| mysql-bin.000013 |       126 |

| mysql-bin.000014 |      1136 |

| mysql-bin.000015 |       126 |

| mysql-bin.000016 |       107 |

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

16 rows in set (0.00 sec)


mysql> purge binary logs to 'mysql-bin.000003';

Query OK, 0 rows affected (0.03 sec)


mysql> show binary logs;

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

| Log_name         | File_size |

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

| mysql-bin.000003 |     27690 |

| mysql-bin.000004 |   1070318 |

| mysql-bin.000005 |       301 |

| mysql-bin.000006 |      1345 |

| mysql-bin.000007 |       126 |

| mysql-bin.000008 |      4063 |

| mysql-bin.000009 |      3787 |

| mysql-bin.000010 |       362 |

| mysql-bin.000011 |       126 |

| mysql-bin.000012 |       126 |

| mysql-bin.000013 |       126 |

| mysql-bin.000014 |      1136 |

| mysql-bin.000015 |       126 |

| mysql-bin.000016 |       107 |

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

14 rows in set (0.00 sec)


mysql> purge binary logs before '20140806';

Query OK, 0 rows affected (0.08 sec)


mysql> show binary logs;

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

| Log_name         | File_size |

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

| mysql-bin.000014 |      1136 |

| mysql-bin.000015 |       126 |

| mysql-bin.000016 |       107 |

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

3 rows in set (0.00 sec)

log_bin和sql_log_bin决定了是否写二进制日志.

sql_log_off该参数决定了是否将一般查询日志写入查询日志文件.

5.中继日志:

mysql> show variables like 'log_slave_updates';

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

| Variable_name     | Value |

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

| log_slave_updates | OFF   |

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

1 row in set (0.00 sec)

用于是否将主服务器同步过来的操作记录到二进制日志中.

mysql> show global variables like 'relay_log%';

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

| Variable_name         | Value          |

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

| relay_log             |                |中继日志文件名

| relay_log_index       |                |中继日志索引文件名

| relay_log_info_file   | relay-log.info |

| relay_log_purge       | ON             |对不再需要的中继日志是否自动清理.

| relay_log_recovery    | OFF            |

| relay_log_space_limit | 0              |设置存储中继日志的可用空间大小.

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

6 rows in set (0.00 sec)

6.事务日志

事务性存储引擎用来保证事务的ACID特性的,相当于oracle中的undo.

mysql> show variables like 'innodb_flush_log_at_trx_commit';

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

| Variable_name                  | Value |

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

| innodb_flush_log_at_trx_commit | 1     |

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

1 row in set (0.00 sec)

0表示每秒刷新,并执行磁盘flush操作.

1表示每事务刷写,并执行磁盘flush操作.

2表示每事务同步,但不执行磁盘flush.


mysql> show variables like 'innodb_log_file%';

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

| Variable_name             | Value   |

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

| innodb_log_file_size      | 5242880 |事务日志文件大小

| innodb_log_files_in_group | 2       |事务日志组中的文件个数.

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

2 rows in set (0.00 sec)

mysql> show variables like 'innodb_mirror%';

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

| Variable_name              | Value |

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

| innodb_mirrored_log_groups | 1     |事务日志镜像组的个数.

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

1 row in set (0.00 sec)