Backup and Recovery - High Performance MySQL - page 636

• The exec_time , which is the difference between the statement's timestamp and the
time at which it was written to the binary log. It's a good idea not to rely on this
value, because it can be very wrong on replicas that have fallen behind in
replication.
• Any error code the event raised on the originating server. If the event causes a
different error when replayed on a replica, then replication will fail as a safety
precaution.
Any further lines contain the data needed to replay the modification. User-defined
variables and any other special settings, such as the timestamp in effect when the state-
ment executed, also appear here.
If you're using the row-based logging available in MySQL 5.1, the event
won't be SQL. Instead, it's a non-human-readable “image” of the mod-
ifications the statement made to the table.
Purging Old Binary Logs Safely
You'll need to decide on a log expiration policy to keep MySQL from filling your disk
with binary logs. How large your logs grow depends on your workload and the logging
format (row-based logging results in larger log entries). We suggest you keep logs as
long as they're useful, if possible. Keeping them is helpful for setting up replicas, ana-
lyzing your server's workload, auditing, and point-in-time recovery from your last full
backup. Consider all of these needs when you decide how long you want to keep your
logs.
A common setup is to use the expire_logs_days variable to tell MySQL to purge logs
after a while. This variable wasn't available until MySQL 4.1; prior to this version, you
had to purge binary logs manually. Thus, you might see advice to remove old binary
logs with a cron entry such as the following:
0 0 * * * /usr/bin/find /var/log/mysql -mtime + N -name "mysql-bin.[0-9]*" | xargs rm
Although this was the only way to purge the logs prior to MySQL 4.1, don't do this in
newer server versions! Removing the logs with rm can cause the mysql-bin.index status
file to become out of sync with the files on disk, and some statements, such as SHOW
MASTER LOGS , can begin failing silently. Changing the mysql-bin.index file by hand might
not fix the problem, either. Instead, use a cron command such as the following:
0 0 * * * /usr/bin/mysql -e "PURGE MASTER LOGS BEFORE CURRENT_DATE - INTERVAL N DAY"
The expire_logs_days setting takes effect upon server startup or when MySQL rotates
the binary log, so if your binary log never fills up and rotates, the server will not purge
older entries. It decides which files to purge by looking at their modification times, not
their contents.