Frequent backups of your MySQL databases should be at the top of your priority list, especially if you have an extremely active dynamic website. This article will cover how to create MySQL Database Backups via the cPanel interface and via Command Line.
Creating MySQL Database Backups in cPanel
-
Login to the cPanel interface for the account owning the MySQL Database that you wish to backup.
-
https://domainexample.com/cpanel or https://yourserversip:2083
-
This method requires use of the cPanel login and password (NOT root)
-
If cPanel access via root is enabled, login to WHM via root, select the cPanel account in reference from ( Home »Account Information »List Accounts )
-
-
Navigate to the Files » Backup Wizard Panel.
-
The first step will prompt you to select or . This one should be self-explanatory, select "Backup".
-
-
- The next step will prompt between a Full Backup or Partial Backups. You'll want to select "MySQL Databases" under Partial Backups.
-
-
-
The final step provides a list of MySQL databases owned by your cPanel account user. Right mouse click on the name of the database that you'd like to backup, then select "Save Link As" (text may differ by the browser). Save the file to your chosen local computer's directory. *Note: This saves the MySQL database to your local computer. It's always a good idea to have a local copy as well, however, keep in mind it may be VERY large depending on tables and rows in the database.
-
Creating MySQL Database Backups via Command Line
-
Login to your server via SSH terminal as the root user.
-
ssh root@yourserversipaddress -p 2200
-
-
Type mysql at the command prompt, to open the MySQL prompt.
-
mysql>should now be at the beginning of your cursor, prompt.
-
-
Type the following command to see a list of all MySQL databases on your server:
-
show databases;-
Here's what my output looked like:
-
+---------------------+ | Database | +---------------------+ | information_schema | | cphulkd | | eximstats | | horde | | khtechs_backupdbtut | | leechprotect | | logaholicDB_kb | | modsec | | mysql | | performance_schema | | roundcube | | whmxfer | +---------------------+ 12 rows in set (0.00 sec)
-
-
For this example, we're going to backup the 'khtechs_backupdbtut' database.
-
-
Exit the MySQL shell by typing
quit; -
You'll now be returned to your previous location on the server, let's check where we are:
-
root@kbtuts [~]# pwd /root
-
So we're in the /root directory. If you want your backup located here, you can skip the next step, otherwise continue as normal.
-
-
Let's make a backup directory for our database and move into that directory.
-
mkdir -p /home/mysql_backups -
cd /home/mysql_backups
-
-
From step 3, you should know the name of your database, remember we're backing up 'khtechs_backupdbtut'. So you'll want to use the following example for your backup:
-
mysqldump khtechs_backupdbtut > khtechs_backupdbtut.sql -
mysqldump <databasename> > <backupname>.sql
-
-
Depending on the size of the database, it could be quick or take a while. Once the prompt returns normally, you can type
ls -lhato make sure it's there.-
root@kbtuts [/home/mysql_backups]# ls -lha total 12K drwxr-xr-x 2 root root 4.0K Oct 27 16:02 ./ drwx--x--x 13 root root 4.0K Oct 27 16:00 ../ -rw-r--r-- 1 root root 1.3K Oct 27 16:02 khtechs_backupdbtut.sql
-
