最後更新: 2024-02-23

目錄

• binlog
  
   - binlog info
   - Disable binlog by "skip-log-bin"
   - 設定 "skip-log-bin" 後清 binlog
   - FLUSH LOGS
   - binlog Settings
   - binlog 的 auto cleanup 時間
   - Manually PURGE BINARY LOGS
   - binlog 在 Master & Slave 狀態
• mysqlbinlog

 

---

binlog

 

binlog info

# 查看是否有啟動 binlog

mysql> show variables like 'log_bin';    -- Default: On      

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

mysql> SHOW BINARY LOGS;

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       819 | No        |
| binlog.000002 |       157 | No        |
+---------------+-----------+-----------+

Disable binlog by "skip-log-bin"

/etc/my.cnf

[mysqld]
skip-log-bin

service mysqld restart

設定 "skip-log-bin" 後清 binlog

查看有什麼 log

mysql> SHOW BINARY LOGS;

ERROR 1381 (HY000): You are not using binary logging

rm -f /var/lib/mysql/binlog.*

FLUSH LOGS

FLUSH LOGS

此 CMD 相當於

• FLUSH BINARY LOGS
• FLUSH ENGINE LOGS
• FLUSH ERROR LOGS
• FLUSH GENERAL LOGS
• FLUSH RELAY LOGS
• FLUSH SLOW LOGS

* Closes and reopens all log files (incremented by one relative to the previous file)

* By default, the server writes FLUSH statements to the binary log so that they replicate to replicas.
   To suppress logging, specify the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

binlog Settings

max_binlog_size = 10M (Default: 1G)                      # 注意, 此 Settings 不可在 binlog 建立後修改

當 binlog 大過此 size => the server rotates the binary logs

* If max_relay_log_size is 0, the value of max_binlog_size applies to relay logs as well. (Default is 0)

expire_logs_days = 7 (Default: 0)

The number of days for automatic binary log file removal.

 * MySQL 8 改用了 binlog_expire_logs_seconds (Default: 2592000 = 30 天 )

Checking

show variables like 'expire_logs_days';

show variables like 'max_binlog_size';              # 注意, 此 Settings 不可在 binlog 建立後修改

binlog 的 auto cleanup 時間

At startup OR Log flushing

 => 如果一定不 restart 及 flushing, 那 binlog 會愈來愈多 !!

 * If you are using replication, you should set the variable
  no lower than the maximum number of days your slaves might lag behind the master

 

Manually PURGE BINARY LOGS

[方式1]

# MySQL 8, 7 days

set global binlog_expire_logs_seconds = 604800;

# 根據 binlog_expire_logs_seconds 立即清 binlog

flush binary logs;

[方式1]

binlog.index                  # mysql 8.0

keeps a list of all binary logs mysqld has generated and auto-rotated.

=> 所以不可以人手 rm binlog 檔 !!

cat /var/lib/mysql/binlog.index

./binlog.000001
./binlog.000002

Examples:

# This will erase all binary logs before ‘mysql-bin.010, mysql-bin.010 會被保留

PURGE BINARY LOGS TO 'mysql-bin.010';

PURGE BINARY LOGS BEFORE '2022-12-31 23:59:59';

To safely purge binary log files, follow this procedure:

1) On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.

2) On Master, use 'SHOW SLAVE STATUS\G'

 

binlog 在 Master & Slave 狀態

Slave 啟用 binlog

1. If a Slave is also a Master
   (log-slave-updates must be enabled on the Slave)
2. You want to failover to the Slave to become a new Master
   (You do not have to restart mysql to enable it later)

 

---

mysqlbinlog

 

mysqlbinlog Usage:

mysqlbinlog [options] log_file ...

當有幾個 binlog 時

shell> mysqlbinlog binlog.000001 >  /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql

restore:

shell> mysql -u root -p -e "source /tmp/statements.sql"

拿某 DB 更新過的 Statement

mysqlbinlog --database=my_db binlog_files > tmpfile.sql

* occur while db_name is been selected as the default database by USE.

拿某時間的 Statement

shell> mysqlbinlog --stop-datetime="2005-04-20 9:59:59" \
         /var/log/mysql/bin.123456 >> restore.sql