Useful mysqldump MySQL Database Backup Examples

The Linux mysqldump command is a mysql client use to backup MySQL/MariaDB Databases. Typically mysqldump command use to export MySQL database to into a text file as a backup, But it has several other options. For example, we can use mysqldump command to backup tables from one database to another or we can backup database from one MySQL Server to another.

In this tutorial we will learn Some useful mysqldump database backup options with examples.

1. Backup Single Database.
2. Backup Multiple Databases.
3. Backup All Databases.
4. Backup One or more tables from a Database.
5. Exclude specific tables from the mysqldump.
6. Backup Database from a Remote MySQL Server.
7. Backup Database to Another database.
8. Backup Database to a remote MySQL Server.

mysqldump command options

Two most important command options are -p (Password) and -u (Username) option.

--password[=password], -p[password]

The -p option use to specify The password to use when connecting to the MySQL/MariaDB server.

mysqldump -p'password' database_name > backup-file-name

Or

mysqldump --password=password database > backup-file-name

If you omit the password value following -p option on the command line, mysqldump prompts for the password.

--user=user_name, -u user_name

The MySQL user name to use when backup the database. The option -u is optional, if you did not use -u option, mysqldump will use your Linux username as the MySQL username.

mysqldump -p'password' -u username  database > backup-file-name

Backup Single Database

Most Common Usage of the mysqldump command is to backup single database to a text file.

mysqldump -p'password' -u username  database > backup-file-name

Example

mysqldump -u root -p example_database > example_database.sql

As above example, mysqldump command will backup the example_database to the example_database.sql file. We use root as the username to connect to the MySQL Server.

Backup Multiple Databases

Command Option --databases use to specify multiple databases to backup.

mysqldump -p'password' --databases database1 database2 database3 > database_backup.sql

The Above example will backup all three databases to the database_backup.sql file.

Backup All Databases

We can use --all-databases option to backup all databases in the MySQL/MariaDB Server.
Example

mysqldump -p'password' --all-databases > all_databases.sql

The above example will backup all databases in the MySQL Server to all_databases.sql file.

Backup One or more tables from a Database

We can also specify which tables we want export Instead of backing up the entire MySQL database. For this we specify one or more table name after the database name.

Example

mysqldump -p example_database table1 table2 > example_database.sql

As per the above example, mysqldump command will backup “table1” and “table2” of the “example_database” Database.

Exclude specific tables from the mysqldump

When you dump a database, you can use --ignore-table option to exclude specific tables. Name of the table must be specified using both the database and table names.

Example

mysqldump -p --ignore-table=example_db.table1 example_db > example_db.sql

To ignore multiple tables, use --ignore-table option multiple times.

mysqldump -p --ignore-table=example_db.table1 --ignore-table=example_db.table2 example_db > example_db.sql

Backup Database from a Remote MySQL Server

We can use -h option of the mysqldump command to backup database from a remote MySQL Server.

Example

mysqldump -p -u username -h 192.168.1.20 example_db > example_db.sql

The mysqldump command will backup example_db from the MySQL Server Located at 192.168.1.20.

Backup Database to Another Database

Instead of writing backup to a Text file, we can backup a MySQL Database to a another Database by piping the output of the mysqldump to mysql command.

Example 1

mysqldump -p'password' database1 | mysql -p'password' database2

As per the above example, database1 will be backup to the database2.

Example 2

mysqldump -p'password' database1 table1 table2 | mysql -p'password' database2

As per the above example, table1 and table2 of the database1 will backup to the database2.

Backup Database to a Another MySQL Server

We can use -h option of the mysql command to specify different MySQL Server as the backup destination.

Example

mysqldump -p'password' database1 | mysql -p'password' -u username -h 192.168.1.20 database1

As per the above example, database1 of the local MySQL Server will backup to the database1 of the MySQL Server located at 192.168.1.20.

Those are some useful mysqldump backup examples you can use to backup MySQL/MariaDB Databases.