Export database from server backup

Foreword

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

Prerequisites

• V-Server
• Authorization to view and restore backups in the customer center
• Existing server backup
• FTP application

Download database file from 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 then navigate to the backup overview of the selected server.

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

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

You will find the required MySQL directory under /var/lib/.

You can use the download symbol at the end of the line to prepare the entire MySQL directory for download.

As soon as the download is ready, the required download information will be provided on an additional page. You will also receive an e-mail with a link to this information page.

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

1. transfer of the MySQL directory and subsequent creation of the database dump

Prerequisites

• Second DB server
• Exactly the same MariaDB version

Implementation

First determine the installed MariaDB version on your server by logging on 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 with the following command:

mariadb -V

You should then receive the following or similar output:

Connect to the second database server and also determine the currently installed version.

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

If MariaDB is not yet installed on the second server (server 2 in the following), you can install this package in the desired version 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

A query of the MariaDB version should now also return the following result:

Stop the MariaDB process on server 2:

systemctl start mariadb.service

Navigate to the location that MariaDB uses to store the databases:

cd /var/lib/

Delete the directory mysql:

rm -rf /var/lib/mysql

Upload the directory mysql from the backup of server 1 e.g. via FTP/SFTP to the directory /var/lib/ and then check the rights.

These should be set as follows:

If the owner and/or group assignment differs, you can correct it with the following command:

chown mysql:mysql /var/lib/mysql

Start the MariaDB server via:

systemctl start mariadb.service

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

Exit the MySQL CLI with the exit statement.

Finally, an SQL dump of the desired database can be created:

mysqldump -u root -p DATENBANKNAME > DATENBANKNAME.sql

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

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

Transfer this again via FTP/SFTP or alternatively using rsync or scp:

rsync:

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

scp:

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

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

Then navigate to the directory to which you have transferred the SQL dump.

This can be used as follows, e.g. on server 1 for restoring the database:

mysql -u root -p DATENBANKNAME < DATENBANKNAME.sql

You will then be asked to enter the password of the corresponding user.

mysql DATABASENAME < DATABASENAME.sql

2. export via automatic DB backup

Prerequisites

• Existing SQL dump

Implementation

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 in which it is located and download it as a single file.

Then transfer this to the corresponding server using FTP/SFTP or rsync or scp.

Command for transfer with rsync:

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

Command for transfer with scp:

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

To be able to restore the corresponding database, the dump must first be unpacked.

To do this, execute the following command:

gzip -d backup_02_08_2024_10_33.sql.gz

Enter the command mysql and then use the SQL command SHOW DATABASES; to check whether the database you want to restore exists. If it does not exist, you can create it with the SQL statement CREATE DATABASE <database name>;.

Replace <database name> with the corresponding name of the database.

Exit the MySQL CLI with the exit statement 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 asked to enter the password of the corresponding user.

mysql DATABASE_NAME < backup_02_08_2024_10_33.sql