Skip to main content

MySQL Backup and Restore

Backups​

Nine creates backups of your servers MySQL databases every day between 01:00 and 02:00. These backups are stored locally for 10 days, and are also backed up to our backup servers daily.

You can access these local backups as the user www-data, to either archive them at a place of your choice, use them on your local machine, or restore them on the system itself.

The backups are stored in the directory /home/database-backup/mysql/ and revisioned in directories with the following timestamp schema: 2021-12-06-0134

The symbolic link /home/database-backup/mysql/latest/ always points to the newest backups.

You'll find the backups of all your databases in the sub-directory customer. If you're looking for the database structure only, find them in the sub-directory structure.

Creating additional backups​

To allow you an easy method to create backups with the same parameters as we do, we're allowing access to our backup script with sudo for the user www-data:

www-data@nine01:~ $ sudo nine-mysql-backup
2021-12-06T09:54:19+01:00 Dumped and compressed database 'nmd_frontend_production' in 53 seconds
2021-12-06T09:55:04+01:00 Dumped and compressed database 'nmd_frontend_staging' in 45 seconds

Restore​

Within each daily revision you'll find the file restore.sh, for example at /home/database-backup/mysql/2021-12-06-0134/restore.sh.

All backed up databases are listed here including the full path to the files. You can copy these and modify them to suit your needs. The commented lines are meant for a restoration into the source database.

...
# Single database restores
# zstdcat /home/database-backup/mysql/2021-12-06-0134/customer/nmd_frontend_production/*.zst | mysql "nmd_frontend_production"
# zstdcat /home/database-backup/mysql/2021-12-06-0134/customer/nmd_frontend_staging/*.zst | mysql "nmd_frontend_staging"

The mysql commands need to be expanded with the password and username option, f.e.: mysql -p -u nmd_frontend_production mysql -p -u nmd_frontend_staging

Entries above the line # Single database restores are meant for use by Nine employees and can't be executed by unprivileged users.

Restoration into the source database​

To restore a backup, you can use the following command. Using this command will restore all tables for the database nmd_frontend_staging into the same, already existing database:

zstdcat /home/database-backup/mysql/2021-12-06-0134/customer/nmd_frontend_staging/*.zst | mysql -p -u nmd_frontend_staging "nmd_frontend_staging"

The parameter -u defines the username for the mysql command, -p the password. You can also provide the password directly to perform a restore without being asked for the password (mysql -pSecurePass -u nmd_frontend_staging "nmd_frontend_staging").

PLease mind that you need to provide the username and password for the database, not your www-data credentials. You will find the correct username and password within your application configuration if you don't have them at hand.

Restoration into another database​

If you need or want to restore the backup into another database, you need to change the name of the target database. We recommend that you restore the structure of the database first:

zstdcat /home/database-backup/mysql/2021-12-06-0134/structure/nmd_frontend_staging.zst | mysql -p -u nmd_frontend_staging_restore "nmd_frontend_staging_restore"

zstdcat /home/database-backup/mysql/2021-12-06-0134/customer/nmd_frontend_staging/*.zst | mysql -p -u nmd_frontend_staging_restore "nmd_frontend_staging_restore"

If you do want to restore a backup into another database, this database must exist beforehand or be created with nine-manage-databases .

Restoration of single tables​

Instead of restoring all tables from the backup (using the "*"), you can restore a single table instead:

zstdcat /home/database-backup/mysql/2021-12-06-0134/customer/nmd_frontend_staging/entities.zst | mysql -p -u nmd_frontend_staging_restore "nmd_frontend_staging_restore"

Additional information​

Backup time​

Creating a database backup usually causes a bit higher load on your system. We've chosen a time during the night that is generally less busy. We're happy to find a better time suiting your needs if the time between 01:00 and 02:00 isn't fitting your applications well.

Compression of backups​

We're using the Zstandard algorithm to compress the backups. This algorithm offers a great balance between speed, compression rate and resource usage and is superior to the common algorithms you might know or use.

If you need the backup for usage on another systems where you don't have access to zst binaries, we recommend to decompress the existing dumps on the system and re-compress them afterwards.

www-data@nine01:~ $ mkdir dump_recompress
www-data@nine01:~ $ cp /home/database-backup/mysql/latest/customer/nmd_frontend_staging/* dump_recompress/ ; cd dump_recompress/

# The option "--rm" will delete the zst archives after decompressing them
www-data@nine01:~/dump_recompress $ unzstd --rm *.zst
nmd_frontend_staging.zst: 3732915490 bytes
www-data@nine01:~/dump_recompress $ bzip2 *
www-data@nine01:~/dump_recompress $ ls -l *.bz2
-rw-r----- 1 www-data www-data 432683536 Dec  6 13:24 nmd_frontend_staging.bz2

Please be cautious to not place database dumps publicly available within a public directory served by the webserver.

Type of backups​

By default, we're creating backups from each table within your databases. This comes with some advantages in the day to day handling of these backups, as single tables are usually smaller and allow for a single table restore.

If you instead wish for a full backup of all tables into a single output file, we can change the behavior of the script to do this. The advantages in the handling will go, however, the dump itself will be more consistent as a whole.

Based on our long time positive experience with dumping single tables, we only recommend to change this for edge cases or for explicit need.