MySQL Master-Master-Slave-Slave Replication
Here we are going to create a Master -> Master -> Slave -> Slave replications architecture.
I am expecting here , you have MySQL installed and set-up as normal.
- Master 1 will be known as Master 1 and Slave 2 with IP 10.1.1.1
- Master 2 will be known as Master 2 and Slave 1 with IP 10.1.1.2
- Slave 1 will be known as Slave 3 with IP 10.1.1.3
- and Slave 2 will be known as Slave 4 with IP 10.1.1.4
- Replication access to all the DB will be granted.
Master 1
Modify your MySQL config file, usually named my.cnf or mysql.cnf , located in /etc directory. (vi /etc/my.conf)
Add the following lines to [mysqld]
[mysqld] server-id=1 auto_increment_offset=1 auto_increment_increment=2 log-bin binlog-ignore-db=mysql binlog-ignore-db=test log-slave-updates
Save and close.
You should note that I have included auto_increment_offset*and *auto_increment_increment. auto_increment_offset is the same as server-id in my case, it does as the name suggests – offsets the auto increment value. auto_increment_increment should be set to the number of servers you have as masters, in this example we have 2.
Now restart MySQL.
Master 2 (Slave 1)
Modify your MySQL config file.
Add the following in [mysqld]
[mysqld] server-id=2 auto_increment_offset=2 auto_increment_increment=2 log-bin binlog-ignore-db=mysql binlog-ignore-db=test log-slave-updates master-host = 10.1.1.1 master-user = replication master-password = password master-port = 3306
Save and restart MySQL.
Now open a MySQL prompt and run the following queries
START SLAVE; SHOW SLAVE STATUSG;
Slave_IO_Running and Slave_SQL_Running must be set to Yes.
Master 1 (Slave 2)
Open a MySQL prompt and run the following query
SHOW MASTER STATUS;
You should see a master record has been created.
Now we need to configure Master 1 to run as Slave 2.
Modify MySQL config and add the following lines to [mysqld]
[mysqld] master-host = 10.1.1.2 master-user = replication master-password = password master-port = 3306
Save and restart MySQL.
Open a MySQL prompt and run the following queries
START SLAVE;
SHOW SLAVE STATUSG;
Slave_IO_Running and Slave_SQL_Running must be set to Yes.
Slave 3 and Slave 4
Now that you have Master – Master replication set up it’s time to attach the slaves.
I am going to do the following
- make Slave 3 slave of Master 1
- and Slave 4 a slave of Master 2.
Slave 3
Open your MySQL config file, under [mysqld] put the following
[mysqld] server-id=3 master-host = 10.1.1.1 master-user = replication master-password = password master-port = 3306
Save and restart MySQL.
Open a MySQL prompt and run the following queries
START SLAVE; SHOW SLAVE STATUSG;
Slave_IO_Running and Slave_SQL_Running must be set to Yes.
Slave 4
Open your MySQL config file, under [mysqld] put the following
[mysql]
server-id=4
master-host = 10.1.1.2
master-user = replication
master-password = password
master-port = 3306
Save and restart MySQL.
Open a MySQL prompt and run the following queries
START SLAVE; SHOW SLAVE STATUSG;
Slave_IO_Running and Slave_SQL_Running must be set to Yes.
Howdy! I just would like to give you a huge
thumbs up for your great information you have got here on this post.
I’ll be returning to your website for more soon.
What’s up, I log on to your blogs like every week.
Your humoristic style is witty, keep it up!
Hi there Dear, are you really visiting this web page regularly,
if so then you will absolutely get nice experience.
Hi my friend! I wish to say that this post is awesome,
nice written and include almost all important infos.
I’d like to see extra posts like this .
Hello i am kavin, its my first occasion to commenting anywhere, when i read this article i thought i could also create
comment due to this sensible article.
Hi there, I enjoy reading all of your article post. I like to write a little comment to support you.
I really like it whenever people get together and share opinions.
Great website, stick with it!
Marvelous, what a web site it is! This web site gives useful information to us, keep it up.
Remarkable! Its in fact remarkable article, I have got much clear
idea regarding from this paragraph.
This is the in the hot spot area and it is going to be front
and center when a user comes to your site.
But the money train doesn’t stop only for big business.
There are many types on Word – Press Membership Sites and the primary
ones are:.
Excellent blog you have here.. It’s hard to find good quality writing like yours these days.
I really appreciate individuals like you! Take
care!!
This is a beautiful picture with very good light 🙂
Good day! Do you know if they make any plugins to safeguard against hackers?
I’m kinda paranoid about losing everything I’ve worked hard on. Any tips?
Thanks for sharing your thoughts. I really appreciate your efforts and I am
waiting for your next post thank you once again.
That is very fascinating, You’re a very professional
blogger. I’ve joined your feed and look ahead to in search of
more of your great post. Additionally, I have shared your web site in my social networks
Hello, I read your blog daily. Your story-telling style is
witty, keep it up!
In case you don’t have one, a nail brush would also do. Fitness
Buddy could be the perfect app to an individual to
with this skill. Strength training though really essential as well in order to have a healthy living
style.
Hello very cool website!! Man .. Beautiful ..
Wonderful .. I’ll bookmark your web site and take the feeds also?
I’m satisfied to search out so many helpful info here in the
put up, we want develop more techniques in this regard, thank you for sharing.
. . . . .
You actually make it seem so easy with your presentation but I find this topic to
be really something which I think I would never understand.
It seems too complicated and extremely broad for me. I am looking forward
for your next post, I’ll try to get the
hang of it!
At this time it looks like Movable Type is the best blogging platform available
right now. (from what I’ve read) Is that what you are using on your blog?
I love reading an article that can make men and women think.
Also, many thanks for allowing for me to comment!
It’s fantastic that you are getting ideas from this article
as well as from our argument made here.
What’s Happening i’m new to this, I stumbled upon this I’ve found It absolutely useful and it has aided me out loads.
I am hoping to give a contribution & aid different customers like its aided me.
Good job.
This is a topic that is close to my heart… Take care! Where are
your contact details though?
Dacht even snel wat schrijven. Is iets over lifttrappen.
Hello, i think that i saw you visited my website so i came to “return the favor”.I’m
trying to find things to enhance my website!I suppose its ok to use a few
of your ideas!!
Wow that was odd. I just wrote an very long comment but after I clicked submit my comment didn’t appear.
Grrrr… well I’m not writing all that over again. Anyway, just wanted to say superb blog!
Ӊellο Dear, are yοu actսally visiting tҺіs site on a regular basis, if ѕo afterward үߋu ѡill
absolutely oƅtain good experience.
It’s perfect time to make a few plans for the future and it’s time to
be happy. I have read this post and if I may I want to recommend you some attention-grabbing issues or tips.
Maybe you can write subsequent articles relating to this article.
I desire to read even more things about it!
I think this is onee of the most impordtant inco ffor me.
And i am glad reading your article. But wanna remawrk on feww general things, The web site style is great,
the articles is really excellent : D. Good job, cheers
Excellent pieces. Keep writing such kind of info on your site.
Im really impressed by your blog.
Hi there, You’ve done an incredible job. I’ll certainly digg it and personally suggest to my friends.
I’m confident they’ll be benefited from this site.
Hello i am kavin, its my first time to commenting anywhere, when i read this article i
thought i could also make comment due to this brilliant paragraph.
I do not know if it’s just me or if perhaps everybody else encountering problems with your site.
It looks like some of the written text within your content are running off the screen. Can somebody else please provide feedback and
let me know if this is happening to them as well?
This might be a issue with my browser because I’ve
had this happen previously. Thank you
I have been exploring for a little for any high quality articles or blog posts in this kind of area
. Exploring in Yahoo I finally stumbled upon this web site.
Reading this information So i’m glad to exhibit that I
have a very excellent uncanny feeling I discovered just what I needed.
I such a lot for sure will make certain to don?t overlook this site and
provides it a look regularly.
It’s awesome designed for me to have a web site, which is beneficial in favor of my knowledge.
thanks admin
Simply wish to say your article is as amazing. The clarity
on your put up is just excellent and that i could think you’re knowledgeable in this subject.
Well with your permission allow me to grasp your feed to keep up
to date with approaching post. Thank you one million and please
keep up the gratifying work.
Actually, Hunter is more of a wilderness adventure book that just happens to be set in post-nuclear
war America.
Pingback: payday loans cash online
Pingback: african mango review
Thanks for your personal marvelous posting!
I really enjoyed reading it, you could be a great author.
I will always bookmark your blog and will often come back someday.
I want to encourage you to definitely continue your great work,
have a nice evening!
This is very fascinating, You are a very skilled blogger.
I have joined your feed and look ahead to in search of more of your fantastic post.
Also, I’ve shared your website in my social networks
Pingback: enhancer female libido
Pingback: Dr. Oz Green Coffee Bean
I’m not that much of a online reader to be honest but
your blogs really nice, keep it up! I’ll
go ahead and bookmark your site to come back later. All the best
my site – clash of clans hack
Awesome blog! I found it while surfing around on Yahoo News.
Do you have any tips on how to get listed in Yahoo
News? I’ve been trying for a while but I never seem to get there!
Appreciate it
Also visit my web page: Male Enhancement
Pingback: Restore MySQL database from binary log