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:
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:
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:
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:
You would need to edit the /etc/my.cnf file with a text editor such as vi or nano:
Once you have made the edit, you must restart the MySQL server with the following command:
You can check to ensure that MySQL is listening properly with the following command:
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
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:
You can then issue the following command to import the sql file from your local worstation to the database on your cPanel server:
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:
CentOS
Then issue the following command to import the sql file from your local workstation to the database on your cPanel server:
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.
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.
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:
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
Code:
mysql -u mysqlusername -p databasename < /path/to/mysqlfile.sql
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 = *
Code:
# vi /etc/my.cnf
# nano /etc/my.cnf
Code:
# /scripts/restartsrv_mysql
Code:
[[email protected] ~]# netstat -nlp | grep 3306
tcp6 0 0 :::3306 :::* LISTEN 14107/mysqld
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)"
You can issue the following command via Terminal to install MySQL via brew:
Code:
brew install mysql
Code:
mysql -h databasehost -u mysqluser -p databasename < /path/to/mysqlfile.sql
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
Code:
sudo yum install mysql
Code:
mysql -h databasehost -u mysqluser -p databasename < /path/to/mysqlfile.sql
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.