mysql的二進位制日誌

lsq_008發表於2015-07-24
MySql
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部落格 ” ，連結：http://blog.itpub.net/10972173/viewspace-1749233/，如需轉載，請註明出處，否則將追究法律責任。

相關文章