This brief tutorial shows you how to backup and restore MariaDB databases and data files on Ubuntu 20.04 | 18.04.
MariaDB 10.1 introduced a new backup feature called Mariabackup.
Mariabackup is an an open source tool provided by MariaDB for performing physical online backups of InnoDB, Aria and MyISAM tables. It also enabled “hot online” backups for InnoDB tables.
Mariabackup doesn’t only back up databases, it also backs up many different files in order to perform its backup and restore operations.
To get started with backing up and restoring MariaDB data, follow the steps below:
Install Mariabackup
When you install MariaDB database server, its backup tool isn’t installed automatically.
If you want to implement and backup and restore process with MariaDB, you’ll want to install its backup tool.
Run the commands below to install it on Ubuntu.
sudo apt update sudo apt-get install mariadb-backup
That should install the tool on Ubuntu.
Backing up MariaDB
Now that the tool is installed, you can run it with different options to perform a full backup and restore of MariaDB databases and data files on Ubuntu and other Linux systems.
First, run the commands below to create a backup directory to store MariaDB backup content.
sudo mkdir ~/mariadb_backup
This should create a backup folder in your home directory: /home/<username>/mariadb_backup.
After creating the backup folder above, simply run the commands below to create a live backup and store in the folder above.
sudo mariabackup --backup --target-dir ~/mariadb_backup -u root
When you run the commands above, it should complete with a success messages.
[00] 2021-01-07 12:28:44 >> log scanned up to (1625621) mariabackup: Stopping log copying thread [00] 2021-01-07 12:28:44 >> log scanned up to (1625621) [00] 2021-01-07 12:28:44 Executing UNLOCK TABLES [00] 2021-01-07 12:28:44 All tables unlocked [00] 2021-01-07 12:28:44 Copying ib_buffer_pool to /home/richard/mariadb_backup/ib_buffer_pool [00] 2021-01-07 12:28:44 …done [00] 2021-01-07 12:28:44 Backup created in directory '/home/richard/mariadb_backup/' [00] 2021-01-07 12:28:44 Writing backup-my.cnf [00] 2021-01-07 12:28:44 …done [00] 2021-01-07 12:28:44 Writing xtrabackup_info [00] 2021-01-07 12:28:44 …done [00] 2021-01-07 12:28:44 Redo log (from LSN 1625612 to 1625621) was copied. [00] 2021-01-07 12:28:44 completed OK!
Run the commands below to list the content of the backup folder.
ls -al ~/mariadb_backup/
It should list something similar to the lines below:
rwxrwxr-x 4 richard richard 4096 Jan 7 12:28 . drwxr-xr-x 16 richard richard 4096 Jan 7 09:39 . -rw-r----- 1 root root 16384 Jan 7 12:28 aria_log.00000001 -rw-r----- 1 root root 52 Jan 7 12:28 aria_log_control -rw-r----- 1 root root 324 Jan 7 12:28 backup-my.cnf -rw-r----- 1 root root 972 Jan 7 12:28 ib_buffer_pool -rw-r----- 1 root root 12582912 Jan 7 12:28 ibdata1 -rw-r----- 1 root root 2560 Jan 7 12:28 ib_logfile0 drwx------ 2 root root 4096 Jan 7 12:28 mysql drwx------ 2 root root 4096 Jan 7 12:28 performance_schema -rw-r----- 1 root root 77 Jan 7 12:28 xtrabackup_checkpoints -rw-r----- 1 root root 459 Jan 7 12:28 xtrabackup_info
That’s it! You have successfully backup MariaDB.
Restoring MariaDB
After creating a backup copy above, you can create a zipped or tarred copy of the folder and export to another server using rsync or scp if you’re migrating.
If you’re exporting the database to another server, simply run the commands below to create an archive.
You’ll want to use sudo since the database content was added using sudo.
sudo tar -czvf mariadb_backup.tar.gz ~/mariadb_backup
Now send to backup data over to a remote location using rsync or scp.
On the remote host, run the commands below to extract the archive.
tar zxvf mariadb_backup.tar.gz
Next, stop MariaDB service and delete any existing MariaDB data.
sudo systemctl stop mariadb.service sudo rm -rf /var/lib/mysql/*
Next, prepare the backup file for restoration.
sudo mariabackup --prepare --target-dir ~/mariadb_backup
When you run the commands above, it should give you a success message as shown below:
2021-01-07 12:55:58 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M 2021-01-07 12:55:58 0 [Note] InnoDB: Completed initialization of buffer pool 2021-01-07 12:55:58 0 [Note] InnoDB: page_cleaner coordinator priority: -20 2021-01-07 12:55:58 0 [Note] InnoDB: The log sequence number 1625452 in the system tablespace does not match the log sequence number 1625612 in the ib_logfiles! [00] 2021-01-07 12:55:58 Last binlog file , position 0 [00] 2021-01-07 12:55:59 completed OK!
After preparing the database for restoration, simply run the commands below to restore.
sudo mariabackup --copy-back --target-dir ~/mariadb_backup
When the command above is completed, it should display a success message similar to the one below:
01] 2021-01-07 13:21:02 Copying ./mysql/help_category.MYD to /var/lib/mysql/mysql/help_category.MYD [01] 2021-01-07 13:21:02 .done [01] 2021-01-07 13:21:02 Copying ./mysql/columns_priv.frm to /var/lib/mysql/mysql/columns_priv.frm [01] 2021-01-07 13:21:02 .done [01] 2021-01-07 13:21:02 Copying ./mysql/columns_priv.MYI to /var/lib/mysql/mysql/columns_priv.MYI [01] 2021-01-07 13:21:02 .done [01] 2021-01-07 13:21:02 Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool [01] 2021-01-07 13:21:02 .done [01] 2021-01-07 13:21:02 Copying ./aria_log.00000001 to /var/lib/mysql/aria_log.00000001 [01] 2021-01-07 13:21:02 .done [00] 2021-01-07 13:21:02 completed OK!
Next, run the commands to give MySQL service control of the mysql folder.
sudo chown -R mysql. /var/lib/mysql
Finally start MariaDB service.
sudo systemctl start mariadb.service
That should do it!
Conclusion:
This post showed you how to backup and restore MariaDB databases and data using Mariabackup tool.
If you find any error above, please use the form below to report.
You may also like the post below: