How to REPLICATE Master/Slave in MySQL.

First of all, MySQL has support for asynchronous (default) and semi-synchronous replications. In infrastructures requiring full synchronization you have to move to different solutions such as MySQL NDB Clustering. Now, the operation is straightforward. The master node maintains a binary log file of the changes performed on its database. On the other hand, the slave(s) is/are periodically reading that file and perform the replication using one of the following two replication types:
- Statement Based Replication (SBR) – (default)
The slave will execute the exact same SQL statements that the master did in its database(s)
- Row Based Replication (RBR)
Here the slave(s) will change/replicate only the changed rows
- Mixed Based Replication (MBR)
Using both of the above replication types.

Introduction:-

Since MySQL 3.23 this very popular RDBMS has replication support. It’s very easy to setup and can be useful for various conditions including:


Security:==

You can have replicated servers that you can take backups without messing around with the production ones.


Performance:==

MySQL allows slave nodes to have read access on the replicated databases. This means, that you can load balance the requests so that the master will handle the write requests while the slave(s) will manage the reading.

In addition to these, MySQL provides a few different options for setting up a replicated environment. Basically, the master node is the one that has full access on the database and the slaves use to replicate its databases. That said, you can have the following architectures:

Master to Slave:==

This is the most common architecture where the master’s data are replicated to a slave node where external services can only perform read operations.

Master to Slaves:==

Similar to the previous one with the main difference of having multiple slave nodes replicating the master’s data.

Master to Slave to slave:==

Here, we have multi-tier architecture where the initial master’s slave node is the master node for a second layer slave and so on.

Circular Replication:==

In this case, we still have a multi-tier architecture like the previous one but the last one serves as a master node for our first server.

Master to Master:===

Here each server is both a master and a slave, the first node acts as a master and slave node to the second one at the same time. This creates a high availability system since with even one working server you still have full access to your data.

How does it Work?
First of all, MySQL has support for asynchronous (default) and semi-synchronous replications. In infrastructures requiring full synchronization you have to move to different solutions such as MySQL NDB Clustering. Now, the operation is straightforward. The master node maintains a binary log file of the changes performed on its database. On the other hand, the slave(s) is/are periodically reading that file and perform the replication using one of the following two replication types:

- Statement Based Replication (SBR) – (default)
The slave will execute the exact same SQL statements that the master did in its database(s)
- Row Based Replication (RBR)
Here the slave(s) will change/replicate only the changed rows
- Mixed Based Replication (MBR)
Using both of the above replication types.

How to set it up?
Here is a quick setup of a simple MySQL master/slave replication using CentOS 5.5 (2.6.18-194.32.1.el5 #1) on two 64-bit x86 systems. I will be calling them master-node and slave-node respectively.

Master Node Setup
First you have to install the MySQL packages

root@Master-node ~]# install mysql-server mysql-client

Next, start the daemon using the default configuration file

root@Master-node ~]# /etc/init.d/mysql start 

And check that it is running

 I suggest setting a password to the MySQL root account like this: 

root@Master-node ~]# mysqladmin -u root -p password

Next, login to the MySQL shell and create a new user for replication as shown below.

Always remember to flush privileges otherwise changes will not take effect. So, we now have a new user ‘myslaveuser’ for our replication server. The next step is to either import or as in my case create an example database.

 As you can see I am using a reading lock in the newly created ‘example_db’ database. I’m doing this to safely take a backup of this using the next command.

root@Master-node ~]# mysqldump --all-database --master-data > master_dump.sql

Now we jump to the configuration file located at ‘/etc/my.cnf’ and insert the following two lines after the ‘mysqld’ tag.

The first one (log-bin) will enable the binary logging feature and use the provided name for it. The second one is used to name the database that the log will monitor. And the last one to identify this server with an ID during the MySQL communication between the two (or more) nodes. After doing this, restart the service…

root@Master-node ~]# /etc/init.d/mysqld restart 

Now, login to your MySQL shell and unlock the locked database using

mysql> unlock tables;

And execute the following command

That shows the binary log file name as well as what databases are affected by it and what is the master’s current position in that file. Now we are pretty much done with the master setup. We can move to the slave node now…

Slave Node Setup

After installing the same packages and starting the service with its default configuration file, you are copying the database to be replicated using the backup we took earlier from the master node.

shell> mysql <master_db.sql

And you reconfigure the ‘/etc/my.cnf’ file to include the following information:

 

Now restart the service and login to the slave’s MySQL shell and issue the following commands…

So, you basically done at this point. You can also use the next command to have a brief overview of what’s going on on the slave node…

As you can see there are numerous options that you can tweak from security related such as the SSL support to performance ones. If we jump back to the master node now and login to its MySQL shell we can see the connected slave like this:

So, that was it. You can now use the master node as a common MySQL database server and see the replicated records on the slave.