mysql的日志内容_mysql查看日志文件内容及日志文件日常操

1.开启binary log功能

[root@mail mysql]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

symbolic-links=0

log-bin=mysql-bin

EXPIRE_LOGS_DAYS=3 //设置日志过期时间3天

mysql> show variables like 'log_bin';

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

| Variable_name | Value |

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

| log_bin | ON |

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

2.首先查看日志文件内容

方式1：

mysql> show binlog events in 'mysql-bin.000001'\G;

*************************** 1. row ***************************

Log_name: mysql-bin.000001

Pos: 4

Event_type: Format_desc

Server_id: 1

End_log_pos: 106

Info: Server ver: 5.1.73-log, Binlog ver: 4

*************************** 2. row ***************************

Log_name: mysql-bin.000001

Pos: 106

Event_type: Query

Server_id: 1

End_log_pos: 210

Info: use `test`; create table ran(id int,name varchar(20))

*************************** 3. row ***************************

Log_name: mysql-bin.000001

Pos: 210

Event_type: Query

Server_id: 1

End_log_pos: 309

Info: use `test`; insert into ran values (1,'ranyuan')

3 rows in set (0.00 sec)

ERROR:

No query specified

方式2：

[root@mail mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000001

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

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

DELIMITER /*!*/;

# at 4

#140916 6:28:17 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.73-log created 140916 6:28:17 at startup

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

ROLLBACK/*!*/;

BINLOG '

AWgXVA8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAABaBdUEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC

'/*!*/;

# at 106

#140916 6:30:11 server id 1 end_log_pos 210 Querythread_id=2exec_time=0error_code=0

use `test`/*!*/;

SET TIMESTAMP=1410820211/*!*/;

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

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

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

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

/*!\C latin1 *//*!*/;

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

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

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

create table ran(id int,name varchar(20))

/*!*/;

# at 210

#140916 6:32:03 server id 1 end_log_pos 309 Querythread_id=2exec_time=0error_code=0

SET TIMESTAMP=1410820323/*!*/;

insert into ran values (1,'ranyuan')

/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

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

3:bin_log的操作

a:查看最后一个bin日志文件是哪个以及,现在位置

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000002 | 106 | | |

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

1 row in set (0.00 sec)

mysql> show binary logs;

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

| Log_name | File_size |

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

| mysql-bin.000001 | 290 |

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

1 row in set (0.00 sec)

b:启用新的日志文件，一般备份完数据库后执行。

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000002 | 106 | | |

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

1 row in set (0.00 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000003 | 106 | | |

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

1 row in set (0.00 sec)

操作系统会自动生成新的日志文件当执行了 flush logs;

[root@mail mysql]# ll

total 20508

-rw-rw---- 1 mysql mysql 10485760 Sep 16 06:28 ibdata1

-rw-rw---- 1 mysql mysql 5242880 Sep 16 06:28 ib_logfile0

-rw-rw---- 1 mysql mysql 5242880 Jul 22 06:33 ib_logfile1

drwx------ 2 mysql mysql 4096 Jul 22 07:29 mail

drwx------ 2 mysql mysql 4096 Jul 22 06:33 mysql

-rw-rw---- 1 mysql mysql 149 Sep 16 07:29 mysql-bin.000001

-rw-rw---- 1 mysql mysql 149 Sep 16 07:31 mysql-bin.000002

-rw-rw---- 1 mysql mysql 106 Sep 16 07:31 mysql-bin.000003

-rw-rw---- 1 mysql mysql 57 Sep 16 07:31 mysql-bin.index

srwxrwxrwx 1 mysql mysql 0 Sep 16 06:28 mysql.sock

drwx------ 2 mysql mysql 4096 Sep 16 06:30 test

c:清空现有的所有bin-log

mysql> reset master;

Query OK, 0 rows affected (0.01 sec)

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 | 106 | | |

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

1 row in set (0.00 sec)

[root@mail mysql]# ll

total 20500

-rw-rw---- 1 mysql mysql 10485760 Sep 16 06:28 ibdata1

-rw-rw---- 1 mysql mysql 5242880 Sep 16 06:28 ib_logfile0

-rw-rw---- 1 mysql mysql 5242880 Jul 22 06:33 ib_logfile1

drwx------ 2 mysql mysql 4096 Jul 22 07:29 mail

drwx------ 2 mysql mysql 4096 Jul 22 06:33 mysql

-rw-rw---- 1 mysql mysql 106 Sep 16 07:33 mysql-bin.000001

-rw-rw---- 1 mysql mysql 19 Sep 16 07:33 mysql-bin.index

srwxrwxrwx 1 mysql mysql 0 Sep 16 06:28 mysql.sock

drwx------ 2 mysql mysql 4096 Sep 16 06:30 test