MySQL二进制日志15分钟自动切换配置及日志大小调整咨询

Hey there! As someone new to MySQL binary logs, let's break down your questions clearly and step by step.

1. Configure 15-Minute Automatic Binary Log Rotation #

To ensure binary logs switch every 15 minutes (regardless of their current size), you'll use the binlog_max_flush_time parameter. This sets the maximum time (in seconds) MySQL waits before rotating to a new binlog file.

How to set it: #

• Permanent setup (survives restarts):
  Edit your MySQL configuration file (usually my.cnf on Linux, my.ini on Windows) and add this line under the [mysqld] section:

  binlog_max_flush_time = 900  # 900 seconds = 15 minutes
  

  Save the file and restart MySQL for changes to take effect.

• Temporary setup (resets on restart):
  Run this SQL command in your MySQL client (like the mysql shell):

  SET GLOBAL binlog_max_flush_time = 900;
  

Note: If a binlog reaches its size limit (we'll cover that next) before the 15-minute mark, MySQL will rotate it immediately—this parameter just enforces a maximum interval between rotations.

2. Adjust Default Binary Log Size (from 1GB to 50MB/100MB) #

The parameter controlling binlog size is max_binlog_size. By default, it's set to 1GB (1073741824 bytes). Here's how to change it:

For 50MB: #

• Permanent setup:
  Add this to your [mysqld] config section:

  max_binlog_size = 50M
  

• Temporary setup:

  SET GLOBAL max_binlog_size = 52428800;  # 50 * 1024 * 1024 = 52428800 bytes
  

For 100MB: #

• Permanent setup:

  max_binlog_size = 100M
  

• Temporary setup:

  SET GLOBAL max_binlog_size = 104857600;  # 100 * 1024 * 1024 = 104857600 bytes
  

Important: MySQL won't split a single transaction across multiple binlogs. If you have a transaction larger than your max_binlog_size setting, the binlog will grow beyond the specified size to accommodate the entire transaction.

3. Impacts of These Adjustments #

Before making these changes, it's good to understand the trade-offs:

• More binlog files: Smaller size limits and frequent rotation will result in more individual binlog files on your disk. This isn't a problem if you have auto-cleanup enabled (use expire_logs_days or binlog_expire_logs_seconds to auto-delete old logs), but you'll need to monitor disk usage to avoid running out of space.
• Minor IO overhead: Rotating logs more often adds small IO operations (closing the old file, creating a new one). For most systems, this is negligible, but high-traffic environments might see a tiny increase in IO load.
• Easier backups/restores: Smaller binlog files are faster to transfer during backups, and if you need to restore from a specific point in time, it's easier to locate the relevant log file(s) instead of sifting through a single 1GB file.
• Oversized logs for large transactions: As mentioned earlier, big transactions will still create binlogs larger than your set limit, so don't rely solely on max_binlog_size to cap file sizes if you handle large transactions.

内容的提问来源于stack exchange，提问作者Gagandeep Singh