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:

64 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
  12. hello sir
    I want to create clone data base from an existing database int that same mysql server
    in that same system
    I want to know the process in both window & Linux

    ReplyDelete
  13. Hi Amigoes... Can u explane how push data from local to central server in spaecific time ...help me ..asap...

    ReplyDelete
  14. how can i write in java... help me

    ReplyDelete
  15. How to sync local mysql database to live server database in offline / online mode

    ReplyDelete
  16. Business users may choose the product according to their business level. Other product by the antivirus is for different categories and users across the world rely on it to secure their data.
    For More information Visit Our Site:
    norton.com/setup
    office.com/setup
    office.com/setup
    mcafee.com/activate

    ReplyDelete
  17. Spirit Airlines Reservations A good Blog gives a lot more knowledge about this. I will continue to support your work. Thank you.

    ReplyDelete
  18. Thanks for sharing the useful information. You have mentioned all essential points for getting Assignment Help easily.Spirit Airlines Cancellation

    ReplyDelete
  19. www.hulu.com/activate A good Blog gives a lot more knowledge about this. I will continue to support your work Thank you.

    ReplyDelete
  20. Allegiant Airlines Reservations Thanks for sharing such a nice blog. I really appreciate that please keep on posting more Blogs like this Thank you.

    ReplyDelete
  21. Allegiant Airlines Reservations It's wonderful as your other blog posts And thank you for posting.

    ReplyDelete
  22. Thanks for sharing such type of information you can also check my post about
    American Airlines Book Flight

    ReplyDelete

  23. Thanks for sharing such type of information you can also check my post about
    Qatar Airways Cancellation Fee

    ReplyDelete
  24. Very interesting post, I have complete solution about Qatar Airways Booking

    ReplyDelete
  25. Thanks for sharing such type of information you can also check my post about
    Swiss Airlines reservations official site

    ReplyDelete

  26. Thanks for sharing such type of information you can also check my post about
    Finnair Airlines book flight

    ReplyDelete

  27. Thanks for sharing such type of information you can also check my post about
    Kenya Airways online booking

    ReplyDelete
  28. Thanks for sharing such type of information you can also check my post about
    Korean Air book seats

    ReplyDelete


  29. Thanks for sharing such type of information you can also check my post about
    Royal Jordanian customer service

    ReplyDelete

  30. Thanks for sharing such type of information you can also check my post about
    TUI Airways reservations

    ReplyDelete

  31. Thanks for sharing such type of information you can also check my post about
    Turkish airlines booking flight

    ReplyDelete

  32. Airlines-gethuman.org is a platform where you can Find Contact Information for all the airlines, Assistance with booking your flights, and vacation packages easily. It helps you to save both, your money and your time.

    Southwest Airlines Reservations
    Southwest Airlines Flights

    ReplyDelete

  33. Thanks for sharing such type of information you can also check my post about
    Ethiopian Airlines booking

    ReplyDelete

  34. Thanks for sharing such type of information you can also check my post about
    Singapore Airlines booking

    ReplyDelete
  35. Thanks for sharing such type of information you can also check my post about
    Westjet book a flight

    ReplyDelete

  36. Thanks for sharing such type of information you can also check my post about
    Hawaiian Airlines vacations

    ReplyDelete
  37. Thanks for sharing such type of information you can also check my post about
    Korean Air Cancellation Fee

    ReplyDelete

  38. Thanks for sharing such type of information you can also check my post about
    Air France booking

    ReplyDelete

  39. Thanks for sharing such type of information you can also check my post about
    Air France book a flight

    ReplyDelete


  40. Thanks for sharing such type of information you can also check my post about British Airways Cancellation Fee

    ReplyDelete

  41. Thanks for sharing such type of information you can also check my post about United Airways Cancellation Fee

    ReplyDelete
  42. Tubi.tv/activate is an online platform that is a creation of Fox Corporation and stream the latest content and videos of your interest.
    For More Information Visit Our Site:
    tubi tv
    tubi.tv/activate
    Roadrunner email login

    ReplyDelete
  43. American Airlines Reservations are the best option for the people and you can reserve American airlines tickets Under the very affordable budget for the reservations. Reservations are no expensive thing for you once you're opting American flights.

    ReplyDelete