mysqldump - MySQL Database Backup Program

To see available option run the command: "mysqldump --help" or "mysqldump -?"

There are 3 general ways to invoke mysqldump (note: You need to have MySQL running):

c:\> mysqldump [options] db_name [tables]
c:\> mysqldump [options] --databases db_name1 [db_name2 db_name3...]
c:\> mysqldump [options] --all-databases or c:\> mysqldump [options] -A

If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are dumped.

ex: mysqldump --add-drop-table -u[username] -p[password] [database] > [backup_file]

To dump all your tables (for  upgrading to 4.1 for example) you can create a backup40.bat file containing:

mysqldump -uroot --password=YourPassword -hlocalhost --all-databases --opt --allow-keywords ^
--flush-logs --hex-blob --master-data --max_allowed_packet=128M --quote-names --result-file=BACKUP_MYSQL_4.0.SQL

Note: You can also perform backups using MySQL Administrator.
Also using full paths (ie "c:\YourPathToMySQL\bin\mysqldump" ) might better since you can then execute the batch file from multiple locations.

Restore (you need to have MySQL running to restore):

mysql -u [username] -p[password] [database] < [backup_file] 

Example: mysql -u user -p dbname < {path}/BACKUP_MYSQL_4.0.SQL

Note: You can also restore using MySQL Adminstrator. I'm getting some error on certain tables...would not bank on this method yet...maybe works better when restoring a similar MySQL DB version (ie 4.1.x to 4.1x ....etc...).

Note: If no character set is specified, mysqldump from MySQL 4.1.2 or later uses utf8, and earlier versions use latin1.
When upgrading from to MySQL 4.1, you might loose your accents unless your indicate the character set, like:

"C:\Program Files\MySQL\MySQL Server 4.1\bin\mysql" -uroot 
--password=YourPassword -f --default-character-set=utf8 < "E:\_NoVeritas\BACKUP_MYSQL_4.0.SQL"

---

Reference

mysqldump — A Database Backup Program