xorl %eax, %eax

How-to: MySQL Master/Slave Replication

with 7 comments

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 ~]# yum install mysql-server mysql-client

Next, start the daemon using the default configuration file

[root@master-node ~]# /etc/init.d/mysqld start

And check that it is running

[root@master-node ~]# netstat -tnlp | grep mysqld
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      3744/mysqld  

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

[root@master-node ~]# mysqladmin -u root password password

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

[root@master-node ~]# mysql -u root -p
Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslaveuser'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>

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.

mysql> CREATE DATABASE example_db;
Query OK, 1 row affected (0.00 sec)

mysql> USE example_db;
Database changed
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

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-databases --master-data > example_db.dmp

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…

[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

mysql> SHOW MASTER STATUS;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| mysqlbin.000004 |       98 | example_db   |                  | 
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

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:

[mysqld]
log-bin
replicate-ignore-db=mysql
log-slave-updates
log-warnings

report-host=192.168.0.2     # Slave's IP or hostname
server-id=2
master-host=192.168.0.1    # Master's IP or hostname
master-user=myslaveuser    # Slave user created on master node
master-password=password   # Slave user's password on master node
master-connect-retry=60
master-port-3306           # Master's MySQL listening port
replicate-do-db=example_db # Which database to replicate from the master

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

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin.000004', MASTER_LOG_POS=98;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

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…

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.1
                Master_User: myslaveuser
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysqlbin.000004
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000005
              Relay_Log_Pos: 234
      Relay_Master_Log_File: mysqlbin.000004
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: example_db
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 234
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

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:

mysql> SHOW SLAVE HOSTS;
+-----------+-------------+------+-------------------+-----------+
| Server_id | Host        | Port | Rpl_recovery_rank | Master_id |
+-----------+-------------+------+-------------------+-----------+
|         2 | 192.168.0.2 | 3306 |                 0 |         1 | 
+-----------+-------------+------+-------------------+-----------+
1 row in set (0.00 sec)

mysql> quit
Bye
[root@master-node ~]#

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.

Written by xorl

March 13, 2011 at 02:23

Posted in administration

7 Responses

Subscribe to comments with RSS.

  1. 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.

    true?

    joseph cheek

    May 17, 2011 at 20:47

  2. The MySQL documentation does not require locking/unlocking tables for executing this command. So, I assume that it internally performs the required locking.

    xorl

    May 17, 2011 at 22:20

  3. Do you know if it’s possible to force the slave to get all the read requests? Is it a kind of load balancing strategy? I’ve implemented it, but it seems to be using a roundRobin approach by default.

    Beth Shaw

    July 18, 2011 at 17:28

  4. 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).

    xorl

    July 18, 2011 at 20:52

  5. On the slave setup, it must be “master-port=3306” and not “master-port-3306”.

    Shakthi Kannan

    August 17, 2011 at 09:34

  6. 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.

    Stephan Edelman

    September 19, 2011 at 03:57

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

    Stephan Edelman

    September 19, 2011 at 04:09


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s