Exporting a Database from a Server Backup

Introduction

This article explains how to use the Customer Center to extract your MySQL databases from a full server backup so you can then transfer them to another system.

Prerequisites

• V-Server
• Permission to view and restore backups in the Customer Center
• Existing server backup
• FTP client

Downloading a Database File from a Server Backup

To extract your databases from the backup, first log in to the Customer Center at account.creoline.com. Then select the desired server under the Server menu item and navigate to the backup overview for the selected server.

Click the file icon next to the Restore button to search the corresponding server backup for the desired MySQL files.

All physical hard drives are initially displayed in the root directory. Navigate to the desired hard drive and then select the partition in the part directory. The files and folders are organized differently depending on the operating system.

You’ll find the required MySQL directory under /var/lib/

Use the download icon at the end of the line to prepare the entire MySQL directory for download.

Once the download is ready, the necessary download information will be provided on a separate page. You will also receive an email with a link to this information page.

To download the file, you must connect via FTPS or SFTP using an FTP application of your choice. Please use the download information provided for this purpose.

1. Transferring the MySQL Directory and Creating the Database Dump

Prerequisites

• Second DB server
• Exactly the same MariaDB version

Procedure

First, determine the installed MariaDB version on your server by logging in to your server via SSH. Alternatively, you can use the web console in our Customer Center. In this example, we are using MariaDB version 10.11.6.

You can display this information with the following command:

mariadb -V

You should then see the following output or something similar:

Connect to the second database server and determine the currently installed version there as well.

If you do not have a second server available, we offer you the option of ordering a server through us with a one-hour contract term.

If MariaDB is not yet installed on the second server (hereinafter referred to as Server 2), you can install the desired version of this package as follows:

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.11"
apt install mariadb-server

Querying the MariaDB version should now also return the following result:

Stop the MariaDB process on Server 2:

systemctl stop mariadb.service

Navigate to the location MariaDB uses to store databases:

cd /var/lib/

Delete the mysql directory:

rm -rf /var/lib/mysql

Upload the mysql directory from the backup of Server 1—for example, via FTP/SFTP—to the /var/lib/ directory, and then check the permissions.

These should be set as follows:

If the owner and/or group assignments are incorrect, you can correct them with the following command:

chown -R mysql:mysql /var/lib/mysql

Start the MariaDB server using:

systemctl start mariadb.service

Enter the mysql command and then use the SQL command SHOW DATABASES; to verify that all databases are displayed:

Exit the MySQL CLI with the exit command.

Finally, you can create an SQL dump of the desired database:

mysqldump -u root -p DATABASE_NAME > DATABASE_NAME.sql

To create a dump of all available databases, you can use the --all-databases parameter:

mysqldump -u root -p --all-databases > DATABASE_NAME.sql

Transfer the file back via FTP/SFTP or, alternatively, using rsync or scp:

rsync:

rsync -az DATABASE_NAME.sql root@sXXXXX.creolineserver.com:/tmp

scp:

scp DATABASE_NAME.sql root@sXXXXX.creolineserver.com:/tmp

You can also specify a directory of your choice instead of /tmp.

Next, navigate to the directory where you transferred the SQL dump.

This can be used, for example, on Server 1 to restore the database as follows:

mysql -u root –p DATABASE_NAME < DATABASE_NAME.sql

You will then be prompted to enter the password for the corresponding user.

mysql DATABASE_NAME < DATABASE_NAME.sql

2. Export via Automatic DB Backup

Prerequisites

• Existing SQL dump

Procedure

If a database dump already exists on your server, you can download it as a single file via the backup overview. To do this, navigate to the directory where it is located and download it as a single file.

Then transfer it to the appropriate server using FTP/SFTP or rsync or scp.

Command for transferring with rsync:

rsync -az backup_02_08_2024_10_33.sql.gz root@sXXXXX.creolineserver.com:/tmp

Command for transferring with scp:

scp backup_02_08_2024_10_33.sql.gz root@sXXXXX.creolineserver.com:/tmp

To restore the corresponding database, the dump must first be extracted.

To do this, run the following command:

gzip -d backup_02_08_2024_10_33.sql.gz

Enter the mysql command and then use the SQL command SHOW DATABASES; to verify that the database you want to restore actually exists. If it does not exist, you can create it using the SQL statement CREATE DATABASE <database_name>;.

Replace <database_name> with the actual name of the database.

Exit the MySQL CLI with the exit command after you have created the database.

You can then restore the desired database with the following command.

mysql -u root –p DATABASE_NAME < backup_02_08_2024_10_33.sql

You will then be prompted to enter the password for the corresponding user.

mysql DATABASE_NAME < backup_02_08_2024_10_33.sql