Aug 20, 2011
tom

How do I backup a mysql database, but at low priority?

Question

I want to backup a database, but during the day when there is load on the server. It’s vital that the backup doesn’t impact apache and other databases running on the same server.

It should be possible to use the mysqldump command, but run the command at low priority.

How can I do this?

Update:
Looks like simply using nice with mysqldump doesn’t work since mysqldump spawns a new process.

Answer

If you have a spare server around that can cope with the write load of your server, you can set up replication to that server, and then backup from the slave server. This also has the advantage that you can stop replication while you do the backup and get a consistent snapshot of your data across all databases, or all tables in one database without impacting the database server. This is the set up I always recommend for backing up MySQL if you have the resources.

As a nice bonus, you now have a read-only slave you can use for slow long-running queries.

Related posts:

  1. How to restore just ONE mysql database from a collective backup
  2. Backup MySQL database and gzip
  3. Does anybody know where I can find a good “how to” on restoring a MySQL database using Backup Exec?
  4. How to properly backup mediawiki database (mysql) without messing up the data?
  5. How to create a SQL dump from the MySQL database files

Leave a comment