Exporting MySQL Data Using SSH
It is good idea to export Mysql database using SSH command in cpanel. There are various methods to export and dump database but you can easily create a database backup using the following command.
First, you have to log in to the server using SSH and as the root user. Then you need to run following command to get database backup. Please note that in cpanel server you can locate your database here, /var/lib/mysql.
mysqldump -p -u username database_name > dbname.sql
You will be prompted for a password, type in the password for the username and press Enter. Replace username, password, and database_name with your MySQL username, password, and database name.
The file databasename.sql now holds a backup of your database and is ready for download to your local computer.
If you want to export a single table from your database you would use the following command:
mysqldump -p –user=username database_name tableName > tableName.sql
Again you would need to replace the username, database and tableName with the correct information.
Once done the table specified would then be saved to your account as tableName.sql
Import A MySQL Database
The file must be in .sql format. It cannot be compressed in a .zip or .tar.gz file. Start by uploading the .sql file onto the server
If you haven’t already done so, create the MySQL database via the cpanel.
Once you are done with creating database follow below steps:
Using SSH, navigate to the directory where your .sql file is.
Next run this command:
mysql -p -u username database_name < file.sql
To import a single table into an existing database you would use the following command:
mysql -u username -p -D database_name < tableName.sql