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.

 

Related Posts

  • 76
    Some times MySQL replication creates problems and slave could not sync properly from master. It may cause with lots of reason. Here is a way to fix it.   Warning: After using this tutorial, All of your bin-log files will be deleted, So if you want, you may take a backup…
    Tags: slave, mysql, master
  • 57
    Birth Of MySQL MySQL started out with the intention of using the mSQL database system to connect to tables using fast low-level (ISAM) routines. However, after some testing, They conclude that mSQL was not fast enough or flexible enough. This resulted in a new SQL interface to our database but with…
    Tags: mysql
  • 48
    Here we will help you to setup master-slave replication between MySQL servers. Setup Details: Master Server: 10.0.10.12 Slave  Server: 10.0.10.18 Database: empmaster 1. Setup MySQL Master Server Create an mysql account on Master server with REPLICATION SLAVE privileges to which replication client will connect to master. Block write statement on…
    Tags: mysql, slave, master
  • 40
    mysqld, also known as MySQL Server, is the main program that does most of the work in a MySQL installation. mysqld, is MySQL server daemon program which runs quietly in background on MySQL Database Server. MySQL Server manages access to the MySQL data directory that contains databases and tables. The…
    Tags: mysql, mysqld
  • 38
    I tried the following steps: Log in as super user or use sudo Open /etc/mysql/my.cnf using gedit Check the all the configuration Find bind-address, and change its value to the database server host machine's IP address. For me, it was localhost or 127.0.0.1 Save and close the file. Come back…
    Tags: mysql, mysqld

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 10, 2015 at 3:58 am
    Permalink

    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.

    Reply
  • February 17, 2015 at 4:12 pm
    Permalink

    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 .

    Reply
  • February 17, 2015 at 8:59 pm
    Permalink

    Hi there, I enjoy reading all of your article post. I like to write a little comment to support you.

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

    Reply
  • March 25, 2015 at 11:46 am
    Permalink

    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?

    Reply
  • 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

    Reply
  • March 25, 2015 at 11:08 pm
    Permalink

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

    Reply
  • 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.

    Reply
  • 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.

    . . . . .

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

    Reply
  • March 27, 2015 at 4:12 pm
    Permalink

    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?

    Reply
  • March 27, 2015 at 5:36 pm
    Permalink

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

    Reply
  • March 27, 2015 at 8:33 pm
    Permalink

    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.

    Reply
  • 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?

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

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

    Reply
  • 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.

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

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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.

    Reply
  • April 19, 2015 at 11:43 am
    Permalink

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

    Reply
  • 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.

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

    Reply
  • 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

    Reply
  • 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

    Reply
  • Pingback: Restore MySQL database from binary log

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: