MySQL replication server configuration


Main steps


Note:  Consider server1 as master and server2 as slave and replicating database as exampledb. 

On Server 1(Master)

      1)      Configuring the Master

ü  To make sure that the replication can work, we must make MySQL listen on all interfaces on the master (server1), therefore we comment out the line bind-address = 127.0.0.1 in /etc/mysql/my.cnf:

Eg: #bind-address           = 127.0.0.1

ü  We have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master), which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database exampledb

Eg:
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = exampledb


  2)   Restart MySQL server

3)   Set up a replication user slave_user that can be used by server2 to access the MySQL    database on server1:

mysql>  GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';
mysql>   FLUSH PRIVILEGES;

4) Find out the master status of the server1 and create a SQL dump of the database.

            mysql>  USE exampledb;
            mysql>  FLUSH TABLES WITH READ LOCK;
            mysql>  SHOW MASTER STATUS;

           In another shell,
          Ex:
          Shell> mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql

5)     Unlock tables
mysql>   UNLOCK TABLES; 


 On server 2(Slave)

1)      Configure slave  server by changing  my.cnf file

         Ex:
        server-id=2
        master-connect-retry=60
        replicate-do-db=exampledb

2)  Restart MySQL server

3)  Import the SQL dump

        Ex:
        Shell> mysql -u root -pyourrootsqlpassword exampledb < snapshot.sq

4)  Give slave parameters

            Ex:
            mysql>  CHANGE MASTER TO MASTER_HOST='192.168.0.100',           
             MASTER_USER='slave_user',   MASTER_PASSWORD='slave_password',          
            MASTER_LOG_FILE='mysql-bin.000001',  MASTER_LOG_POS=19467;

5)  Start slave

              mysql>  START SLAVE;


 6)  Check  slave status (It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output. And also  Slave_IO_State should be Waiting for master to send event)

             mysql> SHOW SLAVE STATUS \G


Ex:
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.100
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 19467
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: exampledb
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 19467
              Relay_Log_Space: 407
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:

11 comments:

  1. I believe Web time sheet software makes the complete employee time clock tracking task easier. Its easy to update, approve and maintain the time sheets in no time.Time Attendance Software

    ReplyDelete
  2. Hi,
    I have taken 2 windows server 2008 r2 machines and joined into domain
    Installed the "mysql-installer-community-5.6.12.0" in both the servers
    i have followed your above mentioned steps

    Problem1 is:
    I have executed the "SHOW MASTER STATUS;" command

    Result:It has shown as "empty set"

    Problem2 is:

    In slave i have executed the comand as "mysql> change master to MASTER_HOST='10.0.0.18',MASTER_USER='repl',MASTER_PASSWO
    RD='slave',MASTER_LOG_FILE='',MASTER_LOG_POS=;"

    Result:ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use near '' at
    line 1

    Could you please help me.

    ReplyDelete
  3. i have changed the my_default text file in master and slave servers;there is no my.cnf ile.

    ReplyDelete
  4. KAYLAN@
    You need to put bin log file e.g; MASTER_LOG_FILE=mysql-bin.000001 and MASTER_LOG_POS=3344 <--(result from mysql command: SHOW MASTER STATUS)

    ReplyDelete
  5. I have executed the "SHOW MASTER STATUS;" command

    Result:It has shown as "empty set(0.00 sec)"

    ReplyDelete
  6. 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
  7. I appreciate if you can help in this task

    ReplyDelete
  8. sorry for the inconvinience..I have resolved the issue myself ..the above issue
    But i am facing another problem:

    >Show slave status;

    Last_IO_Error: error connecting to master 'repl@10.0.0.20:3306' - retry-time: 60 retries: 3

    the above error has shown

    ReplyDelete
  9. The above issue also resolved..Let me know How can i identify the replication is done between master and slave.

    ReplyDelete
  10. make a change on the master database and see if its reflected in the slave database

    ReplyDelete
  11. Interesting color concept:
    dark-black text on a light-black background.

    thankfully, readability.com can fix that.

    ReplyDelete