How do I import an SQL file into MySQL?

How do I import an SQL file into MySQL?

View printable versionView PDF version
Article 132 (00:01 Sep 6th, 2006)

There are three ways you can import a database file (or SQL) file directly into a MySQL database. However the choice will depend on the size of the database you are attempting to upload.

Import an SQL file via DB WebAdmin in Plesk

Over some of the recent major revisions in phpMyAdmin (which is used to manage the MySQL databases), the location of the import utility has changed. In the version currently installed with both Plesk 7.5.x Reloaded and Plesk 8.0.x is can be found in two places:

  • Log onto Plesk using your username and password.
  • Click on the name of the domain the database belongs to.
  • Click on Databases.
  • Click on the name of the database you want to import the file to.
  • Click on DB WebAdmin.
  • Either:
    • Click on the name of the database (above the list of tables on the left) and on the next page click the SQL tab. Or;
    • Click on the SQL box icon above the name of the database (on the left-hand-side) and click on the Import Files tab.
  • In both cases, the import file field will be shown and you can upload an SQL file to be run against the database.

This may take some time. If the browser has stopped responding, then the upload may have failed - try the method below if this keeps happening.

If you have a large file (more than a few megabytes) then this method is not suitable. The next method will work better.

Import an SQL file directly via the MySQL command-line

If you have a very large database (totaling 10's or 100's of megabytes), then uploading via HTTP is not suitable. Instead, it will be quicker and more efficient to import the file directly into the database using the mysql command-line utility on the server.

However, to do this you will need SSH access to the server. If you do not have SSH access, please see our article:

To import an SQL file:

  • First, upload the file via FTP onto your account (into your private/ directory is best as no-one else can access this).
  • Using the SSH client of your choice, log onto the server. You will start at your home directory (/var/www/vhosts/example.com, where example.com is the domain the SSH account is associated with).
  • Run the following command line:
mysql -u username -p database < filename
  • where username is the username created for database. Finally, filename is the filename (and path, if required) to the file you just uploaded.
  • For example, if you created the database forum, with the username forum (and the password f0rum), and uploaded backup.sql to the private/ directory of your domain, you can run the following command:
mysql -u forum -p forum < private/backup.sql
Enter password:

If you see the error "Access denied for user 'username'@'localhost'", check that you are using the correct username and password - this means that either one of them (or both of them) failed.

Depending on the size (and complexity) of the SQL file, this can take from a few seconds to a few minutes to complete. If you want to cancel command while it is running, press Ctrl & C at the same time.

Also, if you have a zipped or gzipped file, you will need to de-compress it before using it (the mysql command line utility will not understand the compressed file). To do this, run the following:

unzip path/to/file.zip

or

gunzip path/to/file.gz

If you are unsure about this method and would like a member of our Support Team to run it for you, please Submit a Ticket and we will do our best to assist you.