Using mysqldump in command line: Tutorial for beginners

mysqldump command utility is used to take copy of one or more MySQL databases for backup or transfer to another server. Using this command you take a dump of a database in different formats like SQL, CSV, text and XML.

A simple dump operation can be done using following command:

mysqldump -u username -p databaseName > "filename.sql"

This command will prompt for password and once you enter the password a copy of that entire database will be generated and saved with the given filename.

Now I will go deep into some very usefull mysqldump options and how to use them in real world with examples.

1. Take dump of all databases

To take backup of all databases you can simply use the option name --all-databases .

mysqldump -u username -p --all-databases  > "filename.sql"

2. Take dump of single table

To do this you just need to specify the table names.

mysqldump -u username -p databaseName tableName > "filename.sql"

3. Take dump without create table statement

For this you can use --no-create-info

mysqldump -u username -p --no-create-info databaseName tableName > "filename.sql"

4. Take dump of a table with where condition

--where option is used for this.

mysqldump -u username -p --no-create-info databaseName tableName --where='id <184310'  > "filename.sql"

5. Take dump of table with custom query

Some times we need to apply complex logic when creating dump. We can implement custom query or multiple joins with mysqldump command like below.

eg 1:

mysqldump -u username -p  --lock-all-tables databaseName table1 --where="table1_id in (select table1_id from table2 where table2_column <100000)" > "filename.sql"

eg 2:

mysqldump -u username -p  --lock-all-tables databasename table1 --where="table1_id in (select table1_id from table2 where table2_column<100000) AND table1_id NOT IN (select table1_id from table2 where table2_column>50000)" > "filename.sql"

Change this example according to your need.

For more mysqldump options visit mysql site.

If you don't familiar with command line then you can use phpmyadmin to export database. But command line is way faster than phpmyadmin. If you have a larger database then use mysqldump command line utility.