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 = in /etc/mysql/my.cnf:

Eg: #bind-address           =

ü  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

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';

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,
          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


2)  Restart MySQL server

3)  Import the SQL dump

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

4)  Give slave parameters

            mysql>  CHANGE MASTER TO MASTER_HOST='',           
             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

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  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
                   Last_Errno: 0
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 19467
              Relay_Log_Space: 407
              Until_Condition: None
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
               Last_SQL_Errno: 0


  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

  2. Hi,
    I have taken 2 windows server 2008 r2 machines and joined into domain
    Installed the "mysql-installer-community-" 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='',MASTER_USER='repl',MASTER_PASSWO

    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.

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

  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)

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

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

  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-" 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
    (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:


  7. I appreciate if you can help in this task

  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@' - retry-time: 60 retries: 3

    the above error has shown

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

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

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

    thankfully, can fix that.

  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

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

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

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

  16. Wonderful post. Thanks for sharing with us.

    Mysql DBA Training