How To Set Up Database Replication In MySQL


1. Configure The Master

a. First we have to edit /etc/mysql/my.cnf. We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out the line

#skip-networking
bind-address = X.X.X.X host IP instead the default IP (127.0.0.1)

b. Furthermore 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 test, so we put the following lines into /etc/mysql/my.cnf:
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=test
server-id=1

c. Then restart Mysql service and login to the mysql prompt, give the following permission.

mysql>GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘<some_password>’;

mysql>FLUSH PRIVILEGES;

 

d.To identify mysql log file in master type the below command

 

mysql> SHOW MASTER STATUS;

 

+——————+———-+————–+——————+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000155 | 198 | test | |

+——————+———-+————–+——————+

1 row in set (0.00 sec)

 

mysql>

 

e. Then quit.

 

 

2. Configure The Slave

 

a. create a database name test in mysql.

 

b. Go to /etc/mysql/my.cnf in the slave server and do the following changes.

Comment the line and type the host ip address instead the default.

#skip-networking
bind-address = X.X.X.X host IP instead the default IP (127.0.0.1)

Also enable or uncomment the below line (server-id)in my.cnf

server-id=2 (here we should mention 2 instead the default 1, since the master is configured as 1)
master-host=192.168.0.100 (add a new entry)

c. Save the my.cnf file and restart mysql.

d. Go to mysql prompt in slave and type the command 
   mysql>show slave status\G;

The Slave_IO_Running: YES ; Slave_SQL_Running: Yes should be in the status yes.
If the IO running No; then the log file in master doesnot match the slave. To over come this issue

mysql>STOP SLAVE;

mysql>CHANGE MASTER TOMASTER_LOG_POS = 0;

mysql>CHANGE MASTER TOMASTER_LOG_FILE = 'required bin file';

mysql>START SLAVE;

Now test the replication in mysql test DB by manipulating creating or inserting or deleting any table in the test DB.
It should work...






Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: