How-to: MySQL Master/Slave Replication

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.

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.

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

[mysqld]
log-bin=mysqlbin
binlog-do-db=example_db
server-id=1

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…

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 < example_db.dmp

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

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:

Share this:

Related

7 Responses

I am thinking that for production databases, the show master status command should come before the unlock tables command, so that the position in the binlog won’t have already incremented before you get the status results.

Yes, you can balance the requests in order to have slave(s) serving the reading and master(s) dealing with the writing operations.

By default, both read and write requests are served by the master node.

If you choose this king of architecture you might need to tune the replication options since by default the changes could take some seconds on heavy loaded DBs to be transferred from master to slave node(s).

You cannot “FLUSH TABLES WITH READ LOCK;” and exit the MySQL client as is indicated above, as it will release the lock when you exit. You need to start up another instance of MySQL client to obtain the master status.

You cannot “FLUSH TABLES WITH READ LOCK;” and exit the MySQL client as is indicated above, as it will release the lock when you exit. You need to start up another shell and tar the database files and copy them over to the slave. You can also use mysqldump from another shell as long as you add-locks=no, otherwise it will just hang.