How to properly set-up an SSH tunnel on FreeBSD for automatic backups

[Matt Crossley]

Olaf Greve wrote:

> Hi all,
>
> Thanks for the replies!
>
> I also received several very helpful off-list replies, and they caused
> me to opt for my plan B, which is simply a 'rendez vous' type
> pull-mechanism. I already had a nightly cron job set up on the live
> server that neatly dumps the MySQL DB instances to a convenient
> directory. Also, I already had set up Rsync such that it only ever
> allows connections from my fall-back machine (I use this for the
> nightly file syncing), so I just decided to add an additional DB
> import script which is scheduled well after the DB dumping on the live
> machine takes place. Those dumps are now picked up using rsync (called
> from the fall-back machine) and they are then simply locally processed
> and worked into my DB.
>
> The main reason for deciding for this mechanism after all is that for
> two normal users I want (and need!) to have SSH access enabled from
> all over the world, using a client like PuTTY, using password
> authentication, yet opening up SSH in anyway for root is for me a no-no
> (I see too many SSH hack attempts in my daily security reports in
> order to feel comfortable with that ;) ).
>
> O.k., at the expense of having a perhaps slightly less elegant DB
> syncing mechanism I have opted for the above and I just tested it and
> it works fine...:)
>
> Cheers,
> Olafo
> _______________________________________________
> freebsd-questions at freebsd.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-questions
> To unsubscribe, send any mail to
> "freebsd-questions-unsubscribe at freebsd.org"

You may also want to look into MySQL replication, which is pretty easy
to set up. There are some instructions available on the MySQL site here:
http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html although
you need MySQL 4.1 at a minimum. This will allow for near-instant
replication of your MySQL databases, rather than one that is set on a
schedule, which would reduce the data loss in the case of a disaster.
You could implement something similar to an SSH tunnel for tunnelling
the mysql traffic if you so wished, as well.

<shameless plug>
I have written a quick little howto on my website, that discusses a
similar project. I had started to write it a little while ago, but I
forgot about it. Your post reminded me that it was still sitting around
unfinished! You can find it at
http://www.freebsdfreaks.net/articles/postfix_secondary_mx_mailbox_replication_mysql_replication_under_freebsd_failover_mx.html
. I use this system on our mail servers, and it works quite well. If we
ever encounter a problem, I have several options for allowing clients to
still receive their email.
</plug>


Cheers,
Matt

http://www.freebsdfreaks.net/index.php