Replication: Configuring Slave

Now that master is all set! Its time for the slave to obey the master. To setup the slave, We need to tell the Slave server which log file it is supposed to read, and yet again we need to give a unique server-id to the slave.

So open the /etc/my.cnf and add the following below the [mysqld] section.

server-id=2
replicate-do-db=test
report-host=slave-server-1

  • The directive replicate-do-db: specifies that only test database is to be replicated.
  • The directive report-host: specifies the hostname that will be seen on the master

Restart your MySQL server.

Now we are ready to tell the server about the master server.
Give following command to the slave server.

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.5.100',
    ->     MASTER_USER='repl',
    ->     MASTER_PASSWORD='slavepass',
    ->     MASTER_LOG_FILE='mysql-bin.000001',
    ->     MASTER_LOG_POS=4;

The master_log_pos is the parameter which tells the server to read the file from a particular position.
You can check the position by the following command on the master server.

mysql> show binlog events in 'mysql-bin.000009' limit 10;
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                               |
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000009 | 4    | Format_desc | 2         | 98          | Server ver: 5.0.19-standard-log, Binlog ver: 4                     |
| mysql-bin.000009 | 98   | Intvar      | 2         | 126         | INSERT_ID=10077                                                    |
| mysql-bin.000009 | 126  | Query       | 2         | 430         | use `test`; insert into testtable (tname) values('ruturaj vartak') |
| mysql-bin.000009 | 430  | Query       | 2         | 511         | use `test`; truncate table testtable                               |
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+

So you can MASTER_LOG_POS set anything in the Pos column.

This should set up all the required configuration of the slave. You just need to tell slave to start the replication of the slave by the following command

mysql> start slave;

Leave a Reply

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