MysqlDump - A program to take Mysql Backup

In This article we will learn What is Mysqldump? how it can be used with multiple options like add-drop-tables,d,e,w,x,f,no-tablespaces.Lets start with what is Mysqldump.

What is Mysqldump?

Mysqldump client is a utility that performs backup .It can be used to backup a database or a collection of databases for backup or transfer to another MySQL server. The dump typically contains SQL statements to create the table, populate it, or both. However, Mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

Mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used.

You can use Mysqldump in three ways:-

• db_name [tbl_name ...]
• --databases db_name ...
• --all-databases

let's see how to use all the three ways of Mysqldump:-

• db_name [tbl_name ...] : This option should be used,if you are taking backup of a single database with multiple tables.

          shell> mysqldump [options] db_name [tbl_name ...]

• --databases db_name ... :  This option should be used,If we have multiple databases to backup. 

          shell> mysqldump [options] --databases db_name ...

• --all-databases: As the name suggest,This options should be used if you need to take backup of complete database.

          shell> mysqldump [options] --all-databases

Now we will se what lies in the [ options ],Mysqldump supports multiples options which can be used but we will be discussing only few of those options here.The options which are going to discuss are given below:-

• --add-drop-table

• -d

• -e

• -w

• -x 

• -f

• --no-tablespaces

--add-drop-table: Add a DROP TABLE statement before each CREATE TABLE statement.So when every you take a backup of database or a table,It will always drop the existing table before creating the new table.It will help you in case having the same schema design.

-d or --no-data: if this option is used,No content from the table will be dumped.This option will only dump the structure of the table or tables

-e or --extended-insert: Use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded. 

-w or --where: Dump only selected records. Quotes are mandatory.

-x or --xml: Dump the database/databases in the well formed XML. 

-f or --force: it will continue to take the dump of the table even if there is any error.

--no-tablespaces: If the option no-tablespaces is used, it will take not take the dump of table structure,only the content will be backup from the tables.

Browse More Articles

Tweet

Written by Unknown

Experience running high-traffic web services, service configuration, monitoring,troubleshooting and change management.Expertise in Linux/Unix system administration, including configuration, troubleshooting, Python scripting language.