We 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, we 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, we had no choice but to import from the Linux client of MariaDB directly.

Step-by-step Process

  • Connecting to the Server

First, we 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, we 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, we executed:

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

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

source /file_path/imported_table.sql;

  • Verification

After the import, we 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;`, we compressed the backup:

gzip ./backup_filename.sql

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

The entire operation took roughly 10 minutes.