Webdock Snapshots & the Binary Log

As you may already know, Webdock offers snapshots that can be restored in case you need to fix something  which has gone wrong or to roll back to the previous state of your server.

The snapshots we take include everything on disk in your server, including the database and any other data on your server - as a whole. This means your server is entirely backed up and you can restore using any snapshot at any point in time.

However, the way we take snapshots may sometimes cause database corruption when the data in RAM is not yet flushed to disk. We instruct the Linux system in your server to flush all caches to disk before taking the snapshot, but this does not trigger MariaDB/MySQL to flush any transactions which have not been commited to disk yet. This means certain workloads don't allow this syncing to the disk and can thereby cause database corruption as your database on disk is not fully synched with the latest transactions in RAM. In order to prevent this becoming a problem we enabled binary logging on our LEMP/LAMP stacks which will automatically recover the database once your VPS starts in case this corruption has taken place.

The rest of the article details how to enable binary logging and periodically back up your database using a cronjob.

Checking MariaDB version

MariaDB recently changed the way they manage default configuration. The changes were seen from version v10.6

Execute the below command to know which version of MariaDB you're on.

$ mariadb --version

You'll see similar output:

mariadb  Ver 15.1 Distrib 10.9.3-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

As you can from the above output, my MariaDB version is 10.9.3. Yours may differ.

Enabling binary logs

Open the configuration file depending on your MariaDB version.

For MariaDB version >10.6

$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

For MariaDB version < 10.6

$ sudo nano /etc/mysql/my.cnf

For MySQL 8:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

The directives for MySQL 8 will be similar, and once you see them those will be self-explanatory.

Look for the following lines in the configuration file.

#log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
#max_binlog_size        = 100M

As you see, the log_bin directive is commented out. To enable binary logging, uncomment the log_bin directive. If you want to increase max_binlog_size, you can uncomment that line as well and change the "100M" to "250M", for example.

So after making the changes the text should look like this:

log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size        = 250M

In order for these changes to take effect you need to restart the database - which means your apps/websites will fail to connect to the database. So restart the database at your own convenience.

To restart the database simply execute the following command.

$ sudo systemctl restart mariadb

Taking periodic backups

Using a cronjob you can take periodic backups of your database.

To make things easy and to avoid entering any credentials in your cron job we'll be create a .my.conf file in your home directory and create a cronjob to dump the database into an exportable SQL file.

In your home directory (on our stacks it will be /home/admin) create a .my.cnf file with your database credentials.

$ cd ~
$ nano .my.cnf

Copy the following content to the file, replace dbusername and dbpassword with the credentials of your database, and save the file. Make sure the dbusername has access to the database you want to backup.

[client]
user = "dbusername"
password = "dbpassword"
host = localhost

Once done, run the following command to take a backup of your database.

$ mysqldump dbname > "/home/youruser/database-backup-$(date +"%m_%d_%Y").sql"

Replace "dbname" with the name of the database you want to back up. Here we dump the files to a home directory for the user named "youruser" but feel free to replace the path with whatever location makes the most sense for you on your server - e.g. you could create /var/www/dbbackups if you want to be able to access the backups via. the default Webdock FTP user, as an example.

As you may have noticed the filename for the backup above is created dynamically so you get a single backup per day. If you want old backup files to be removed, you need to create a script for this or delete them periodically by hand. If you can live with just having the latest backup and don't care about backups going back x days, you can simplify the cronjob line to:

$ mysqldump dbname > "/home/youruser/database-backup-latest.sql"

Now in the Webdock dashboard, you can add a cronjob to run the backup periodically. Head over to "Cron Jobs" and add the mysqldump command to crontab as shown in the image below and click "Add to Crontab" to add the cronjob.

The above cronjob runs at 2 AM every day as the user 'admin" and will be stored in my home folder (/home/admin). You can change the fields appropriately. Change "dbname" to the name of the database you want to backup.

Conclusion

This guide outlined how to enable binary logging and provided instructions on how to periodically take database backups.

Contact Webdock Support if you encounter any issues.