marcus welz

MySQL Replication for Offsite Backups

Posted on October 19, 2009

I'm cheap. I tend to run pet projects on shoe string budgets. For one, it's just a good habit. It's easier to increase spending when it's really necessary (after some growth and revenue) than it is to turn off a service that you've come to rely on.

Whatever the case may be, having backups is crucial. Most businesses do not recover from a failure that includes data loss if they don't have backups. I have a production server that is running out there somewhere and if it were to go down, I need to be certain that I don't lose my data. It could go down for any reason at any time. Network issues, hardware failure, ISP going out of business, etc. So I want to be sure that I have all my important data where I need it, backed up at home on media under my direct control.

Although MySQL on my production servers is typically configured to only listen on localhost (127.0.0.1), I use SSH port forwarding to access it remotely.

The command for that is rather easy. On my laptop (runs Linux) I use the following command:

$ ssh user@example.com -L 3300:localhost:3306

Aside from using SSH to log into my server it also means that any connection made to port 3300 on my laptop is forwarded to the server, and on that side it'll connect to localhost port 3306, which is where MySQL is listening. That way I can fire up the MySQL Query Browser on my laptop connect to localhost port 3300 and work on my server's MySQL instance. Everything else is transparent, not to mention encrypted.

So with that in mind, I configure my production machine's MySQL server for replication (turn on binlog). And on the slave side (A headless Linux machine I have sitting under my desk at home) I setup key based authentication and use the following command:

$ while [ 1 ]; do ssh user@example.com -L 3300:localhost:3306 -N; echo "reconnecting..."; done

This will keep reconnecting in case of a connection failure. The MySQL slave will keep retrying to get to the master and I don't really have to worry about much.

Print This Post Print This Post
Tagged as: , , No Comments

Primer on SSH and SCP

Posted on November 13, 2004

Hardly anyone uses telnet to log into their UNIX shell anymore. And with good reason; the idea of having your password transmitted in clear text (i.e. unencrypted), thus easily obtained by nosey people equipped with the proper sniffing tools, just isn't all that appealing. The replacement for telnet is SSH, which stands for SecureSHell and became mainstream quite a number of years ago. Popular clients are the commercial SecureCRT, the free Teraterm, and the also free and extremely popular PuTTY.

But while most of us take the usage of SSH for granted, there are still a surprisingly larger number of people that use FTP to copy their files onto their (SSH enabled) web server — even though FTP is just as insecure, transmitting username and password in clear text. Not only that, but having to keep typing in a password (for SSH and FTP) is annoying as well. The solution to this first and foremost insecure problem is very simple:

Key-based authentication

The idea behing key based authentication is rather simple, while the mathematics behind it are a lot more complicated. Let me try to explain it in non-cryptographer terms.

Technically we create a key-pair. The pair consists of a private key, which we keep secret, and a public key, which we send out to others — or the server we want to access in this case. The private and public key are mathematically related, yet it is computationally infeasible to deduce one from the other. Anyone with the public key can encrypt a message but not decrypt it. Only the person with the private key can decrypt the message. Thus, things are fairly secure.

The necessesary tools

For starters, we'll need two tools: PuTTY, a small and free, but powerful SSH client. And WinSCP, an explorer-like SCP client, which supports drag’n’drop, a file upload queue, integrated file viewer, editor, and much more. WinSCP also ships with PuTTYgen and Pageant, of which we'll need both. We could also download PuTTYgen and Pageant from the PuTTY download page directly, but since we'll use WinSCP, too, it would be an unnecessary step.

Download and install both (PuTTY has to be installed manually by copying putty.exe to the harddrive and creating a shortcut on the desktop, or placing putty.exe on the desktop directly.)

SSH key generation

Now let's create the key that will be used to access the server. PuTTYgen is used for this task. It can be found in “Start / Programs / WinSCP3 / Key tools / PuTTYgen”.

Once started, at the bottom of PuTTYgen, change the parameters to SSH2 RSA. You can leave the number of bits set to the default of 1024, or if you're really paranoid, increase the number. 4096 is considered overkill. Click “Generate”. After the key has been generated, change the key comment to “yourname’s Access key”. The comment doesn't really have any functionality other than being helpful if you're using multiple keys. Setting a pass phrase is highly recommended. If you don't, anyone who gains access to your private key will be able to use it. Save the private key to your hard drive. Name it “yourname-access-key.ppk” and, if possible, keep a secure backup copy of it on a floppy, USB memory stick/watch, etc. Keep the PuTTY Key Generator open for the next step.

Configuring PuTTY

I'll use phpwebhosting.com as an example since they're fairly popular. Most other hosting services will work similarly.

Start PuTTY. Enter the hostname of your web server (i.e. zeus.phpwebhosting.com). Select the SSH protocol. In the "Connection" settings, set Auto-login username to your username (i.e. your FTP username). Enter a value of 60 for Seconds between keepalives (Seems to help with certain routers and hosting providers). In the "Connection / SSH / Auth" settings, browse to your private key (your .ppk file we just created during the last step). Then go back to the "Session" settings, type in a session name (e.g. phpwebhosting) and click Save. Double click on the saved session or click on Open to connect to the server.

You should get a prompt similar to this one:

Using username "marcus"
marcus@zeus.phpwebhosting.com's password:

But wait, that's nothing new. How come the server didn't automagically log me in? That's because the server doesn't have a clue what to do with the private key — it doesn't have our public key yet. So, for now type in the account's password to get to the shell.

Telling the server about our new key

The SSH server so far has no way of verifying our private key. We'll first need to give it the public key, which needs to be stored in ~/.ssh/authorized_keys2. Go ahead and use your favorite editor (vi, emacs, pico, nano) to do that. Or, if you want to, try the following method:

First, let's go into the .ssh directory.

No mail.
Last login: Sat Nov 13 12:19:55 2004 from c-24-131-102-112
marcus@zeus:~$ cd .ssh
marcus@zeus:~/.ssh$

Copy the key from the PuTTY Key Generator, and in PuTTY type:

marcus@zeus:~/.ssh$ cat >> authorized_keys2

Now paste your key using SHIFT+INSERT, hit ENTER for a newline, and press CTRL+D, to signal the end of input. That's it.

The server should now be aware of your public key. And just to be sure, do the following:

marcus@zeus:~/.ssh$ chmod 644 authorized_keys2

That will set the permissions so only you have access to the file. Fair Warning: If you skip this step, the SSH server may not want to read your authorized_keys2 file.

Now open a new PuTTY window, and log in again. This time, it should look somewhat like this:

Using username "marcus".
Authenticating with public key "Marcus' Access Key"
Passphrase for key "Marcus' Access Key":

And you'll be prompted to enter your key's pass phrase. If you opted to not use a pass phrase, you should be logged in by now.

Pageant – the PuTTY key agent

To avoid needing to constantly type in your pass phrase for PuTTY (and WinSCP), it is recommended that you setup and use Pageant, the PuTTY key agent. It also can be found in “Start / Programs / WinSCP3 / Key tools / Pageant”. Start it. It will appear in your system tray as a little computer wearing a hat.

Double click on the Pageant tray icon to open the Pageant Key List window. Click "Add Key", and browse to your private key. You will be prompted to provide your password. After entering your password, your key will appear as an entry in the key list. You can now close the Key List window.
Start another PuTTY session to your web host. You should not need to provide your pass phrase anymore. The login process should look somewhat like this:

Using username "marcus".
Authenticating with public key "Marcus' Access Key" from agent
No mail.
Last login: Sat Nov 13 12:56:05 2004 from c-24-131-103-111
marcus@zeus:~$

From now on, just load Pageant, load your private key, provide your pass phrase once, and you'll have easy access to your shell account. This works especially well if you manage multiple accounts using the same key. Of course security advocates will tell you to generate a new key for each account. ;-)

Setting up WinSCP is rather self explanatory. And with Pageant running, logging in is just as simple.

Other programs, such as TortoiseSVN benefit from a key-based authentication setup as well, if you're using the svn+ssh:// protocol.

Print This Post Print This Post
Tagged as: , , , No Comments

Auto-properties in Subversion

Posted on November 12, 2004

I'm a big fan of Subversion and use it for nearly every project I work on. I work on our software projects in several ways: On the Linux development server directly, which I access from a Windows desktop using UltraEdit (Load/Save via SFTP), WinSCP, PuTTY, and the command line svn client. On my Windows PC, with Apache 2, PHP5, MySQL 4 and PostgreSQL 8 installed, using TortoiseSVN. On my Windows Laptop, also with Apache 2, PHP5 and MySQL 4, and TortoiseSVN installed, so I can develop and test anywhere I am.

Not too long ago I noticed that the auto-properties in Subversion weren't applied when adding new files in TortoiseSVN on my windows machines.

On the development server where I work some of the time the /etc/subversion/config file looks like this:

[miscellany]
enable-auto-props = yes
[auto-props]
*.php = svn:keywords=Id
*.phtml = svn:keywords=Id
*.lx = svn:keywords=Id
*.mysql4 = svn:keywords=Id
*.sql = svn:keywords=Id
*.tlx = svn:keywords=Id
*.txt = svn:keywords=Id
*.css = svn:keywords=Id
*.js = svn:keywords=Id

That config file is used by the commandline svn client and makes sure that all file with the listed extensions have their svn:keywords property set to Id.

On Windows, however, the equivalent (actually, the per-user configuration) file is located at C:/Documents and Settings/<username>/Application Data/Subversion/config. This file has the same format as the UNIX equivalent.

If you're not familiar with keyword substitution or the svn:keyword feature at all, you may want to read up on it. I typically stick the following header into every file:

/*
User Signup page
User Management module
Copyright 2004 Lucidix, Inc. All rights reserved.
$Id: signup.lx 2341 2004-11-01 18:04:01Z marcus $
*/
Print This Post Print This Post