Backup Strategies

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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....

- Rajesh

Re: Backup Strategies

Rajesh Kapur wrote:
Quoted text here. Click to load it
I use the following backup script to backup my replication client so my
server can stay online all the time without read locks being issued on it.


A: Because it messes up the order in which people normally read text.
Q: Why is it such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

Site Timeline