How to take complete backup of mysql database using mysqldump command line utility

Question

How can I make a complete backup of mysql database using mysqldump? When I am making a backup, my tables from specified database are only getting backed up. The procedures and functions are not.

Here's the backup command I am using :
(Operating system is Windows Vista.)

mysqldump -u username -p db1 > backup.sql

Answer 1

If you want to take a full backup i.e., all databases, procedures, routines, and events without interrupting any connections:

mysqldump -u [username] -p -A -R -E --triggers --single-transaction > full_backup.sql

1. -A For all databases (you can also use --all-databases)
2. -R For all routines (stored procedures & triggers)
3. -E For all events
4. --single-transaction Without locking the tables i.e., without interrupting any connection (R/W).

If you want to take a backup of only specified database(s):

mysqldump -u [username] -p [database_name] [other_database_name] -R -e --triggers --single-transaction > database_backup.sql

If you want to take a backup of only a specific table in a database:

mysqldump -u [username] -p [database_name] [table_name] > table_backup.sql

If you want to take a backup of the database structure only just add --no-data to the previous commands:

mysqldump -u [username] –p[password] –-no-data [database_name] > dump_file.sql

mysqldump has many more options, which are all documented in the mysqldump documentation or by running man mysqldump at the command line.