Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Currently I have a Master and Slave.

Both of them have same set of data(Can Say are in Sync).The Replication is running fine.

What i need to accomplish is that...

Need to Archive the Master(Say I need Quarter(Only Last 3 months) of data in Master) but need the Full data on Slave.

How can we do it with having minimal downtime.what strategy should we follow?

share|improve this question
add comment

1 Answer

If you are sure the Slave is sync'd with the Master, you could do the following on the Slave:

  • Activate Binary Logging
  • Perform a full mysqldump every three months
  • Run FLUSH LOGS; every midnight
  • After the FLUSH LOGS;, make a physical copy of new binary logs, except the last one
  • OPTIONAL Set expire_logs_days=180 in my.cnf and run SET GLOBAL expire_logs_days=180; (I used 180 because this give you 90 days to have access to changes from the previous two quarters. If you produce more than one binary log per day, make sure you have enough diskspace to hold 180G of binary logs or just reduce the number from 180. The idea is to have the changes to the Master. They are stored already in the Master's Binary Logs. Those same changes can be on the Slave as well if you activate Binary Logging on the Slave. The Master never needs to feel anything throughout this process.

You could use pt-table-checksum and pt-table-sync to sync up the Slave from the Master. (I wrote a shell script to automate them using mk-table-checksum and mk-table-sync)

Doing these things will allow you to reload a snapshot of the beginning of a Quarter onto another server. Replay all logs on the Slave (using mysqlbinlog) to any datetime from those logs to view different days within the next Quarter.

If all your data is InnoDB, here is an Alternative from the Master

  • Binary Logging on the Master should already be enabled due to Replication
  • Perform a full mysqldump (using --single-transaction) every three months
  • Run FLUSH LOGS; every midnight
  • After the FLUSH LOGS;, make a physical copy of new binary logs, except the last one
  • OPTIONAL Set expire_logs_days=180 in my.cnf and run SET GLOBAL expire_logs_days=180; (I used 180 because this give you 90 days to have access to changes from the previous two quarters. If you produce more than one binary log per day, make sure you have enough diskspace to hold 180G of binary logs or just reduce the number from 180.

These are just some ideas you can try to organize using the Slave or the Master.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.