← ServerPilot Docs

How to Import a Database Using the Command Line

If you're not familiar with using SSH, it's easier to import a database using Adminer. However, if your database is very large (for example, more than 1GB), it can be easier to import a database using MySQL from the command line.

These instructions assume you have exported your database using the command line, but will work with any database export file ending in .sql or .sql.gz.

If you haven't already copied the file containing your database export file to your server, you should first use SFTP to copy it to your server. You should SFTP the file to the server using the same system user that owns the app.

Next, SSH into your server as the same user you used to SFTP the file to your server.

Once you've logged in via SSH, you'll want to navigate to the directory where you uploaded your database or exported it. If you followed our guide for exporting your database using the command line, then your database export file will be in the directory you ran the mysqldump command. Unless you changed directories after logging in via SSH, this will be your ServerPilot system user's directory: /srv/users/SYSUSER, with SYSUSER being your actual system user.

The correct command to run to import the database depends on the file extension of the exported database dump.

If the file ends in .sql.gz, use the following command to import the file, where DB_NAME is the name of an existing database you wish to import the export into and DB_USER is your database username that is associated with that database:

zcat DB_NAME.sql.gz | mysql --password --user DB_USER DB_NAME

If the file ends in just .sql (there's no .gz), use the following command to import the file:

cat DB_NAME.sql | mysql --password --user DB_USER DB_NAME

The import commands provided above assume you're in the same directory where the database export file is located. If you're not in the same directory, then you will need to specify the full path (eg. /srv/users/SYSUSER/some-directory/DB_NAME.sql.gz) to the database export file in the command.

Once you run either of the above commands, you'll be prompted for the database user's password. After you've entered the correct password, the import process will begin. If the database is rather large in size, then it may take a while to complete. No output will be returned if the import is successful and you will be returned to the command prompt where you can enter commands again.

Note: The MySQL username and password are not the same as the system user password you used to SSH into your server. If you do not remember your database user's password, you can follow our guide here to reset it.

Last updated: April 5, 2020

Launch your first site in 5 minutes