I have about 20 databases in a single MySQL instance running 4.0.21 on
RHEL3. I have a healthy mix of MyISAM and InnoDB tables. Howerver these two
types do not mix within a single database. A read/write master MySQL server
replicates to five read-only database servers for load distribution of the
web traffic. I need to come up with a backup strategy that supports the
following objectives...
(1) Minimum down time during backups, if any.
(2) Ability to restore individual databases or the entire server for
disaster recovery.
(3) Clear identification of which binary logs to apply to the restored
databases to achieve up-to-the-minute recovery.
I have been reading the fine manual and trying out some ideas. Some of the
options in the manual are for versions later than 4.0.21.
I tried the mysqldump for all databases with the --flush-logs option in
order to get a clean break on the binary logs. This resulted in binary log
switching for every database, causing 20 log switches in the backup. I have
no idea which binary logs I would apply after restoring a single database. I
am concerned about the loss of any updates during the backup process.
The --lock-all-tables option is not available in 4.0.21. The --lock-tables
and --single-transaction options are mutually exclusive. I am not sure which
options will give me a consistent backup. Moreover these options seem to
lock tables at a 'per-database' level.
Another option I have been toying with is to issue a 'FLUSH TABLES WITH READ
LOCK;' from an independent session. This seems to lock the databases
properly, but I do not know how to code a script to issue this command from
a separate session for the duration of the backup.
I am sure there are others who would have faced and conquered these issues.
Can someone please point me in the right direction?
I would appreciate if you can share any scripts or point me to the white
papers or other web resources....
Thanks!
- Rajesh
