MySQL Master-Slave Replication

In this post, we will introduce how to setting up matser-slave replication in MySQL. First why we want to use MySQL master-slave replication is in our application, the times users read data is much more than the times they edit data. To make our database more effiecient, we want to use master-slave replication, seperating read and write operations.

What is master-slave replication?

MySQL replication is a process that enables data from one MySQL database server(the master) to be copied automatically to one or more MySQL database servers(the slaves). It is a one-way replication(from master to slave), only the master database is used for the write operations, while read operations are spread on multiple slave databases.

Its advantages

• Analytic applications can read from the slave(s) without impacting the master
• Backups of the entire database of relatively no impact on the master
• Slaves can be taken offline and sync back to the master without any downtime

Principle

The above image explains the principle of master-slave replication.

• Step 1: The data changes in master database server firstly are written in mysql’s binary log.
• Step 2: The master’s binary log is read by the slave’s IO thread and then written to slave’s relay log.
• Step 3: Slave’s SQL thread reads the relay log and write it to slave database server.

Setting up steps

Next, we will explain how to realize MySql master-slave replication on both database and code levels.

1. Modify Database configurations

Step 1: Create master and slave database servers

The version of master database must be lower or equal than that of ther slave database. So we use MySQL 5.7 as the master database and MySQL 8.0 as the slave database.

Step 2: Configurate master and slave database servers

We need to configurate 1) Binary log, 2) Relay log, and 3) assign the master server to the slave server.

• Configurate binary log: in C:\ProgramData\MySQL\MySQL Server 5.7\my.ini, do modification like this.

  port=3308
  # Server Id.
  server-id=1
  # Binary Logging.
  log-bin=master-bin
  log-bin-index=master-bin.index

• Configurate Relay log: in C:\ProgramData\MySQL\MySQL Server 8.0\my.ini, do modification like this.

  port=3306
  # Server Id.
  server-id=2
  

relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

After modifying `my.ini` files, we need to restart the servers. We can use command shell command `net stop server_name; net start server_name`.

- **Assign master server to slave server**.

Go to the **master server**, check its status.

SHOW MASTER STATUS;

<img src="masterstatus.png" width="80%" height="80%">

Then create replication.
``` sql
CREATE user repl;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1' IDENTIFIED BY 'mysql';
FLUSH PRIVILEGES;

Go to the slave server. And type in the following command.

change master to master_host='127.0.0.1',master_port=3308,master_user='repl',master_password='mysql',master_log_file='master-bin.000001',master_log_pos=0;
FLUSH PRIVILEGES;
start slave;

We can now check the slave status.

SHOW SLAVE STATUS \G;

By now, we have completed the configuration of master-slave replication.

Step 3: Assigning CRUD privileges to master and slave servers

In master server, we create a new user work, and grant it CRUD privileges.

GRANT select, insert, update, delete ON *.* TO 'work'@'%' IDENTIFIED BY '230230' WITH GRANT OPTION;

For slave server, we do the following.

CREATE user  'work'@'%' IDENTIFIED BY '230230';
GRANT all privileges on *.* to 'work'@'%' with grant option;

2. Configuration master-salve replication in our app

• 1. Create DynamicDataSource class that extends AbstractRoutingDataSource and override determinCurrentLookupKey() method.

public class DynamicDataSource extends AbstractRoutingDataSource {
  @Override
  protected Object determineCurrentLookupKey() {
    return DynamicDataSourceHolder.getDbType();
  }
}

• 2. Create a class DynamicDataSourceHolder used to store info of variables like DB_MATER, DB_SLAVE.

CLICK TO SEE MORE

public class DynamicDataSourceHolder {

  private static Logger logger = LoggerFactory.getLogger(DynamicDataSourceHolder.class);

  //Ensure thread safe
  private static ThreadLocal<String> contextHolder = new ThreadLocal<String>();

  public static final String DB_MASTER = "master";
  public static final String DB_SLAVE = "slave";

  public static String getDbType() {
    String db = contextHolder.get();
    if (db == null) db = DB_MASTER;
    return db;
  }

  /**
   * Set db type
   * @param str
   */
  public static void  setDbType(String str){
    logger.debug("Using Data Source:" + str);
    contextHolder.set(str);
  }

  /**
   * Clear connections
   */
  public static void clearDbType(){
    contextHolder.remove();
  }
}

• 3. Create a class DynamicDataSourceInterceptor which implements Mybatis Interceptor. Override Interceptor method which assigns different operations to master server or slave servers. Override plugin method, which tells the created interceptor only intercept Executor (i.e. CRUD SQL info).

CLICK TO SEE MORE

@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class DynamicDataSourceInterceptor implements Interceptor {

  private static Logger logger = LoggerFactory.getLogger(DynamicDataSourceInterceptor.class);

  private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*";

  @Override
  public Object intercept(Invocation invocation) throws Throwable {
    //true if the method is transactional
    boolean synchronizationActive = TransactionSynchronizationManager.isActualTransactionActive();
    Object[] objects = invocation.getArgs();

    // the first arguments are insert, select ,delete,...
    MappedStatement ms = (MappedStatement) objects[0];
    String lookupKey = DynamicDataSourceHolder.DB_MASTER;
    if (!synchronizationActive) {
      if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
        // if use generated primary key, should use master server. 
        // Cos next operation will use this primary key to update other info
        if (ms.getId().contains((SelectKeyGenerator.SELECT_KEY_SUFFIX))) {
          lookupKey = DynamicDataSourceHolder.DB_MASTER;
        } else {
          BoundSql boundSql = ms.getSqlSource().getBoundSql(objects[1]);
          // sql statement compressed and for the convenience of Regular Express Matching.
          String sql = boundSql.getSql().toLowerCase(Locale.CANADA)
                               .replace("[\\t\\n\\r]", " ");
          if (sql.matches(REGEX)) {
            lookupKey = DynamicDataSourceHolder.DB_MASTER;
          } else {
            lookupKey = DynamicDataSourceHolder.DB_SLAVE;
          }
        }
      }
    } else {
      lookupKey = DynamicDataSourceHolder.DB_MASTER;
    }

    logger.debug("Setting Method[{}] use[{}] Stratege, SqlCommandType [{}]..", ms.getId(), lookupKey, ms.getSqlCommandType().name());

    // set up the MySQL Server  type
    DynamicDataSourceHolder.setDbType(lookupKey);

    // continue to process the sql statement
    return invocation.proceed();

  }

  /**
   * Return object or its proxy after intercepting.
   * Only intercept Executor object(CRUD SQL info);
   *
   * @param target
   * @return
   */
  @Override
  public Object plugin(Object target) {
    if (target instanceof Executor) return Plugin.wrap(target, this);
    return target;
  }

  @Override
  public void setProperties(Properties properties) {}
}

• 4. In mybatis.xml, configurate the created DynamicDataSourceInterceptor as a plugin.

     <plugins>
     <plugin interceptor="com.example.o2o.dao.split.DynamicDataSourceInterceptor">
     </plugin>
     </plugins>

• 5. Edit jdbc properties including different urls for master and slave servers.

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.slave.url=jdbc:mysql://localhost:3306/o2odb?useUnicode=true&characterEncoding=utf8
jdbc.master.url=jdbc:mysql://localhost:3308/o2odb?useUnicode=true&characterEncoding=utf8
jdbc.username=sysTJbQiw8g= #(work)
jdbc.password=yxhZD/edEzCAyXoBoTkTRQ==

• 6. Edit dataSource bean in spring-dao.xml.
     • Let C3P0 data source bean become an abstractDataSource, so both the master and slave servers can extend it.
     • Create master and slave bean which extends abstractDataSource, configurate their url, driverClass, username, and password.
     • Create dynamicDataSource bean, using the class DynamicDataSource we created. And inject master and slave bean in Map<Object, Object> targetDataSources of this bean.
     • Configurate proxy LazyConnectionDataSourceProxy. Inject created dynamicDataSource in it to let the connection of master or slave server is created at runtime according to the SQL statment.

CLICK TO SEE MORE

<bean id="abstractDataSource"
      abstract = "true"
      class="com.mchange.v2.c3p0.ComboPooledDataSource"
      destroy-method="close">
    <!-- c3p0连接池的私有属性 -->
    <property name="maxPoolSize" value="30" />
    <property name="minPoolSize" value="10" />
    <!-- 关闭连接后不自动commit -->
    <property name="autoCommitOnClose" value="false" />
    <!-- 获取连接超时时间 -->
    <property name="checkoutTimeout" value="10000" />
    <!-- 当获取连接失败重试次数 -->
    <property name="acquireRetryAttempts" value="2" />
</bean>

<!--Master /Slave DataSource-->
<bean id="master" parent="abstractDataSource">
    <!--connection pool attrs-->
    <property name="driverClass" value="${jdbc.driver}"/>
    <property name="jdbcUrl" value="${jdbc.master.url}"/>
    <property name="user" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
</bean>

<bean id="slave" parent="abstractDataSource">
    <!--connection pool attrs-->
    <property name="driverClass" value="${jdbc.driver}"/>
    <property name="jdbcUrl" value="${jdbc.slave.url}"/>
    <property name="user" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
</bean>

<!--Configure Dynarmic Data Routing Data Source -->
<bean id ="dynamicDataSource" class= "com.example.o2o.dao.split.DynamicDataSource">
    <!-- Map<Object, Object> targetDataSources; -->
    <property name="targetDataSources">
        <map>
            <entry value-ref="master" key="master"></entry>
            <entry value-ref="slave" key="slave"></entry>
        </map>
    </property>
</bean>

<!--Evaluate DataSource when it is needed at runtime, lazy connection/evaluation-->
<bean id ="dataSource" class ="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
    <property name="targetDataSource">
        <ref bean="dynamicDataSource"></ref>
    </property>
</bean>

Now, we have completed the whole setting up procedure of master-slave replication. It’s a little complicated. But following the above steps I think we can ultimately fix it. Hope this post would be helpful.