分库分表技术之MyCat(读写分离 & Mysql主从复制) --- step2

接着上一篇文章继续::::分库分表技术之MyCat(实战水平分库) --- step1_程序猿二鍋頭的博客-优快云博客

1 MyCat读写分离

1.1 什么是读写分离

        在实际的生产环境中, 数据的读写操作如果都在同一个数据库服务器中进行, 当遇到大量的并发读或 者写操作的时候,是没有办法满足实际需求的,数据库的吞吐量将面临巨大的瓶颈压力.

• 主从复制
  
          通过搭建主从架构, 将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责 处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改 善。
   
• 读写分离
  
         读写分离就是让主库处理事务性操作，从库处理select查询。数据库复制被用来把事务性查询导 致的数据变更同步到从库，同时主库也可以select查询。
   

        读写分离的数据节点中的数据内容是一致。

 1.2 MySQL主从复制(同步)

 MyCat的读写分离是建立在MySQL主从复制基础之上实现的，所以必须先搭建MySQL的主从复制架 构。

主从复制的用途

• 实时灾备，用于故障切换
• 读写分离，提供查询服务
• 备份，避免影响业务

主从部署必要条件

• 主库开启binlog日志（设置log-bin参数）
• 主从server-id不同
• 从库服务器能连通主库

主从复制的原理

• Mysql 中有一种日志叫做 bin 日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL 语句（insert,update,delete,create/alter/drop table, grant 等等）。
• 主从复制的原理其实就是把主服务器上的 bin 日志复制到从服务器上执行一遍，这样从服务器上的 数据就和主服务器上的数据相同了。

1. 主库db的更新事件(update、insert、delete)被写到binlog
2. 主库创建一个binlog dump thread，把binlog的内容发送到从库
3. 从库启动并发起连接，连接到主库
4. 从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
5. 从库启动之后，创建一个SQL线程，从relay log里面读取内容，执行读取到的更新事件，将更新内容写入到slave的db

1.3 主从复制架构搭建

Mysql的主从复制至少是需要两个Mysql的服务，当然Mysql的服务是可以分布在不同的服务器上，也 可以在一台服务器上启动多个服务。

1) 第一步 master中创建数据库和表

-- 创建数据库
CREATE DATABASE test CHARACTER SET utf8;
-- 创建表
CREATE TABLE users (
id INT(11) PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) DEFAULT NULL,
age INT(11) DEFAULT NULL
);
-- 插入数据
INSERT INTO users VALUES(NULL,'user1',20);
INSERT INTO users VALUES(NULL,'user2',21);
INSERT INTO users VALUES(NULL,'user3',22);

2) 第二步 修改主数据库的配置文件my.cnf

vim /etc/my.cnf

插入下面的内容

#master ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
#设置不要复制的数据库
binlog-ignore-db=mysql
#设置需要复制的数据库
binlog-do-db=test
#设置logbin格式(默认STATEMENT)
binlog_format=STATEMENT
 

• server-id=1 中的1可以任定义，只要是唯一的就行。
• log-bin=mysql-bin 表示启用binlog功能，并制定二进制日志的存储目录，
• binlog-do-db=test 是表示只备份test 数据库。
• binlog_ignore_db=mysql 表示忽略备份mysql。
• 不加binlog-do-db和binlog_ignore_db，那就表示备份全部数据库。

3) 第三步 从机修改my.cnf(或者my.ini)配置文件

server-id=2
relay-log=mysql-relay

4) 第四步 重启master 和 slave MySQL

service mysqld restart

注意：不管是windows 还是linux（systemctl stop firewalld） 建议关闭防火墙  

5）第五步：在主数据库上, 创建一个允许从数据库来访问的用户账号.

授权：GRANT REPLICATION SLAVE ON *.* TO '用户名'@'从服务器IP' identified by '密码';

use mysql
GRANT REPLICATION SLAVE ON *.* TO 'slave111'@'%' identified by 'Panghl$0';

查询master的状态

show master status;

注意：记录下File和Position的值

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化！！！

6)第六步：在slave从机上配置需要复制的主机

故障避免（如果你是虚拟机克隆的相同的两台mysql存在问题）：

[MySQL高级](七) MySQL主从复制及读写分离实战_睁眼看世界-优快云博客_mysql主从复制

修改auto.cnf中的UUID,保证唯一

- 编辑auto.cnf vim /var/lib/mysql/auto.cnf

-- 修改UUID的值 server-uuid=a402ac7f-c392-11ea-ad18-000c2980a208

-- 重启 service mysqld restart

如何重新配置：stop slave； reset master;

配置信息如下：

CHANGE MASTER TO MASTER_HOST='主机IP',
MASTER_USER='主机创建的用户名',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='File名字',
MASTER_LOG_POS=Position数字

egg:  

7) 启动slave从服务器复制功能

start slave;

 查看主从是否搭建成功

show slave status\G

 

8) 测试

注意：别忘了从库也需创建对应的数据库表。

-- 创建数据库
CREATE DATABASE test CHARACTER SET utf8;
-- 创建表
CREATE TABLE users (
id INT(11) PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) DEFAULT NULL,
age INT(11) DEFAULT NULL
);
-- 插入数据
INSERT INTO users VALUES(NULL,'user1',20);
INSERT INTO users VALUES(NULL,'user2',21);
INSERT INTO users VALUES(NULL,'user3',22);

在master库中执行插入数据，再查看从库中表是否有数据即可！

如何停止从服务复制功能：

stop slave;

1.2 实现读写分离

数据库读写分离对于大型系统或者访问量很高的互联网应用来说，是必不可少的一个重要功能。对于 MySQL来说，标准的读写分离是主从模式，一个写节点Master后面跟着多个读节点，读节点的数量取 决于系统的压力，通常是1-3个读节点的配置

 在schema.xml文件中配置Mycat读写分离。使用前需要搭建MySQL主从架构，并实现主从复制， Mycat不负责数据同步问题。

server.xml

修改用户可以访问的逻辑表为 tes

<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">test</property>
    <property name="defaultSchema">test</property>
</user>

schema

• 逻辑库 name="test"
• 逻辑表 name="users"
• 读写分离 不设置分片规则 ruleRequired=false
• 分片节点 dataNode="dn4"

<schema name="test" checkSQLschema="true" sqlMaxLimit="100">
    <table name="users" dataNode="dn4" ruleRequired="false" primaryKey="id"
        autoIncrement="true" >
    </table>
</schema>

dataNode

<!-- 读写分离 -->
<dataNode name="dn4" dataHost="localhost3" database="test" />

dataHost

<!-- 读写分离 -->
<dataHost name="localhost3" maxCon="1000" minCon="10" balance="1" writeType="0"
    dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- 主 -->
    <writeHost host="M1" url="192.168.40.110:3306" user="root"
        password="$Panghl0">
    <!-- 从 -->
    <readHost host="S1" url="192.168.40.111:3306" user="root"
        password="$Panghl0"
        weight="1" />
    </writeHost>
</dataHost>

balance参数：
0 ：所有读操作都发送到当前可用的writeHost
1 ：所有读操作都随机发送到readHost和stand by writeHost
2 ：所有读操作都随机发送到writeHost和readHost
3 ：所有读操作都随机发送到writeHost对应的readHost上，但是writeHost不负担读压力

writeType参数：
0 ：所有写操作都发送到可用的writeHost
1 ：所有写操作都随机发送到readHost
2 ：所有写操作都随机发送到writeHost，readHost

重启MyCat

./mycat restart

执行查询和插入操作

1) 插入一条数据, 观察否两个表都同时新增了,如果同时新增,证明插入的是主库的表.

INSERT INTO users(NAME,age) VALUES('测试abc',26);

2) 在从库插入一条数据, 然后进行查询, 查询的是从库中的数据,证明查询操作在从库进行.

SELECT * FROM users;

shardingsphere 实现分库分表及主从复制：https://blog.youkuaiyun.com/qq_45441466/article/details/120559510