Sunday, May 20, 2007

MYSQLDUMP, mind your options!!

In the process of upgrading my desktop installation of MySQL I noticed that, unlike triggers, stored procedures are not automatically dumped, that's a "not so nice" feature of mysqldump, sic, here is a snippet for those who don't have time to check the manual

C:\Documents and Settings\user>mysqldump -uroot --single-transaction --routines
--databases test remote_test > c:/backup_mysql_20_05_07.sql

-- routines does the trick.
--single-transaction is InnoDB specific for a consistent backup (doesn't apply to MyISAM and other table handlers)
--databases is used because I want to backup only a specific number of databases

Note that after MySQL 5.0.11 triggers are dumped automagically (option enabled by default), use --skip-triggers if downgrading your table structure.

Note also that before 5.0.11 triggers were not dumped and routines had to wait till 5.0.13 (well, 5.0.20 for routines with DEFINER ...) so act accordingly!!!

Another trick, you can use mysqldump to copy database structure from one db or server to another this way:

C:\Documents and Settings\pcuser>mysqldump -uusername -ppassword -d sourcedatabase -n | mysql -uusername -ppassword targetdatabase

This is common on *nix, but it works in windows too.
Key is the
-n no create database
parameter

2 comments:

linuxrunner said...

The bad part is there is no way to dump just stored procedures and not the entire DB. The only workaround is to use the -d option and then edit the resulting dump file... I can't be the only one who needs to backup several stored procedures separately.

Scott Dellinger said...

Ack, I missed that one when I upgraded our servers from 4.1 to 5.0 last week. Fortunately, we don't get frequent restore requests, so I lucked out.

/me adds it to our backup script.

Thanks!