Backing Up and Restoring MySQL Databases Using SSH

For Web Hosting Linux packages with MySQL databases

This article shows you how to back up and restore your database using the mysqldump tool provided by MySQL.

In contrast to backup via phpMyAdmin or PHP script, this method has the advantage that you are not bound to PHP script limits. This allows backups of databases of any size to be created without additional effort.

Prerequisites

You need a web hosting package with shell access.

Creating a backup of a MySQL database

Step 1

Connect to your web space via SSH.

Step 2

To create a full backup of your database, run the mysqldump command as follows:

mysqldump --no-tablespaces --host=dbxxxxx.hosting-data.io --user=dboXXXXXXX --password=geheimes_Passwort dbXXXXXXX > dbXXXXXXXX.sql 

You can find the access data for your MySQL database in your 1&1 IONOS.

Please note: If your file names, passwords or user names contain special characters, write these in ''-characters, for example password='password'.

A SQL dump is now created. This is a text file containing all the data and instructions required to restore the database in SQL format.

Restore database from backup

To restore a database from a.sql file, run the mysql command on the shell as follows:

mysql --host=dbxxxxx.hosting-data.io --user=dboXXXXXXX --password=geheimes_Passwort dbXXXXXXX < dbXXXXXXXX.sql 

If the SQL dump is packed in gzip format, use the following command:

gunzip -c dbXXXXXXX.sql.gz | mysql --host=dbxxxxx.hosting-data.io --user=dboXXXXXXX --password=geheimes_Passwort dbXXXXXXX