Oracle 5.0, Справочное руководство

Страница: 643 / 3232

Establishing a Backup Policy
623
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections
For the cases of file system crashes or hardware problems, we can assume that the MySQL disk data
is not available after a restart. This means that MySQL fails to start successfully because some blocks
of disk data are no longer readable. In this case, it is necessary to reformat the disk, install a new one,
or otherwise correct the underlying problem. Then it is necessary to recover our MySQL data from
backups, which means that backups must already have been made. To make sure that is the case,
design and implement a backup policy.
7.3.1. Establishing a Backup Policy
To be useful, backups must be scheduled regularly. A full backup (a snapshot of the data at a point in
time) can be done in MySQL with several tools. For example,
InnoDB Hot Backup
provides online
nonblocking physical backup of the
InnoDB
data files, and
mysqldump
provides online logical backup.
This discussion uses
mysqldump
.
Assume that we make a full backup of all our
InnoDB
tables in all databases using the following
command on Sunday at 1 p.m., when load is low:
shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql
The resulting
.sql
file produced by
mysqldump
contains a set of SQL
INSERT
statements that can be
used to reload the dumped tables at a later time.
This backup operation acquires a global read lock on all tables at the beginning of the dump (using
FLUSH TABLES WITH READ LOCK
). As soon as this lock has been acquired, the binary log
coordinates are read and the lock is released. If long updating statements are running when the
FLUSH
statement is issued, the backup operation may stall until those statements finish. After that, the dump
becomes lock-free and does not disturb reads and writes on the tables.
It was assumed earlier that the tables to back up are
InnoDB
tables, so
--single-
transaction
[304] uses a consistent read and guarantees that data seen by
mysqldump
does not
change. (Changes made by other clients to
InnoDB
tables are not seen by the
mysqldump
process.)
If the backup operation includes nontransactional tables, consistency requires that they do not change
during the backup. For example, for the
MyISAM
tables in the
mysql
database, there must be no
administrative changes to MySQL accounts during the backup.
Full backups are necessary, but it is not always convenient to create them. They produce large backup
files and take time to generate. They are not optimal in the sense that each successive full backup
includes all data, even that part that has not changed since the previous full backup. It is more efficient
to make an initial full backup, and then to make incremental backups. The incremental backups are
smaller and take less time to produce. The tradeoff is that, at recovery time, you cannot restore your
data just by reloading the full backup. You must also process the incremental backups to recover the
incremental changes.
To make incremental backups, we need to save the incremental changes. In MySQL, these changes
are represented in the binary log, so the MySQL server should always be started with the
--log-
bin
[1467] option to enable that log. With binary logging enabled, the server writes each data change
into a file while it updates data. Looking at the data directory of a MySQL server that was started with