A realtime experience that depicts MysQl Replication

Install mysql in both machines. The first machine will act as Master 1 and Slave 2 M1/S2 with ip (10.0.0.1) and the second will act as Slave 1 and Master 2 S1/M2 (10.0.0.2)

Plan out which all database you want to replicate and which all you want to ignore. Synchronize both database in master and slave using rsync first. If its brand new machines then create databases in both machines, as replication doesn’t mean that if you create a database in the master it will automatically get created on the slave. It will replicate only the data inside the databases.

In the master M1 open my.cnf and add the following entries under [mysqld] section.

server-id =1 #this value should be unique

log-bin=db-bin

relay-log=db-relay-bin

binlog-do-db=shijil #database that needs to be replicated

binlog-ignore-db=mysql #database that doesn't needs to be replicated

auto_increment_increment=2  #This is added in order to avoid collision with slave

auto_increment_offset=1

Now in M1 create a user replication such that S1 have the privilege to access all the databases of M1

mysql> GRANT ALL PRIVILEGES ON *.* TO REPLICATION@'10.0.0.2' IDENTIFIED BY 'pass1';
Restart the master mysql
/etc/init.d/mysqld restart
Enter the mysql prompt and issue the command
mysql>show master status \G;

The output will be something like below

show master status \G;

*************************** 1. row ***************************

File: db-bin.000001

Position: 220159222

Binlog_Do_DB: shijil

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

ERROR:

No query specified

************************************************

Now in the S1 10.0.0.2 open its my.cnf and add the entry under [mysqld] section

server-id=2

master-host = 10.0.0.1

master-user = replication

master-password = pass1

master-port = 3306

It is also good to add the host entries from mysql command prompt and it should be like this

mysql> CHANGE MASTER TO

->MASTER_HOST='10.0.0.1',

-> MASTER_USER='replication',

-> MASTER_PASSWORD='pass1',

-> MASTER_LOG_FILE='db-bin.000001' ;

Remember the master log file is the same as the output shown by show master status \G; in the master server.

mysql> start slave ;

mysql> show slave status \G;

If there is no errors and the entries below

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

are both showing Yes then you are good to go. Also check the mysql log file and make sure it is connected to master.

Phase II

Now we are going to setup the Slave as Master2 M2 (10.0.0.1) and Master as S1 (10.0.0.1)
Again open my.cnf and add the entries under [mysqld] section

log-bin=db-bin

relay-log=db-relay-bin

binlog-do-db=shijil #database that needs to be replicated

binlog-ignore-db=mysql #database that doesn't needs to be replicated
auto_increment_increment=2

auto_increment_offset=2

and create a user replication so that S2 (10.0.0.1) will have all the privilege to access all the database in M2 (10.0.0.2)
mysql> GRANT ALL PRIVILEGES ON *.* TO REPLICATION@'10.0.0.1' IDENTIFIED BY 'pass2';
Now restart the mysql in s2
/etc/init.d/mysqld restart
Go to mysql prompt and issue the command;
show master status \G;

It will show the output as something like below

show master status \G;

*************************** 1. row ***************************

File: db-bin.000001

Position: 220153453

Binlog_Do_DB: shijil

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

ERROR:

No query specified

************************************************

Now log into S2 and edit its my.cnf add the following entries

master-host = 10.0.0.2

master-user = replication

master-password = pass2

master-port = 3306

After saving it issue the command in mysql prompt

mysql> CHANGE MASTER TO

-> MASTER_HOST='10.0.0.2',

-> MASTER_USER='replication',

-> MASTER_PASSWORD='pass2',

-> MASTER_LOG_FILE='db-bin.000001' ;

mysql> start slave ;

mysql>show slave status \G;

Here there shouldn’t be any errors also check the log file and make sure it is connected to master.

Now create a table named epson in shijil in M1 and check for the table epson in S1.

Troubleshooting
If at all any errors stop both mysql servers and remove all the log files from /var/lib/mysql

rm -fv db-bin* master.info relay*

This should be done on both servers and then start Master first and then Slave.

Sai Kottuvally Articles, MySQL Mysql replication