On my database server there is a cronjob that backups all the databases in a way that makes it easy to restore them.
It is something like this:
0 5 * * * /usr/local/bin/backup.php
The problem is that the website (using that db server) is very slow during that process. Even, Pingdom sends me a 'website down' alert at the start of the process.
To solve the problem, I have tried this change:
0 5 * * * /bin/nice -n 19 /usr/local/bin/backup.php
but it doesn't seem to improve the situation.
How is that possible? Probably the problem is the backup script locks the database. But if I don't lock the tables, the backup may not be consistent.
How would you solve the problem under these requirements? 1. no purchase of any hardware 2. easy to implement and maintain 3. no proprietary solutions
This is a very common problem and can b solved in a number of ways. One of the best and most reliable is using master/slave replication. In brief:
The advantage is of course that the master, which is serving you web site, doesn't miss a beat.