Windows 7, Cygwin with SSH and MySQL
Once you boil it down, this is actually pretty easy. One thing though, we need to have SSH and MySQL installed as part of Cygwin.
To access the local drive from inside Cygwin, we type the following command at the cygwin prompt.
1 | $ cd /cygdrive/c |
That takes us to the root of C:. If your root drive was D:, we would change into that directory using:
1 | $ cd /cygdrive/d |
You get the idea, right? So to dump the database, we will use the MySQL command line tool mysqldump (also part of the MySQL tool in Cygwin). The whole command we’re gonna use looks like this:
1 | $ ssh root@8.8.8.8 'mysqldump -uusername -ppassword -B databasename --lock-tables=false' > /cygdrive/c/wamp/www/dbbackups/db.sql |
That command is going to do a few cool things for us. Let’s break it down.
- First, we SSH into the remote host.
- You will need to authenticate (either with a password or with a pre-shared key).
- Once we’re there, we issue the mysqldump command in single quotes on the remote server.
- Lastly we exit the quotes (the remote command) and put the contents of the command into a local file on our local drive (we save the file with a .sql extension).
That’s all there is to it. SSH on a Windows 7 machine using Cygwin to get a remote database. Easy eh? Or you could just spend £1,800 and get a Mac and leave out Cygwin entirely!
Optional – gzip compression
If you want to reduce the download size and save space on your local drive, you could zip the output on the remote end before download. We just pipe (|) the output through gzip before download and add a .gz extension to the filename. The whole command would then be:
1 | $ ssh root@8.8.8.8 'mysqldump -uusername -ppassword -B databasename --lock-tables=false' | gzip > /cygdrive/c/wamp/www/dbbackups/db.sql.gz |
Happy exporting!