MySQL Replication Logs


On the MySQL Manual page they have several pieces of helpful code for dealing with replication logs.

___
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY);

So you could set up a weekly cron job like:

0 9 * * mon mysql -uroot -e “PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY);”

___

Heres a good way to purge the logs if you only have one slave (run this on the slave):

mysql -uroot -hMaster_HostName -pMasterPassword -e “PURGE MASTER LOGS TO ‘`mysql -uroot -pSlavePassword -e ‘show slave status\G’|awk ‘$1 == “Master_Log_File:” {print $2}’`’;”

What it does is it checks to see which binary log the slave is currently reading and then connects to the master and purges all binary logs up to, but not including, that log.

___

Another variant of purging log files(run this on slave):
mysql -pMASTER_PWD -e “PURGE MASTER LOGS TO ‘`mysql -h SLAVE_HOST -pSLAVE_PWD -e ‘SHOW SLAVE STATUS\G’|grep -m 1 Master_Log_File|sed “s/^.*: //g”`'”

___

not in the comments, but still helpful

You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days (see Section 5.1.3, “System Variables”). 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s