Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!
cPanelResources

Tutorial How to import a MySQL database on the command line

Importing MySQL databases on the command line.

  1. cPanelResources
    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:
    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:
    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.
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice