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)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
