[Leaplist] database backup tips?

[Aaron Morrison]

On 30 Jan 2009, at 18:55, Fred Moore wrote:

> Ingo Claro wrote:
>> Jason Boxman wrote:
>>> On Friday 30 January 2009 17:47:25 tom foster wrote:
>>> <snip>
>>>
>>>> * 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?
>>>>
>>>
>>> I'd run it on the backup system and simply use ssh to execute a
>>> remote command:
>>>
>>> ssh user at host 'mysqldump ...' | cat > /tmp/mydump.sql
>>>
>>> I think that'd work, though untested.  It'll pipe the whole thing
>>> over SSH to the local system for you.  (You'll need ssh keys
>>> configured for it to work via cron, though.)
>>>
>>> I do something similar right now as a post-run script with Dirvish.
>>>
>>>
>>>
>> also for not typing a mysql password you can have a .my.cnf file in
>> your home, and there put the user and password for mysql. for  
>> example:
>>
>> /root/.my.cnf:
>> [mysqldump]
>> user = root
>> password = 'the root password'
>>
>> don't forget to chmod 600 the file.
>>
>>
>>
> can you just replicate the mysql server and let it happen  
> automatically
> all the time...  Aaron was telling me how to do this last year.. Fred


FWIW,

mysqldump -h {hostname} -u {username} -p{password} DatabaseName

will connect to a remote host (over port 3306 by default) and dump the  
database DatabaseName as it's (standard) output.  {hostname} can be  
anything that can be resolved into an IP address.  Also note that  
there can NOT be a space between the -p and {password} argument if you  
want to specify the password on the command line. (If you put a space  
there, it will be interpreted as a blank password which causes a  
password prompt.)  Usual caveats apply when providing the password on  
the command line.

Also, MySQL has a very easy and surprisingly robust replication  
mechanism as well.  See http://www.howtoforge.com/mysql_database_replication 
  for a HOWTO on setting this up.

--am