How to import a database through shell
When you are importing a database over 50MB, you will run into the limitation in your PhpMyAdmin that prevents you from importing a database over 50MB. On shared servers you can try installing your own PhpMyAdmin to import it or you will need to contact tech support to import it for you.
On VPS and Dedicated servers, you can install your own PhpMyAdmin or you can import the database using shell. This article will explain how to import a database through shell on a VPS or Dedicated server.
Important! You must first have a database created like the image to the right.
Steps to Import a database through shell
Note! This is for VPS and Dedicated servers only.
- Login to your server via SSH.
- Go to the directory your backup .sql file is located in. For example, if your database backup is named new_databse.sql in your cPanel home directory for userna5, you will change to that directory like the following command.
- Check the directory for your .sql file by typing
ll. You should see an output similar to below:
root@vps#### [/home/userna5]# ll total 328 drwx--x--x 21 userna5 userna5 4096 Apr 30 15:40 ./ drwx--x--x 17 root root 4096 Dec 6 16:28 ../ lrwxrwxrwx 1 userna5 userna5 34 Apr 14 2011 access-logs -> /usr/local/apache/domlogs/userna5/ drwxr-xr-x 2 userna5 userna5 4096 Oct 9 2012 bin/ -rw-r--r-- 1 root root 201467 Apr 30 15:40 new_databse.sql -rw-r--r-- 1 userna5 userna5 4264 Apr 30 12:10 error_log drwxr-x--- 3 userna5 mail 4096 Feb 22 09:02 etc/ drwxr-x--x 9 userna5 userna5 4096 Feb 21 09:53 mail/ drwxr-xr-x 3 userna5 userna5 4096 Mar 27 11:26 perl/ lrwxrwxrwx 1 userna5 userna5 14 Mar 27 11:26 perl5 -> perl/usr/local/ drwxr-xr-x 9 userna5 userna5 4096 Oct 9 2012 php/ drwxr-xr-x 3 userna5 userna5 4096 Apr 14 2011 public_ftp/ drwxr-x--- 47 userna5 userna5 4096 Apr 26 13:17 public_html/ drwxr-xr-x 10 userna5 userna5 4096 Apr 23 11:30 tmp/ lrwxrwxrwx 1 userna5 userna5 11 Apr 14 2011 www -> public_html/
- Next you can Import the database.
user_name: This is your cPanel username or the database user you created and added to the database.
data_base: This is the name of the database. In this example its userna5_tester.
new_databse.sql: This is the name of the database you want to import into your empty database on your server.
The general syntax for the command is as follows:
mysql -p -u user_name data_base < new_databse.sql
In this example we will use the cPanel Username. Hit Enter.
mysql -p -u userna5 userna5_tester < new_databse.sql
- You will be asked to enter your password like the following. Enter the users password. (For the cPanel user you use the cPanel password. If you added a user to the database, you will use that users password.)
root@vps#### [/home/userna5]# mysql -p -u userna5 userna5_tester < new_databse.sql Enter password:
Support Center LoginOur Login page has moved, Click the button below to be taken to the login page.
2015-09-20 7:10 pm
This worked well but it does require uploading a sometimes large .sql file to our dedicated server. Would this work if I imported the database directly from our local computer by adding the -h (--host=) option to the mysql command?