Introduction to MYSQLdump

August 23, 2018
By MYSQLGYAN

MYSQLdump is used for taking backup of MYSQL tables and databases.

Basic syntax of taking mysqldump is :-

For Entire Database :-

		
mysqldump -u username -p db_name > backupname.sql

For All Databases :-

		
mysqldump -u username -p --all-databases > all_db_backupname.sql

NOTE :- mysqldump does not dump the information_schema ,performance_schema database by default. To dump these databases, name it explicitly on the command line and also use the –skip-lock-tables option.Information_schema cannot be and should not be dumped as on server where you are going to restore this data will automatically generate this for you.

For Specific Tables Within A Database :-

		
mysqldump -u username -p db_name tablename1 tablename2 > table_backup.sql

Useful mysqldump options :-

For drop database statement and create database statement in mysqldump :-

		
--add-drop-database

For triggers ,routines and events in mysqldump :-

		
--triggers --routines --events

For tables not to be locked during mysqldump :-

		
--single-transaction
--skip-lock-tables

NOTE :-
--single-transaction works with innodb tables only.
--single-transaction produces a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes. Those incoming changes do not become part of the dump.