Share via:


Please Refer the previous article.


Using mysqldump utility we can take the backup of only data(ignoring structure )

Check the databases list.

To take the backup of only data we use –no-create-info

Note :

When ever backup is started using mysqldump automatically  the tables has been locked and data will be copied to sql file .

Once the backup backup completed locks has been released.

Sequentially tables has been locked when your trying to take multiple tables.

To know all the options in MySQLDUMP we can use help command.

Here will discuss few more options in mysqldump.


It’s only works for InnoDB Storage engines.

Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multi-versioning

(currently only InnoDB does). The dump is NOT guaranteed to be consistent for other storage engines. While a –single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, as consistent snapshot is not isolated from them. Option automatically turns off –lock-tables.


This causes the binary log position and filename to be appended to the output. If equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will be prefixed with a comment symbol. This option will turn –lock-all-tables on, unless –single-transaction is specified too (in which case a global read lock is only taken a short time at the beginning of the dump; don’t forget to read about –single-transaction below). In all cases, any action on logs will happen at the exact moment of the dump. Option automatically turns –lock-tables

Note : When we use both single-transaction with master-data then tables will not be locked that means Transaction still running on server.

Lets check the content inside the file.

In Above file we able to see

This means our backup is consistent  upto above binary log file  “binlog.000006”.

This  backup will be useful when we setup slave server or replication or point in time recovery .

If  we keep –master-data=2  then change master will be commented in output

Inside file change master will be commented

Note :

By default mysqldump will not take the backup of triggers ,procedures, routines  etc..,

In order to take complete snapshot of your system .

Now the backup contains complete instance  which includes triggers and routines.


Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook :
LinkedIn :
Twitter :
YouTube :
Instagram :

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Add Comment