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  gt  backup sql

User · Answer

I am using MySQL 5 5 40  This version has the option --all-databases  mysqldump -u lt username gt  -p lt password gt  --all-databases --events  gt   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

User · Answer

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  lt backup user gt   lt backup host gt     My minimal set of GRANT privileges for the backup user are   GRANT USAGE ON     TO     GRANT SELECT  LOCK TABLES ON  lt target db gt    TO     GRANT SELECT ON  mysql   proc  TO

User · Answer

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  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  Above command will prompt for password then it will start processing

User · Answer

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 · Answer

Use these commands  -  mysqldump  lt other mysqldump options gt  --routines  gt  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  lt database gt   gt  outputfile sql   If you need to import them to another db server you will have to run something like   mysql  lt database gt   lt  outputfile sql

User · Answer

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

User · Answer

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  gt  file name sql   Exemple    mysqldump -u root -p mr sbc clean -R -E  gt  mr sbc clean dump sql   For deploying Dump   Command    mysql -u user name -p database name  lt  file name sql   Exemple    mysql -u root -p mr sbc clean new  lt  mr sbc clean dump sql

User · Answer

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  gt  full backup sql    -A For all databases  you can also use --all-databases  -R For all routines  stored procedures  amp  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  gt  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   gt  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   gt  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

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

Examples related to mysql

Examples related to backup

Examples related to mysqldump

Examples related to complete