How to backup a databases from the command line
This article will show you how to create the command line to export any database in your cPanel account. This command line can also be used as the basis for a scheduled Cron job. This article assumes you are already logged into 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 other 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.
Firstly, the script is going to be run without an interactive SSH session being present. Therefore we need to tell the system exactly where the commands live and exactly where the output file should be placed.
Secondly, we might be fast asleep when the script runs, so the password will have to be entered as part of the command line.
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 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
The above command line would also work as a Cron job command.