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

Tells the utility toact as the user named admin_backup when interacting with the
MySQL server. I showed how to create this user in Restricting the Access of User Ac-
counts , so create a special user with the right privileges now if you have not already
done so. Although you might be tempted to use the root user for backups, you should
always use a special administrative user, as we're doing here. The user just needs the
proper permissions to lock tables and read data from all the databases and tables.
--password
Tells the utility that theuser needs to be prompted for a password, which will have to
be typed in on the next line when asked. This acts the same way as the mysql client. If
the backup is to be executed by cron through a shell script, this option can be changed
to --password= my_pwd , where my_pwd is the password. That means, though, that
the password will be in crontab in plain text. This is a good example of why you
shouldn't use the root user.
--lock-all-tables
Makes MySQL lockall of the tables before performing the backup. The lock won't be
released until the process is finished. For a busy database with many users, locking all
of the tables for a lengthy period of time can create problems. We'll look at alternatives
in a bit.
--all-databases
Specifies that all ofthe databases are to be exported. In the next subsection, in which
we will backup only some databases, we'll replace this option with another so that we
may specify the databases to backup.
The greater-than sign in the command line shown here is a shell redirect of the standard
output ( STDOUT ) to the path and filename given after it. Set the path and filenames to suit
your system and preferences.
The resulting dump file will generally contain separate INSERT statements for each row
or each table. To bundle INSERT statements into one statement for each table in the dump
file, include the --extended-insert option. Thiswill make a smaller dump file. Ad-
ditionally, the combined INSERT statements will execute faster when you have to restore
a database. If your server generates extended inserts in a dump file by default, but you
prefer them as separate statements, usethe --skip-extended-insert option.
The INSERT statements don't include the column names — it just lists the values in the
same order as the columns. If you want the column names included, though,you would
add the --complete-insert option.