Skip to content

Backing up MySQL Database on Linux with Rclone and B2⚓

Summary⚓

MySQL and MariaDB contain a utility called mysqldump which can be used for creating backup of a database or systems of databases. This utility creates a logical backup, which allows for granule recovery scenarios. Full backups are recommended for full disaster recovery scenarios.

NOTE: I have not yet got this working 100%. The syntax appears to work, but because the database is remote and not hosted locally, it doesn't seem to be making the connection to the remote database. Further testing and tweaking will be necessary.

How the database backup functions⚓

As an example, here’s a command for manually backing up all of the databases in the system.

mysqldump --all-databases --single-transaction --lock-tables=false > full-backup-$(date +%F).sql -u root -p

Breakdown of the options:

  • --all-databases dumps all databases.
  • --single-transaction option provides a way of making an online backup.
  • --lock-tables=false by default, mysqldump will lock the table of your database during the dump process to make sure there will not have new data added during this time-frame. This may impact your apps during the db dump.
  • $(date +%F) specifies the timestamp formatting. %F displays the full date.

Setting up config file to not expose MySQL database password⚓

As stated in the MySQL End-User Guidelines for Password Security manual, our best option is to store the password in an option file. Putting the password in crontab in convenient but insecure, as every time the command runs, the password can be visible in ps.

Create a new user where you’ll be running the crontab and Rclone. In the user’s home directory, create a new .your_filename_here.cnf and restrict it by running the command chmod 600 /home/user/.your_filename_here.cnf.

Example .cnf file:

[client]
user = root
password = mysql_root_pwd

We can then add --defaults-extra-file=/home/bckusr/.my.cnf when using the mysqldump command for logging in.

(Optional) Setting up the default editor for crontab⚓

A bit controversial, but if you don’t like vi and prefer nano instead, you can change the default editor for a specific user by editing .bash_profile. To change the editor, paste this into your bash profile, save it, then logout and login (exit from the SSH session as well):

export VISUAL="nano"
export EDITOR="nano"

Setting up RClone⚓

Follow the RClone Backblaze B2 configuration documentation for setting up RClone.

Setting up crontab⚓

Execute crontab -e as the user you want to run backups from. You can use Crontab Guru for quickly setting up the cron schedule execution.

Here’s what the crontab for daily backup running at 1 AM looks like:

0 1 * * * /usr/bin/mysqldump mysql -h ${{ db_name }} -P 25060 -u doadmin --defaults-extra-file=/home/dave/.my.cnf -u root --single-transaction --lock-tables=false --all-databases > full-backup-$(date +\%F).sql && rclone copy /home/dave/full-backup-*.sql b2:ImageBackup/DigitalOcean && rm -rf /home/bckusr/full-backup-*.sql && <healthchecks.io ping url>

This creates a new database backup, which then copies the backup to b2. After successfully copying to b2, it cleans up after itself and removes the local mysqldump.

Resources⚓