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.
- 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:==
Security:==
You can have replicated servers that you can
take backups without messing around with the production ones.
Performance:==
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:==
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:==
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:==
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:==
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:===
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:
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
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
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.
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
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.
No comments:
Post a Comment