MySQL Backups

[beancole]

Hi All,

 

How does everyone backup their MySQL? I had a problem with mine and it was a nightmare. I had to recover the whole server from a backup, this is time consuming 18 HOURS!!

 

I know there a loads of ways to do it but I cant seem to filter though the gumf without adding to an already big headache that is MySQL.

 

The thing I highlighted was if i install a new version how do I migrate the old data into a fresh server. What files should I be backing up. I did a manual backup before I started playing with it which game me a .sql file but could I heckers get it into the new server.

 

Any help would be much appreciated. I know I can just recover the whole lot but I would like to have a quicker solution (i.e. just recovering certain files etc.)

 

cheers,

 

Ben

[Meldrew]

Hmm. I personally use MySQLDump.exe. This dumps all the tables out to an SQL text file that you can then restore on the same server or a different one using (from memory) mysql.exe -u [mysql username] -p [mysql password] [database name (schema)] <[path to .sql file]

 

I usually run the mysqldump from a batch file which then uses 7za.exe to compress the sql file down to a reasonable size.

 

HTH,

 

Meldrew

[benjie]

I use mysqldumper to do mysql back ups. You can run it as a cron job, it auto deletes old back ups for you. MySQLDumper - Backup your MySQL-Database (e.g. forum, guestbooks and online shops)

[Geoff]

My server is a VM with the storage thin provisioned by a NetApp Filer. So I have a separate LUN for Data vs OS. All of MySQLs database lives in /var/lib/mysql which I have etup as a separate LVM volume that goes back to the Data LUN on the NetApp. This is snapshotted hourly (The OS is only a daily snapshot).

 

mysqldump will work fine for upgrades. I still do that here in those circumstances, even with the snapshots.

[unixman_again]

Here are the two crucial lines from my quick & dirty MySql backup bat script. Obviously you need include the appropriate set commands for the vars and I have recovered the db from the dump, so we know it works.

REM yyyy-mm-dd
SET today=%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%

"%mysql_dir%\mysqldump.exe" --user=%bu_user% --password=%bu_pass% --compress --quote-names --extended-insert --create-options --result-file="%bu_dir%\%bu_out%.%today%.sql" "%bu_db%"

[Marci]

Ditto - mysqldump on a per-database basis, piped to gzip, with daily, weekly and monthly retention... run from cron.

 

I never back up the whole server. It takes less than an hour to set up a clean server (inc downloading the distro image), drop in our my.conf and network config files, and restore backups.

[RobD]

I use a sql script that I schedule:

 

BACKUP DATABASE NAMEOFDB TO DISK = 'c:\temp\Backup.bak'
with STATS = 1