【分布式架构初探】
第四节-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数据库的安装
- 下载mysql的repo源
如果没有 wget 先yum install -y wget
$ wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
- 安装mysql-community-release-el7-5.noarch.rpm包
非 root 用户 加上 sudo
$ rpm -ivh mysql-community-release-el7-5.noarch.rpm
- 安装mysql
$ yum install -y mysql-server
- 启动mysql
$ service mysqld start
- 进入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
- 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`");
总结一下：自己实现读写分离以及负载均衡需要
- 配置Master-Slave 的MySQL数据同步的机制（打开binlog ,relaylog 等）
- 应用层实现读写分离(通过jdbc:mysql:replication驱动实现 or 中间件SQL解析器实现)
下一节我们学习Mycat中间件，也是采用这种方式对SQL进行解析，完成读写分离和负载均衡的逻辑实现。
