Manuel, Thank you very much for this information. This sounds like a very good strategy. I think I will try switching some slaves from one relay to another to familiarize myself and get practice and them do it to deploy a new master.
Again, thank you very much. Richard > El Jan 18, 2014, a las 2:00 AM, Manuel Arostegui <man...@tuenti.com> escribió: > > > > > 2014/1/17 Richard Reina <gatorre...@gmail.com> >> I have 3 relay MySQL database servers on my small office LAN backing up a >> master and 3 more machines backing up each relay (1 each). They are all >> replicating all databases and all tables. The master although running fine >> is almost eight years old. I'm thinking it's probably time to make one of >> the relays the master, but I've never done this before. >> >> I want the new master to have the IP address of the old master 192.168.0.1 >> . To make the change I was going to take the master off the LAN and >> shutdown mysql on all the machines, change the IP address on the chosen >> relay to that of the master 192.168.0.1, then restart mysql on all the >> machines. I always refer to the machines by their IP addresses and never by >> their hostnames. Once I successfully make the change I was planning on >> making the old master a relay since it is still working fine. >> >> Will this plan work ok? Is there a better or easier way? > > If the three machines are sync'ed and have consistent data I don't see the > need of stopping MySQL: > > - Stop whatever writes to your current master > - Once you are completely sure there are no writes in your current master, > set it to read_only = ON > - In the slave which will become the master, get the logfile and current > position with: show master status; > - Set the new IP in the new master > > > Using the position taken in the new master go to the slaves machines and: > > stop slave; change master to master_host='IP', > master_log_file='whatever_file_name_you_got', > master_log_pos=whatever_number_you_got, > master_user='replication_or_whatever_you_have', > master_password='replication_or_whatever_you_have'; start slave; > > - Set read_only = OFF in your new master > - Start your application so you can start getting writes again. > > As soon as you get writes if you do a "show master status;" in the new master > you should see the position going forward. > > I see that faster than any other thing. > > Hope this helps > Manuel. >