Re: [tlug] Remote MySQL backup and download

[Josh Glover <jmglov@example.com>]

2009/3/13 Dave M G <dave@example.com>:

> What I want to do is set up a batch script that will log in to each server
> and download a copy of the database, just to keep everything backed up.
> Right now I'm backing up each database manually after each time I make
> changes, but since there are other users making continual changes, I want to
> do something more regular, all encompassing, and automatic.

Use mysqldump with SSH:

: jglover@example.com; mysqldump --help
mysqldump  Ver 10.11 Distrib 5.0.70, for pc-linux-gnu (i686)
By Igor Romanenko, Monty, Jani & Sinisa
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
[...]

: jglover@example.com; for host in host1.domain1.com host2.domain2.com
hostN.domainN.com; do
:;     ssh $host mysqldump -h $host -u username table -p \
:;      >db-backup.$host.sql
:; done

-- 
Cheers,
Josh