How to import a large database into MySQL
This article will show you how you can import MySQL databases that are too large to upload reliably using the phpMyAdmin tool included in cPanel. Uploading a large database via phpMyAdmin can take a long time, and a dropped connection or other issues can make it very frustrating. In cases like this, it is better to import the database manually using an SSH (Secure Shell) session.
You'll need to connect to your server using SSH to complete this process. We have separate guides on connecting to your server with SSH for PC and Apple Mac.
Two things to sort out first: SSH is not enabled on a new account, so turn it on from My Services in your Client Area, and our SSH port is 722 rather than the usual 22.
The rest of this article assumes you have successfully opened an SSH session. Keep that session open until the import has finished. Our platform will not cut a long import short, but if your connection drops the import stops wherever it had got to, leaving you with a partly filled database rather than a failed one. If that is a risk, use screen as described below.
Importing your database
In order to import your database you will need to have the following information to hand:
- the location and filename of your database dump file (this often has a .sql extension) - you can upload this file into your home directory using SFTP, which is available on every account by default and, unlike plain FTP, encrypts the transfer.
- the name of the database you want to import into - the database must already exist so create it if you haven't already done so.
- a valid database username (and password) that has full permissions to the database
Here is an example command:
mysql -u krystald_myuser -p krystald_mydata < mydumpfile.sql
- mysql : the MySQL command-line client. Because you are feeding it a file rather than typing queries at it, it runs in batch mode - it reads the file, runs everything in it, and exits.
- -u krystald_myuser : this is the mysql user that you configured in cPanel beforehand.
- -p : this tells mysql that you will enter a password manually.
- krystald_mydata : this is the full name of the database you want to import into.
- < : this is the redirection symbol. Here it is saying take what's on the right and send it into what's on the left.
- mydumpfile.sql : this is the mysql backup/dump file that you want to import (in this example the file is in the current working directory so it does not need to be preceded with a path. If your dump file is located in a different folder - either change to that folder and run the command from there or include the full path to the dump file.)
Just substitute in your MySQL database name, username, and SQL dumpfile name. When you hit Return you will be prompted to enter the password for the database user. Just enter it (it won't echo to the screen) and press Return again to start the import.
There is no progress bar. The command simply sits there and returns you to the prompt when it has finished, which - for a database large enough to need this method - can be anything from seconds to a good many minutes depending on its size and how busy the server is. Leave it alone until you get the prompt back.
If your dump file is compressed
Large dumps usually arrive gzipped, with a .sql.gz extension. You can't feed one of those straight into mysql - it would try to read the compressed data as SQL and fail immediately. Either unpack it first:
gunzip mydumpfile.sql.gz
...which leaves you with mydumpfile.sql to import as above, or decompress and import in one go without ever writing the unpacked file to disk:
zcat mydumpfile.sql.gz | mysql -u krystald_myuser -p krystald_mydata
The second is worth knowing if the unpacked file would be large - an uncompressed dump is often several times the size of the .gz, and it all counts against your disk space.
Stopping a dropped connection from ruining it
If your SSH session drops mid-import, the import stops with it. For anything that will take a while, run it inside screen, which keeps the session alive on the server even if your connection dies:
screen -S import
Start your import as normal. To leave it running and return to your ordinary prompt, press Ctrl+A then D. You can close your laptop, lose your wifi, or disconnect entirely, and it carries on. To come back to it later, reconnect over SSH and run:
screen -r import
What can go wrong?
One limit worth knowing about: our MySQL service accepts a maximum of 256MB in any single statement. The size of the dump file as a whole doesn't matter, but if it contains one enormous row - a large file stored directly in the database, for instance - the import will stop with a "packet too large" error. That is uncommon, but it is not something you can work around from your end, so get in touch if you hit it.
By far the most common error is #1044 - Access denied for user. It means the database user you gave the command does not have permission on that database - not that there is anything wrong with your dump file.
If the import fails or stops part-way, the cleanest way to start over is to delete the database in cPanel (assuming you don't need what is in it) and recreate an empty one. One catch: deleting a database also removes your user's access to it. When you recreate it you have to add the user to the new database and grant ALL PRIVILEGES again, or the retry fails with exactly the access denied error above. Our guide on creating a MySQL database and users covers that step.
A common problem with importing MySQL files can occur if the dump file includes instructions to CREATE or USE a particular database name. If such instructions exist (and they will be at the top of the file if anywhere), then either comment them out or remove them. To comment a line out, put two hyphens followed by a space at the start of it, like -- CREATE DATABASE .... The space matters: MySQL only treats -- as a comment when the second hyphen is followed by a space, so --CREATE is a syntax error while -- CREATE is a comment.
