cPanelResources

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:
  1. Login to cPanel with the cPanel username and password.
  2. Click on the MySQL Databases icon.
  3. Enter a database name and click Create Database.
  4. Scroll down to "MySQL Users".
  5. Enter a MySQL username and password, click Create User.
  6. Scroll Down to the "Add User To Database" section.
  7. Select your new user and new database and click Add.
  8. Put a check next to ALL PRIVILEGES.
  9. 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:
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:
  1. Login to your cPanel account via your browser
  2. 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.
Author
cPanelResources
Views
364
First release
Last update