How to import a large database into MySQL
This article was posted in: Web Hosting
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 issue can be very frustrating. In cases like this it is better to import the database manually using an SSH (Secure SHell) session.
In order to connect using SSH, please firstly read the article How to install PuTTY for SSH shell access. Then connect to your server using SSH. The rest of this article assumes you have successfully opened an SSH session.
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 file (this often has an .sql extension) - you can upload this file into your home directory using FTP.
- 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 who has full permissions to the database
Here is an example command :
mysql -u krystald_myuser -p krystald_mydata < mydumpfile.sql
- mysql : this is the command to run mysql in interactive mode.
- -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 is it saying take what's on the right and send it to what's on the left!
- mydumpfile.sql : this is the mysql backup/dump file that you want to import (in this case the file is in the current working directory so it does not need to be preceded with a path.)
Just substitute in your MySQL database name, username, and dumpfile name and enter the command! When you hit you will be promted to enter the password for the database user. Just enter it (it won't echo to the screen) blindly and press again to start the import.
Restoring a large database with thousands of hundreds of thousands of rows might take a minute or so to restore, so be patient or go and make a pot of tea - it WILL finish eventually.
What can go wrong
Not much really, but if the import fails or does not complete due to errors, it's easiest just to delete the database from within cPanel (assuming you don't need it!), and recreate an empty one and try again.
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 (by inserting two hyphens -- at the start of each line) or remove them.