As discussed in the post entitled Backing Up Your Database with PhpMyAdmin, there are times when using phpMyAdmin to back up your database will not work. This is especially true if the database is quite large, causing phpMyAdmin to time out. In this case, your best option is likely to be using the command line to back up your database. This requires that you have what’s called “shell access” to your website. Some hosting providers do not permit that, in which case ask them to back it up for you. Here at Brighter Vision, we do allow it, but you’ll need to request it, as it’s not granted by default. The reason is that in the hands of those who don’t know what they’re doing, shell access can pose great danger, & in the hands of those who are knowledgeable but w/criminal intent, it can also pose great danger.
You will also need a terminal client that can be used to log into your website from your computer using something called Secure Shell Access, or, more commonly, SSH for short. Both Linux as well as Mac operating systems have built-in terminal clients that can be used for this purpose. Windows does not, however, so those using it need to download one. Probably the most common is called PuTTY, available free of charge from PuTTY
Choose the proper version of the software, depending on whether your Windows operating system is 32- or 64-bits. The software is a standard Windows installer, so simply install it as you would any program.
To run the program once it’s set up, you’ll need to invoke the command prompt. There are many ways to do this, depending on your machine’s configuration and what you’re personally comfortable with. Perhaps the quickest way is to click the Start Menu on the bottom left of the screen (in most Windows configurations), type ‘cmd’ (without the ‘), in the search box that appears, & press the ‘enter’ key. you’ll be presented w/a prompt that looks like:
C:\users\YourUserName> & a blinking cursor.
Note that YourUserName is clearly a placeholder for just that. (Screenreader users, hit the Windows key or press control+escape to bring up the Start Menu, then type ‘cmd without the apostrophes, & press enter).
Now change to the directory where you installed PuTTY. I chose c:\putty. The command is:
& again press the enter key.
The prompt appears again, except it will look like:
C:\putty> w/that same blinking cursor which, in essence says, “what next?”
- For the host name, type in your domain name if you have one, else type in your IP address.
- For the port, type in your hosting provider’s SSH port number. In the case of Brighter Vision, it’s 22.
- Make sure the SSH button is selected.
- If you wish, you can save the entry for future use (more on that in a moment). To do so, in the ‘Save Session As’ box, type a name for this configuration, i.e., brighter-vision, then click the ‘Save’ button. (Screenreader users, use the tab & shift-tab keys to navigate the dialog box). If you do decide to saved a session, then you can use it later by typing:
putty -l SessionName at the appropriate command prompt, where SessionName is what you called the saved session. A list of sessions will be presented, from which you can choose the one you want.
Type in your username for the site & press enter.
Type the correct password & press enter. Your site now displays a command prompt. Mine looks like this:
Now that you’re at a command prompt, to back up your database, type:
mysqldump -u UserName -p DatabaseName > BackupFileName.sql & press enter. Type in your password at the prompt that appears & again press enter.
UserName, DatabaseName, & BackupFileName are, obviously, placeholders for those values. You will now be able to download the backup file you created from the server, using either your hosting provider’s file manager or an FTP client. You should use encryption for the transfer, & you should delete the file from your server when you’re done downloading it.
You may notice that, while you typed in your username after the -u option, you did not type in your password after the -p switch. That’s because doing so is not secure. There is one other parameter you may need to type in, & that’s the hostname, or -h option. The default is localhost, but, if yours is something other than that, you will need to include it as an option on the command line, as in -h HostName -u Username -p. All of these parameters are found in your wp-config.php file if you use WordPress, settings.php if you use Drupal, & configuration.php when using Joomla!.
While backing up your database on the command line may initially seem intimidating, using the pointers in this article should make it almost a breeze. If you have questions, though, please ask in the comments.