1. 编辑my.cnf中加入：
log-bin=mysql-bin

2.重启mysql
[root@mysqldb ~]# service mysql stop
Shutting down MySQL...[ OK ]
[root@mysqldb ~]# service mysql start
Starting MySQL.[ OK ]
[root@mysqldb ~]#

3.登录数据库执行dml操作
[root@mysqldb ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.23-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql>
mysql>
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> insert into emp values(4,'llj');
Query OK, 1 row affected (0.00 sec)

mysql> delete from emp;
Query OK, 33 rows affected (0.00 sec)

mysql> insert into emp values(1,'lsq');
Query OK, 1 row affected (0.01 sec)

mysql> select * from emp;
+------+-------+
| eno | ename |
+------+-------+
| 1 | lsq |
+------+-------+
1 row in set (0.00 sec)

4.查看二进制日志中的内容

[root@mysqldb mysql]# mysqlbinlog mysql-bin.000001>mysql-bin.000001.txt

[root@mysqldb mysql]# cat mysql-bin.000001.txt
/*!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
#150721 14:26:46 server id 1 end_log_pos 120 CRC32 0x1c794404 Start: binlog v 4, server v 5.6.23-enterprise-commercial-advanced-log created 150721 14:26:46 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
5o6uVQ8BAAAAdAAAAHgAAAABAAQANS42LjIzLWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAADmjq5VEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQRE
eRw=
'/*!*/;
# at 120
#150721 14:27:22 server id 1 end_log_pos 199 CRC32 0x02120a85 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1437503242/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 199
#150721 14:27:22 server id 1 end_log_pos 304 CRC32 0x8813bbc0 Query thread_id=1 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1437503242/*!*/;
insert into emp values(4,'llj')
/*!*/;
# at 304
#150721 14:27:22 server id 1 end_log_pos 335 CRC32 0x68d84e80 Xid = 11
COMMIT/*!*/;
# at 335
#150721 14:28:44 server id 1 end_log_pos 414 CRC32 0x6272d836 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1437503324/*!*/;
BEGIN
/*!*/;
# at 414
#150721 14:28:44 server id 1 end_log_pos 503 CRC32 0x6778715e Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1437503324/*!*/;
delete from emp
/*!*/;
# at 503
#150721 14:28:44 server id 1 end_log_pos 534 CRC32 0xfb7f6fee Xid = 22
COMMIT/*!*/;
# at 534
#150721 14:29:01 server id 1 end_log_pos 613 CRC32 0x7f7bf7c6 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1437503341/*!*/;
BEGIN
/*!*/;
# at 613
#150721 14:29:01 server id 1 end_log_pos 718 CRC32 0xd9c1f55e Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1437503341/*!*/;
insert into emp values(1,'lsq')
/*!*/;
# at 718
#150721 14:29:01 server id 1 end_log_pos 749 CRC32 0x44c0fc28 Xid = 24
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可见，类似于oracle的归档日志，dml操作已经被记录到日志中了。

ITPub博客

mysql的二进制日志

最新文章