One of the most important task of a database administrator is backing up and restoring databases. MySQL is a popular database server and provides tool named mysqldump
for backup and restore operations. We will look different usage types of mysqldump in this tutorial.
Syntax
mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS]
Help
$ mysqldump --help

Connect Remote Database Server
By default mysqldump will try to connect local MySQL server database. Remote databases server can be specified with -h
option as IP address or domain name. In this example we will try to connect host named poftut1
.
$ mysqldump -h poftut1 -u root -p mysql > back.sql
Backup Single Database
Mysqldump have a lot of usage possibilities but the most popular one is backing up single database. This will only backup single specified database. We will specify user name and password with database name. Username will be specified with -u
options and password specified with -p
option. We will specify database name after these information without an option.
$ mysqldump -u root -p mysql > mysql.sql

By default backup output is written to the standard output. But in this example we have redirected the output to the file named mysql.sql
. the backup file is an sql
file which holds sql schemas and data.
Backup Multiple Databases
We can backup multiple databases just adding more databases to the end of the mysqldump command. In this example we will backup databases mysql
, sys
. We will use --databases
option.
$ mysqldump -u root -p --databases mysql sys > bak.sql

Backup All Databases
In previous example we have specified multiple databases for backup. If we need to backup all databases in the database server specifying them one by one is error prone work. We can use --all-databases
command to backup all databases without specifying them one by one.
$ mysqldump -u root -p --all-databases > bak.sql

Backup Single Table
Sometimes our application databases uses only single table. All transactions are done in a single database. In this situation we may need backup single table. We can specify the table name after the database name which contains the table. In this example we will backup table named general_log
which resides in database mysql
$ mysqldump -u root -p mysql general_log > mysql_general_log.sql

Backup To Remote
Backup on the local directory is practical but some times we may need to backup the databases to the remote server. There are different ways to backup remote directory like scp, file share etc. We prefer a pratical one where we will use ssh
command to redirect to backup content to the remote server. We provide password of the database server in command line. In this example we will copy database named mysql
to the remote system or server named ubu2
with shh
by using cat > big.sql
command.
$ mysqldump -u root -p123456 mysql | ssh ubu2 'cat > big.sql'
Check remote backup file can be done with following command.
$ ssh ubu2 head big.sql

Backup Only Database Schema
The backup of databases, tables will take copy of the schema of databases, tables and currently stored data on these tables and databases. If the data is test data which is not important for us or if we want to create clone of the current database and tables without data we can omit the data. We wil use --no-data
or -d
option to specify this.
$ mysqldump -u root -p -d mysql > schema.sql

Compress Backup
Creating backups take time and storage. Especially if we have more than one backup there will be need a lot of storage. As we know the backup files are sql
format by default. Sql is a text file and have a high compression rate. We can write backup as compressed format which will make storage usage very effective. We will use gzip
to enable msqldump compression.
$ mysqldump -u root -p mysql | gzip > back.gz

Compress Server Client Transmission
Another useful performance option is compression transmission between mysqldump client and MySQL server. As there may be a bulk of data while transmitting them through network compressing is a good habit for network performance . We will use -C
or --compress
options to enable compression.
$ mysqldump -h localhost -u root -p mysql -C > back.sql
Force Backup Operation
While taking backup some times errors or warnings occurs. These warnings and errors can interrupt the backup process b$ mysql -h poftut1 -u root -p < back.sqly default. We can use -f
option to force and omit the errors and warnings.
$ mysqldump -u root -p -f mysql > back.sql
Restore and Import Database
We will use mysql
tool to restore database. We assume that backup is in Sql
format. We will redirect the backup file to the mysql command. While using mysql command we need to specify the username with -u
and password with -p
options.
$ mysql -u root -p < back.sql
Restore Remote Database
In order to restore database to the remote database server we will specify the remote databases server IP address or hostname. In order to specify IP address or hostname we will use -h
option. In this example we use remote database server named poftut1
$ mysql -h poftut1 -u root -p < back.sql