0

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

Daniele
  • 661
  • 1
  • 7
  • 10
  • 4
    Would be a lot easier to answer this question if we actually knew what /usr/local/bin/backup.php did? What kind of databases do you have? – andol Jul 13 '10 at 08:52

1 Answers1

2

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:

  • You run a second instance of the database (must be on another port). That is normally kept in sync with the master.
  • When it's time to backup you lock the tables on the slave and back it up.
  • Once the backup is complete, release the locks and the slave will catch up with the master.

The advantage is of course that the master, which is serving you web site, doesn't miss a beat.

John Gardeniers
  • 27,458
  • 12
  • 55
  • 109