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

user_104004 0
Viewed 184613

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
backup mysql
8 Answers

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.
user_1301040

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
user_84905980

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.
user_13923380
Related