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

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

Use '-R' to backup stored procedures, but also keep in mind that if you want a consistent dump of your database while its being modified you need to use --single-transaction (if you only backup innodb) or --lock-all-tables (if you also need myisam tables)

I am using MySQL 5.5.40. This version has the option --all-databases

mysqldump -u<username> -p<password> --all-databases --events > /tmp/all_databases__`date +%d_%b_%Y_%H_%M_%S`.sql

This command will create a complete backup of all databases in MySQL server to file named to current date-time.

In addition to the --routines flag you will need to grant the backup user permissions to read the stored procedures:

GRANT SELECT ON `mysql`.`proc` TO <backup user>@<backup host>;

My minimal set of GRANT privileges for the backup user are:

GRANT USAGE ON *.* TO ...
GRANT SELECT, LOCK TABLES ON <target_db>.* TO ...
GRANT SELECT ON `mysql`.`proc` TO ...

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.

On MySQL 5.7 its work for me, I'm using CentOS7.

For taking Dump.

Command :

mysqldump -u user_name -p database_name -R -E > file_name.sql

Exemple :

mysqldump -u root -p mr_sbc_clean -R -E > mr_sbc_clean_dump.sql

For deploying Dump.

Command :

mysql -u user_name -p database_name < file_name.sql

Exemple :

mysql -u root -p mr_sbc_clean_new < mr_sbc_clean_dump.sql

To create dump follow below steps:

1. Open CMD and go to bin folder where you have installed your MySQL
   ex:C:\Program Files\MySQL\MySQL Server 8.0\bin. If you see in this
   folder mysqldump.exe will be there. Or you have setup above folder in your Path variable of Environment Variable.

2. Now if you hit mysqldump in CMD you can see CMD is able to identify dump command.

3. Now run "mysqldump -h [host] -P [port] -u [username] -p --skip-triggers --no-create-info --single-transaction --quick --lock-tables=false ABC_databse > c:\xyz.sql"
4. Above command will prompt for password then it will start processing.

Use these commands :-

mysqldump <other mysqldump options> --routines > outputfile.sql

If we want to backup ONLY the stored procedures and triggers and not the mysql tables and data then we should run something like:

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt <database> > outputfile.sql

If you need to import them to another db/server you will have to run something like:

mysql <database> < outputfile.sql