Geek Rewind

How to Backup and Restore MariaDB on Ubuntu

Richard

·

This tutorial guides users through the process of backing up and restoring MariaDB databases and data files on Ubuntu using Mariabackup. Mariabackup, an open-source tool introduced in MariaDB 10.1, does “hot online” backups of InnoDB tables and other files. The tutorial includes step-by-step instructions on installing Mariabackup, creating backups, and restoring data from these backups.

This brief tutorial shows how to back up 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 open-source tool MariaDB provides for performing physical online backups of InnoDB, Aria, and MyISAM tables. It also enabled “hot online” backups for InnoDB tables.

Mariabackup not only backs up databases but also backs up many different files 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 the MariaDB database server, its backup tool isn’t installed automatically.

If you want to implement a 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 install mariadb-backup

That should install the tool on Ubuntu.

Backing up MariaDB

Once installed, you can run the tool with different options to perform a full backup and restore 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, run the commands below to create a live backup and store it 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 message.

[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 backed up MariaDB.

Restoring MariaDB

After creating a backup copy above, you can create a zipped or tarred copy of the folder and export it to another server using Rsync or SCP if you’re migrating.

If exporting the database to another server, 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 backup data 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 the 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, 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 the MariaDB service.

sudo systemctl start mariadb.service

That should do it!

Conclusion:

  • Successfully installed Mariabackup for backup and restore operations on MariaDB.
  • Created a backup of the MariaDB databases and associated data files.
  • Backed up the data directory in a specified target folder.
  • Prepared the backup for restoration after migrating to another server.
  • Restored the databases successfully back to the MariaDB server.
  • Ensured the proper ownership of the MySQL directory for seamless operation.
  • Ready to utilize the backup strategy for future data protection and recovery needs.
Richard Avatar
Richard
Richard, the owner and lead writer at Geek Rewind, is a tech enthusiast passionate about simplifying complex IT topics. His years of hands-on experience in system administration and enterprise IT operations have honed his ability to provide practical insights and solutions. Richard aims to make technology more accessible and actionable. He’s deeply committed to the Geek Rewind community, always ready to answer questions and engage in discussions. He enjoys watching football, cheering for the Minnesota Vikings, and spending time with his family when he’s not writing or managing Geek Rewind.
,

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *


Exit mobile version