[Leaplist] database backup tips?

[John Simpson]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 2009-01-30, at 1747, tom foster wrote:
>
> I want to backup some remote mysql databases on a
> regular basis. I have dynamic dns set up (but no ssh
> keys just yet).  How does this sound?
>
> * dump the database to sql on the remote server.

i'm assuming the database is on a "real" web server, with a fixed IP  
address, and you want the backups to end up on a machine at your  
house, which does not have a fixed IP address.

there are a few different ways you can do this.

(1) if *all* you're backing up is the databases, you can run a command  
like this from home.

$ ssh user at server 'mysqldump -u mysql_user -p mysql_passwd dbname' >  
backup.sql

the potential problem is that anybody who runs a "ps" command on the  
remote server while this is going on, just got your mysql userid and  
password (because they will be on the command line.)

(2) if you're backing up other files as well, is to just grab a copy  
of the raw database files a few minutes before the normal backup takes  
place. this is actually what i used to do on my own server, when i was  
running mysql (i'm now using postgresql and sqlite only.) the  
"mysqlhotcopy" command will assert a write-lock on the entire  
database, copy the raw files while they're in a consistent state, and  
then release that lock- resulting in a fraction of a second during  
which writes within the database are held.

what i used to do is make a "mysqlhotcopy" of all of the databases,  
into a "/backup/cut" directory, then rename "/backup/cut" to "/backup/ 
mysql.YYYY-MM-DD.HHMMSS", then just include that directory as part of  
the normal filesystem backup (which i do using rsync, with "exclude"  
options to keep rsync from trying to backup the live /var/lib/mysql  
directory where the live files are kept.

(3) you can configure mysqld to accept TCP connections, but only from  
the localhost interface, so it can't be reached from other machines.  
edit /etc/my.cnf and look under the "[mysqld]" heading. if you have a  
line saying "skip-networking", comment it out (i.e. put "#" in front  
of it) or remove it. then add the line "bind-address=127.0.0.1".

then, from the remote machine, run this to open an SSH port forwarding  
tunnel:

	$ ssh -L3306:127.0.0.1:3306 -N user at server &

then give a normal mysqldump command, specifying 127.0.0.1 as the  
server. if you're writing a script to do this, don't forget to "sleep  
3" between the "ssh" command above, and this command. you need to give  
it a few seconds to set up the tunnel.

	$ mysqldump -h 127.0.0.1 -u mysql_user -p mysql_passwd dbname >  
backup.sql

then kill the ssh command which created the tunnel:

	$ ps | grep 3306
	12345 ttys001    0:00.02 ssh -L3306:127.0.0.1:3306 -N user at server
	$ kill 12345

also note that if you're running a mysql server on your local machine  
and IT is also configured to allow TCP connections, the "ssh" command  
will fail because port 3306 will already be in use by the local mysqld  
process. you can use a different port for the transfer if you like.  
specify a different local port number in the forwarding, and tell the  
mysqldump command to use that other port number. for example, to use  
3308 instead of 3306...

	$ ssh -L3308:127.0.0.1:3306 ...
	$ mysqldump -h 127.0.0.1 -P 3308 ..

you will notice the ssh command specifies port 3308 for the local end  
of the tunnel, but 3306 for the remote end. make sure you understand  
how this works- port 3308 on your localhost is routed through the  
tunnel and will connect to 127.0.0.1 port 3306 on the machine at the  
other end of the tunnel.


> this is already what I've done in the past, but I'd
> like to script it and let cron do the work.  The
> problems I don't know how to solve:
>
> * I don't know how to give mysql its password unless
>  I'm typing it in by hand.

"man mysqldump" or "mysqldump --help" will explain the "-p" option.

you will notice it talks about a "-h" option. this allows you to run  
mysqldump on your local machine, and it will connect to the remote  
mysql server directly and pull the data across. the problems with this  
are (1) it requires you to configure the mysql server on the "real"  
server to accept connections via TCP, which is a MAJOR security risk  
if not properly managed (firewall rules, binding it to the localhost  
interface, etc.) and (2) the data being transferred is not encrypted  
or compressed. i don't recommend you use the "-h" option unless you  
have some kind of VPN running between the two machines, which provides  
an encrypted link. ssh obviously provides encryption, but it can also  
do compression- read "man ssh" and look for the "-C" option.


> * I'm not sure on which machine to run the
>  cronjob--remote, or local, or both?  one on the
>  remote to dump and compress, one on the local to
>  slurp and decompress and wget?

it's easier to set up a cron job on your machine at home which "pulls"  
the data. it's a lot easier for the home machine to find the server's  
static IP than it is for the server to try and guess what your home  
machine's dynamic IP might be.


> What's a better way, or how should I fix my way up so
> that it can be done without my interaction?

what i'm actually doing myself is a "pg_dumpall" command on the  
server, storing the output in a "/backup/pgdata" directory, and  
running a command to delete all but the ten most recent files. then  
the "/backup" directory is included as part of what my normal  
filesystem backup cron job covers (details on this page.)

http://www.jms1.net/code/rsync-backup.shtml


- ----------------------------------------------------------------
| John M. Simpson    ---   KG4ZOW   ---    Programmer At Large |
| http://www.jms1.net/                         <jms1 at jms1.net> |
- ----------------------------------------------------------------
| http://video.google.com/videoplay?docid=-1656880303867390173 |
- ----------------------------------------------------------------





-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (Darwin)

iEYEARECAAYFAkmEslMACgkQj42MmpAUrRrQpgCgrEpGrYR1BcUGnv6NPcHrk4Df
u/wAoK3QjvD6DK1EatOs1C/QHKOiFusc
=qu5W
-----END PGP SIGNATURE-----

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.