How to Backup and Restore MariaDB on Ubuntu
Backing up and restoring MariaDB on Ubuntu uses the Mariabackup tool. Mariabackup is a free program from MariaDB that makes safe copies of your database while it’s running, which means you don’t have to shut anything down. It works with different kinds of MariaDB tables, including InnoDB, Aria, and MyISAM.
This method protects all your database information, letting you get it back easily. You can use this on systems like Ubuntu 20.04 or 18.04. You’ll use simple commands in your computer’s terminal to make these backups and bring your data back if needed.
Install mariadb-backup using apt, create a backup directory with mkdir, and then run mariabackup –backup –target-dir. Restore by stopping MariaDB, preparing the backup with mariabackup –prepare, and then copying files back.
Install Mariabackup
To back up your MariaDB databases on Ubuntu, you first need to install the mariadb-backup tool. This tool helps you create backups. Run these commands to get it: sudo apt update and then sudo apt install mariadb-backup.
To implement a backup and restore process for MariaDB, you must first install its dedicated backup tool. This tool, often referred to as `mysqldump`, creates a snapshot of your MariaDB database, saving it as a SQL file. You can then use this SQL file to restore your database to its previous state if needed.
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
After installing the mariadb-backup tool on Ubuntu, you can start making backups by creating a special folder to keep them safe. Run the command sudo mkdir ~/mariadb_backup to make a new folder in your home area just for your MariaDB backups.
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
Once you have a MariaDB backup on Ubuntu, you can get it ready for restoring or moving it to another computer by making a compressed file. Use the command sudo tar -czvf mariadb_backup.tar.gz ~/mariadb_backup to create this archive from your backup folder.
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
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!
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.
Was this guide helpful?
About the Author
Richard
Tech Writer, IT Professional
Richard, a writer for Geek Rewind, is a tech enthusiast who loves breaking down complex IT topics into simple, easy-to-understand ideas. With years of hands-on experience in system administration and enterprise IT operations, he’s developed a knack for offering practical tips 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.
No comments yet — be the first to share your thoughts!