MySQL Master-Master Replication

3 Comments

Master-Master Replication

Creating and maintaining a duplicate copy of a database or file system on a different computer, typically a server.  The term usually implies the intelligent copying of parts of the source database which have changed since the last replication with the destination.

Replication may be one-way or two-way.  Two-way replication is much more complicated because of the possibility that a replicated object may have been updated differently in the two locations in which case some method is needed to reconcile the different versions.

We have seen how the one-way replication works i.e. Master-Slave; here whatever changes are updated on the Master server is replicated onto the Slave server.

Two-way replication i.e. Master-Master replication will be bit different. As the name itself suggests both the servers will be Master to another.

Master-Master replication allows data in multiple databases to be in sync. For example in a master-master replication, if a row is inserted into one of the databases in a system, it will be automatically propagated to other databases in system. Similarly the updates and deletes too get propagated automatically to other databases.

Configuring the Master-Master Replication

To avoid any confusion we will define our servers as below.  Server 1 and Server 2 will be used as reference wherever required

Server 1: Master 1 and Slave 2

Server 2: Slave 1 and Master 2

On Server 1 (Master 1 and Slave 2):

We will be configuring the Server 1 which will act as both Master and Slave at the same time.

At the prompt> vi /etc/my.cnf (Configuration file where you define the variables for setting up master and slave)

http://dbperf.files.wordpress.com/2010/03/master1_cnf.jpeg

Insert the following information as show in the screen below

If you look at the my.cnf file, we are providing information to the Server 1 to act as Master to Server 2 as well as Slave at the same time. Similarly we would be configuring the Server 2 to act as Slave and Master at the same time to Server 1 later.

Master 1 Information in the my.cnf

‘log-bin’: location where the master log files is saved, whenever there is a change to the database, the log is updated with the changes. These logs are read by the slave who will replicate the same changes onto its server.

You need to specify the location for these logs to be saved on Master; the default location is /var/lib/mysql.

‘binlog-do-db’: As the name suggest, it needs input of a databases name for which you want to create a binary log and save it to above mentioned location for ex: /var/log/mysql. You can specify any number of databases.

It is a good practice to specify a binlog-do-db separately for the number of databases you want to replicate. However there are dba’s who would mention the database names to a single binlog-do-db by use commas as separators.

‘server-id’: You tell the server what is it?  Master or a Slave, id 1 would suggest that it’s a master and id 2 will suggest it is a slave.


After you save and exit the my.cnf, at the mysql prompt grant the access privileges to the server which will be configured as Slave

Slave 2 Information in my.cnf

‘master-host’: IP address of the Server 2.

‘master-connect-retry’: Retries slave need to try connecting to Master 2 on Server 2.

‘master-user’: User name which was granted replication slave privilege on Server 2.

‘master-password’: Password provided by using identified by clause to connect to Master 2 on Server 2.

‘relay-log’: Relay log save events that describes database changes on Server 2.

‘relay-log-index’: Index file contains the names of all used relay log files on Server 2.

After you save and exit the my.cnf, at the mysql prompt grant the access privileges to the server 2 which will be configured as Slave

GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’ @ 10.X.X.X   IDENTIFIED BY ‘password’;

Now it is very important to restart the mysql daemon, since you can defined all your variables under [mysqld] in your /etc/my.cnf file, the changes will be picked up only after you restart you daemon.

On Server 2 (Slave 1 and Master 2):

At the prompt> vi /etc/my.cnf (Configuring the Server 2 to act as Slave 1 and Master 2)

Follow the screen below configuration

http://dbperf.files.wordpress.com/2010/03/master2_cnf.jpeg

Once done with the my.cnf file save and exit and at the mysql>

Grant Replication slave on *.* to ‘slave_user’@10.X.X.X identified by ‘mysql123’;

Restart the daemon for the changes made to my.cnf take effect. To start the daemon at the prompt>

mysqld –u root &

Once both the servers have started, use the following commands.

On Server 1:

Show Master Status;

http://dbperf.files.wordpress.com/2010/03/server1_master.jpeg

Similarly you will get the same screen on Server 2 on the command Show Master status.

Slave Start;

Show Slave Status\G;

http://dbperf.files.wordpress.com/2010/03/server1_slave.jpeg

Slave_IO_Running , Slave_SQL_Running should show the status as YES and Slave_IO_State show be ‘waiting for master to send event’.

Use the above commands on Server 2 to check if everything is running fine.

Try creating tables and test the functionality, the tables and data should get replicated both ways. If everything goes fine you have setup your Master-Master replication.

MySQL Master-Slave Replication

4 Comments

HOW DOES THE MASTER SLAVE REPLICATION WORK?

When any changes are made onto your databases on the Master server, will be logged and saved on you server location. The logs can be configured as where you want them to be saved. You can specify the log location in your my.cnf file which is most commonly found in /etc directory.

These logs are read by the slave server and the changes mentioned in the master log are replicated onto the databases lying on the slave machine.

The replication between master and slave sounds very simple, however there are several challenges you will face when you want it to start working as per your expectations.

STEP BY STEP CONFIGURATION

ON MASTER:

At the Prompt> vi /etc/my.cnf (Mysql does not create a my.cnf at this location, if not created go ahead and create it with the same command)

http://dbperf.files.wordpress.com/2010/03/master.jpeg

This image is a screen shot of a sample replication, my.cnf can be optimized as per you preference.

Here you can declare few setting in you my.cnf, which are defined as follow,

log-bin’: location where the master log files is saved, whenever there is a change to the database, the log is updated with the changes. These logs are read by the slave who will replicate the same changes onto its server.

You need to specify the location for these logs to be saved on Master; the default location is /var/lib/mysql.

‘binlog-do-db’: As the name suggest, it needs input of a databases name for which you want to create a binary log and save it to above mentioned location for ex: /var/log/mysql. You can specify any number of databases.

It is a good practice to specify a binlog-do-db separately for the number of databases you want to replicate. However there are dba’s who would mention the database names to a single binlog-do-db by use commas as separators.

‘server-id’: You tell the server what is it?  Master or a Slave, id 1 would suggest that it’s a master and id 2 will suggest it is a slave.

These are the few configuration properties which are required to create a replication Master, you can set many more variables in this my.cnf, and at this point the above variables are enough to go ahead with the setup.

After you save and exit the my.cnf, at the mysql prompt grant the access privileges to the server which will be configured as Slave

GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’ @ 10.X.X.X   IDENTIFIED BY ‘password’;

Now it is very important to restart the mysql daemon, since you can defined all your variables under [mysqld] in your /etc/my.cnf file, the changes will be picked up only after you restart you daemon.

Note: I would prefer to restart the mysql daemon rather than mysql service is because the service does not load the changes defined under mysqld. Hence incase if you are unable to notice the changes by just restarting you /etc/init.d/mysql service, go ahead and start you daemon.

At the prompt> mysqld –u root &

ON SLAVE:

At the Prompt> vi /etc/my.cnf

http://dbperf.files.wordpress.com/2010/03/slave.jpeg

‘Server-id’: As mentioned earlier will define master or slave.

‘master-host’: IP address of the Master server.

‘master-connect-retry’: Retries slave need to try connecting to Master.

‘master-user’: User name which was granted replication slave privilege on Master.

‘master-password’: Password provided by using identified by clause to connect to Master.

‘relay-log’: Relay log save events that describes database changes.

‘relay-log-index’: Index file contains the names of all used relay log files.

Restart the daemon on the Slave server for the configuration to take effect.

As above we have mentioned two databases SampleDB and My_database for replication hence make sure the databases are present on both Master and Slave.

On Master run the following command at mysql prompt

FLUSH TABLES WITH READ LOCKS;

SHOW MASTER STATUS;

Show master status should return the Master bin-log file number and bin-log position along with the databases names which are configured for replication.

Status will look similar to this,

http://dbperf.files.wordpress.com/2010/03/binlog.jpeg

On the Slave server use the following commands at the mysql prompt,

SLAVE STOP;

CHANGE MASTER TO MASTER_HOST=’10.X.X.X’, MASTER_USER=’slave_user’, MASTER_PASSWORD=’slave_password’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=20097900;

SLAVE START;

SHOW SLAVE STATUS\G;

Show slave status command should return the following.

http://dbperf.files.wordpress.com/2010/03/slavestatus.jpeg

Ensure the Slave_IO_Running and Slave_SQL_Running is YES, and Slave_IO_State is waiting for master to send event.

If any of these status is No or blank, the replication will not function.

After starting the slave run the following command on Master,

UNLOCK TABLES;

Check creating a test table on Master and see if the same table is getting displayed when you run SHOW TABLES;

If everything goes well your Master-Slave Replication is configured.

Some challenges you might face if your replication goes due to server crash,

Slave_IO_Running will show the status as NO, and no replication is done.

Solution: Clear the relay logs and master.info files from /var/lib/mysql and repeat the above steps. Do not forget to restart the daemons if any changes are made to the my.cnf file.

Wait for the Next topic Master-Master Replication.

Follow

Get every new post delivered to your Inbox.

Join 54 other followers