How do I import a database backup?

Den här artikeln finns även på: Svenska

To restore a database from our automatic backup solution Acronis, please refer to these instructions.

Sometimes, you need to import a database backup, e.g. from a previous provider. There are two ways to do this, and this guide will detail both methods. Keep in mind that you need to create a database to where the backup should be imported (if you’re not overwriting the content of an existing database). These instructions detail how you create a database.

If you receive an error message stating Access denied even though you’ve entered correct credentials for the database, it may be because your database file contains a CREATE DATABASE statement. The error then arises because you cannot create databases from anywhere else than from cPanel. Make sure that your database file does not contain CREATE DATABASE statements.

If you receive an error message regarding Index column size too large, edit your database file. Go to the row mentioned in the error message: you should find a CREATE TABLE statement there. Add ROW_FORMAT=DYNAMIC to it.

phpMyAdmin via cPanel

1. Begin by logging on to cPanel.
2. Then click the icon phpMyAdmin under Databases.
   
3. Once you’ve entered phpMyAdmin, you will see a list of your databases to the left. Click the one to which you’d like to import your backup.
4. Now, in the menu at the top of the page, click the tab Import.
   
5. Click Browse and select the SQL file you wish to import.
   
6. Select SQL as format, if it’s a SQL file you’re about to import. Finally, click Go at the bottom of the page.
   

If your database file is larger than 200MB, you will not be able to import it via phpMyAdmin. If it too large, try compressing it and see if it becomes smaller than 200MB. If it’s still too large, you need to import it via SSH instead (use the uncompressed file), please read the instructions below.

Via SSH

Begin by uploading your SQL file to your account. You may use the File Manager found via cPanel for this purpose.

Do not upload the file to a folder reachable via the Internet. If you do, anyone could download the database file. Instead, upload it to e.g. the document root of the web hosting account.

Then you need to connect to your web hosting account via SSH to run the import command below. This guide describes how you access your account via SSH.

Finally, run this command:

mysql -u username -p dbname < path/to/mysqlbackup.sql

Replace username with the database username, dbname with the name of the database, and enter the correct path to where you have uploaded the database file. You will then be prompted for the database user’s password.

Keep in mind that the database to where the backup should be imported (if you’re not overwriting an existing one) have to exist before you begin. These instructions detail how you create a database and database user via cPanel.