Install MariaDB and XtraBackup In Debian/ubuntu
MariaDB is a popular open-source relational database management system (RDBMS) that is designed to be a drop-in replacement for MySQL. It was created by the original developers of MySQL after concerns arose over its acquisition by Oracle Corporation.
MariaDB has high compatibility with MySQL and has the same library binary parity and matches the MySQL APIs and commands exactly. This makes the database migration from MySQL to MariaDB easier.
If you are using MariaDB, it is essential for you to know how to make MariaDB backups and restore them. In this article, you are going to understand backup and restore MariaDB with Mariabackup and the other two methods. Then, you may ask, what is Mariabackup?
What is Mariabackup?
Mariabackup, forked from Percona XtraBackup, is an open-source tool created by MariaDB that can be used to perform online backups of InnoDB, MyRocks, Aria, and MyISAM tables in a physical format.
“apt for debian, ubuntu / yum for centos, redhat, fedora..”
1- Install Mariabackup
First, update the package list to make sure you are installing the latest available packages.
$sudo apt update
Install MariaBackup:
$sudo apt install mariadb-backup
Verify Installation:
$mariabackup --version
In the beginning of this post I mention that we recently upgraded Jira to the newest version and MariaDB Server to 10.3. In that older environment we used XtraBackup. To get backups for MariaDB Server 10.3 we had to update our backup scripts to use MariaDB Backup.
2- create table mariabackup
Create a MySQL User with Appropriate Privileges:
The first thing we need to do is create a new MySQL user configured to handle backup tasks. We will only give this user the privileges it needs to copy the data safely while the system is running.
To be explicit about the account’s purpose, we will call the new user backup. We will be placing the user’s credentials in a secure file, so feel free to choose a complex password:
CREATE USER ‘admin’@’localhost’ IDENTIFIED BY ‘admin’;
Next we need to grant the new backup user the permissions it needs to perform all backup actions on the database system. Grant the required privileges and apply them to the current session by typing:
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON . TO ‘admin’@’localhost’;
FLUSH PRIVILEGES;
Create a MySQL Configuration File with the Backup Parameters:
Begin by creating a minimal MySQL configuration file that the backup script will use. This will contain the MySQL credentials for the MySQL user.
Open a file at /etc/mysql/mariadb.conf.d/mariabackup.cnf in your text editor:
sudo nano /etc/mysql/mariadb.conf.d/mariabackup.cnf
[mariabackup]
user = admin
password = admin
3- start backup
After switching to MariaDB Backup the backup command looks like this:
$ mariabackup --backup --target-dir=/backup/to/dir --user=username --password=password
We had to change the command itself from being innobackupex to mariabackup and we added two options; –backup to tell mariabackup that we want it to create a backup and –target-dir to specify that the given directory is where the backup files should go. It should be noticed that if we would have been using a more recent version of the XtraBackup the command line options would have been completely compatible with XtraBackup, so the only thing to change then would have been the command itself from xtrabackup to mariabackup.
To be sure that the backup works we’ll copy it to another server and try to restore it there. To restore a backup we first have to prepare it:
$ mariabackup --prepare --target-dir=/backup/to/dir
Notice that I have a full backup of a MariaDB Server instance which will replace anything that would exist in the instance where I’m restoring to. I’ll therefore stop the server and remove any data files it holds.
$ sudo service mariadb stop
$ sudo rm -rf /var/lib/mysql/*
Now the backup files can be put in the data directory of this server instance. It should be done with mariabackup to get it done right. It does some things related to the redo log format explained above.
$ sudo mariabackup --copy-back --target-dir=/backup/to/dir
Make sure permissions are correct. In my case normal user and group are in use. Then start the server.
$ sudo chown mysql:mysql /var/lib/mysql -R
$ sudo service mariadb start
=======================================================================================
If you want backup all your database in one server, follow this steps , and goodluck @marouanelouguid 😉
4- backup in one server
Prepare the Backup:
After the backup is complete, you’ll need to prepare it for use. Use the –prepare option:
mariabackup --prepare --target-dir=/path/to/backup/directory
This step is essential to make the backup consistent and ready for restoration.
Compress and Archive (Optional):
You can compress the backup directory to reduce its size before transferring it to the remote server. Use a tool like tar or gzip to create a compressed archive.
tar -czvf backup.tar.gz /path/to/backup/directory
Transfer this compressed archive to the remote server using a secure method like SCP or SFTP.
On the Remote Server (Server where you want to store the backup):
Prepare the Remote Directory:
Create a directory on the remote server where you want to store the backups. Ensure that it has appropriate permissions for writing.
ssh remote_user@ip_server 'mkdir -p /remote/backup/directory'
Transfer the Backup:
Transfer the backup directory or the compressed archive from the source server to the remote server using a secure method like SCP or SFTP.
scp /path/to/backup/directory/backup.tar.gz debian@ip-server:/remote/backup/directory/
This command transfers the backup to the remote server.
Extract the Backup (If using compressed archive):
If you used compression, you’ll need to extract the backup on the remote server.
ssh debian@ip_server 'tar -xzvf /remote/backup/directory/backup.tar.gz -C /remote/backup/directory/'
