Optimal way to make MySQL backups for fairly large databases (MyISAM / InnoDB)
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 :).
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:
- Is there any equivalent of mysqlhotcopy for InnoDB?
- Backup MySQL Server
- How do you usually back up your database?
xtrabackup - at least for innodb.
The most common way to solve this issue is to set up another MySQL server, which can even be on the same machine, and run master/slave replication. You can then perform the backup on the slave, with zero impact on the master.