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:
- Easy to set-up
- Enables migration to a different MySQL version on restore
- Non-binary. For example you could manually delete some tables or modify data before restoring
- Can be smaller because it does not include indexes
Disadvantages:
- Restore time can be longer because MySQL/MariaDB has to rebuild indexes
- Incremental backups can cause large transfers with UrBackup
- The complete database (excl. indexes) has to be read during incremental backups
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:
- Easy to setup on Windows
- UrBackup only transfers changed data
- Proper progress bar
- Fast restores if the network is fast because indexes are included in the backup
Disadvantages:
- You might not be able to setup a snapshotting method on Linux
- Because indexes are backed up, the backups might be bigger than the SQL dump method
- Restore to a previous MySQL version is not possible
- The complete database has to be read during incremental backups if no change block tracking is available
- Snapshots may slow down your database depending on where the snapshot stores the copy-on-write data (shadow storage on Windows)
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:
- UrBackup only transfers changed data
- The complete database is not read during the more frequent backups (only differences)
- Configuration option if indexes should be included in the backup
- The database is not slowed down by a snapshot
Disadvantages:
- You need to install a version of Percona XtraBackup matching your database
- Might be slightly slower than the backup using snapshots
- Currently only implemented and documented for Linux
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.
Like
Follow