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 schedules 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 username -p databasename > backup.sql

Note : Iwhen 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 monute 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:

/usr/bin/mysqldump --opt -u username -ppassword databasename > /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. Note: There are NO SPACES between the -p and the actual password.

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.