How to Backup a MariaDB Database on a Plesk Server

I needed to create a full database backup and import a new SQL dump during this project.

Here's some context: Our server runs on CentOS, Plesk, and MariaDB. Our company manages these as part of a project for one of our clients.

Theoretically, I could have used Plesk and phpMyAdmin for this task. However, the number of records in the SQL dump was over a million. This enormous number was causing timeouts on phpMyAdmin. As a result, I had no choice but to import from the Linux client of MariaDB directly.

Step-by-step Process

• Connecting to the Server

First, I connected to the server via SSH, using the command in the Terminal.app:

ssh -i "access_key.pem" username@<server_ipv4_address>

• Creating a Backup

To prevent the database from becoming a jumbled mess of 10 GB data, I backed up the entire database using the command:

mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` database_name > backup_filename.sql

A helpful tip: The Plesk password is stored in the file at `/etc/psa/.psa.shadow`, which can be directly passed as a parameter to the mysqldump command via the output from the `cat` command.

• Importing the SQL Dump

With the complete database backup in place, I executed:

mysql -uadmin -p`cat /etc/psa/.psa.shadow` database_name

After connecting to the database, I imported the dump file by running:

source /file_path/imported_table.sql;

• Verification

After the import, I conducted a quick check using:

SHOW TABLES;

and:

SELECT * FROM table_name LIMIT 10;

Everything was functioning correctly.

• Archiving the Backup

It is prudent to archive the database backup. After exiting the MariaDB client with `exit;`, I compressed the backup:

gzip ./backup_filename.sql

Finally, I downloaded and stored this file in the client's OneDrive account as a backup measure.

 

The entire operation took roughly 10 minutes.

Cheers!