Different methods

All the backup methods on this page are zero-copy (except for incremental backups with XtraBackup). That is, the data will be directly transferred to the backup server without creating a local copy first.

Backup using SQL dump

Advantages:

Disadvantages:

Conclusion: Use this backup method if you have a small database (e.g. 1GB) on Linux.

How to setup:

On Linux with the binary client: Change 0 to 1 in /usr/local/etc/urbackup/mariadbdump.conf. Afterwards file backups will include the dump file of the database at urbackup_backup_scripts/mariadbdump.sql.

How to restore:

Start with an empty database, download the SQL dump from the server and then apply the SQL dump with:

mysql -u root -p < mysqldump.sql

Backup using snapshot

Advantages:

Disadvantages:

Conclusion: Easiest method on Windows, good method on Linux if a file system snapshotting method is available. Best method if change block tracking is available.

How to setup:

Configure the client to backup the MySQL data directory. E.g. /var/lib/mysql on Linux and C:\ProgramData\MySQL\MySQL Server 5.5\data on Windows. Make sure you have configured a snapshotting method on Linux.

How to restore:

Stop MySQL/MariaDB and then restore /var/lib/mysql on Linux and C:\ProgramData\MySQL\MySQL Server 5.5\data on Windows from one of the file backups.

Binary backup using Percona XtraBackup

Advantages:

Disadvantages:

Conclusion: Best method for high traffic and large MySQL/MariaDB instances.

How to setup (Linux):

Change 0 to 1 in /usr/local/etc/urbackup/mariadbxtrabackup.conf and change other configuration options if necessary.

Setup the server by running

/usr/local/share/urbackup/scripts/setup-mariadbbackup

This will configure backups of the correct directories and create a virtual client [incr] for the incremental backups.

Test if XtraBackup works by running:

/usr/local/share/urbackup/scripts/mariadbxtrabackup > /dev/null

Then configure the backup interval of [incr] to be relatively small and the backup interval of the main client with the (virtual) full backup script relatively big and schedule it via backup window at a time where it does not interfere with database usage.

How to restore:

Run the script

/usr/local/share/urbackup/scripts/restore-mariadb

to restore your database server to an incremental or full database backup. The script will allow you to select the full/incremental backup you want to restore to, start/stop MySQL/MariaDB and then automatically apply first the full and then the incremental backups.