Backing up and restoring MySQL and MariaDB databases using SSH

Backing up a database with mysqldump

The mysqldump tool creates a backup file containing SQL commands that can be used to rebuild the database structure and restore the data. You can then import this backup into a database with the MySQL client to restore the data.

To back up your database with mysqldump, complete the following steps:

• Establish an SSH connection to your webspace. Then carry out the following steps in the terminal window. For instructions on establishing an SSH connection, see the article Connecting to Your Webspace via Secure Shell (SSH) using PuTTY.

• To create a complete backup of your database, run the mysqldump command using the following pattern:

			mysqldump --no-tablespaces --host=HOSTNAME --user=USERNAME --password=PASSWORD DATABASE_NAME > FILE
		

Replace the placeholders in capital letters with the access data of the database you want to back up. Also enter the name you want to give 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 ran the mysqldump command. Depending on the amount of data, this may take some time. You can then download the file and keep it in a safe place.

Troubleshooting

Below you will find suggested solutions for common errors that can occur when carrying out these steps.

Using special characters

If your file names, passwords, or user names contain special characters, you must enclose them in single quotation marks (') in the mysqldump or mysql command.

Example:

mysqldump [...] --password='%gCjmnrmLk!xnSefw'

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 found.

  Suggested solution: Check that the database name is correct. Note that it is case-sensitive.

• mysqldump: Got error: 1045: Access denied for user [...]

  This error occurs when a user tries to log in to MySQL with invalid access data (user name or password).

  Suggested solution: Check that the user name and password are correct. Note that they are case-sensitive.

• 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 tries to perform an action they do not have permission for. Depending on how the backup file was created, it may, for example, also contain an instruction to create a database, which is not possible here but also not necessary. For what to do in this case, see the article "1044 - Access Denied" Error when Importing a MySQL/MariaDB Database.

• 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 or password).

  Suggested solution: Check that the user name and password are correct. Note that they are case-sensitive.

• ERROR 2005 (HY000): 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.

Ignoring MySQL errors

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

Example:

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