Setting up MySQL replication

1. Settings to be done in Master DB server:

Edit my.cnf file (/etc/my.cnf) and enter the following:
server-id = 1
log-bin

2. Login to mysql DB in master server and run the following command:
mysql> show master status;

Note down the following values: MASTER_LOG_FILE & MASTER_LOG_POS
Then give permission for the slave server
mysql>GRANT REPLICATION SLAVE ON *.* TO user@slave_IP IDENTIFIED BY ‘password’;
mysql>GRANT FILE ON *.* to user@ slave__IP identified by ‘password’;

3. Take the DB dump in master server and restore it in slave server.

4. Edit my.cnf file in slave server (/etc/my.cnf) and enter the following:
server-id = 2
master-host = Master_Server_IP
master-user = user
master-password = password
log-warnings

5. Login to mysql DB in slave server and run the following command:
mysql>CHANGE MASTER TO MASTER_HOST=’Master_Server_IP’, MASTER_USER=’user’,
MASTER_PASSWORD=’password’,
MASTER_LOG_FILE=’Log filename’,
MASTER_LOG_POS= number;
mysql>start slave;

6. Restart mysql service in both the servers.

Leave a Reply

Your email address will not be published.