Day 19: Administrating MySQL

Overview

In this chapter, you will look at how to administrate MySQL. This covers a broad range of situations,
including backing-up and restoring data and recovering a corrupted database.
You will learn how to use the mysqladmin utility for general MySQL administration, and you will also
look at MySQL's internal functions for accessing administrative data from within SQL statements.

Backing up Your Data

Backing up is a vital part of the administration of any database system. Reasons for using a backup can
range from fire and theft to accidental user corruption, but whatever the perceived risk, the backup
addresses a business risk. Just as important as the backup files is the means of restoring data, if disaster
strikes, in a way that is fast and safe and gets business back to normal operation with a minimum of
downtime.
MySQL includes two useful utilities for backing up and restoring databases: mysqldump.

Between these two utilities, you can save a part or the whole database and restore it into the same or a

new location.

Saving Files

mysqldump is a useful utility for saving a MySQL database. It can be used both for backup and for saving a
database so that it can be relocated. mysqldump can be run by typing it at a command line, or it can be
invoked by a scheduler such as cron under Unix or Linux or the At command in Windows NT/2000.
The syntax for mysqldump is as follows:
mysqldump [options] database [tables]
If run without any options or tables specified, mysqldump will dump (save) the entire specified
database.
When you run mysqldump, it actually creates a file full of CREATE and INSERT statements that can be
used as instructions to recreate the table specifications and data.
After you have the dump file, you can keep it for backup purposes or send it to the destination where
you want the database moved. In a moment, you'll see how to use mysqlimport to go through this file
and import the database. But first, look at a few of mysqldump's most useful options:
ƒ --add-locks Adds LOCK TABLES commands before and UNLOCK TABLES
commands after table dumps. These cause table locking at the mysqlimport
destination, which will speed up the restore process.
ƒ --add-drop-table Adds a DROP TABLE command before each CREATE TABLE
statement. If a copy of the database already exists at the destination, this will ensure
that no duplication occurs, and that the table specification completely replaces
anything that was there before.
ƒ -e, --extended-insert Causes mysqldump to use multiline INSERT syntax,
which should give INSERT statements that are more compact and faster.
ƒ -q, --quick Dumps the result directly to stdout rather than buffering. You may
need to use this if you are short on system memory.
ƒ --opt The same as --add-locks, --add-drop-table, --eextended-insert,
and --quick and is a convenient way to get the fastest possible dump and import.
ƒ -F, --flush-logs Flushes the MySQL server's logs file before starting the dump.
ƒ -h=hostname, --host=hostname Causes the dump to be created on hostname
rather than on localhost, which is the default.
ƒ -l, --lock-tables Causes tables to be locked before beginning the dump. If you
have complex updates occurring on your system, this will ensure that you get a
complete set of data that is not partially updated.
ƒ -t, --no-create-info Omits CREATE TABLE statements (saves the data only).
ƒ -d, --no-data Omits the actual data and saves only the table definitions; useful if
you just want to save your design but not the contents.
ƒ --tab=path, -T=path Sends the dump to files at the destination in your file system
given by path. For each table, mysqldump creates a file called table_name.sql