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
~$ 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
If you want to backup tables of a database, you can use the following format:
~$ mysqldump [options] db_name [tbl_name ...]
~$ mysqldump -u root -p db_name users products likes orders > users.sql
orders are some tables of database
If you want to backup multiple databases, you can use the following syntax:
~$ mysqldump [options] --databases db_name ...
~$ mysqldump -u root -p --databases db_1 db_2 db_3 > db.sql
db_3 are some databases backed up to
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.