MySQL Replication HowTo Tutorial Video
Introduction
This tutorial explains how to set up MySQL Replication between a master database and a single slave. There is a video demonstration of the whole proces and this page contains detailed step by step instructions.
Detailed Instructions
The video and the following instructions describe how to set up MySQL replication. Our test setup consists of three servers named server1, server2 and server3. We use server1 to run our sample client application (the bookstore example that ships with dbShards and server2 and server3 are our master/slave servers, respectively.
Step 1. MySQL Server Configuration
The first thing we need to do to set up replication is edit the MySQL configuration (usually located in /etc/my.cnf) to give each server a unique server-id. I am using an ID of 1 for the master and 2 for the slave, but you can use any unique IDs here.
For maximum reliability we also need to enable binary logging and ensure that each transaction is flushed to the log immediately on commit. These settings help minimize the number of transactions that are lost in the case of the master database failing.
MySQL replication is asynchronous in nature, meaning that transactions are replicated to the slave db after they have been written to the master database and if the master database fails then any transactions not yet transmitted to the slave will be lost. If this is not acceptable for your application then you should consider alternatives such as dbShards Reliable Replication.
Master
[mysqld] server-id=1 log-bin=mysql-bin innodb_flush_log_at_trx_commit=1 sync_binlog=1
Slave
[mysqld] server-id=2 log-bin=mysql-bin innodb_flush_log_at_trx_commit=1 sync_binlog=1
Once the configuration file has been modified it is necessary to restart MySQL.
Step 2. Create Replication User
Next, we need to create a replication user. The slave will connect to the master server using these credentials.
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'password';
Step 3. Start Slave
Next, we need to run the SHOW MASTER STATUS command to see the current status of the master server.
mysql> show master status; +---------------+-----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------+-----------+--------------+------------------+ | binlog.000005 | 849349769 | | | +---------------+-----------+--------------+------------------+ 1 row in set (0.00 sec)
This output shows us the current position in the transaction log file. We need to take a note of these settings and run the CHANGE MASTER command on the slave to tell it where to start replicating from.
CHANGE MASTER TO MASTER_HOST='server2', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=849349769;
Once we have run the CHANGE MASTER command we can now start replication by executing the START SLAVE command on the slave databases.
START SLAVE;
Step 4. Run The Application
I will now start the bookstore application running on server 1. This demo starts a number of threads simulating customers placing orders for books.
replication-demo:
[java] Connecting to database...
[java] Creating Publishers...
[java] [TIME] Created 1000 Publishers.
[java] [TIME] Created 2000 Publishers.
[java] Creating Books...
[java] [TIME] Created 1000 Books.
[java] [TIME] Created 2000 Books.
[java] [TIME] Created 3000 Books.
[java] [TIME] Created 4000 Books.
[java] [TIME] Created 5000 Books.
[java] [TIME] Created 6000 Books.
[java] [TIME] Created 7000 Books.
[java] [TIME] Created 8000 Books.
[java] [TIME] Created 9000 Books.
[java] [TIME] Created 10000 Books.
[java] Starting Demo...
[java] Iteration #1: Throughput (TPS): read=0.0; write=299.4; error=0.0
[java] Iteration #2: Throughput (TPS): read=0.0; write=303.0; error=0.0
We should now see the row count increasing on both the master and slave databases, showing that replication is now working.
Transaction Loss due to Replication Lag
To see what happens in the event of the master database or server failing, I am now going to remove the network cable from the master database server to simulate a failure.
[java] Iteration #14: Throughput (TPS): read=0.0; write=290.6; error=0.0
[java] Iteration #15: Throughput (TPS): read=0.0; write=293.4; error=0.0
[java] Iteration #16: Throughput (TPS): read=0.0; write=253.6; error=0.0
[java] Iteration #17: Throughput (TPS): read=0.0; write=0.0; error=0.0
[java] Iteration #18: Throughput (TPS): read=0.0; write=0.0; error=0.0
We can see that the client is now failing to write transactions so I am going to stop the client.
I am now going to check the row count on the slave database.
mysql> SELECT COUNT(*) FROM dbs_bookstore.customer_order; +----------+ | COUNT(*) | +----------+ | 25278 | +----------+ 1 row in set (0.00 sec)
I'm re-connecting the network cable now so we can see how many orders were written to the master database.
mysql> SELECT COUNT(*) FROM dbs_bookstore.customer_order; +----------+ | COUNT(*) | +----------+ | 25323 | +----------+ 1 row in set (0.00 sec)
As you can see, 45 transactions have been lost. Depending on the nature of the application, this could be undesirable to say the least.
These numbers are from running with low throughput on low-cost servers but on a typical production setup with a higher load, a higher number of transactions could be lost.
Summary
MySQL Replication is easy to set up and use, and for many purposes it is a very good tool. However, it is not intended for use in reliability or high-availability support, so keep that in mind when you decide to implement it for your application.
If you need the performance of MySQL Replication but without the risk of transaction loss, check out our dbShards Reliable Replication product. dbShards does support full transaction reliability, failover and high availability.