Hello All, A few days ago there was a thread about how to perform backups on large scale enterprise systems of mysql databases. This is an issue that I have a problem with and came up with a possible cheap solution. Problem:
Currently I have about 40 Mysql Servers with about 25 of these servers are configured as a slave / slave-master. I transfer more then 50 GB of mysql data a day to handle 195 Mb of traffic for my company's websites. How do you backup nearly 200 GB of data across 40 different servers? The 1st approach was to hotbackup the slaves. But a slave lag of more then 300-600MB resulted which produced choppy data among page views. So, users noticed a slave lag from an application point of view. Also a problem with these hotbackups the read lock on the table was lost to due to the feature wait-timeout. This was solved by forking a process to do a copy and cycling a mysql ping on the parent process as it waits for the child PID to finish. Still a 300-600 MB lag was created but consistent data was backed up. The 2nd approach was to buy a server for every master to build a real-time backup based off of the replication feature. We needed a replication server for every master since mysql replication can have only one master. There is nothing worse then spending 5K x 20 for a write-only db. Additionally to the 5 K up-front costs: issues such as maintenance + depreciation costs of hardware not to mention cage space also has to be accounted for. The 3rd approach was to write software with some pointers from a book called MySQL "The definitive guide to using, programming, administering MySQL databases 2nd edition" that I picked up at the last convention. I created a backup server. This server runs many instances of mysqld 3.53.27 on RedHat 7.3 Boxes. For my 1st environment I am able to run 4 instances of mysqld processes and slave effectively for a real time backup of 4 of my heaviest write traffic masters. The software manages starting / stopping, monitoring, dynamic repair / recovery and graphing of the slave lag. The current environment runs on a crappy x4 p3 with ICP-RAID5 of 3 76GB drives. I get around 200GB of space for backups. This feature is not processor bound but IO bound thus the motivating move towards a p3. If I had a few more disks in the array then I can probably add a few more masters to this one box. In conclusion this one stop location for backups has saved my company around $110K. I'd like to give a presentation at the next mySQL convention as to how this is accomplished and what is a generic ratio of backup servers to masters. Jeremy do you think you can aid me in getting this done? The current ratio with the hardware I'm running is about 4 masters to 1 backup server.
