Requirements

AhsayOBM Installation

Make sure that the latest version of AhsayOBM is installed directly on the machine where the MariaDB database(s) are hosted.

Add-On Module Requirement

Make sure the MariaDB Database Server add-on module has been enabled in your AhsayOBM user account.

Please contact your backup service provider for more details.

Backup Quota Requirement

Make sure that your AhsayOBM user account has sufficient quota assigned to accommodate the storage of MariaDB Database Server backup set and retention policy. Please contact your backup service provider for more details.

Java Heap Size

The default Java heap size setting on AhsayOBM is 2048MB. It is highly recommended to increase the Java heap size setting to be at least 4096MB to improve backup and restore performance. The actual heap size is dependent on amount of free memory available on your MariaDB Database Server.

Network Drive

The login accounts for network drives must have read and write access permission to ensure that backup and restore would be successful.

MariaDB Database Server Requirements

Please ensure that the following requirements and conditions are met on the MariaDB database server.

MariaDB Version

AhsayOBM support MariaDB version 10.0 or above. For details of all supported MariaDB versions please refer to:

FAQ: Ahsay Software Compatibility List for version 9.1 or above.

Verifying MariaDB Database Version

You can run the following query to verify the MariaDB Database Version:

MariaDB [(none)]> select version();
+-----------------+
| version() |
+-----------------+
| 10.4.12-MariaDB |
+-----------------+
1 row in set (0.00 sec)

However, for some older MariaDB database versions, you can only use the /usr/bin/mysql command to verify the version.

# /usr/bin/mysql --version
/usr/bin/ mysql Ver 15.1 Distrib 10.0.38-MariaDB, for debian-linux-gnu (x86_64)
using readline 5.2

For some older MariaDB database versions, to connect to MariaDB database use the mysql -u root -p command.

>mysql -u root -p
Enter password: ********
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.1.22-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select version();
+-----------------+
| version() |
+-----------------+
| 10.1.22-MariaDB |
+-----------------+
1 row in set (0.00 sec)

However, for some older MariaDB database versions, you can only use the mysql –u root -p command to login.

# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04 distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select version();
+---------------------------------------+
| version() |
+---------------------------------------+
| 10.0.38-MariaDB-0ubuntu0.16.04.1 |
+---------------------------------------+
1 row in set (0.000 sec)

MariaDB Database Status

Windows

The MariaDB database instance is online.

For some older MariaDB database versions check the MySQL, which is the MariaDB database, instance is online.

Linux

The MariaDB database instance is online. To check use either the service mariadb status or service mysql status command.

# service mariadb status
mariadb.service - MariaDB 10.4.15 database server
 Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset
disabled)
 Drop-In: /etc/systemd/system/mariadb.service.d
www.ahsay.com AhsayOBM MariaDB Backup and Restore Guide | 10 of 86
└─migrated-from-my.cnf-settings.conf
 Active: active (running) since Wed 2020-10-21 09:41:54 PST; 1 day 6h ago
 Docs: man:mysqld(8)
 https://mariadb.com/kb/en/library/systemd/
Main PID: 831 (mysqld)
 Status: "Taking your SQL requests now..."
 Tasks: 30 (limit: 4657)
 Memory: 260.8M
 CGroup: /system.slice/mariadb.service
 └─831 /usr/sbin/mysqld

OR

# service mysql status
● mariadb.service - MariaDB 10.4.15 database server
 Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor
preset disabled)
 Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
 Active: active (running) since Wed 2020-10-21 09:41:54 PST; 3 months 0
days ago
 Docs: man:mysqld(8)
 https://mariadb.com/kb/en/library/systemd/
Main PID: 831 (mysqld)
 Status: "Taking your SQL requests now..."
 Tasks: 30 (limit: 4657)
 Memory: 260.8M
 CGroup: /system.slice/mariadb.service
 └─831 /usr/sbin/mysqld

However, for some older MariaDB database versions, use the service mysql status command.

# service mysql status
● mysql.service - LSB: Start and stop the mysql database server daemon
 Loaded: loaded (/etc/init.d/mysql; bad; vendor preset: enabled)
 Active: active (running) since Wed 2021-01-20 16:02:03 HKT; 23h ago
 Docs: man:systemd-sysv-generator(8)
 CGroup: /system.slice/mysql.service
 ├─1234 /bin/bash /usr/bin/mysqld_safe
 ├─1443 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql -
-plugin-dir=/usr/lib/mysql/plugin --user=mysql --skip-log-error --pidfile=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --
port=3306
 └─1444 logger -t mysqld -p daemon error
Jan 20 16:02:01 ubuntu1604 mysqld[1444]: 210120 16:02:01 [Note] InnoDB:
Waiting for purge to start
Jan 20 16:02:01 ubuntu1604 mysqld[1444]: 210120 16:02:01 [Note] InnoDB:
Percona XtraDB (http://www.percona.com) 5.6.42-84.2 started; log sequence
number 9155283
www.ahsay.com AhsayOBM MariaDB Backup and Restore Guide | 11 of 86
Jan 20 16:02:01 ubuntu1604 mysqld[1444]: 210120 16:02:01 [Note] Plugin
'FEEDBACK' is disabled.
Jan 20 16:02:01 ubuntu1604 mysqld[1444]: 210120 16:02:01 [Note] Server
socket created on IP: '127.0.0.1'.
Jan 20 16:02:02 ubuntu1604 mysqld[1444]: 210120 16:02:02 [Note]
/usr/sbin/mysqld: ready for connections.
Jan 20 16:02:02 ubuntu1604 mysqld[1444]: Version: '10.0.38-MariaDB0ubuntu0.16.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Ubuntu
16.04
Jan 20 16:02:03 ubuntu1604 mysql[1068]: ...done.

TCP/IP Port

Check the listening port of the MariaDB database instance (default is 3306) using the command netstat –an.

C:\>netstat -an
Active Connections
Proto Local Address Foreign Address State
TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
TCP 0.0.0.0:2179 0.0.0.0:0 LISTENING
TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING
TCP 0.0.0.0:3389 0.0.0.0:0 LISTENING
TCP 0.0.0.0:5985 0.0.0.0:0 LISTENING
TCP 0.0.0.0:47001 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49664 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49665 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49666 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49668 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49669 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49670 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49671 0.0.0.0:0 LISTENING
TCP 0.0.0.0:50000 0.0.0.0:0 LISTENING
TCP 10.16.10.88:139 0.0.0.0:0 LISTENING

Mysqldump Utility

The mysqldump utility is installed on the MariaDB database server.

Windows

Example: The default location for the mysqldump utitlity for MariaDB v10.9 is located in the following folder C:\Program Files\MariaDB 10.9\bin

Linux

The mysqldump utility is installed on the MariaDB database server.

To locate the mysqldump utility use the whereis command:

# whereis mysqldump
mysqldump: /usr/bin/mysqldump /usr/share/man/man1/mysqldump.1.gz

Mysqldump Utility Version

The mysqldump utility is the same version as the MariaDB database.

To check the mysqldump version use the mysqldump ––version command.

C:\Program Files\MariaDB 10.4\bin>mysqldump --version
mysqldump Ver 10.17 Distrib 10.4.12-MariaDB, for Win64 (AMD64)
C:\Program Files\ MariaDB 10.4\bin>

User Account Privileges

A MariaDB database user account with the following privileges must be setup for the backup operation.

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO "username"@"localhost" IDENTIFIED
BY "password";
Query OK, 0 rows affected (0.003 sec)
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO "username"@"localhost.localdomain"
IDENTIFIED BY "password";
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]>

LocalHost

Verify that “localhost” on the MariaDB database server is resolvable using the ping localhost command.

C:\>ping localhost
Pinging w2k16-std [::1] with 32 bytes of data:
Reply from ::1: time<1ms
Reply from ::1: time<1ms
Reply from ::1: time<1ms
Reply from ::1: time<1ms
Ping statistics for ::1:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
C:\>

“localhost” is allowed to access the MariaDB database instance on the MariaDB service listening port (default 3306) using the command telnet localhost 3306.

# telnet localhost 3306
Y
5.5.5-10.4.12-MariaDB7Ip{8E1e,■
≈!G_,X[>cWm=&Fmysql_native_password

hr>

MariaDB Virtual System Databases

The “information_schema” and “performance_schema” databases are MariaDB virtual system databases, which contains information about the user databases on the MariaDB instance, are automatically excluded from the backup source. They are read-only and cannot be backed up.

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| chinook |
| information_schema |
| mysql |
| nation |
| performance_schema |
| sportdb |
| test |
+--------------------+
7 rows in set (0.002 sec)

Temporary Directory

The databases selected for backup will be temporarily spooled to a temporary directory before being uploaded to the backup server or destination storage.

Ensure the temporary directory configured for the MariaDB database backup has sufficient disk space for the backup operation. The free space on the temporary directory drive should be at least 150% of the database size, as the temporary directory is also used for storing index files and any incremental or differential delta files generated during the backup job before they are uploaded to the backup destination.

Example:

If the default setting for Delta ratio is 50% for in-file delta, if the total MariaDB database size is 100GB and there is only one backup destination, the minimum free space needed on the drive where the temporary directory folder is located = 150GB.

100GB = Total MariaDB database size

50GB = Total maximum size of incremental or differential delta files generated.

Please bear in mind the size of the databases may grow over time and you may need to review the temporary directory free space requirements on a regular basis.

To calculate for the size of your databases run the command below:

MariaDB [(none)]> SELECT
-> table_schema 'Database Name'
-> ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MB'
-> FROM information_schema.tables
-> GROUP by table_schema;
+--------------------+--------------+
| Database | Size in (MB) |
+--------------------+--------------+
| chinook | 1.83 |
| information_schema | 0.19 |
| mysql | 2.17 |
| nation | 3.55 |
| performance_schema | 0.00 |
| sportdb | 2.89 |
| test | 0.77 |
+--------------------+--------------+
7 rows in set (0.378 sec)