MySQL-读写分离

最新推荐文章于 2024-08-23 20:16:36 发布

xiaotanggao

最新推荐文章于 2024-08-23 20:16:36 发布

阅读量514

点赞数

分类专栏： linux运维（所有） linux远程服务文章标签： mysql 数据库负载均衡

本文链接：https://blog.csdn.net/gxy_learning/article/details/120894644

版权

linux运维（所有）同时被 2 个专栏收录

58 篇文章 9 订阅

订阅专栏

linux远程服务

22 篇文章 0 订阅

订阅专栏

MySQL-读写分离

介绍

读写分离目的

数据库负载均衡

当数据库请求增多时，单例数据库不能够满足业务需求。需要进行数据库实例的扩容。多台数据库同时相应请求。也就是说需要对数据库的请求，进行负载均衡

但是由于数据库服务特殊原因，数据库扩容基本要求为：数据的一致性和完整性。所以要保证多台数据库实例的数据一致性和完整性，以MySQL为例来说，官方提供了主从复制机制
读写分离

数据库的负载均衡不同于其他服务的负载均衡，数据要求一致性。在主从复制的基础上，常见的数据库负载均衡使用的是读写分离方式。写入到主数据库，在从数据库读取。
数据库读写分离，是一种特殊的负载均衡实现

实际业务中，读写分离的实时性不高，对于要求实时性高的业务，可通过以下方式实现：

读主（不常见）
读取缓存：先写入主库，在写入缓存，最后读取缓存
弹出提示信息：等待跳转（5s）|数据正在审核中

实现方式

业务代码直接实现读写分离

需要在业务代码中，判断数据操作是读还是写，读连接从数据库服务器操作，写连接主数据库服务器操作。以LNMP为例，可以通过PHP代码实现读写分离
特点：操作方便，成本低

中间件代理实现读写分离

在业务代码中，数据库的操作，不直接连接数据库，而是先请求到中间件服务器（代理服务器），由代理服务器，判断是读操作去从数据服务器，写操作去主数据服务器
特点：除了能够实现读写分离，还可以作为数据库集群的管理平台
常见如下

代理中间件	说明
MySQL Proxy	MySQL官方测试版不再维护
Atlas	奇虎360 基于MySQL Proxy https://github.com/Qihoo360/Atlas
DBProxy	美团点评
Amoeba	早期阿里巴巴
cobar	阿里巴巴
MyCat	基于阿里的Cobar 开源
kingshared	go语言开发https://github.com/flike/kingshard
proxysql	http://www.proxysql.com/

读写分离实现

主从复制搭建

主从复制原理：主服务器开启bin-log，记录写操作。从服务器获取到主服务器的bin-log ，记录到relay-log中。从服务器在通过异步的线程方式，基于relay-log进行数据重演。

虚拟机	主机名	IP地址
server03	mysql1.master	192.168.139.130
server06	mysql2.slave	192.168.139.133

基于server03克隆server06
基本环境配置

vim /etc/sysconfig/network
vim /etc/sysconfig/network-scripts/ifcfg-eth
cd /etc/udev/rules.d/
rm -rf 70-persistent-net.rules
reboot
cat >> /etc/hosts <<EOF
	192.168.139.130 mysql1.master
	192.168.139.133 mysql2.slave
	EOF

master配置

添加专门用于主从复制的用户
[root@mysql1 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'slave'@'192.168.139.%'identified by '123456';
mysql> select * from mysql.user\G

开启二进制日志
[root@mysql1 ~]# vim /etc/my.cnf 
  7 [mysqld]
 15 log_bin = mysql-index
 21 server_id = 1
[root@mysql1 ~]# cd /usr/local/mysql/data
[root@mysql1 data]# service mysqld restart
[root@mysql1 data]# ls
mysql-index.000001

导出数据库，确保主从开始数据一致（由于从数据库是克隆主数据库的，可不做此步骤）
[root@mysql1 ~]# mysqldump -uroot -p'123456' --all-databases > /root/mysql.sql

查看master上bin-log的位置
mysql> show master status;
+--------------------+----------+
| File               | Position | 
+--------------------+----------+
| mysql-index.000001 |      120 |
+--------------------+----------+

slave配置

修改server-id（与主不一致）
[root@mysql2 ~]# vim /etc/my.cnf 
  7 [mysqld]
 21 server_id = 2
 
修改/usr/local/mysql/data/auto.cnf中的server-uuid（与主不一致）
[root@mysql2 ~]# rm -rf /usr/local/mysql/data/auto.cnf
[root@mysql2 ~]# service mysqld restart

主从复制配置
mysql> stop slave;
mysql> change master to
	master_host='192.168.139.130',
	master_user='slave',
	master_password='123456',
	master_log_file='mysql-index.000001',
	master_log_pos=120;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.139.130
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-index.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql2-relay-bin.000002
                Relay_Log_Pos: 285
        Relay_Master_Log_File: mysql-index.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

代码实现读写分离

[root@web1 ~]# vim rwbymysqli.php 
<?php
//创建类
class Mysql {
  //构造方法
  //当类被实例化时,会触发此方法
  public function __construct(){
     $this->master = @new mysqli('192.168.139.130','tp5shop','$Abc3721','tp5shop');
	 $this->slave = @new mysqli('192.168.139.133','tp5shop','$Abc3721','tp5shop');
     // 如果从连接报错,就连接主
     if($this->slave->connect_errno){
      $this->slave=$this->master;
     }
  }
  // SQL执行方法
  public function query($sql){
    //判断语句中是否存在selete
    if(strpos($sql, 'select') !== false){
      //这是查询操作
      $rs = $this->slave->query($sql);
    }else{
      $rs = $this->master->query($sql);
    }
    return $rs;
  }
}
$mysql = new Mysql();
$sql = "select * from tpshop_goods";
$rs = $mysql->query($sql);
while($row = $rs->fetch_assoc()){
    var_dump($row['goods_name']);
}

框架配置实现读写分离

配置

[root@web1 ~]# vim /usr/local/nginx/html/tp5shop/application/database.php
 12 return [
 13     // 数据库类型
 14     'type'            => 'mysql',
 15     // 服务器地址
 16     'hostname'        => '192.168.139.130,192.168.139.133',
 17     // 数据库名
 18     'database'        => 'tp5shop',
 19     // 用户名
 20     'username'        => 'tp5shop',
 21     // 密码
 22     'password'        => '123456',
 23     // 端口
 24     'hostport'        => '3306',
 25     // 连接dsn
 26     'dsn'             => '',
 27     // 数据库连接参数
 28     'params'          => [],
 29     // 数据库编码默认采用utf8
 30     'charset'         => 'utf8',
 31     // 数据库表前缀
 32     'prefix'          => 'tpshop_',
 33     // 数据库调试模式
 34     'debug'           => true,
 35     // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
 36     'deploy'          => 1,
 37     // 数据库读写是否分离 主从式有效
 38     'rw_separate'     => true,
    #1、主从数据库的IP地址，主在前，从在后，逗号分隔
	#2、主从数据库的名称、用户、密码一样的话，只配置一份即可。如果不一样，按照先主后从顺序，逗号分隔

域名解析到web服务器
浏览器访问：宕掉主库，检查是否可读

MyCAT实现读写分离

介绍

国内最活跃的、性能最好的开源数据库中间件
官方网址：http://www.mycat.io/
mycat是由java语言开发，必须使用java的运行环境才能进行启动和操作

环境准备

在这里插入图片描述

虚拟机	主机名	IP地址
server01	web1.master	192.168.139.128
server03	mysql1.master	192.168.139.130
server06	mysql2.slave	192.168.139.133
server07	mycat	192.168.139.134

基于server06克隆server07
基础环境配置

vim /etc/sysconfig/network
vim /etc/sysconfig/network-scripts/ifcfg-eth
cd /etc/udev/rules.d/
rm -rf 70-persistent-net.rules
reboot
cat >> /etc/hosts <<EOF
	192.168.139.134 mycat
	EOF

JDK安装

java为编译型语言，脚本需要先编译再执行
JDK：Java Development Kit (JDK) 是Sun公司针对Java开发员的软件开发工具包
javac可以编译java代码；jdk中含有javac
JRE：Java运行环境（Java Runtime Environment，简称JRE）是一个软件，JRE可以让计算机系统运行Java应用程序（Java Application）
JDK是整个Java的核心，包括了Java运行环境(JRE)，一堆Java工具tools.jar和Java标准类库 (rt.jar)
下载jdk：https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html （建议下载：jdk-…-linux-x64.tar.gz)

[root@mycat ~]# cd /root/soft
[root@mycat soft]# rz
jdk-8u192-linux-x64.tar.gz 
[root@mycat soft]# tar -xzf jdk-8u192-linux-x64.tar.gz 
[root@mycat soft]# mv jdk1.8.0_192/ /usr/local/java
[root@mycat soft]# echo "PATH=/usr/local/java/bin:$PATH" >> /etc/profile
[root@mycat soft]# source /etc/profile
[root@mycat soft]# java -version
java version "1.8.0_192"
Java(TM) SE Runtime Environment (build 1.8.0_192-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.192-b12, mixed mode)

MyCAT

安装

[root@mycat soft]# rz
	Mycat-server-1.6.5-release-20180122220033-linux.tar.gz  
[root@mycat soft]# tar -xzf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz 
[root@mycat soft]# mv mycat/ /usr/local/mycat
[root@mycat soft]# cd /usr/local/mycat
[root@mycat mycat]# ls
bin  catlet  conf  lib  logs  version.txt

启动

[root@mycat mycat]# cd bin/
选择前端启动,可以看到执行过程（若启动失败，可能是hosts文件中，没有解析主机名）
[root@mycat bin]# ./mycat console

配置读写分离

文件名称	作用
server.xml	配置mycat的对外的用户、密码、映射数据库名称等信息
schema.xml	配置后端真实数据库的用户、密码、真实数据库名称等信息

Mycat是由java开发，java开发的软件大部分配置文件是xml格式。现以server.xml为例说明xml格式。

xml声明信息<?xml version="1.0" encoding="UTF-8"?>
具有一个根标签
<mycat:server xmlns:mycat=“http://io.mycat/”>
</mycat:server>

双标签和单标签格式
标签闭合
注释格式为

修改server.xml
[root@mycat conf]# cd /usr/local/mycat/conf
[root@mycat conf]# vim server.xml

在这里插入图片描述

修改schema.xml
schema=>dataNode=>dataHost=>writeHost=>readHost

[root@mycat conf]# vim schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <!-- 1、TESTDB和server.xml配置文件中的映射的数据库名称要一致;dataNone填写下面的dataNode名称 -->
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn3"></schema>
	
    <!-- 2、dataNode name和上面的一致;dataHost填写下面的dataHost的name名称;database填写后端真实数据库名称-->
    <dataNode name="dn3" dataHost="localhost1" database="tp5shop" />
    
    <!-- 3、可以配置负载均衡、读写分离算法;暂时可以不用改动-->
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
       
        <!-- 对后端数据库服务器 发送的心跳检测 -->
		<heartbeat>select user()</heartbeat>
		
        <!-- 4、配置后端真实数据库的相关登录信息 -->
		<writeHost host="hostM1" url="192.168.139.100:3306" user="tp5shop" password="123456">
			
            <!-- can have multi read hosts -->
			<readHost host="hostS2" url="192.168.17.105:3306" user="tp5shop" password="$Abc3721" />
		</writeHost>
		
        <!-- <writeHost host="hostS1" url="localhost:3316" user="root" password="123456" />-->
        <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	
    </dataHost>
</mycat:schema>

具体修改如下
[root@mycat conf]# cat schema.xml 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

第一步：添加dataNode="dn1"，具体名称看下面dataNode name="dn1"
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
	
第二步：将database=修改为真实数据库名
	<dataNode name="dn1" dataHost="localhost1" database="tp5shop" />
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		
第三步：修改相应参数（写主机）
		<writeHost host="hostM1" url="192.168.139.130:3306" user="tp5shop" password="123456">
			
第四步：修改相应参数（读主机）
			<readHost host="hostS2" url="192.168.139.133:3306" user="tp5shop" password="123456" />
		</writeHost>
	</dataHost>
</mycat:schema>

查看启动过程，成功启动，即可start开启服务
[root@mycat bin]# ./mycat console
	vm 1    | MyCAT Server startup successfully. see logs in logs/mycat.log
[root@mycat bin]# ./mycat start

[root@mycat bin]# ss -tnalp|grep 8066
[root@mycat bin]# ss -tnalp|grep 9066

在上述配置中出现了 balance=“0” |writeType=“0”|switchType=“1”，各自代表什么意思？

balance

balance=“0”, 不开启读写分离机制,所有读操作都发送到当前可用的 writeHost 上
balance=“1”, 全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且 M1 与 M2 互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡
balance=“2”, 所有读操作都随机的在 writeHost、readhost 上分发
balance=“3”, 所有读请求随机的分发到 writerHost 对应的 readhost 执行,writerHost 不负担读压力,注意 balance=“3” 只在 1.4 及其以后版本有

writeType

writeType=“0”, 所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个writeHost,重新启动后已切换后的为准
writeType=“1”,所有写操作都随机的发送到配置的 writeHost,1.5 以后废弃不推荐

switchType

switchType="-1"，表示不自动切换
switchType=“1”，默认值，自动切换
switchType=“2”，基于 MySQL 主从同步的状态决定是否切换

客户端使用

[root@mycat bin]# mysql -h127.0.0.1 -uroot -p123456 -P8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
#TESTDB映射为tp5shop数据库
mysql> use TESTDB;
mysql> show tables;
+--------------------+
| Tables_in_tp5shop  |
+--------------------+
| tpshop_address     |
| tpshop_attribute   |
| tpshop_auth        |
| tpshop_cart        |
| tpshop_category    |
| tpshop_goods       |
| tpshop_goods_attr  |
| tpshop_goodspics   |
| tpshop_manager     |
| tpshop_order       |
| tpshop_order_goods |
| tpshop_role        |
| tpshop_type        |
| tpshop_user        |
+--------------------+

管理端

[root@mycat bin]# mysql -h127.0.0.1 -uroot -p123456 -P9066

查看使用帮助
mysql> show @@help

查看服务器状态
mysql> show @@heartbeat;
+--------+-------+-----------------+------+---------+-------+
| NAME   | TYPE  | HOST            | PORT | RS_CODE | RETRY | 
+--------+-------+-----------------+------+---------+-------+
| hostM1 | mysql | 192.168.139.130 | 3306 |       1 |     0 | 
| hostS2 | mysql | 192.168.139.133 | 3306 |       1 |     0 |
+--------+-------+-----------------+------+---------+-------+
+--------+---------+--------------+---------------------+-------+
| STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+--------+---------+--------------+---------------------+-------+
| idle   |       0 | 1,1,1        | 2021-10-21 17:34:41 | false |
| idle   |       0 | 1,1,1        | 2021-10-21 17:34:41 | false |
+--------+---------+--------------+---------------------+-------+

业务代码配置

[root@web1 ~]# vim /usr/local/nginx/html/tp5shop/application/database.php
 12 return [
 13     // 数据库类型
 14     'type'            => 'mysql',
 15     // 服务器地址
 16     'hostname'        => '192.168.139.134',
 17     // 数据库名
 18     'database'        => 'TESTDB',
 19     // 用户名
 20     'username'        => 'root',
 21     // 密码
 22     'password'        => '123456',
 23     // 端口
 24     'hostport'        => '8066',
 25     // 连接dsn
 26     'dsn'             => '',
 27     // 数据库连接参数
 28     'params'          => [],
 29     // 数据库编码默认采用utf8
 30     'charset'         => 'utf8',
 31     // 数据库表前缀
 32     'prefix'          => 'tpshop_',
 33     // 数据库调试模式
 34     'debug'           => true,
 35     // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
 36     'deploy'          => 0,
 37     // 数据库读写是否分离 主从式有效
 38     'rw_separate'     => false,

域名解析到web服务器或许web服务器的负债均衡服务器

解决宕掉master后，发现slave也不可读的问题

解决方案：将mycat默认的writeHost和readHost（readHost配置在writeHost下，一旦writeHost宕机，readHost自然不可用），改写为两个writeHost
但这样又出现了一个问题：master宕机后，slave不仅可以读，甚至可以写数据
所以应该在从服务器上设置readonly

库表前缀
32 ‘prefix’ => ‘tpshop_’,
33 // 数据库调试模式
34 ‘debug’ => true,
35 // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
36 ‘deploy’ => 0,
37 // 数据库读写是否分离主从式有效
38 ‘rw_separate’ => false,


- 域名解析到**web服务器**或许**web服务器的负债均衡服务器**

> 解决**宕掉master后，发现slave也不可读**的问题
>
> - 解决方案：将mycat默认的writeHost和readHost（readHost配置在writeHost下，一旦writeHost宕机，readHost自然不可用），**改写为两个writeHost**
> - 但这样又出现了一个问题：master宕机后，slave不仅可以读，甚至可以写数据
> - 所以应该在从服务器上设置**readonly**