Replication: Configuring and Checking the Slave

Once we’ve issued the command start slave;, The Replication should ideally start, But as well all know nothing works initially. So to check everything is working, issue the following command.

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.5.99
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000009
        Read_Master_Log_Pos: 37822065
             Relay_Log_File: ruturaj-vartak-relay-bin.000028
              Relay_Log_Pos: 37822202
      Relay_Master_Log_File: mysql-bin.000009
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: test
        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: 37822065
            Relay_Log_Space: 37822202
            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
1 row in set (0.00 sec)


Check the parameters

  • Slave_IO_Running (Should be yes)
  • Slave_SQL_Running (Should be yes)

If either of them are No, Open the /hostname.err file. This is the mysql error file, You can check it for a specific configuration error. Most often either the Master log is not present (incorrect filename) or the master log position is incorrect. You can re-specify the parameters by using change master to .. command.

But be sure you issue stop slave first and then change master to … command and finally start slave

Once both the Slave_IO and Slave_SQL parameters are yes, you can start writing, modifying anything in the test database of the server.

You can also check the hosts connected by issuing this command on the master

mysql> show slave hosts;
+-----------+----------------+------+-------------------+-----------+
| Server_id | Host           | Port | Rpl_recovery_rank | Master_id |
+-----------+----------------+------+-------------------+-----------+
| 1         | slave-server-1 | 3306 | 0                 | 2         |
+-----------+----------------+------+-------------------+-----------+

Leave a Reply

Your email address will not be published. Required fields are marked *