Home / server / Questions / 139593
Accepted
WinkyWolly
Asked: 2010-05-08 06:05:06 +0800 CST

Optimal way to make MySQL backups for fairly large databases (MyISAM / InnoDB)

  • 772

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 :).

backup mysql database-backup

6 Answers

  1. 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:

    • 5

  2. xtrabackup - at least for innodb.

    • 4

  3. 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.

    • 3

  4. On EC2 EBS, I'm right now using xfs_freeze. I'm looking into possibly switching to xtrabackup at some point, but when I gave it a first test run it was very, very CPU hungry.

    • 0

  5. If you're running replication of a database that is shared across applications, it seems there is an obvious question of whether you can improve performance of many things, including backups, by dedicating database servers to apps. Shared is nice, until it's not.

    • 0

  6. If you're keeping your MyISAM tables for legacy reasons only (you haven't bothered altering them), here's what I use to easily fix that:

        mysql -u root --password=<password> --database=db_name -B -N -e "SHOW TABLES" | awk '!/not_this_db/ && !/or_this_one/ && /^[a-z]/ {print "ALTER TABLE", $1, "ENGINE=INNODB;"}' | mysql -u root --password=<password> --database=db_name

    You can exclude and include databases with the awk regex such as only dbs starting with a lowercase letter in my example above. This will of course lock the tables during the alter.

    Then use xtrabackup to copy the entire database straight to another server without locking any tables or using too much disk IO (after setting up ssh rsa keys):

    innobackupex --throttle=500 --compress --stream=xbstream /doesntneedtoexist | ssh user@otherhost "xbstream -x -C /root/backup/"

    and then you can do the apply log step completely separate and save disk space, IO, and CPU on the production server.

    Percona's HowTO's for using xtrabackup

    • 0