mysql binary log 操作

来源：互联网发布：淘宝企业店铺怎么申请编辑：程序博客网时间：2024/05/07 14:34

1、查看log：
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 29653 |
| mysql-bin.000002 | 1035665 |
| mysql-bin.000014 | 107 |
+------------------+-----------+

2、查看当前的log：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000014 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

3、切换binary log：
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

4、清除所有binary log：
mysql> reset master;
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 0 |
+------------------+-----------+

5、查看log里的events：（bin-log因为是二进制文件，不能通过记事本等编辑器直接打开查看，mysql提供两种方式查看方式）
方法一：
1）查看当前日志中的events
mysql> show binlog events in "mysql-bin.000013";
+------------------+-----+-------------+-----------+-------------+------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------+
| mysql-bin.000013 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.25a-log, Binlog ver: 4 |
| mysql-bin.000013 | 107 | Query | 1 | 204 | use `test`; create table tb1(name varchar(50)) |
| mysql-bin.000013 | 204 | Query | 1 | 272 | BEGIN |
| mysql-bin.000013 | 272 | Table_map | 1 | 316 | table_id: 33 (test.tb1) |
| mysql-bin.000013 | 316 | Write_rows | 1 | 350 | table_id: 33 flags: STMT_END_F |
| mysql-bin.000013 | 350 | Xid | 1 | 377 | COMMIT /* xid=22 */ |
| mysql-bin.000013 | 377 | Rotate | 1 | 420 | mysql-bin.000014;pos=4 |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------+
7 rows in set (0.00 sec)

2）从某一点上来看：
mysql> show binlog events in "mysql-bin.000013" from 204;
+------------------+-----+------------+-----------+-------------+--------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+------------+-----------+-------------+--------------------------------+
| mysql-bin.000013 | 204 | Query | 1 | 272 | BEGIN |
| mysql-bin.000013 | 272 | Table_map | 1 | 316 | table_id: 33 (test.tb1) |
| mysql-bin.000013 | 316 | Write_rows | 1 | 350 | table_id: 33 flags: STMT_END_F |
| mysql-bin.000013 | 350 | Xid | 1 | 377 | COMMIT /* xid=22 */ |
| mysql-bin.000013 | 377 | Rotate | 1 | 420 | mysql-bin.000014;pos=4 |
+------------------+-----+------------+-----------+-------------+--------------------------------+
5 rows in set (0.00 sec)

3）查看所有的：
mysql> show binlog events;

注：为了排序美观，可以在结尾加\G使结果横变纵，此时结尾无需加；语句结束符。
eg:
mysql> show binlog events in 'mysql_bin.000001'\G
...............省略...............
*************************** 3. row ***************************
Log_name: mysql_bin.000001
Pos: 174
Event_type: Intvar
Server_id: 1
End_log_pos: 202
Info: INSERT_ID=2
*************************** 4. row ***************************
Log_name: mysql_bin.000001
Pos: 202
Event_type: Query
Server_id: 1
End_log_pos: 304
Info: use `test`; insert into bin(name) values ('orange')
*************************** 5. row ***************************
...............省略...............
Log_name:此条log存在那个文件中，从上面可以看出这2条log皆存在与mysql_bin.000001文件中。
Pos:log在bin-log中的开始位置
Event_type:log的类型信息
Server_id:可以查看配置中的server_id,表示log是那个服务器产生
End_log_pos：log在bin-log中的结束位置
Info:log的一些备注信息，可以直观的看出进行了什么操作

方法二：用mysql自带的工具mysqlbinlog,这是我们就需要知道bin-log存在硬盘的什么位置。可以在mysql控制台上使用：show variables like '%dir%'; 来查看文件位置：
C:\ProgramData\MySQL\MySQL Server 5.1\data>mysqlbinlog mysql_bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#140215 16:35:56 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.51-community-log created 140215 16:35:56 at startup
ROLLBACK/*!*/;
BINLOG '
7Mp7UA8BAAAAZgAAAGoAAAAAAAQANS4xLjUxLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADsyntQEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#140215 16:36:51 server id 1 end_log_pos 174 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1350290211/*!*/;
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=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 174
#140215 16:36:51 server id 1 end_log_pos 202 Intvar
SET INSERT_ID=3/*!*/;
# at 202
#140215 16:36:51 server id 1 end_log_pos 309 Query thread_id=2 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1350290211/*!*/;
insert into bin(name) values('xishizhaohua')
/*!*/;
# at 309
#140215 16:36:51 server id 1 end_log_pos 336 Xid = 28
COMMIT/*!*/;
# at 336
#140215 16:37:25 server id 1 end_log_pos 379 Rotate to mysql_bin.000002 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
可以使用重定向命令输出到文件。

0 0