MySQL Master-Master-Slave-Slave Replication

Here we are going to create a Master -> Master -> Slave -> Slave replications architecture.

database_structure

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.

 

46 comments

  1. garcinia cambogia 1500Mg gnc

    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.

  2. taking online surveys

    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 .

  3. WordPress google penalty

    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:.

  4. painting services in brandon fl

    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?

  5. weight loss programs

    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

  6. Eve

    Hello, I read your blog daily. Your story-telling style is
    witty, keep it up!

  7. air max baratas

    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.

  8. nice hack

    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.

    . . . . .

  9. reklama

    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!

  10. titlovani filmovi

    I love reading an article that can make men and women think.
    Also, many thanks for allowing for me to comment!

  11. seo

    This is a topic that is close to my heart… Take care! Where are
    your contact details though?

  12. pet rescue cheats

    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!!

  13. loan for people with bad credit

    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!

  14. lose body fat

    Ӊ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.

  15. Arthur

    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!

  16. warez db

    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

  17. Advancing Income

    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.

  18. Betting UK

    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.

  19. happy mall story game online

    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

  20. porn

    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.

  21. joe strummer

    It’s awesome designed for me to have a web site, which is beneficial in favor of my knowledge.
    thanks admin

  22. refinance mortgage loans

    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.

  23. african mango review

    …Additional Information ca be found here…

    […]The entire glance of your web site is wonderful, let well as the content![…]…

  24. Supplier Geomembrane

    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!

  25. Asphalt 8 Hack

    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

  26. enhancer female libido

    …For further Information click here…

    […]The whole glance of your web site is fantastic, let well as the content![…]…

  27. Dr. Oz Green Coffee Bean

    …Websites you should visit…

    […]I am no longer certain the place you’re getting your information, but great topic.[…]…

  28. Male Enhancement

    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

Leave a Reply