Backup and Download mySQL database backup on a cPanel server
How to take mySQL database backup on a cPanel server
You can take mysql database backup either through cPanel (Frontend) or from server backend (command line)
1. Open a new web browser and log in to cPanel
https://YourServerIP:2083
Or
https://Domain.com:2083
2. Enter cPanel username and password to login
3. Search for “Backup” in the cPanel home page
4. Click on “Backup” under “Files”
cPanel Home >> Files >> Backup
Here you can download backup of the databases in your cPanel account
5. Under “Download a MySQL Database Backup” you can see list of all databases in your cPanel account
6. Click on the database name to download the SQL backup in gz format
SQl backup download will start and you can choose the location on PC to save the sql backup.
Restore MySQL Backup
You can follow the same steps to restore the mySQL database backup
1. Log in to cPanel and click on “Backup”
2. Under “Restore a MySQL Database Backup” you can see the option to upload the database backup
Click on “choose File” and select the database backup from the PC.
3. Click on “Upload” to restore the database
Steps to backup the mySQL database from Server backend
1. Log into your server via SSH as ‘root’ user
2. You can use mysqldump command to create an SQL backup of the mySQL database
mysqldump databasename > databasename.sql
Use the below mysqldump command if you have set mySQL root password on the server
mysqldump -u root -p databasename > databasename.sql
Do the below steps to Restore the mySQL database from server backend
mysql databasename < databasename.sql
OR
mysql -u root -p databasename < databasename.sql
How to Backup and Restore mySQL database using phpMyAdmin
To access phpMyAdmin you must first login to your cPanel account. cPanel account login details will be provided by your webhosting company.
1. Log in to cPanel
Use the below url to access cPanel
http://YourServerIP:2082
OR
https://Yourdomain.com:2083
YourServerIp and Yourdomain.com should be replaced with the actual domain name.
2. In the cPanel login screen you must enter the cPanel username and password to login
3. Search for phpMyAdmin in cPanel search bar
4. Click on “phpMyAdmin” Under “Databases”
5. In the phpMyAdmin page (on the left side) you can see list of databases in your cPanel account
6. Click on the database you want to backup
7. You can see “Export” tab to export tables of the database
8. You can select one of the export method
Custom – display all possible options
Quick – display only the minimal options
If you select custom, you can select the Character set (utf-8, iso-8859-8 etc), compression type (zip, gzipped, bzipped etc), output format (SQL, JSON, CSV, YAML, XML etc) etc. If you want to dump only selected tables then you can choose Custom import method instead of Quick.
9. Select quick option, SQL format and click on ‘Go”.
Choose the directory in which you wish to save the backup.
Now you have saved the backup of the database to the PC. Do the below steps to restore the database.
It is better to restore the database to an empty database. Create a new database in cPanel and restore it. If you are restoring the database to an existing database with tables you might see errors like “tables already exists” etc. If you want to restore to the same database then delete the tables first and then restore.
Restore mySQL database backup to an existing database using phpMyAdmin
You don’t have to do the below steps if you’re restoring the database backup to a new empty databse.
1. Log into cPanel
2. click on “phpMyAdmin” Under “Databases”
3. Select the database to which you’re going to restore the backup
4. Click on “Structure” table and delete the tables in the database
Click on “Check All” to select all tables like shown in the below picture. Select “Drop” from the dropdown.
4. Click on “Import” tab to import the database backup
5. You will see the below message when the import is successfully completed.