This question concerns best practices for mysql backup with replication and large number of databases. I'm looking for your point of view and suggestions :)
I - Current situation
I have two databases master/slave (mysql replication) on each one of my app servers. I have one database by customer and one "admin" database.
Currently I get a total of 500 databases and soon it will exceed 1K.
Each databases includes around 100 tables and for the moment and I arrive to around 10mo to 30mo by database after mysqldump operation (uncompressed).
II - What I need
I'm now looking to backup these databases. The main goal is for sure to answers to server crash and to build a disaster recovery process, but before all that I'm more worried about my customers that messed up themselves their data. To sum up I want to be able to:
- Backup and recover all databases in case of servers crash or attack. (And then to perform a disaster recovery).
- Backup all databases and recover specific database to restore a fresh copy (Let say not older than 24 hours) when a customer mess up with his account's data.
III - My current ideas and my questions
1 - Backup location
The backup of the database should not be done on the same server than the database itself. As I'm using replication on two different servers, do you think it's a good practice to backup my data on the other server? Or what would be the other solutions as a dedicated server and why?
2 - Backup process
First, I will backup from the slave database following the principle of stopping temporary the replication and making a backup in "read only".
I think that I have here to consider two different types of backup. The first backup will be a backup of raw data files that I will perform let say every week (as I'm using replication I'm quite safe). And the second type of backup would be a mysqldump of each databases that I will perform every 24 hours. Is it something that seems logical?
Is there any others points that I should consider? Is incremental backup something interesting for me?
Thanks a lot!