Monday 24 March 2014

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

No comments: