Thursday, February 23, 2012

Master Slave, Master Master Replication

I just struggled 3 days for doing this. Just writing it down in case I forget it soon.
It will be helpful for other people also, who will get a good head start into this field.

These are the steps for creating Master Slave replication......

1. Make data base of Master and Slave identical by running the dump of Master on Slave.
2. Master configurations in my.ini
server-id = 1
auto_increment_offset=1
# total number of master servers
auto_increment_increment=2
# local slave replication options
log-bin=master1-bin
log-slave-updates
3. Slave Configuration in my.ini
server-id = 2
auto_increment_offset=2
# total number of master servers
auto_increment_increment=2
# local slave replication options
log-bin=master2-bin
log-slave-updates
5. Restart both severs.
6. Create a new user on Master and Grant him replication permissions.
CREATE USER 'mike'@'masterhostname' IDENTIFIED BY 'pass';
GRANT REPLICATION SLAVE ON *.* TO 'user1'@'%' IDENTIFIED BY 'pass';
7. Run this command on Master
show master status \G;
It will show something like this...
*************************** 1. row ***************************
File: master1-bin.000001
Position: 1739
Binlog_Do_DB:
Binlog_Ignore_DB:
Note down File name and Position of log file...
8. Run this query on Slave
CHANGE MASTER TO MASTER_HOST='masterhostname', MASTER_USER='user1', MASTER_PASSWORD='pass',
MASTER_PORT=3307, MASTER_LOG_FILE='master1-bin.000001', MASTER_LOG
_POS=1739; [remember to change port number accordingly]

9. Run this query on Slave
start slave;
10. Now run any query on Master and you will see that change on Slave.
11. Make sure network connections is stablished and all required ports are open between two
server.
12. Repeat the above process if you want to add more slaves.
13. Same process can be used for creating Master-Master replication also. You will have to run
CHANGE MASTER query, with right parameters on Master which is slave to another Master.

9 comments:

  1. Replication is failing.Could you please help me.

    I have followed the below steps:

    1.I have taken 2 windows server 2008 r2 machines and joined into domain(consider master as C1 and slave as C2)
    2.Installed the "mysql-installer-community-5.6.12.0" in both the servers
    3.In Master(C1) machine:
    1.go to(C:\Programfiles\MYsqL\mYsql server 5.6) and edited my_default file

    added the below lines into the file
    server-id = 1
    log_bin = /var/log/mysql/mysql-bin.log
    expire_logs_days = 10
    max_binlog_size = 100M
    binlog_do_db = exampledb
    (I have restarted the machine)
    4.Open the command prompt in master(C1) and navigate to mysql bin path and connected to the mysql with the credentials
    5.create a user using the below command

    create user 'username'@'slave machine ipaddress' identified by 'user password';

    6.Grent permissions to the user using the below command

    GRANT REPLICATION SLAVE ON *.* TO 'username'@'slave ipaddress' IDENTIFIED BY 'user password';
    7.typed the command 'Show master status;'

    it has shown as "empty set(0.00 sec)"

    7.Go to slave machine and edited my_default file

    added the below lines into the file
    erver-id=2
    master-connect-retry=60
    replicate-do-db=exampledb
    (I have restarted the machine)
    8.Open the cmd in slave,navigate to mysql bin path and connected to the mysql with the credentials, typed the below command:

    CHANGE MASTER TO
    MASTER_HOST='10.0.0.18',
    MASTER_USER='k',
    MASTER_PASSWORD='slave',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=3344;

    ReplyDelete
  2. This article is quite simplified and there are missing details. If you really want to help people in trouble, take the time to include all the details.

    Regards,
    Anthony

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Wonderful blog! I found it while browsing on Yahoo News

    Regards= office.com/setup

    ReplyDelete
  5. This is really an amazing article. Your article is really good and your article has always good content with a good powerpoint with informative information.

    McAfee.com/Activate
    www.mcafee.com/activate
    office.com/setup
    norton.com/setup 
    www.mcafee.com/activate

    ReplyDelete
  6. AOL Desktop Gold is an excellent administration of AOL Mail which anybody can bear the cost of by paying the amount every month. AOL Gold will give you heaps of premium offices like programmed update and ultra-security that makes your information secure from programmers. You can introduce AOL Desktop Gold from the official site of AOL Gold. If you want any sort of help concerning the AOL Gold Software Installation method at that point communicates our customer care administrator.
    Read More...

    ReplyDelete
  7. Primevideo.com/mytv - Do you need to enlist your viable gadget at amazon? Simply go to the site Amazon.com/mytv and adhere to the directions to finish the enrollment of your gadget, at that point you can begin getting a charge out of Amazon Prime Instant Video administration for nothing.
    Read more…

    ReplyDelete
  8. we provide best packers and movers service all over india and i am the regular reader at your site your posts are very genuine i also like your posts so please keep posting new updates
    Packers and Movers Delhi

    ReplyDelete