Database raw files are located in “/var/lib/mysql” directory. You can take backup of the mysql databases using the following command.
mysqldump db_name > db_name.sql
How to Back Up and Restore MySQL® Databases in cPanel
Our goal is to dump a database and download the resulting SQL file to our computer, where we can keep it or move it to a more secure location. It is possible to do this from the command line with the “mysql” client, but the cPanel MySQL backup and restore offers an easy-to-use interface.
Either of these methods can be used to back up databases on your server. For this example we are going to use a WordPress installation and a WordPress Database.. A quick warning before we proceed: a WordPress site is composed of a database and files and a complete WordPress backup must include both.
- irst, select Backup in the Files section of the main page. This page is a useful unified interface for backing up the files and databases associated with your cPanel account.
- Next, find the Download a MySQL Database Backup section, where you will see a list of the databases that you can download.Finally, click on your chosen database to initiate a backup, and cPanel will generate an SQL file and save it to the default download location on your computer.
This is the most straightforward way to back up MySQL in cPanel, but you can also use the integrated phpMyAdmin administration tool to adjust export settings or back up several databases at once. Here’s how to do that:
Back Up a Database in cPanel with phpMyAdmin
Select phpMyAdmin in the Databases section of the cPanel main page.
On the left is a list of databases associated with your cPanel account. Click on the one you would like to back up and then on the Export tab at the top of the page.
In most circumstances, the Quick setting provides the optimal export configuration for MySQL backups, but the Custom setting is useful to exclude tables, rename the exported file, or carry out more advanced configuration. If you would like to export several databases simultaneously, open phpMyAdmin and, without selecting a database in the sidebar, click the Export tab. The default Quick export method will export all available databases. To customize which databases are exported, choose the Custom export method, and select the ones you would like to back up.
Schedule MySQL Backups with Cron in cPanel
We’ve seen how easy it is to back up MySQL databases in cPanel, but what if you want to automatically back up a database on a schedule? It’s all too easy to forget, and scheduling ensures that the safety of your data don’t depend on your memory.
To automate a MySQL backup, you can use the cron task scheduler and the command-line “mysqldump” tool. Navigate to the cPanel Cron Jobs page, which you’ll find under Advanced on the main page.
Use the drop-down boxes to select a time interval, such as once per week.
In the command field, enter the following, replacing the uppercase sections with the appropriate details for your database:
mysqldump -u USERNAME -p PASSWORD database > database_backup.sql
Click the Add New Cron Job button, and you’re done. Cron will run the backup at the indicated time interval, saving the SQL file to the location specified at the end of the command (the part after the > symbol).