Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Rajesh Kapur
May 10, 2005, 10:35 pm
rate this thread
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
(1) Minimum down time during backups, if any.
(2) Ability to restore individual databases or the entire server for
(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....
Re: Backup Strategies
Rajesh Kapur wrote:
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?
Q: What is the most annoying thing on usenet and in e-mail?
- » Please Help: Transaction viewer with balance forward
- — Next thread in » MySQL Database Forum
- » Application approval #9914718SKX Wed, 11 May 2005 05:05:48 -0800
- — Previous thread in » MySQL Database Forum
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum