MySQL Master-Master-Slave-Slave Replication

by

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 thoughts on “MySQL Master-Master-Slave-Slave Replication”

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

    . . . . .

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

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

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

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

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

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

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

  38. Pingback: payday loans cash online
  39. Pingback: african mango review

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

  42. Pingback: enhancer female libido
  43. Pingback: Dr. Oz Green Coffee Bean
  46. Pingback: Restore MySQL database from binary log

Leave a Comment