mysql web日志_mysql日志管理

た入场券 2022-10-31 12:13 267阅读 0赞

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;

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

| mysql-bin.000014 |      107 |              |                  |

\+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

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

mysql> show master status;

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

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

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

| mysql-bin.000014 |      210 |              |                  |

\+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

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

\+------------------+-----+-------------+-----------+-------------+------------------------------------------------------+

\+------------------+-----+-------------+-----------+-------------+------------------------------------------------------+

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

\+------------------+-----+------------+-----------+-------------+------------------------------------------------------+

\+------------------+-----+------------+-----------+-------------+------------------------------------------------------+

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

\+------------------+-----+------------+-----------+-------------+--------------------------------+

\+------------------+-----+------------+-----------+-------------+--------------------------------+

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

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

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

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

| mysql-bin.000015 |      107 |              |                  |

\+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

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

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

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

| mysql-bin.000015 |      107 |              |                  |

\+------------------+----------+--------------+------------------+

\[root@idc131 ~\]\# service mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL... SUCCESS!

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

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

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

mysql web日志_mysql日志管理

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL日志管理：忽略与理解的日志问题

相关 MySQL日志混乱：问题分析与日志管理策略

相关 MySQL数据丢失：日志管理问题

相关 MySQL 日志管理

相关 mysql web日志_mysql日志管理

相关 MySql Study之--MySql日志管理

相关 MySQL 错误1067（日志管理)

相关 MySQL日志管理

相关 Mysql日志管理

相关 Mysql运维之日志管理