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
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
-A For all databases (you can also use --all-databases)-R For all routines (stored procedures & triggers)-E For all events--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.
It depends a bit on your version. Before 5.0.13 this is not possible with mysqldump.
From the mysqldump man page (v 5.1.30)
--routines, -R
Dump stored routines (functions and procedures) from the dumped
databases. Use of this option requires the SELECT privilege for the
mysql.proc table. The output generated by using --routines contains
CREATE PROCEDURE and CREATE FUNCTION statements to re-create the
routines. However, these statements do not include attributes such
as the routine creation and modification timestamps. This means that
when the routines are reloaded, they will be created with the
timestamps equal to the reload time.
...
This option was added in MySQL 5.0.13. Before that, stored routines
are not dumped. Routine DEFINER values are not dumped until MySQL
5.0.20. This means that before 5.0.20, when routines are reloaded,
they will be created with the definer set to the reloading user. If
you require routines to be re-created with their original definer,
dump and load the contents of the mysql.proc table directly as
described earlier.
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
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 ...
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.
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)
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:
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.
Now if you hit mysqldump in CMD you can see CMD is able to identify dump command.
