Here very quickly below I would explain how you can backup a large MySQL DB with tables having millions of record on a cPanel server. Also as a bonus I would give details on how you can automate this process on daily basis. The typical problem with backing large databases through phpMyAdmin is that when 100s of megs of sql file is to be generated, in many situation either the process and/or the connection times/maxes out. So therefore below I am mentioning a reliable way to create database dumps.

To begin you will need shell access to your cPanel Account. If you do not have, ask your hosting provider to enable it for you. Once you have shell access, you can connect to your account via SSH protocol using Putty on a Windows Machine or using SSH command from any Mac or Linux Machine. Even if you do not have access these utilities, you can still access the shell from with cPanel -> Advanced -> Terminal

Next you need to create a MySQL backup user. Here I am assuming that you already have an existing MySQL Users which may have been created by an app installer like Softaculous. Softaculous is very commonly used to install WordPress and other such softwares To create MySQL backup user, go to cPanel -> Databases -> MySQL Databases

Under there go to ‘Add a New User’ and type name like backupuser . Importantly the actual MySQ username would be prefixed with cPanel username . So in my case if the cPanel username is lo7 , the actual MySQL username would be lo7_backupuser . Fill in the password and password (again), and click on Create User. Please note down the password in some file as it would be required later.

Now once the backupuser is created, then go to the section ‘Add User to the Database’ . Here you would need to map the user to the existing Databases

And then on the next screen, check ‘All Privileges’ and the click ‘Make Changes’

Now to create. a backup, we have to simply run the mysql dump command with relevant option. Here my actual MySQL backup username is lo7_backupuser and the DB with which i have mapped it is lo7_db1. Here is the actual command

mysqldump -u lo7_backupuser -p --events --triggers --routines --single-transaction lo7_db1 > lo7_db1.sql

The above command will prompt you enter the password for lo7_backupuser , and it will then dump the output to lo7_db1.sql

Now lets improve this process further so that we can automate it. First we have to save the MySQL user cerdentials in a file called .my.cnf within your home directory. Run the below command to open the nano file editor in terminal to create/edit this file.

nano ~/.my.cnf

Just copy paste the following

[client]
user="lo7_backupuser"
password="MySQL_USER_PASSSWORD"

Press Ctrl+O , the. press Y to exit . Voila the MySQL credential file has successfully been created.

As this file contains the credentials, its important to secure this file. To secure it further, we need to set its permissions to 600

chmod 600 ~/.my.cnf

Now we can run the modified mysqldump command without being prompted for the password

nohup mysqldump --events --triggers --routines --single-transaction lo7_db1 > lo7_db1.sql &

Above command will run in background, and once it completes, you will see a done message. Advantage of running in the background is that even if your terminal connection were to get disconnected, the backup file would still be generated.

Now something very important. Even when the mysqdump has completed, you need to verify whether the backup file is broken or not. In some cases if a MySQL table or a view or a procedure has an inconsistent state, the SQL dump file would have data till the point an error occurred. That would result in a broken file which you cannot use for restoring a database.

To verify that your backup file is clean, at the end of the file it should have something like ‘– Dump completed on DATE TIME’ . Now if your dump file is in few KBs it is just easy to read through its contents via cPanel -> Files ->File Manager . But since here we may be dealing with a SQL file which is 10s of MBs or even greater than 1 GB, that option to read it is impractical. So we can use the tail command to show us just the last 2 lines

tail -2 lo7_db1.sql 

And it show the following output in my case

-- Dump completed on 2021-04-16  6:31:08

If it does not show dump completed, then it means there are some inconsistencies in the database or in table(s), and those need to fixed before taking a backup.

The icing on the cake would be to run it every day on certain time, so that we have a backup file created without our intervention.

This can be done by creating a cron job. From within cPanel go to cPanel -> Advanced -> Cron Jobs

And there put in the following values. Under Common Settings, select Once Per Day. This means that this job should run at 00:00 every night. And under command put in the following

mysqldump --events --triggers --routines --single-transaction lo7_db1 > /home/lo7/lo7_db1.sql

Basically what we are doing above is that we are redirecting the output of the mysqldump command to the lo7_db1.sql file which is under /home/lo7 folder i.e. the home directory. Below is the screenshot of how it should look from within cPanel

Now we conclude this article. Using the above techniques you can easily generate backup SQL files for any DB at frequent intervals without human intervention. As a matter of best practices, you should keep an offline copy of this data by downloading it your local PC or laptop.

Here at Pack Web Hosting with our Cloud/VPS Solutions and retail Linux Hosting Solutions , we use our 19+ years of experience to offer you the best experience for your web infrastructure deployment needs. For further consultation/advice, please contact me on 91-98722-63977