You can easily import/export your database using phpMyAdmin menu in cPanel. To do so, follow the instructions here. It allows processing only the databases that do not exceed 50Mb. If your database is bigger, you will need to use SSH commands.
Shell access is disabled on shared servers by default, so if shell access was not enabled for your account previously, first of all, please submit a ticket at our HelpDesk using Hosting – HelpDesk department with the request to enable jailed shell for your account.
The next step is to log into your account via SSH. This tutorial will guide you through this process.
Note that before performing an export or import, you will need to assign a database to a user. You can do it in cPanel > MySQL Databases menu > Add a User to a Database option:
Make sure that you enable All Privileges option:
You can find more instructions on database creation and assigning it to a user here.
Please note that our MYSQL database is on another dedicated MYSQL server , so the host is not localhost.You can get the server address from Control Panel ---> Databases ---> MYSQL database.
In order to import a database, use the following command:
mysql -h mysqlserver -u dbuser -ppassword dbname < /home/lvgvnwr6s/public_html/xxx.sql
In order to export, use this one:
mysqldump -h mysqlserver -u dbuser -ppassword dbname < /home/lvgvnwr6s/public_html/xxx.sql
Explanation of flags within the above import command
mysqldump — writes a copy of a database to a single file. lvgvnwr6_hshs: it is the MYSQL db name , you need to replace it as your mysql db name.
-p--- specifies the MYSQL user password.
-u--- specifies the db username associated with the MYSQL database(Not your FTP or Shell username).
-h--- specifies the MYSQL host name
mysqldump---writes a copy of a database to a single file
< --- it followed the path where your .sql file stored./path/to/file/file.sql: the path to the mysql dump that you are importing to (then a new file will be created) or exporting from your cPanel account. Here you can use both relative and absolute path formats. If you are in the folder with the file, you can just type the file name with the extension. With the absolute path, for example, it will be the following: /home/CPANEL_USERNAME/public_html/database.sql
Let us provide with an example. We will use the following settings:
dbusername: lvgvnwr6_hdhdsk (as a database user), nctests (as a cPanel username)
dbpassword: 123456789s
dbname: lvgvnwr6_hshs
host: mysql7002.site4now.net
path to the file: /home/lvgvnwr6s/public_html/sue.sql
Database import
mysql -h mysql7002.site4now.net -u lvgvnwr6_hdhdsk -p123456789s lvgvnwr6_hshs < /home/lvgvnwr6s/public_html/sue.sql
NOTE: You can also use the following command:
mysql -h mysql7002.site4now.net -u lvgvnwr6_hdhdsk -p lvgvnwr6_hshs < /home/lvgvnwr6s/public_html/sue.sql
This way, you need to enter the password in the next line:
Database Export
mysqldump -h mysql7002.site4now.net -u lvgvnwr6_hdhdsk -p123456789s lvgvnwr6_hshs < /home/lvgvnwr6s/public_html/sue.sql
You can see dump completed info once the process end.
NOTE: You can also use the following command:
mysqldump -h mysql7002.site4now.net -u lvgvnwr6_hdhdsk -p lvgvnwr6_hshs < /home/lvgvnwr6s/public_html/sue.sql
This way, you need to enter the password in the next line: