From: B. Keith Murphy (bmurphyparagon-cs.com)
Date: Fri Dec 14 2007 - 15:32:58 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Jenny Chen wrote:
> Hi,
>
> I'm planing to do hot backup MySQL(innodb db) onto tape drive, and
> propose
> the following solution/script:
>
> # delete old mysql dumps
> rm -r -r /backup/mysql
> mkdir /backup/mysql
>
> # Dump all mysql databases
> mysqldump --all-databases -single-transaction --flush-logs >
> all_databases.sql
> mysqldump --database=mysql --lock-all-tables --flush-logs >
> system.sql
>
> # Do backup
> tar cvf /dev/rmt/0 /backup/mysql
>
> My question is: is there any other better solutions(including commercial
> solutions) to do hot MySQL backup to tape that can provide better
> performance, or can backup directly to the tape(no need to dump to disk
> first), etc. comparing to use mysqldump.
>
> Thanks in advance for your information, or comment on the above solution.
>
>
> Regards,
> Jenny
>
>
You didn't specify if this was a master or slave. I certainly wouldn't
dump off a master server. It will lock the database for the duration of
the backup. One of the ways we do backups is to do an "lvmsnapshot" of
the data partition on the slave and then just mount and rsync the
snaphsot of the data off to the backup server. Inefficient for space,
but would be vastly faster restoring a backup like this then running a
restore from a mysldump. You will still need to tar this take it
acceptable for tape.

Another option would be to replace mysqldump with Baron Schwartz's
mk-parallel-dump and mk-parallel-restore tools. They are faster than a
traditional dump/restore and are much more compact. These tools (and a
number of others) are located at maatkit.sourceforge.net.net.

Hope that helps.

Keith

--
B. Keith Murphy

Paragon Consulting Services
http://www.paragon-cs.com
850-637-3877

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]