- How to Backup
- How to Restore
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.
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
$ 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
$ 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.
$ mysqldump -u [user] -p --no-data db1 > db1_nodata.sql
Dumping only data from a database
$ 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