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 	Query	thread_id=2	exec_time=0	error_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 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1410820323/*!*/;
insert into ran values (1,'ranyuan')
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET <a target=_blank href="mailto:COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/">COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/</a>;

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