Backing up your database with mysqldump

Introduction

Backups are very important and we have a lot of web projects using MySQL. Therefore a Junior DevOps needs to know how to set up simple backup solution via scripts and do it manually. In this article we will show how to make database dumps and how to restore databases.

About mysqldump

There are different ways to backup a MySQL database. We usually use mysqldump to export data from a database or import data from a database dump.

The MySQL command line tool mysqldump is used to create backup copies (or dumps) of databases including the structure or schema and the data itself. There are a number of command line flags which can get MySQL to dump just the data or just the structure instead of everything.

How to Backup

Dumping all databases

To dump all databases, invoke mysqldump with the --all-databases option:

$ mysqldump -u [user]  -p --all-databases > dump.sql

This will dump all databases in a single file whichi can be used to restore all tadabases at once.

Dumping all databases from a specific host

Any options explicitly specified on the command line override implicit specified options in the dump path file or other option or config files.

For example, if the credentials in the dumping all databases path also apply for the host example.com, connect to the server on that host like this:

$ mysqldump -h example.com -u [user] -p --all-databases > dump.sql

To dump only specific databases sequentially, name them on the command line and use the --databases option:

$ mysqldump -u [user] -p --databases db1 db2 db3 > dump.sql

Dumping tables from a specific database

For very large databases, you may want to backup the data based on tables rather than the whole database. To backup just one table, the following could be entered from the command line:

$ mysqldump -u root -p --lock-tables employees salaries > /tmp/salaries.sql

Dumping only structure from database

Sometimes it’s useful to have a schema only backup. That is a dump of the objects in your database without data. This is useful when we need to build a test server, without all the production data or some subset of it.

Execute command:

$ mysqldump -u [user] -p --no-data db1 > db1_nodata.sql

Dumping only data from a database

Execute command:

$ mysqldump -u [user] -p --no-create-info db1> db1_onlydata.sql

How to Restore

Importing dump to a database

You can easily restore your dumped database from your backup file.

Import a dump without data to a new database

$ mysqldump -u [user] -p new_db < db1_nodata.sql

Import a dump with data to a new database

$ mysqldump -u [user] -p new_db < db1_onlydata.sql