Binary Log主要是儲存SQL語法的DML和DDL,INSERT INTO,UPDATE,DELETE,REPLACE,CREATE,ALTER和DROP等。 Binary Log 主要應用在

• Replication-資料庫複寫
• Data restore資料庫恢復(根據時間點恢復)

預設 Mysql服務是每有啟動Binlog，需要到/etc/my.cnf(針對RPM安裝的)

#vim /etc/my.cnf

1

[mysqld]
~略~
binlog-do-db=dbName #指定哪一個資料庫,此參數可略,預設為全部資料庫
log-bin = /var/lib/mysql/mysql-bin.*  #指定bin-log檔案名稱或是用預設的base_name(此值就是正在運作服務器主機名稱)及路徑
expire-logs-days=5                    #指定bin-log要保留幾天。預設是0,表Binary log不會被移除
max_binlog_size=104857600             #指定單一bin-log檔案大小
[mysqld_safe]
~略~

$mysqladmin --defaults-file=/etc/my.cnf -uroot -p reload

#mysqlbinlog mysql-binName

#mysqlbinlog --start-date="YYYY-MM-DD hh:mm:ss" --stop-date="YYYY-MM-DD hh:mm:ss" mysql-binName >mysql-binName.sql

mysql> show binary logs;

或是列出服務器正在讀寫哪一個Binary Log及哪一個byte offset(Position)

mysql>show master status;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000012 |      106 |              |                  |
+------------------+----------+--------------+------------------+

另外,利用系統工具stat可以看到binary log 文件大小,也就是byte offset(Position)

#stat /var/lib/mysql/mysql-bin.000012

File: ‘/var/lib/mysql/mysql-bin.000012’
  Size: 106       	Blocks: 8          IO Block: 4096   普通檔案
Device: fd00h/64768d	Inode: 158758      Links: 1
Access: (0660/-rw-rw----)  Uid: (  498/   mysql)   Gid: (  498/   mysql)~ 略

刪除全部binlog

mysql>RESET MASTER;

指定刪除哪一個binlog之前(不包含自己本身)

mysql> purge binary logs to 'mysql-bin.000003';

以上的手動刪除,是利用MySQL 提供的SQL語法來刪除Binary Log。若是用system 工具刪除 rm -f /var/lib/mysql/mysql-bin.*是不 建議的做法,因為會引發一些不必要的warning 或是Error 等訊息

若是用system 工具刪除 例如

mysql> show binary logs;

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       149 |
| mysql-bin.000002 |       149 |
| mysql-bin.000003 |       149 |
| mysql-bin.000004 |       149 |
| mysql-bin.000005 |       106 |
+------------------+-----------+

利用system工具

#rm -rf /var/lib/mysql/mysql-bin.000001

再用mysql工具觀察

mysql>show binary logs;

------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |         0 |
| mysql-bin.000002 |       149 |
| mysql-bin.000003 |       149 |
| mysql-bin.000004 |       149 |
| mysql-bin.000005 |       106 |
+------------------+-----------+

若再用mysql的purge

mysql> purge binary logs to 'mysql-bin.000005'; 
Query OK, 0 rows affected, 1 warning (0.03 sec)

看warning 訊息

mysql> show warnings;

+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1612 | Being purged log ./mysql-bin.000001 was not found |
+---------+------+---------------------------------------------------+

1. MySQL 使用 binary log 回覆 某段時間區間 的資料
2. 網庫小程
3. 如何管理 MySQL 的 binlog
4. mysql bin-log日誌清理