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)

總結

以上是生活随笔為你收集整理的mysql web日志_mysql日志管理的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。