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


Mysql Problem Overview

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

Mysql Solutions

Solution 1 - Mysql

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.

Solution 2 - Mysql

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.

Solution 3 - Mysql

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

Solution 4 - Mysql

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 SELECT ON `mysql`.`proc` TO ...

Solution 5 - Mysql

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.

Solution 6 - Mysql

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)

Solution 7 - Mysql

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

Solution 8 - Mysql

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.


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionMySQL DBAView Question on Stackoverflow
Solution 1 - MysqlNarasimhaTejaJView Answer on Stackoverflow
Solution 2 - MysqlKnut HaugenView Answer on Stackoverflow
Solution 3 - Mysqlajay singh mittalView Answer on Stackoverflow
Solution 4 - MysqljonfmView Answer on Stackoverflow
Solution 5 - MysqlsunilView Answer on Stackoverflow
Solution 6 - MysqlVitaly KushnerView Answer on Stackoverflow
Solution 7 - MysqlM. Hamza RajputView Answer on Stackoverflow
Solution 8 - MysqlRahul MauryaView Answer on Stackoverflow