I left out the details because of the fear of putting
people to sleep. Here it goes
The requirement is to design High Availability for an
Application that is using a MySQL database. The
following is the configuration I have in mind.
CONFIGURATION:
- Two machines, Primary and Secondary. Each machine
has the __capability__ to run an instance of our
Application and an instance of MySQL Server.
- Bidirectional replication is setup between the
Primary and Secondary, i.e.; MySQL on both machines
are running with '--log-bin' and without
'--log-slave-updates'.
- A Heartbeat Manager runs on both boxes providing
the status of the Application (ACTIVE, STAND-BY or
DOWN).
- Also, the Application will be designed to ensure
that a database access or update is possible only if
the Application is ACTIVE.
INITIAL STATE:
- The Heartbeat Managers are running on both the
machines.
- To start off, Primary is acting as the Master.
The Application and the MySQL process on the Primary
are ACTIVE, performing database operations at any
given time.
- Secondary is the Slave. The Application and the
MySQL process on the Secondary are in STAND-BY mode,
i.e., no data access or update.
- All the data written to the Primary database is
being replicated onto the Secondary database via MySQL
Replication.
FAILOVER SCENARIO:
- Primary becomes unavailable. Primary is DOWN.
- The Heartbeat Mgr on the Secondary becomes aware
and makes the Secondary ACTIVE. This means
- The secondary will start taking requests and
making updates to the database.
- In short the secondary is the Master now.
Later....
- Primary is brought back up again. It will be in
the STAND-BY state.
- Since bidirectional replication is setup, the
updates on the Secondary get replicated onto the
Primary.
Now to the questions;
> "mirror each other" implies a dual-master scenario.
> Is that what
> you're planning? If so, are you aware of the
> problems in doing so,
> such as the lack of conflict resolution?
Yes. The Slave is expected to take over when the
Master goes down.
No, I am not aware of 'lack of conflict resolution'.
I am waiting for the 'High Performance MySQL' book to
explain this to me :-). Would be great if you explain
it or point me to some link.
>> Q2. Are there any gotchas that I need to watch out
for?
> Yes. ;-)
Hopefully the above details will help you provide me
more information.
Also, on a more general tone:
- How do people using MySQL as the database provide
High availability solutions?
- What is the alternative to Replication?
- Is the 'shared storage device' solution preferred in
which you have the Primary and Secondary pointing to
the same storage area; when machine fails, the other
machine is brought online against the same data.
All feedback is most welcome.
Gowtham.
--- Jeremy Zawodny <[EMAIL PROTECTED]> wrote:
> On Wed, Feb 18, 2004 at 02:18:59PM -0800, Gowtham
> Jayaram wrote:
> > Hello all:
> >
> > I am looking into the Replication issues that
> surface in a Failover
> > scenario (Master goes down and Slave is pressed
> into service).
> >
> > I understand that it is critical for the Master
> and Slave databases
> > to mirror each other for Replication to work.
>
> "mirror each other" implies a dual-master scenario.
> Is that what
> you're planning? If so, are you aware of the
> problems in doing so,
> such as the lack of conflict resolution?
>
> > I am trying to ensure that this integrity is
> maintained when the
> > Failover scenario occurs.
> >
> > Reading the MySQL FAQ, I see the following
> recommendation.
> >
> > 1. On the Master, execute these commands:
> >
> > mysql> FLUSH TABLES WITH READ LOCK;
> > mysql> SHOW MASTER STATUS;
> >
> > Record the log name and the offset from the
> output
> > of the SHOW MASTER STATUS statement.
> >
> > 2. On the Slave, issue this command, where the
> > replication coordinates that are the arguments to
> the
> > MASTER_POS_WAIT() function are the values recorded
> in
> > the previous step:
> >
> > mysql> SELECT MASTER_POS_WAIT('log_name',
> > log_offset', [timeout]);
> >
> > The SELECT statement will block until the Slave
> > reaches the specified log file and offset. At that
> > point, the Slave will be in sync with the Master
> and
> > the statement will return.
> >
> > 3. On the Master, issue the following statement to
> > allow the master to begin processing updates
> again:
> >
> > mysql> UNLOCK TABLES;
> >
> > 4. Also, I plan to add an extra (maybe redundant)
> > check to make sure that the SQL Thread is in 'Has
> read
> > all relay log' state.
> >
> > QUESTIONS:
> >
> > Q1. Will the above steps be __sufficient__ to
> synchronize the
> > databases?
>
> Well, you really haven't said much about how the
> failover and recovery
> are going to work. What you've described above is a
> partial method of
> syncing the two servers. But it's not clean when
> and how you inted to
> use it.
>
> > Q2. Are there any gotchas that I need to watch out
> for?
>
> Yes. ;-)
>
> > I would also like to hear any recommendations of
> how to handle the
> > scenario in which the Master database crashes.
> Since I cannot
> > connect to the Master database, I cannot perform
> above Steps 1, 2
> > and 3.
>
> Correct. If the master is dead, the slave has the
> data is has and
> there's not much else you can do about it until the
> master comes back
> up (assuming it does at all).
>
> > Q3. Will Step 4 work in this scenario (since it is
> file based)? If
> > it does work is it sufficient to ensure database
> mirroring?
>
> Maybe.
>
> > Q4. If Step 4 does not work what is the
> recommendation for this
> > scenario.
>
> Can you be more clear on the setup you have in mind?
> Does "pressed
> into service" mean you want to slave to take updates
> too?
>
> Jeremy
> --
> Jeremy D. Zawodny | Perl, Web, MySQL, Linux
> Magazine, Yahoo!
> <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/
>
> MySQL 4.0.15-Yahoo-SMP: up 158 days, processed
> 1,941,264,213 queries (141/sec. avg)
__________________________________
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
http://antispam.yahoo.com/tools
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
