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.

 

Satya Prakash

VOIP Expert: More than 8 years of experience in Asterisk Development and Call Center operation Management. Unique Combination of Skill Set as IT, Analytics and operation management.

46 thoughts on “MySQL Master-Master-Slave-Slave Replication

  • February 28, 2015 at 9:51 pm
    Permalink

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

  • March 25, 2015 at 10:20 pm
    Permalink

    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

  • March 26, 2015 at 6:36 am
    Permalink

    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.

  • March 27, 2015 at 5:51 am
    Permalink

    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.

    . . . . .

  • March 27, 2015 at 3:40 pm
    Permalink

    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!

  • March 29, 2015 at 7:44 pm
    Permalink

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

  • March 30, 2015 at 9:50 am
    Permalink

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

  • March 30, 2015 at 9:17 pm
    Permalink

    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!

  • March 31, 2015 at 5:17 am
    Permalink

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

  • April 1, 2015 at 3:30 am
    Permalink

    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!

  • April 1, 2015 at 4:35 am
    Permalink

    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

  • April 1, 2015 at 4:57 pm
    Permalink

    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.

  • April 4, 2015 at 5:57 am
    Permalink

    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.

  • April 17, 2015 at 7:11 am
    Permalink

    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

  • April 18, 2015 at 3:33 am
    Permalink

    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.

  • April 19, 2015 at 2:42 pm
    Permalink

    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.

  • Pingback: payday loans cash online

  • Pingback: african mango review

  • May 3, 2015 at 12:41 pm
    Permalink

    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!

  • May 4, 2015 at 2:18 am
    Permalink

    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

  • September 5, 2015 at 7:14 am
    Permalink

    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

Leave a Reply