MySQL读写分离实战

【分布式架构初探】

第四节-MySQL读写分离实战

---

4.1 预备工具以及环境
4.2 虚拟机的准备
4.3 CentOS7的安装
4.4 MySQL数据库的安装
4.5 MySQL主从配置(一主一从)
4.6 实现读写分离代码：驱动方式
4.7 实现读写分离代码：SQL解析方式

---

4.1 预备工具以及环境

这个实战我们是在CentOS7操作系统下面进行的，所以我们需要准备：
1. VMWare 虚拟机           [http://sw.bos.baidu.com/sw-search-sp/software/61e22b5779e96/VMware_workstation_full_12.5.0.11529.exe](下载链接)
2. CentOS7 GHost 镜像文件 [http://mirrors.aliyun.com/centos/7/isos/x86_64/](下载链接)
3. MySQL5.7 数据库        
4. Java/php的编译环境开发

4.2 虚拟机的准备

如果有裸机的童鞋可以略过这一节，这里假设VMWare 安装在Windows操作系统上
VMWare 的安装很简单，下一步、下一步，完成，最后启动就是这样的

4.2-01.png

4.3 CentOS7的安装

我们在VMWare 上面来安装CentOS7操作系统，因为我们要做一主一从的数据库架构，
所以我们需要准备两个CentOS7 的节点，一个作为主库，另外一个作为从库。
安装步骤如下：

1，选择文件->新建虚拟机

4.3-01.png

2，选择安装来源，选择下载的 Centos7 ios 文件目录

4.3-02.png

3，选择客户机操作系统，（Linux CentOS 64 位）

4.3-03.png

4，填写虚拟系统名称及存放位置（可以默认）

4.3-04.png

5，指定磁盘容量（视宿主机硬盘大小）

4.3-06.png

6，成功创建之后

4.3-07.png

7, 点击开启虚拟机

4.3-08.png

8，选择 Install CentOS 7 安装

4.3-09.png

9，继续之后如图，选择安装位置为自动分区，点击网络和主机名进入并配置，点击开始安装

4.3-10.png

10，配置Root账号密码，等待安装即可

4.3-11.png

4.4 MySQL数据库的安装

1. 下载mysql的repo源
   如果没有 wget 先yum install -y wget

$ wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

2. 安装mysql-community-release-el7-5.noarch.rpm包
   非 root 用户 加上 sudo

$ rpm -ivh mysql-community-release-el7-5.noarch.rpm

3. 安装mysql

$ yum install -y mysql-server

4. 启动mysql

$ service mysqld start

5. 进入mysql and 修改 mysql 密码

$ mysql
$ UPDATE mysql.user SET password=PASSWORD("your password") WHERE user="root" AND Host="localhost";

4.5 MySQL主从配置(一主一从)

---

环境说明:

• 系统环境:Centos 7.
• Mysql版本：5.6.33
• Master-Server : 192.168.157.88
• Slave-Server : 192.168.157.89
• 主从数据库都建立测试库（如：testdatabase）

异步配置

Master 主库配置

1, 增加从库同步账号

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
-> TO repl@'192.168.157.89' IDENTIFIED BY 'repl_password';

2, 配置my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1

3, 重启 Mysql , 查看 Master Status

# Service mysqld restart;
//进入mysql
mysql> SHOW MASTER STATUS;

4.5-01.png

Slave 从库配置

1, 配置 my.cnf

[mysqld]
log_bin           = mysql-bin
server_id         = 2
relay_log         = mysql-relay-bin
log_slave_updates = 1
read_only         = 1

# Service Mysqld restart;

2, 链接Master ，启动Slave

mysql> CHANGE MASTER TO MASTER_HOST='server1',

    -> MASTER_USER='repl',

    -> MASTER_PASSWORD='repl_password',

    -> MASTER_LOG_FILE='mysql-bin.000001',

    -> MASTER_LOG_POS=0;
//启动 Slave
mysql> START SLAVE;

4.5-02.png

如图：
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
同步配置成功，可以在主库插入数据库测试！

---

半同步配置

Master 主库配置

1, 安装插件：semisync_master.so

mysql> INSTALL　PLUGIN rpl_semi_sync_master soname 'semisync_master.so';
//配置全局变量
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
mysql> SHOW VARIBALES LIKE '%semi%';

4.5-03.png

2,配置 my.cnf 让 Mysql 启动生效

[mysqld]
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000;

# Service mysqld restart

Slave 从库配置

1, 安装插件

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';  
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;  
mysql> STOP SLAVE;
mysql> START SLAVE;
mysql> SHOW VARIABLES LIKE '%semi%';

4.5-04.png

2,配置 my.cnf 让 Mysql 启动生效

[mysqld]
rpl_semi_sync_slave_enabled = 1

# Service mysqld restart

检查半同步是否生效

Master:

mysql> show global status like 'rpl_semi%';

4.5-05.png

4.6 实现读写分离代码：驱动方式

通过驱动的方式来实现应用层的读写分离，前提还是要完成上面的步骤：
MySQL主从配置同步复制

然后通过

jdbc:mysql:replication://192.168.157.88:3306,192.168.157.89:3306/DB_TEST7?roundRobinLoadBalance=true&characterEncoding=UTF-8

实现读写分离
或者
负载均衡

jdbc:mysql:loadbalance

这里先简单介绍下MySQL驱动下面，loadbalance 与 replication 的区别：
当MySQL架构采用Master-Master 主主架构时候，我们就可以用jdbc:mysql:loadbalance这种方式
应用层任意读写任何一个节点都不会出现问题，因为是双向同步复制机制的。

但是当你的架构是一主一从/一主多从的情况下(Master-Slave) ，再使用loadbalance就会出现问题，
因为loadbalance 是采用随机或者轮询的策略来做负载均衡算法，如果这个时候正好insert/update 随机到一个slave从机上面的时候，
你的数据就无法与Master进行同步了。

因此这种情况下，需要实现“主读写，从只读”，则使用jdbc:mysql:replication 可以完成这个目的。
replication 可以实现insert/update 写操作发送到 Master执行， 而读操作 从Slave 上面执行。

下面用Java来开发一个程序进行测试：
工程的目录如下：

4.6-01.png

Master-Server : 192.168.157.88
Slave-Server : 192.168.157.89

1. mysqlDB.properties 的配置

DBDriver=com.mysql.jdbc.Driver
url=jdbc\:mysql\:replication\://192.168.157.88\:3306,192.168.157.89\:3306/DB_TEST7?roundRobinLoadBalance\=true&characterEncoding\=UTF-8
name=root
pass=123456
characterEncoding=utf8

2.initdatabase.sql

create database DB_TEST7;
use DB_TEST7;
CREATE TABLE users (
  id int(5) NOT NULL auto_increment,
  name varchar(20)NOT NULL,
  PRIMARY KEY  (`id`)
); 

3.TestMysql.java

package com.xiaozhangwangxiao.test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
import com.xiaozhangwangxiao.utils.DBUtil;

/**
 * 测试数据库读写类
 * 
 * @author 小张网校 学员：张瑞超
 * 
 */
public class TestMysql {
    @Test
    public void testSelectAndWrite() throws SQLException {
        DBUtil uW = new DBUtil();
        DBUtil uR = new DBUtil();
        Connection connR = uR.getConn(); // connectionsToHostsMap()
        Connection connW = uW.getConn(); // connectionsToHostsMap()
        connW.setAutoCommit(false); // 自动提交为False
        connR.setAutoCommit(false); // 自动提交为False
        String inSql = "insert into users(name) values('copy');";
        String sql = "select name,count(1) as count_num from users where name = 'copy'";
        Statement sW = connW.createStatement();
        Statement sR = connR.createStatement();
        ResultSet r = null;
        int l = 1;
        try {
            for(int i = 0; i < 100; i++){
                sW.execute(inSql);
                connW.commit();
                System.out.println("sql = " + sql);
                r = sR.executeQuery(sql);
                while (r.next()) {
                    System.out.println("name" + r.getString("name") + " 第：" + l
                             + "条");
                }
                l++;
            }
        } catch (Exception e) {
            connW.rollback();
            e.printStackTrace();
        }
        r.close();
        sW.close();
        sR.close();
        connW.close();
        connR.close();
    }
}

测试类主要模拟批量数据提交到Master ,然后从 Slave读取数据，实现读写分离，测试主从库数据是否同步一致。

4.7 实现读写分离代码：SQL解析方式

另外我们还有一种方式来实现读写分离，这种方式也是现在一些中间件的做法， 通过SQL解析器解析出select,或者insert
送到对应的Master/Slave的数据库节点进行执行，下面我们show出代码，在select还采用了 随机负载算法来进行 负载均衡。

php代码实现如下：

备注：
Mysql 主库 ：192.168.157.88
Mysql 从库 ：192.168.157.89

<?  
/** 
* mysql读写分离 
* @author 小张网校 学员：nemo
*/  
class db   
{   
    //定义链接
    pbulic $host = array(
                'read' =>array(
                    'host'=>'192.168.157.89:3306',
                    'username' => 'root',
                    'password' => 'root'
                ),
                'write' =>array(
                    'host'=>'192.168.157.89:3306',
                    'username' => 'root',
                    'password' => 'root'
                )
            );
    public function __construct($sql)   
    {   
        $chestr = strtolower(trim($sql));  
        
        //根据 sql 判断链接库  
        if(substr($chestr,0,6)=='select')   
        {   
       
            echo 'I am using select db..<br>';  
            $rand = rand(0,1);
            $middleArr = array('read','write');//读库分流
            $link = mysql_connect(join(',',$this->host[$middleArr[$rand]])) or die("Could not connect: " . mysql_error());   
            mysql_select_db("test");   
            $result = mysql_query($sql);   
            while ($row = mysql_fetch_array($result, MYSQL_NUM))   
            {   
                printf("%s %s", $row[0],$row[1]);   
            }   
            echo mysql_get_host_info($link).mysql_get_server_info($link).mysql_get_proto_info($link).mysql_get_client_info().'<br>';          
        }   
        else  
        {   
            echo 'I am using insert db..<br>';  
            $link = mysql_connect(join(',',$this->host['write'])) or die("Could not connect: " . mysql_error());   
            mysql_select_db("test");   
            $result = mysql_query($sql);   
            echo @mysql_affected_rows($result);   
            echo mysql_get_host_info($link).mysql_get_server_info($link).mysql_get_proto_info($link).mysql_get_client_info().'<br>';         
        }   
            
    }   
}   
   
$d = new db(" INSERT INTO nemo values('nemo','420818119') ");   
$d2 = new db("SELECT * from `nemo`");  

---

总结一下：自己实现读写分离以及负载均衡需要

1. 配置Master-Slave 的MySQL数据同步的机制（打开binlog ,relaylog 等）
2. 应用层实现读写分离(通过jdbc:mysql:replication驱动实现 or 中间件SQL解析器实现)

下一节我们学习Mycat中间件，也是采用这种方式对SQL进行解析，完成读写分离和负载均衡的逻辑实现。