MySQL 5 has introduced some new interesting features, like stored procedures and triggers.
I will show in this small post how we can backup and restore these components using mysqldump.

mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior:

–routines – FALSE by default
–triggers – TRUE by default
This means that if you want to include in an existing backup script also the triggers and stored procedures you only need to add the –routines command line parameter:

mysqldump –routines > outputfile.sqlLet’s assume we want to backup ONLY the stored procedures and triggers and not the mysql tables and data (this can be useful to import these in another db/server that has already the data but not the stored procedures and/or triggers), then we should run something like:

mysqldump –routines –no-create-info –no-data –no-create-db –skip-opt > outputfile.sql
and this will save only the procedures/functions/triggers of the . If you need to import them to another db/server you will have to run something like:

mysql < outputfile.sql

========== basic dump ============

shell> mysqldump –opt db_name > backup-file.sql

You can read the dump file back into the server like this:

shell> mysql db_name < backup-file.sql

Or like this:

shell> mysql -e “source /path-to-backup/backup-file.sql” db_name

mysqldump is also very useful for populating databases by copying data from one MySQL server to another:

shell> mysqldump –opt db_name | mysql –host=remote_host -C db_name

It is possible to dump several databases with one command:

shell> mysqldump –databases db_name1 db_name2 … > my_databases.sql

If you want to dump all databases, use the –all-databases option:

shell> mysqldump –all-databases > all_databases.sql

Posted in Uncategorized
Comments Off Posted by admin