MySQL - Consistent Backups With no Downtime

As popular as NoSQL databases are, MySQL is still by far the most common last time I checked. It is important to take regular backups, no matter what your setup is, and here is how you can take a backup of your database with no downtime.

Steps

    Set up a replication server and wait for it to be fully replicated. [tutorial coming soon].
    Run the following command:
    mysqldump -u [user] \
    -p[password] \
    -h [replication-server-host] \
    --lock-all-tables \
    [database-name] > [backup-filename.sql]

    I use the above command as it will always work/general-purpose and making your replication server read only is no big deal, there is no downtime on your actual main database!

    If you know that you are only running InnoDB tables and are not worried about 'alter table' queries coming in, resulting in missing tables, then you can run this command instead which will not read lock all your tables:

    mysqldump -u [user] \
    -p[password] \
    -h [replication-server-host] \
    --single-transaction \
    [database-name] > [backup-filename.sql]

    NOTE: that you can swap out [database-name] with --all-databases in either of the above commands if you want to dump all the databases, rather than dumping them by name.

No comments:

Post a Comment