Do you need to access a remote server on the Internet through a secure tunnel? Perhaps you need to back up your website database?
You might be interested in an SSH client called PuTTY, a free terminal emulator for executing commands in the command line interface.
Since Windows does not have a default SSH (Secure Shell) capability for remote servers, you might want to use PuTTY. It acts as a great alternative to WordPress plugins or PhpMyAdmin for backing up a WordPress website database using mysqldump.
It doesn’t take too long to master the basic commands.
I wrote a post about manually creating a WordPress backup without plugins. And mentioned SSH.
Installing PuTTY for Windows
Use the following steps to download and install PuTTY on your Windows computer.
1) Create directory for Windows
- Go to C: (or local drive)
- In this directory create a folder named Bin
This folder is where you will upload the PuTTY files.
Note: It doesn’t really matter where you upload the files, but adding this directory makes it easier to keep track of the files.
2) Open the download page in your browser:
3) Download the following files to C: bin folder:
4) For easier access, create a shortcut on your desktop:
- Right click on the desktop and select “New> Shortcut”
- Navigate to the location of your putty.exe file (it should be C: Users yourusername bin putty.exe)
- Save shortcut
Alternatively, right click on the putty.exe icon and drag the cursor to the desktop. You will be prompted to save the shortcut.
Before connecting to the server, you need to log into your hosting control panel and make sure SSH is enabled for the user credentials you will be using.
Basically, you use your FTP username and password – the same details used when accessing a remote server to copy website directories.
When backing up a website, I used the FTP credentials for that website.
In addition, you need to take note of the name of the server to which you will connect, you need to enter this in Host Name field in PuTTY.
You may need to check with your web host or refer to their knowledge base to find the hostname and port settings.
In my case, the server name is jasper.dreamhost.com as shown below:
After opening the terminal window, you need to enter your FTP username and password.
Please note that when entering a password, it will not actually be shown. It may look like nothing is being printed at all. Symbols are not visually printed, so keep that in mind.
If you made a mistake when entering the password, you will see the message “access denied”.
After successfully entering your credentials, you will be greeted with a welcome message:
Error: “The server closed the network connection unexpectedly”
If you get this error, it might be because you left too much time between entering your username and password. If you leave more than 10 – 15 seconds, the software appears to be disabled.
Also, you may have to ask your hosting company to whitelist you IP.
When trying to connect via SSH, if the IP address is not whitelisted, it may be blocked if you continue to connect or disconnect from the server for a short period of time.
Contact hosting support and ask to check for blocking your IP. You can whitelist your IP yourself in most cases.
If you don’t know your IP, visit whatismyipaddress.com.
Back up SQL SSH database
Now you need your MySQL database name and hostname. In addition, you need the username and password associated with the database.
Do not confuse the database username and database password with the FTP username and FTP password.
The command that you would normally enter would look something like this, all on one line:
mysqldump -u [username] -p[password] -h [hostname] [databasename] > [filename.sql]
Obviously you will fill in your credentials instead of the examples. This command tells the server to create a backup SQL in the root folder.
Please note that there is no space between
[password]… This is because any spaces here will mean that the password will be interpreted as the database name for mysqldump.
I prefer to execute the command line as shown below instead, which means that I will enter the password on a new line:
mysqldump -u [username] -p -h [hostname] [databasename] > [filename.sql]
This is how it looks in the PuTTY terminal:
So I want to be prompted for the password on a new line because it remains invisible when I type it.
I don’t want to send plain, visible text over a connection that could lead to a password hijacking. (If I was backing up from localhost like WAMP it wouldn’t matter).
Anyway, assuming your password was correct, you will receive absolutely no sign of success. Ha ha! Seriously, everything you see looks like this:
This means the database is being built and saved. Default
newwebdb.sql placed in the user’s root directory
example… Let me repeat, for the sake of emphasis, the .SQL file will be saved to the root directory of the FTP user, not the root directory of the site.
At the moment I go and make a cup of tea, but the time it takes to build depends on the size of the database.
You need FTP on your web server such as FileZilla (see the guide on the 6 best FTP clients) using the username and password used to access PuTTY at the beginning.
It is assumed that you do not disable FTP access for the user when SSH is enabled, you will be able to get and see the SQL database file.
Here’s what I see in the user’s root directory:
Error 2013 in mysqldump
I seem to be getting a timeout that aborted my database builds.
Here is the error:
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dffh_posts` at row: 4407
I tried GZip format instead of SQL. Apparently my database was causing the timeout because it was so big.
I tried the following command and I was able to finish my backup with no problem. All I had to do. is to download the file to your machine and unzip it.
mysqldump -u [username] -p -h [hostname] [databasename] | gzip > [filename.sql.gz]
The dump was compressed to 10MB and I only needed WinRar to decompress it.
Error 1044 in mysqldump
The database name you enter is case sensitive because we are dealing with a Linux server. Don’t make the mistake of entering all lowercase characters if the database uses some uppercase characters too.
i got error like this:
mysqldump: Got error: 1044: Access denied for user 'example'@'jasper.dreamhost.com' to database 'webdb' when selecting the database
The trick here is to type the database in capital letters. for instance
Error 1045 in mysqldump
The first time I tried to dump the database, I got the following error message:
mysqldump: Got error: 1045: Access denied for user 'example'@'jasper.dreamhost.com' (using password: YES) when trying to connect
I found this error to be annoying and it may have been because I was connecting and reconnecting to the server while running various tests. This type of activity often raises a red flag because spammers or hackers tend to be a problem for hosts.
Backquotes for passwords containing special characters
Another tip is that if your password contains special characters like @ ^, you must end your password within single or double quotation marks?!: