In part 1 of How to Backup MySQL Database, we've learned basic usage of mysqldump command. In this part we'll see some advanced usage of mysqldump command.
mysqldump command can be used to take backup of database in different ways such as entire database backup, only structure, only data, particular table, exclude particular table, etc.
1. Structure Only:
To take backup of table structure, we need use '--no-data' option. Check the example below:
$ mysqldump --no-data -uroot -p drupal > drupal_dbbackup_structure.sql
The above command will create database backup of structure of tables from database named drupal and skip the data in table.
2. Data Only:
To take backup of table data, we need use '--no-create-info' option. Check the example below:
$ mysqldump --no-create-info -uroot -p drupal > drupal_dbbackup_structure.sql
The above command will create database backup of data of tables from database named drupal and skip the structure in table.
3. Particular table(s):
To take backup of particular table(s) from database we need to include table names after database name. Check the example below:
$ mysqldump -uroot -p drupal node node_revision > drupal_dbbackup_structure.sql
The above command will only take backup of node and node_revision table from database drupal.
4. Exclude Particular table(s):
To skip particular table(s) from database backup, we need to use '--ignore-table' options and provide table names after database name. Check the example below:
$ mysqldump -uroot -p drupal --ignore-table=drupal.cache --ignore-table=drupal.cache_form > drupal_dbbackup.sql
The above command will backup database drupal by excluding cache and cache_form tables.