Backup and recovery test of MariaDB on arm64 platform

Author: zhaorenhai

This article plans to test the operation of MariaDB’s backup and recovery function on the arm64 platform, and provide a reference for those interested in deploying MariaDB on the arm64 platform.

The test platform chooses the 8C16G Kunpeng virtual machine on Huawei Cloud, the OS is Ubuntu 18.04, and MariaDB we plan to choose the latest version on github for testing.

Then log in to our virtual machine and create a user:

adduser mariadb
All subsequent work is carried out under this user.

Switch to this user, download the latest code from github, and compile it.

1
2
3
4
5
6
7

su - mariadb 
git clone https://github.com/mariadb/server 
sudo apt-get install build-essential libncurses5-dev gnutls-dev bison zlib1g-dev ccache libnuma-dev libxml2-dev cmake 
mkdir build-mariadb-server 
cd build-mariadb-server 
cmake ../server -DCMAKE_BUILD_TYPE = RelWithDebInfo 
cmake --build .

Now that we have a database program of the latest version, let’s continue to create the database configuration file and run the database.

Create a database parameter file:
vi ~/mariadb.cnf

Set the following parameters:

1
2
3
4
5
6
7
8
9

[mariadb]
datadir=/home/mariadb/data/dir 
lc_messages_dir=/home/mariadb/server/sql/share
innodb_buffer_pool_size = 8G 
innodb_log_file_size = 1G 
innodb_flush_method = O_DIRECT 
skip-grant-tables
# open binlog
log-bin

Run the database

1
2
3

mkdir -p /home/mariadb/data/dir 
./scripts/mysql_install_db --srcdir=../server --defaults-file=~/mariadb.cnf 
sql/mysqld --defaults-file=~/mariadb.cnf

Now a database is up and running.

The main purpose of this article is to test the backup and recovery functions. Before that, we have to load some data. We try to load more data. The tool for loading data we use the tpcc-mysql, only its data loading function.

1
2
3
4
5
6
7
8
9
10

sudo apt install libmariadbclient-dev 
git clone https://github.com/Percona-Lab/tpcc-mysql 
cd tpcc-mysql/src 
make 
cd .. 
export PATH=~/build-mariadb-server/client:$PATH   
mysqladmin create tpcc100 
mysql tpcc100 <create_table.sql 
./tpcc_load -h127.0.0.1 -d tpcc100 -u root -p "" -w 100 
mysql tpcc100 <add_fkey_idx.sql

Now start to test the database backup function.

MariaDB database backup includes logical backup and physical backup.

The advantage of logical backup is that the files that are backed up are SQL formatted text files, which can be used for other types of databases, and are also convenient for importing or data migration. The disadvantage is that the database performance is greatly affected during backup and during recovery. The tool MariaDB uses for logical backup is mysqldump.

Physical backup is actually a backup of the physical files of the database, so the impact on the database is relatively small, but the files backed up cannot be used in other types of databases， can only be used for backup and recovery. The tool that MariaDB uses for physical backup is mariabackup.

MariaDB’s logical backup and physical backup are both for the backup of the database at a certain point in time, and cannot achieve complete lossless recovery. If you want to achieve lossless recovery, you also need to use the replay function of the binlog . MariaDB implements this, the tool is mysqlbinlog.

Let’s test these three tools one by one.

Logical backup and recovery

First test the logical backup.

1

mysqldump --all-databases --master-data=2 --single-transaction > all_databases.sql

The –master-data=2 option in the above command means to record the binlog location during the backup in the backup file. If subsequent lossless recovery is performed, you can provide the mysqlbinlog tool with the location to start the recovery. --single-transaction means that the backup is executed in one transaction, so that it does not affect the normal operation of other sessions and can also ensure the consistency of the backup data.

Our data directory occupy a total of 8.6G disk space, and it only took 3 minutes to complete the backup. The backup file is 7.2G, and the performance is still acceptable. Of course, this is also related to the disk used. We are using Huawei Cloud SSD, which should be faster.

The recovery of logical backup is also very simple, just execute the exported sql file directly. We are only testing here, so we will execute it directly on the original database.

1

mysql -u root < all_databases.sql

The above 7.2G sql was executed for about 21 minutes, and the performance is not bad. If it is restored on a new database, it is estimated that it will be faster.

Physical backup and recovery

We continue to test the physical backup.

First create a directory for storing backup files:

1

mkdir -p /home/mariadb/data/backup

Then there can be no skip-grant-tables option when backing up, we first delete this configuration item in the backup file, restart the database, and then execute to mysqlenter the database and modify the root user password:

1

alter user 'root'@'localhost' identified by 'backuptest';

The mariabackup we compiled is in the extra directory of the compilation directory, and we enter that directory to execute:

1
2
3

cd ~/build-mariadb-server/extra/mariabackup 
./mariabackup --backup --target-dir=/home/mariadb/data/backup -uroot -pbackuptest 
./mariabackup --prepare --target-dir=/home/mariadb/data/backup

The entire backup is completed in about two minutes, and the speed is relatively fast.

Let’s continue to test the recovery:

Stop the database during recovery.

Then we back up the original data directory and create a new directory, because the data file directory must be empty when restoring

1
2
3

cd /home/mariadb/data 
mv dir dirbak 
mkdir dir

Then start to restore:

1
2

cd ~/build-mariadb-server/extra/mariabackup 
./mariabackup --copy-back --target-dir=/home/mariadb/data/backup --datadir=/home/mariadb/data/dir/

Recovery took only one and a half minutes

Lossless recovery

Let me continue to test the lossless recovery.

Suppose the following scenario: After a database backup is made, some new tables are created, and some data is inserted. At this time, the database data is all deleted, but the backup file and binlog are still there. We try to use the backup file and binlog to completely restore the database.

Let’s use the mariabackup tool to back up the database first, then execute the following SQL to create some tables and insert some data:

1
2
3
4
5
6

create  database  test ; 
use  test ; 
create  table binlogtest( id  int , descs varchar(10)); 
insert  into binlogtest values ( 1 , 'test1' ); 
insert  into binlogtest values ( 2 , 'test2' ); 
commit ;

Then execute the following command, to simulate data files are cleared. We use mv here, mainly to preserve the binlog file. After all, in our test environment, binlog did not make multiple copies.

1
2
3

cd /home/mariadb/data 
mv dir dirbak2 
mkdir dir

Now that the data files are all cleared, then we use mariabackup to restore the files during the backup, first stop the database, and then execute the following command:

1
2

cd ~/build-mariadb-server/extra/mariabackup 
./mariabackup --copy-back --target-dir=/home/mariadb/data/backup --datadir=/home/mariadb/data/dir/

Then start the database, log in to the database,

Use the following sql to query:

1

select * from test.binlogtest;

You will get an error message that the table does not exist.

Then we try to use binlog to restore.

First check the xtrabackup_binlog_info file of the backup file, and find the pos point to start the recovery:

1

cat xtrabackup_binlog_info

The result is as follows:

1

mariadb-arm-perf-test-bin.000012 358 0-1-7541

Means that the pos point to start recovery should be 358.

1
2

cd /home/mariadb/data/dirbak2 
mysqlbinlog mariadb-arm-perf-test-bin.000001 --start-position=344| mysql -uroot -pbackuptest

Then log in to the database again to query the previous binlogtest table, and you will find that the data has been restored.

Summary

After testing so far, we can find that MariaDB’s various backup and recovery functions work perfectly on the arm64 platform, and the performance is good.