Hello, list. I have seen this subject come up here more than once, but I have never seen a solution I liked.
The drill is pretty standard: I have several (4 to be more precise) MySQL 4.1.2 servers running 13 different databases. All of them use MyISAM tables, except for one, which uses some InnoDB tables. I didn't want to (okay, I don't have the budget to) set up 4 slaves, one for each master, so I thought how could I use a single server to be slave for all 4 master servers.
I don't like the obvious solution, which is to run 4 instances of MySQL on the slave, because this wastes my precious memory. I also wouldn't like to keep dumping and importing tables all day long, because mysqldump locks the master servers while doing the dump.
So I came up with something that made me happy and seems to be working. Well, for at least 4 days now. It goes like this:
First, replicate all master databases on the slave server just like the replication FAQ specifies (i.e.: lock tables, show master status, write down info, mysqldump, unlock tables). At this point you can choose your slave server to be slave for any of your masters.
Now the tricky part begins. For each of your master servers, do the following on the slave:
1. change master to master #1
2. start slave
3. see that it is replicating ok
4. stop the slave io_thread (stop slave io_thread)
5. wait until the sql_thread has processed all the relay log (you must see "Has read all relay log; waiting for the slave I/O thread to update it" as the output of mysqladmin processlist | grep "system user")
6. stop the slave (stop slave) and then copy master.info to master.1 (this is master #1, remember)
Now repeat steps 1 to 5 for all the masters (#2, #3 etc). I had to do that four times, so I ended up with master.1 to master.4. Everytime you reach step 5 your relay log should have been fully processed by the slave sql_thread. Running change master ensures another relay log is created from scratch.
All you need now is rotate through your master.# files. This is an example for my situation (4 masters):
a. Read line #2 from master.1; this is the master_log_file parameter
b. Read line #3 from master.1; this is the master_log_pos parameter
c. Read line #4 from master.1; this is the master_host parameter
d. Read line #5 from master.1; this is the master_user parameter
e. Read line #6 from master.1; this is the master_pass (shame on me, I'm not using passwords, so I don't know if it will work on that situation)
f. On mysql do a "change master" using the values gathered above on the parameters
g. Still on mysql do a start slave
To point the slave to the next master, you repeat steps 4, 5, 6, 1, 2, 3 (on this order) from the previous instructions (of course, use the values your got from a-g on step #1). I wrote a little script (attached to this message) that does all the job of rotating through the masters for me. I have it on the system crontab to run every 10 minutes. So far, so good.
So... what am I missing here? If anyone wants more details, just ask. I'd be pleased to help.
Oh yes. I don't have to say to try this stuff at your own risk, right? :-)
Regards.
--
Thiago Figueiró Gerência de Operações
[EMAIL PROTECTED] Cel.: (21) 9329-0308
(21) 9888-6784
rotate_mysql_slave.DEFANGED-22
Description: application/defanged-22-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
