[geeks] Mysql backup questions...

[Brian Dunbar]

On Apr 11, 2005, at 11:16 PM, Bill Bradford wrote:

> So, this is what I'm using to backup MySQL on a nightly basis (this 
> file
> is then rsynced to an offsite machine).  Any tips on optimization or
> improvements?  I'm running 4.10.
>
> #!/bin/bash
> cd /usr/local/mysql/backup
> DATE=`date +%m-%d-%y`
> /usr/local/mysql/bin/mysqldump --password=password --create-options 
> -qAcCe --allow-keywords --add-drop-table > 
> /usr/local/mysql/backup/mysql-$DATE.sql
> gzip --best mysql-$DATE.sql
>
> -- 
> bill bradford
> houston, texas


This is how my host provider recomends that you backup / restore your 
database

backup
mysqldump --opt -uusername -ppassword -h yourMySQLHostname dbname > 
filename.sql

restore
cat filename.sql | mysql -uusername -ppassword -h yourMySQLHostname 
dbname


My backup scripts do this

mysqldump --opt -uusername -ppassword -h yourMySQLHostname dbname  | 
gzip > filename.gz

I see up-thread that some people halt their database, run the backup 
and start the database.  Is halting a MySQL database required?  I've 
restored several times using the above method, no failures noted yet.


Brian Dunbar
System Administrator II
Desk: (920) 751-3364
Cell: (920) 716-2027
brian.dunbar at plexus.com
http://www.plexus.com