MySQL数据目录下master.000xxx文件的用途及具体身份咨询

Hey there! Let's dive into those master.000xxx files cluttering up your /data directory—they're not random junk, that's for sure.

These are MySQL binary log (binlog) files. Depending on your MySQL version or configuration, you might also see them named mysql-bin.000xxx, but the master prefix is just an older or custom naming convention for the same core tool.

Binary logs don’t store raw database data—instead, they record every single write operation (like INSERT, UPDATE, DELETE) that modifies your database. Think of them as a step-by-step log of all changes made to your data.

Here’s what they’re critical for:

• Point-in-time recovery: If your database crashes, or you accidentally drop a table, you can replay these logs to restore your data to a specific moment before the issue occurred.
• Master-slave replication: In a master-slave setup, slave servers pull these binlogs from the master to replicate all write operations, keeping your secondary databases in sync with the primary.
• Auditing: You can parse these logs to track who made what changes to your database, which is a huge help for debugging or compliance checks.

MySQL doesn’t reuse a single binlog file indefinitely—it creates new ones automatically in a few common scenarios:

• When the current binlog hits the size limit set by the max_binlog_size parameter (default is 1GB). Once it reaches that threshold, MySQL rolls over to a new file with an incremented suffix (e.g., master.000001 → master.000002).
• Every time you restart the MySQL service, it spins up a fresh binlog file.
• If you run the FLUSH LOGS command manually, it also triggers a new binlog file.

If your database has heavy write activity (like frequent inserts/updates) or you restart MySQL often, these files can pile up quickly.

Before you start deleting files manually (don’t do that—you could break replication or ruin recovery options!), try these safe methods:

• Auto-expire old logs: Add the expire_logs_days parameter to your MySQL config file (my.cnf or my.ini). For example, expire_logs_days = 7 will automatically delete binlogs older than 7 days. Just make sure your slave servers have enough time to sync before logs get wiped.
• Manually purge old logs: Use the PURGE BINARY LOGS command to remove logs up to a specific date or file. Examples:

  -- Remove all logs before May 1, 2024
  PURGE BINARY LOGS BEFORE '2024-05-01 00:00:00';
  

  -- Remove all logs before master.000050
  PURGE BINARY LOGS TO 'master.000050';
  

  Always double-check that these logs aren’t needed for replication or recovery first!
• Check current binlog status: Run SHOW BINARY LOGS; in MySQL to see all existing binlog files, their sizes, and which one is currently active.

Just remember: Binary logs are critical for data safety and replication, so don’t delete them unless you’re 100% sure you won’t need them anymore.

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