Backing Up and Restoring MySQL/MariaDB Databases Using SSH

For web hosting Linux packages

In this article, you will learn how to back up and restore MySQL and MariaDB databases via your shell access. To do this, you use the command line programmes mysqldump and mysql, which are already installed as standard in your Linux web hosting packages and on managed servers.

Backup database with mysqldump

The mysqldump tool creates a backup file containing SQL commands that can be used to reconstruct the database structure and restore the data. The data backup created in this way can then simply be imported back into a database using the MySQL client to restore the data.

To back up your database with mysqldump:

• Establishan SSH connection to your web space. Then carry out the following steps in the terminal window.

• To create a complete backup of your database, execute the mysqldump command according to the following scheme:

mysqldump --no-tablespaces --host=HOSTNAME --user=BENUTZERNAME --password=PASSWORT DATENBANKNAME > DATEI

Replace the placeholders written in capital letters with the access data of the database you wish to back up. Also enter the desired name for your backup file.

Example:

mysqldump --no-tablespaces --host=db1234567890.hosting-data.io --user=dbu654321 --password=2gCjmnrmLkxnSefw dbs7654321 > dbs7654321_dump_29-04-2024.sql

Your backup file is created and saved in the directory in which you executed the mysqldump command. Depending on the amount of data, this may take some time. You can then download the file and store it in a safe place.

Restore database with the MySQL client

• Upload the backup file to your web space using an FTP programme.

• Establishan SSH connection to your web space. Then carry out the following steps in the terminal window.

• If your backup file is located in a directory, change to the corresponding directory.

• To restore your data, execute the mysql command according to the following scheme:

mysql --host=HOSTNAME --user=BENUTZERNAME --password=PASSWORT DATENBANK < DATEINAME

Replace the placeholders written in capital letters with the access data of the database in which you want to restore the data.

Example:

mysql --host=db1234567890.hosting-data.io --user=dbu654321 --password=2gCjmnrmLkxnSefw dbs7654321 < dbs7654321_dump_29-04-2024.sql

Backup file is in gzip format (.gz)

If the file is packed in gzip format, combine the mysql command with the gunzip command according to the following scheme:

gunzip -c DATEINAME | mysql --host=HOSTNAME --user=BENUTZERNAME --password=PASSWORT DATENBANK

Example:

gunzip -c dbs7654321_dump_29-04-2024.sql.gz | mysql --host=db1234567890.hosting-data.io --user=dbu654321 --password=2gCjmnrmLkxnSefw dbs7654321

Problem solution

Below you will find suggested solutions for typical errors that can occur when carrying out the instruction steps.

Error messages: mysqldump

• mysqldump: Got error: 1044: "Access denied for user [...] to database [...]
  
  This error occurs, for example, if the specified database name cannot be assigned.
  
  Suggested solution: Check whether the database name is correct. Pay attention to upper and lower case.
• mysqldump: Got error: 1045: "Access denied for user [...]
  
  This error occurs when a user attempts to log in to MySQL with invalid access data (user name and/or password).
  
  Suggested solution: Check whether the user name and password are correct. Pay attention to upper and lower case.
• mysqldump: Got error: 2005: "Unknown MySQL server host [...]
  
  This error occurs when MySQL cannot establish a connection to the specified database server.
  
  Suggested solution: Check the host name for typing errors.

Error messages: MySQL client

• ERROR 1044 (42000) at line X: Access denied for user [...]
  
  This error occurs when a user attempts to perform an action for which they are not authorised. Depending on how the backup file was created, it may also contain the instruction to create a database, for example, which is not possible here, but also not necessary. What to do in this case is described in the article 1044 - Fixing the "Access denied" error when importing a MySQL databasedescribed.
• ERROR 1045 (28000): Access denied for user [...]
  
  This error occurs when a user tries to log in to MySQL with invalid access data (user name and/or password).
  
  Suggested solution: Check whether the user name and password are correct. Pay attention to upper and lower case.
• ERROR 2005 (HY000): Unknown MySQL server host [...]
  
  This error occurs if MySQL cannot establish a connection to the specified database server.
  
  Suggested solution:: Check the host name for typing errors.

Ignore Mysql error

You can instruct the MySQL client to ignore errors using the -force parameter. This means that even if errors occur, the import process is not stopped. Instead, the client attempts to continue with the next commands or data records.

Example:

mysql –force --host=db1234567890.hosting-data.io --user=dbu654321 --password=2gCjmnrmLkxnSefw dbs7654321 < dbs7654321_dump_29-04-2024.sql