Mysql Replication

Follow the steps if you want to do Mysql Replication.

Example:-

MySQL Server A and Server B

(1) Database should be same on both servers.

(2) Create Slave User on each of the 2 servers:-
USE mysql;
INSERT INTO user(User, Passsword, Select_priv, Reload_priv, Super_priv, Repl_slave_priv) VALUES
('USR','PASSWD','Y','Y','Y','Y');
FLUSH PRIVILEGES;

(3) Configuring the MySQL servers:-
- Know IP address of each servers.
- Edit my.cnf or my.ini
Server A
========
server-id = 1
replicate-same-server-id = 0

# Configuration variables that prevent key collisions.
# auto-increment-increment = N [N: No of servers in the replication setup]
# auto-increment-offset = 1
# auto-increment-offset and server-id should be integer, same and consecutive.

auto-increment-increment = 2
auto-increment-offset = 1

master-host = <IP of Server B>
master-user = <slave user>
master-password = <slave password>
master-connect-retry = 60
replicate-do-db = <DB Name>

log-bin = C:\mysql\log\log-bin/log # Change this path as per your system
binlog-do-db = <DB Name>

Server B
========
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2

master-host = <IP of Server A>
master-user = <slave user>
master-password = <slave password>
master-connect-retry = 60
replicate-do-db = <DB Name>

log-bin = C:\mysql\log\log-bin/log # Change this path as per your system
binlog-do-db = <DB Name>

- Restart the 2 servers.

(4) Syncronizing the servers.

slave stop;
show master status; # Use result of Server A to Server B

CHANGE MASTER TO MASTER_HOST='<master's IP>',
MASTER_USER='<slave user>',
MASTER_PASSWORD='<slave password>',
MASTER_LOG_FILE='<master's log file name>',
MASTER_LOG_POS=<master's log file position>;

start slave;
show slave status;

(5) Reseting Replication
- Shutdown both servers
- delete relay logs
- follow steps (1) to (4) again

(6) Testing
- Primary keys generated on Server A should be odd numbers
- Primary keys generated on Server B should be even numbers