Backing Up and Restoring Databases - Learning MySQL and MariaDB - page 333

Making Backups
One of the best utilities youcan use to make backup copies of data in MySQL or MariaDB
is mysqldump . It'sincluded with both servers and it costs you nothing. You probably
already have it installed on your server. Best of all, it doesn't require you to shut down
MySQL services to make a backup, although you might restrict access to the backup utility
for better consistency of data. There are other backup utilities (e.g., MySQL Enterprise
Backup and Percona XtraBackup), some with a GUI and some that are more comprehens-
ive. You can learn about other types of backups and tools in the topic MySQL
Troubleshooting (O'Reilly) by Sveta Smirnova. However, mysqldump is the most popular
one, and as a new administrator, you should know how to use it, even if you later will use
one of the commercial releases. We will use this utility for the examples in this chapter.
The mysqldump utility works very simply: it queries the server for the schema and data of
each database and table and exports all of this to a text file. The default text file it creates,
which is known as a dump file , includesthe SQL statements necessary to reconstruct the
databases and data. If you were to open a dump file generated by mysqldump , you would
see CREATE TABLE statements and a multitude of INSERT statements. That may seem
cumbersome, but it's simple and manageable.
The mysqldump utility offers many options. You can make a backup of all of the databases,
or only specific ones. You can also back up just specific tables. In this section, we'll look at
many of the available options and go through some examples of combinations for common
uses.
Backing Up All Databases
The simplest way tomake a backup is to dump all of the databases with all of the tables
and their data. You can do this easily with mysqldump . Try executing something like the
following at the command line on your server, using the administrative user you created in
Chapter13 . You'll have to change the path given from /data/backups/ , to a path on your
server. Or you can omit it and the dump file will be created in the current directory:
mysqldump --user= admin_backup \
--password --lock-all-tables
--all-databases > /data/backups/all-dbs.sql
The options used here include the following:
--user= admin_backup