Tutorial - How to import a MySQL database on the command line

Prerequisites
In order to import a database via the command line, you will first need to ensure that you have the database and database user created. You can do so with the following steps:

Login to cPanel with the cPanel username and password.
Click on the MySQL Databases icon.
Enter a database name and click Create Database.
Scroll down to "MySQL Users".
Enter a MySQL username and password, click Create User.
Scroll Down to the "Add User To Database" section.
Select your new user and new database and click Add.
Put a check next to ALL PRIVILEGES.
Click Make Changes.

Now that you have a database and user, you can use one of the following options to import the database.

Upload, Then Import (Any Operating System)
NOTE: To use this method, you must ensure that Shell Access has been enabled for the account.
NOTE: To use the Terminal icon, the Terminal feature must be enabled for your account. You can enable this feature via the Feature Manager in WHM if needed.

You will first need to upload the SQL file to your cPanel account by using FTP or SFTP access.

When uploading the file, avoid putting the file in the /home/cpanelusername/public_html directory. Preferably, you should place the file in a directory that is not publicly accessible such as:

/home/cpanelusername

Once you have the file uploaded, you can either use the Terminal icon in cPanel to execute the import command, or you can use SSH.

To connect via SSH you would issue the following command on your Mac or Linux computer:

Code:

ssh [email protected]

You would then need to enter your cPanel password. That should bring you to the bash prompt on your cPanel server.

To get to the Bash prompt via the terminal icon (Recommended for Windows users) use the following steps:

Login to your cPanel account via your browser
Click on the Terminal icon

Now that you are at the bash prompt issue the following command to import the database that you have previously uploaded:

Code:

mysql -u mysqlusername -p databasename < /path/to/mysqlfile.sql

Preparation for Importing Directly with the MySQL Client
Importing directly with the MySQL client requires that MySQL is listening publicly. Enabling public access to the MySQL server can be a security risk. Please consult with a qualified systems or security administrator before allowing MySQL to listen publicly.

If you would like to allow MySQL to listen publicly you must update or add the bind-address directive to the /etc/my.cnf file and restart the MySQL server.

An example of this directive is shown below:

Code:

[[email protected] ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
max_allowed_packet=268435456
bind-address = *

You would need to edit the /etc/my.cnf file with a text editor such as vi or nano:

Code:

# vi /etc/my.cnf
# nano /etc/my.cnf

Once you have made the edit, you must restart the MySQL server with the following command:

Code:

# /scripts/restartsrv_mysql

You can check to ensure that MySQL is listening properly with the following command:

Code:

[[email protected] ~]# netstat -nlp | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      14107/mysqld

NOTE: The above output shows that mysqld is listening on tcp6. This includes the IPv4 protocol as well.

You must also ensure that your workstation's IP address is whitelisted in cPanel >> Remote MySQL.

Importing Directly with MySQL Client On MacOS
First install Brew on you Mac workstation: Homebrew

Code:

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

Then, using brew, install MySQL. This installs the MySQL server on your mac computer in addition to the MySQL client. If you prefer to install only the MySQL shell, you'll need to use different instructions. I prefer to use the normal MySQL client because it uses the standard command syntax.

You can issue the following command via Terminal to install MySQL via brew:

Code:

brew install mysql

You can then issue the following command to import the sql file from your local worstation to the database on your cPanel server:

Code:

mysql -h databasehost -u mysqluser -p databasename < /path/to/mysqlfile.sql

You should replace databasehost with the domain name or the IP address of the server where your database server is hosted. You should replace mysqluser and databasename with the user and database name that you created above. And finally you should enter the path on your computer where the sql file is stored that contains your database to import.

Importing Directly with MySQL Client On Linux
Use the following to install the MySQL client for the version of Linux that is running on your local workstation (not your server):

Ubuntu:

Code:

sudo apt-get install mysql-client

CentOS

Code:

sudo yum install mysql

Then issue the following command to import the sql file from your local workstation to the database on your cPanel server:

Code:

mysql -h databasehost -u mysqluser -p databasename < /path/to/mysqlfile.sql

Questions/Feedback
Feel free to click on the Discussion tab to let us know if you have any trouble with the instructions in this tutorial, or have additional questions and feedback.

Search

Search

Tutorial How to import a MySQL database on the command line

Share this resource