High Performance MySQL [Electronic resources] نسخه متنی

9.3 Tools and Techniques

With
an understanding of the various backup-related issues you need to
consider, let's move on to examining the tools
available. If you have a complex configuration or unusual needs,
there's a chance that none of these alone will do
the job for you. Instead, you'll need to build a
custom solutionpossibly using one or more of the tools
described here.

This section isn't intended to be a comprehensive
reference for each tool. Instead, it focuses on presenting the
relevant features of each one so that you better understand your
choices. Once you've selected a tool, be sure to
consult the documentation for it. There's a good
chance that the tool has options that didn't exist
when this book was written. We will post news about available tools
at our web site: http://highperformancemysql.com (see the
Preface for more information).

9.3.1 mysqldump

For a long time, mysqldump was
the only backup tool available for MySQL. It is a command-line
utility for dumping tables of any type into SQL flat files. It even
handles foreign-key constraints properly.
mysqldump comes with MySQL, so
you're guaranteed to have it installed already.

Using mysqldump to perform dumps is ideally
suited to backing up small databases. The resulting files are large
compared to the data being dumped, and it's not a
very efficient process.

To back up all the databases on a server, execute the following
command:

$ mysqldump -u root -pPassword -x --all-databases > dump.sql

The -x flag tells mysqldump
to lock all tables during the backup to ensure consistency.

There are a few drawbacks to that method. Most importantly, the
entire dump will go to a single file, which can result in a very
large file if you have a lot of data to back up.
mysqldump doesn't have an
option to split the output into separate files based on database or
table name.

If you need to back up a subset of all the databases, you can provide
a list of database names on the command line:

$ mysqldump -u root -pPassword -x --databases db1 db2 db3 > dump.sql

This creates a dump file with the information necessary to recreate
the db1, db2, and
db3 databases.

If you need to back up only a few tables from a single database, you
can provide the database and table names:

$ mysqldump -u root -pPassword -x db1 table1 table2 table3 > dump.sql

mysqldump works well over the network, too. By
adding a -h argument, you tell it to connect to a
remote MySQL host instead:

$ mysqldump -h db.example.com -u root -pPassword -x --all-databases > dump.sql

9.3.1.1 Restoring

No
matter which options you use, restoring a dump is always
straightforward. Simply feed the dump file back through the
mysql command-line tool:

$ mysql -u root -pPassword < dump.sql

If you find yourself restoring dump files frequently (perhaps on a
test server), consider using
mysqldump's
--extended-insert option. It tells
mysqldump to bundle many insert statements
together using MySQL's bulk insert syntax:

INSERT INTO mytable (col1, col2, col3)
VALUES (val1, val2, val3) (val1, val2, val3) ...

This makes the restore run far faster than the default method, which
uses one insert statement per row. It also results in much smaller
dump files.

Normally, mysqldump requests all the rows for
the table it is dumping, buffers them in memory, and writes the data
to disk. It does this to minimize the amount of time tables are
locked on the server. However, when dumping large tables, you need to
use the --quick option; it prevents the buffering,
instead telling mysqldump to fetch rows from the
server one at a time. While it's a bit
slower^[5] than the default method,
it's the only option when your tables are too big to
fit in memory on the host that's running the dump.

^[5] That's not a typo. The
--quick option causes the dump process to take a
bit more time.

In fact, you might consider using the --opt
option. It enables several useful options at once, including
--quick and --extended-insert.

Windows users should use the --result-file option
to specify an output file:

$ mysqldump -u root -pPassword --all-databases --result-file=dump.sql

Otherwise, Windows converts all newline characters
(\n) to a carriage return plus newline
(\r\n). The silent conversion will cause endless
frustration when you need to restore a table in a hurry.

9.3.2 mysqlhotcopy

Originally created by Tim Bunce (the architect of
Perl's DBI),
mysqlhotcopy
is a Perl script included in the standard MySQL distributions. Its
purpose is to automate the process of backing up a database
consisting of ISAM and MyISAM tables while the server is running.
It's the most popular tool available for performing
online raw backups and is best suited to backing up single databases
on a live server. It operates by getting a read lock on all the
tables to be copied, copying them, and then releasing the lock. This
means it doesn't scale very well as traffic or size
increase.

To back up a live database, such as the test database, run:

$ mysqlhotcopy -u root -p Password test /tmp

You'll end up with a test
subdirectory in /tmp that contains all the
tables from the backed up database.

$ ls -l /tmp/test
total 108
-rw-rw----    1 mysql    users        8550 May  3 12:02 archive.frm
-rw-rw----    1 mysql    users          25 May  3 12:02 archive.MYD
-rw-rw----    1 mysql    users        2048 May 23 12:58 archive.MYI
-rw-rw----    1 mysql    users        8924 Mar  4 21:52 contacts.frm
-rw-rw----    1 mysql    users        7500 Mar  5 21:11 contacts.MYD
-rw-rw----    1 mysql    users        5120 May 23 12:58 contacts.MYI
-rw-rw----    1 mysql    users        8550 May  3 12:02 dirty.frm
-rw-rw----    1 mysql    users          25 May  3 12:02 dirty.MYD
-rw-rw----    1 mysql    users        2048 May 23 12:58 dirty.MYI
-rwxr-xr-x    1 mysql    users        8558 Feb 26  2001 maybe_bug.frm*
-rwxr-xr-x    1 mysql    users          45 Feb 26  2001 maybe_bug.MYD*
-rwxr-xr-x    1 mysql    users        2048 May 23 12:58 maybe_bug.MYI*
-rwxr-xr-x    1 mysql    users        8715 Jan 15  2001 test_more_info.frm*
-rwxr-xr-x    1 mysql    users         784 Jan 16  2001 test_more_info.MYD*
-rwxr-xr-x    1 mysql    users        2048 May 23 12:58 test_more_info.MYI*

As you can see, mysqlhotcopy copies the data
(.MYD), index (.MYI), and
table definition (.frm) files for each table in
the test database. To conserve space, you may choose to back up only
the .frm and .MYD files in
their entirety. Given the --noindices option,
mysqlhotcopy copies only the first 2,048 bytes
of each .MYI file. That's all
MySQL needs to reconstruct the indexes at a later date.

$ mysqlhotcopy -u root -p Password --noindices test /tmp

Because it is written in Perl, mysqlhotcopy has
support for regular expressions too. To back up every database that
contains the string test in its name, run:

$ mysqlhotcopy -u root -p Password --regexp=test /tmp

In practice, few users use that capability, but it is there.

9.3.2.1 Restoring

To
restore one or more tables, simply copy the files into the proper
subdirectory of MySQL's data directory. For example,
if you need to restore the test_more_info table
into the test database, run:

$ cp /tmp/test/test_more_info.* datadir/test

If you used the --noindices option to truncate the
.MYI files, you need to repair the tables before
you can use them. You can use either the myisamchk
-r command:

$ cd datadir/test
$ myisamchk -r test_more_info

or the REPAIR TABLE test_more_info command from
within MySQL:

mysql> REPAIR TABLE test_more_info

That's all there is to it. You can then freely use
the restored table.

9.3.3 mysqlsnapshot

Jeremy
originally wrote mysqlsnapshot to simplify the
process of configuring replication slaves at Yahoo! using MySQL
3.23.xx. As the amount of data grew, he realized one day that a
better online backup system was needed. After working with the code
for mysqlsnapshot, Jeremy realized that if he
added one more feature it would do the job quite well. In addition,
it would be a much smaller and easier to maintain than
mysqlhotcopy.

He hasn't yet submitted mysqlsnapshot
for inclusion in the MySQL distribution. It may be there by the time
you read this, but if not, you can find it at http://jeremy.zawodny.com/mysql/mysqlsnapshot/.

mysqlsnapshot is best used to back up an entire
database server without taking it offline. It has no options for
specifying particular databases or tables to include or exclude in
the process. It copies everything.

To back up all databases on a server, run:

$ mysqlsnapshot -u root -p Password -s /tmp/snap --split -n
checking for binary logging... ok
backing up db database... done
backing up db jzawodn... done
backing up db mysql... done
backing up db nuke... done
backing up db phplib... done
backing up db prout... done
backing up db test... done
snapshot completed in /tmp/snap/

This results in one tar file for each database, written to the
/tmp/snap directory. If you remove the
--split option, mysqlsnapshot
puts all the data in a single tar file. If you supply the
-z argument, it compresses the backup using
gzip.

9.3.3.1 Restoring

Restoring
a backup created with mysqlsnapshot is just a
matter of untarring the files in MySQL's data
directory. To restore the prout database, you
execute:

$ cd datadir/test
$ tar -xvf /tmp/prout.tar

This illustrates one reason you ought to consider keeping each
database in a separate tar file. By doing so, your backups will be
more manageable (you can selectively delete them on a per-database
basis), and you can be selective about what you restore.

9.3.4 InnoDB Hot Backup

If you're keeping a
large amount of data in InnoDB and would like online backups, the
InnoDB Hot Backup tool is the best choice. Unlike MySQL,
it's not free. Rather, it's a
relatively inexpensive commercial tool developed by the makers of
InnoDB. See http://www.innodb.com/hotbackupl for
details.

To use the Hot Backup Tool
(ibbackup), you create a configuration file
that tells ibbackup where to archive the data.
Then run it like this:

$ ibbackup /etc/my.cnf /etc/ibbackup.cnf

The backup tool needs to read the MySQL configuration file as well as
its own configuration. Recent versions of
ibbackup have added the ability to compress the
backup (--compress).

It's important to note that
ibbackup doesn't back up the
.frm files for your tables. So even if you use
InnoDB tables exclusively in MySQL, you still need to back up the
.frm files separately from using
ibbackup. This is slated to change in the
future, so check the InnoDB manual for the most recent news.

Restoring a backup is a straightforward process. With MySQL offline,
simply run:

$ ibbackup --restore /etc/ibbackup.cnf

Then start MySQL.

9.3.5 Offline Backups

As discussed earlier, there are
numerous benefits to shutting down MySQL before performing a backup.
To recap:

There will be no consistency problems.

You can use existing backup software.

Backups can be very fast.

If you are using a home-grown backup script of some sort, simply add
a call to the
mysqladmin command like
this:

# Now, shut down MySQL before the backup begins.
mysqladmin -u root -pPassword shutdown
# And start the backup
...
# Then bring MySQL back up
/usr/local/mysql/bin/mysqld_safe &

If you use a prepackaged backup system, you need to ensure that MySQL
is down before it starts. If the backup software is run locally on
the MySQL server, that's easy. Rather than running
the software directly, create a small shell script or batch file that
handles the stopping and starting of MySQL around the backup
processmuch like the previous example.

In larger environments, it is common to run client/server backup
software. The backup server contacts a daemon running on a remote
server when it is time for the backup process to begin. That daemon
(running on your MySQL server) then feeds data to the backup server
over the network. It is also common in such environments to let the
backup software control the exact starting time of the backup.

In a case like that, you may need to find an alternative approach for
backing up MySQL, or you'll need to do some digging
in the backup software's manual.
There's a good chance that you can find a way to
make the backup software start and stop MySQL when it needs to. If
not, you may be able to use one of the other backup strategies. If
you have sufficient disk space, you can perform the backup directly
on the MySQL server and let your normal backup process back up those
files.

9.3.5.1 Restoring

Once again, MySQL makes it easy to
restore data.^[6] Unless you're restoring the entire MySQL
installation, you need to recover the files that make up the tables
and databases you need to restore. Once you have them, copy them back
into MySQL's data directory and start MySQL.

^[6] Your backup software may not, but
there's little we can do about that here.

9.3.6 Filesystem Snapshots

Taking a snapshot of
MySQL's data is the fastest and least intrusive
method of backing up an online server. While the implementation
details vary, a snapshot is an online copy of your datausually
stored on the same filesystem or volume. In fact, most systems use a
copy-on-write scheme to minimize the free space required to take a
snapshot.

MySQL itself provides no support for taking snapshots, but various
free and commercial filesystems and storage solutions do. In the
Linux world, LVM (the
Linux volume manager) has snapshot capabilities.
Veritas sells a filesystem product for
most versions of Unix (and Linux) that can take snapshots. FreeBSD
5.x may offer snapshot capabilities too.

In the hardware space, Network Appliance's
popular "filers" can be used to
take filesystem snapshots. EMC has two ways of doing this: snapshots,
which are just like the snapshots described above, and BCVs (business
continuance volumes). They are, in effect, additional mirrors of a
volume that can be broken off and mounted on other systems. They
require double the amount of storage and are therefore expensive.

Snapshots are best used with a more traditional backup solution. By
itself, a snapshot doesn't do much to guard against
hardware failures. Sure, you can use a snapshot to quickly restore an
accidentally dropped table, but all the snapshots in the world
won't help if the disk controller catches fire.

Be sure that you have sufficient space reserved on your volume for
the number of snapshots you plan to keep online. Most
snapshot-capable filesystems require that you reserve a minimum
amount of disk space for snapshot data. If your server processes a
lot of write queries, you can easily exceed the reserved space. Check
your filesystem documentation for complete details.

Just as with the other approach to online backups, you must be
careful to flush and obtain a read lock on all ISAM and MyISAM tables
before initiating a snapshot. The easiest way to do this is to use
MySQL's
FLUSH TABLES
WITH READ
LOCK command. It will hold the lock until you
disconnect from MySQL or issue an UNLOCK
TABLES command. We'll discuss
this in the next section.