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.

2 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