The MySQL server, and mysql, the MySQL client, a MySQL installation comes
with many useful utility programs. We have seen mysqladmin, which is
responsible for the control and retrieval of information about an operational
MySQL server, for example.
mysqldump is another such program. When
run, it connects to a MySQL server (in much the same way as the mysql
program or the PHP language does) and downloads the complete contents of the
database you specify. It then outputs these as a series of SQL CREATE
TABLE and INSERT commands that, if run in an empty MySQL database,
would create a MySQL database with exactly the same contents as the
original.
If you redirect the output of mysqldump to a file, you can
store a “snapshot” of the database as a backup. The following command (typed all
on one line) connects to the MySQL server running on myhost as user
root with password mypass, and saves a backup of the database
called dbname into the file dbname_backup.sql:
shell%mysqldump -h myhost -u root -pmypass dbname > dbname_backup.sql
shell%mysqladmin -h myhost -u root -pmypass create dbname shell%mysql -h myhost -u root -pmypass dbname < dbname_backup.sql
The first command uses the mysqladmin program to create the
database; alternatively, you can do this at the MySQL command line. The second
connects to the MySQL server using the usual mysql program, and feeds in
our backup file as the commands to be executed.
Facilities exist in MySQL to keep up-to-date backups that are not
adversely affected by server activity at the time at which the backups are
generated. Unfortunately, they require you to set up a backup scheme
specifically for your MySQL data, completely apart from whatever backup measures
you have established for the rest of your data. As with any good backup system,
however, you'll appreciate it when the time comes to use it.
You can also edit update logs to undo mistakes that may have been made. For
example, if a co-worker comes to you after having accidentally issued a DROP
TABLE command, you can edit your update log to remove that command before
you restore your database using your last backup and the log application. In
this way, you can even keep changes to other tables that were made after the accident. And, as a precaution, you should probably
also revoke your co-worker's DROP privileges.
An update log is a record of all SQL queries that were received by the database,
and which modified the contents of the database in some way. This includes
INSERT, UPDATE, and CREATE TABLE statements among
others, but doesn't include SELECT statements.