Currently we have one beefy MySQL database that runs a couple of high traffic Django based websites as well as some e-commerce websites of decent size. As a result we have a fair amount of large databases using both InnoDB and MyISAM tables.
Unfortunately we've recently hit a wall due to the amount of traffic so I've setup another master server to help alleviate reads / backups.
Now at the moment I simply use mysqldump with a few arguments and it's proven to be fine.. until now. Obviously mysqldump is a slow quick method however I believe we've outgrown its use. I now need a good alternative and have been looking into utilizing Maatkits mk-parallel-dump utility or an LVM snapshot solution.
Succinct short version:
- I have a fairly large MySQL databases I need to backup
- Current method using mysqldump is inefficient and slow (causing issues)
- Looking into something such as mk-parallel-dump or LVM snapshots
Any recommendations or ideas would be appreciated – since I have to re-do how we're doing things I rather have it done properly / most efficient :).
Best Answer
I've had good success with MySQL replication and nightly tarballs. For smaller db, the mysql database, and schema I use a combination of scripts designed to use mysqlhotcopy and mysqldump.
InnoDB hot backup is a great commercial product but I'm not sure how it handles mixed tables in the same database. pQd's recommendation for XtraBackup may be good to compare against this.
Others like LVM snapshots and I'd say that's definitely something to consider. Ultimately, a combination of solutions would probably be best.
It's also notable this is an old topic. Between the High Performance MySQL book, the MySQL manual, and previous ServerFault questions-- this has been exhausted on a general basis. See: