MySQL Replication - promote slave

--------------030805030000090306080302
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi all,
I need to document the switch between master and slave
and I want to double check if the outlined procedure is correct.

We have a simple master slave replication setup on OEL 5.5 and MySQL
5.5.13.


Original config: machineA master, machineB slave.
Target: machineB master, machineA not operational.

On the master:

1. flush logs on the master (only if it's accessible, of course).

On the slave:

2. stop slave io_thread
3. Wait until show processlist reports "Has read all relay log".
4. stop slave
5. reset master
6. change master to master_host='' and reset slave (only to be sure that
the slave couldn't be started easily by mistake)
7. Start the application and checK if everything's OK

Now I should have only machineB operational,
I don't care for the state of machineA at this point.

To restore the initial state: machineA master, machineB slave:

1. Stop the application (assuming that's not a problem).
2. Generate a consistent dump on machineB:

mysqldump -u username -ppassword -A -x > dump_file

3. Import on machineA

mysql -u username -ppassword < dump_file

4. On machineA after the import:

reset master;
change master to master_host=''; # just in case, I prefer to have all
info reset for safety
reset slave; # see above
flush tables with read lock; # just in case, because there
are no connections but mine at this time
show master status; # just in case, it should
be 4, right after the reset master command

5. On machineB (the slave):

change master to
master_host='machineA',
master_user='',
master_password='',
master_log_file='mysql-bin.000001',
master_log_pos=;

master_log_file and master_log_pos should be unnecessary because after
the reset master they should correspond to the default values
of change master command, but again, it's just for safety.

Am I missing something?


Best regards
Dimitre





--------------030805030000090306080302--

Re: MySQL Replication - promote slave

Hi,

> 1. flush logs on the master (only if it's accessible, of course).
>
Not really necessary if you block clients (firewall rule for new
connections to port 3306?).

Anyway, why don't you use a dual-master setup?

I find that this is a whole lot easier to administer than a master/
fallback-slave situation. In particular, restoring the master after it
comes back happens automatically, or (if you need to re-install the master
from scratch) the command
slave# mysqldump --single-transaction --master-data=1 --all-databases \
| ssh master mysql
ensures that you can continue to use the slave while restoring the master.

Assuming you use only transaction-safe tables, of course.
(You should.)

--
-- Matthias Urlichs

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL Replication - promote slave

On 22/06/2011 15:21, Matthias Urlichs wrote:
> Hi,
>
>> 1. flush logs on the master (only if it's accessible, of course).
>>
> Not really necessary if you block clients (firewall rule for new
> connections to port 3306?).

Hi Matthias,
thank you for replying!


Isn't the flush logs command necessary in order to flush
any remaining buffered content and make it available to
the dump reading replication thread, even when there are
no client connections?


> Anyway, why don't you use a dual-master setup?

Yes, this could be an option.

We have two application environments:
production and spare, the app data is synchronized with rsync and
we use replication for the databases. If the application is started on
the wrong node by mistake, with multi-master replication active,
the production database could be "logically corrupted".


> I find that this is a whole lot easier to administer than a master/
> fallback-slave situation. In particular, restoring the master after it
> comes back happens automatically, or (if you need to re-install the master
> from scratch) the command
> slave# mysqldump --single-transaction --master-data=1 --all-databases \
> | ssh master mysql
> ensures that you can continue to use the slave while restoring the master.
>
> Assuming you use only transaction-safe tables, of course.
> (You should.)

Unfortunately we have both MyISAM and InnoDB tables,
I don't have control over this choice.


Best regards
Dimitre

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org