MySQL - Making Snapshots and Loading Snapshots

Just a quick note on how to make database snapshots with MySQL.

Create a compressed snapshot:

$ mysqldump --single-transaction -udbuser -pdbpass somedb | bzip2 > somedb.sql.bz2

• The --single-transaction option can be left out if you are not using InnoDB.
• In newer versions of MySQL/Moriadb, --opt is the default, so there's no need to specify it.

Load a compressed snapshot:

$ bunzip2 -c somedb.sql.bz2 | mysql -u dbuser -pdbpass somedb 

These commands are usually best done as a background job, as they can take some time to complete. Also, they may cause long delays for any applications using the database, so it's a good idea to shut the application servers down before creating a snapshot.