How to backup a databases from the command line
This article will show you how to manually create a backup of your databases via. SSH and through a cron job. This article assumes you are already logged in to cPanel, and that you already have your Windows or Mac computer setup for SSH access, and that you understand directories and files reasonably well.
Creating a one-off backup via SSH
Using PuTTY or another SSH client, log into your SSH account. Once logged in, just enter the following command, changing mysqluser and mysqldatabase for the MySQL database name and username you have configured in cPanel.
mysqldump --opt -u mysqluser -p mysqldatabase > backup.sql
When running the command with -p you will be asked to enter the password manually each time you run the command. The output file, in the above case backup.sql, will be created in the present working directory. On large databases, this might take a minute or two to complete (or longer) - just wait for it to finish.
Automating a MySQL backup from a script or Cron job
If you want to backup a MySQL database as part of a shell script, or Cron job, then there are a few extra considerations.
Most importantly, this script will run in the background of your day to day website tasks and therefore will need to be configured to tell the system exactly where the commands live and exactly where the output file should be placed.
As this can run at any time of the day, the password will also need to be stored so the script can run even whilst we're playing Tennis or asleep!
The command needs to look like this example:
Change mysqluser and mysqldatabase for the MySQL database name and username you have configured in cPanel. Change the password for the database users password. And finally, change cpanelusername for your cPanel accounts username.
/usr/bin/mysqldump --opt -u mysqluser -ppassword mysqldatabase > /home/cpanelusername/backup.sql
Here, we are being explicit about where mysqldump is installed on the server. We are also being explicit about where the output file will be created - in this case, in the root of your cPanel account home directory.
The above works fine provided your password only uses letters and numbers. If you have included special ASCII characters (brackets, punctuation, @$%& etc) then you must enclose the password in single quotes. For example, if the cPanel account username is krystald, the mysql username is krystald_fred, the database name is krystald_joomla, and the password is tY$645=&nm and you want to dump the file in your home directory root then the command would be:
/usr/bin/mysqldump --opt -u krystald_fred -p'tY$645=&nm' krystald_joomla > /home/krystald/backup.sql
You can also add this command as a cron job, Setting up CRON jobs through these steps!
