Some Mysqldump Techniques

The mysqldump client is  used to dump a database or a collection of databases for backup or transfer to another SQL server . The dump typically contains SQL statements to create the table, populate it, or both. 

Also, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

 

 

Here i have given the various techniques to take a mysqldump based on our needs.

 

To dump all databases;

          $ mysqldump --all-databases > backup.sql 

To reload all databases

          $ mysql < backup.sql

          from mysql server,use ‘source’

          mysql > source backup.sql

In order to get event table also,

          $ mysqldump --all-databases --events > backup.sql 

To dump a particular database,

          $ mysqldump --database db1 > db1.sql

To reload it,

          Create a database using mysqladmin

          $ mysqladmin create db1

          $ mysql  db1 < db1.sql

Alternately within mysql server,

          mysql > create database if not exists db1;

          mysql > use db1;

          mysql > source db1.sql

To dump specific databases,

          $ mysqldump --databases db1 db2 db3 > dump.sql

To reload it,

          $ mysql < dump.sql

To dump a particular table in a database

          $ mysqldump --database db1 --table tb1 > tb1.sql

To reload it,

          $ mysql db1 < tb1.sql

To dump specific tables

          $ mysqldump --database db --tables tb1 tb2 > tables.sql

To reload them,

          $ mysql db1 < tables.sql

To dump stored programs (trigger,events,stored routines)

          $ mysqldump --all-databases --events –triggers --routines> backup.sql

They can be skipped using

          $ mysqldump --all-databases –skip-triggers > backup.sql

 

To dump definition and data separately

          mysqldump --no-data db_name > backup.sql

                   -With no data only create information will be there

          mysqldump --no-create-info db_name > backup.sql

                   -With no create info only datas  will be present

Hope it helped all,......... Thank you..................