Backing up databases is one of the important parts of running apps or services that maintain huge amount of data. Established businesses would use automated softwares like Cloudberry, iperiusbackup or Goodsync. But often times you may come across situations where you may like to do manual backups using command line.

In Linux, we use the mysqldump client utility program to do manual backup. This program performs logical backups. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

As a quick reference, the following command produces a backup of db_name database to db_name.sql file.

~$ mysqldump -u root -p db_name > db_name.sql

And as evident, to execute this command you have to be a root user. The most common syntax of mysqldump command looks as follows:

~$ mysqldump [OPTIONS] [DATABASE NAME] > [BACKUP FILE NAME]

To see the list of options supported by mysqldump, just run the command mysqldump --help.

If you want to backup tables of a database, you can use the following format:

~$ mysqldump [options] db_name [tbl_name ...]

For example:

~$ mysqldump -u root -p db_name users products likes orders > users.sql

Where users, products, likes and orders are some tables of database db_name.

If you want to backup multiple databases, you can use the following syntax:

~$ mysqldump [options] --databases db_name ...

For example:

~$ mysqldump -u root -p --databases db_1 db_2 db_3 > db.sql

Where db_1, db_2 and db_3 are some databases backed up to db.sql file.

And if you want to backup the entire databases, you can use the following syntax:

~$ mysqldump [options] --all-databases

Now if you want to compress your database file, you may use the -C options as shown below:

~$ mysqldump -u root -p -C db_name > users.sql.tgz

This post is a part of Tips and Tricks series of Hoineki.com. Hope you found something useful.