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