MySql第四章，MyCat读写分离、分区分片
一、读写分离
Redis是从 头 开始复制
MySql是从 接入点 开始复制
1、一主一从
1.1、MySql主从复制搭建
1>、主从配置
Master
修改配置文件: vim /etc/my.cnf
#主服务器唯-ID
server-id=l
#启用二进制日志。
log-bin=mysql-bin
#设置logbin格式
binlog_format=STATEMENT
#设置不要复制的数据库(可设置多个)。
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=mycatdb #该数据库一定要 master 还未创建，等开启主从复制后 再在主机上创建
解析logbin三种格式
statement：(默认)
记录SQL语句，但是遇到update xxx set xxx time = now() where xxx;时 主从的time就会出现不一致
[用statement效率高，但是不可以在 代码 中使用 函数]
row:
记录行变化, 1 time->1.5 2 time->2 3 time->1; 如果全表更新的话，效率会很慢
MIXED
mixed是statement和row的混合，解决了一部分数据不一致或效率慢的问题。
但是遇到 @@host name 获取当前主机名称时，会识别不了
Slave
修改配置文件: vim /etc/my.cnf
#从服务器唯- ID
server-id=2
#启用中继日志。
relay-log=mysql-relay
2>、关闭主从防火墙
systemctl stop firewalld
##查看防火墙状态 systemctl status firewalld
3>、Master上创建账户并赋权Slave
##授权
GRANT REPLICATION SLAVE ON *.* TO 'ren'@'192.168.0.107' IDENTIFIED BY 'admin123';
或
GRANT REPLICATION SLAVE ON *.* TO 'ren'@'%' IDENTIFIED BY'admin123";
##刷新权限
flush privileges;
4>、Slave上配置需要复制的主机
CHANGE MASTER TO MASTER_HOST='192.168.0.111',
MASTER_USER='ren',MASTER_PASSWORD='admin123',
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=154;
##启动从服务器复制功能
start slave;
##检测--下面两个参数都是YES，则说明主从配置成功 show slave status \G
show slave status \G
1.2、MyCat读写分离配置
1>、server.xml
##mycat对外暴露的逻辑MySQL，name为用户名、password为用户密码、schemas为逻辑数据库名称
<user name="mycat">
<property name="password">mycat</property>
<property name="schemas">TESTDB</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
2>、schema.xml
##schema：mycat对外暴露的逻辑库
##dataNode：MySQL对应的数据库
##dataHost：MySql对应的主从名称
##############balance设置为3#############
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<!--指定库名-->
<dataNode name="dn1" dataHost="host1" database="mycatdb" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!--写节点-->
<writeHost host="hostM1" url="192.168.0.111:3306" user="root"
password="admin123">
<!--读节点-->
<readHost host="hostS1" url="192.168.0.107:3306" user="root" password="admin123" />
</writeHost>
</dataHost>
</mycat:schema>
balance参数解析说明：
修改dataHost的balance属性，通过此属性配置 读写分离 的类型
负载均衡类型，目前的取值有4种:。
1、balance="0",不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。。
2、balance="1", 全部的readHost与stand by writeHost参与select 语句的负载均衡，简单的说，
当双主双从模式(M1->S1，M2->S2， 并且M1与M2互为主备)，正常情况下，M2,S1,S2 都参与select
语句的负载均衡。。
2、balance="2"， 所有读操作都随机的在writeHost. readhost. 上分发。
4、balance="3"， 所有读请求随机的分发到readhost 执行，writerHost 不负担读压力。
switchType参数解析说明：
switchType指的是切换的模式，目前的取值也有4种：
1. switchType='-1' 表示不自动切换
2. switchType='1' 默认值，表示自动切换
3. switchType='2' 基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status
4. switchType='3'基于MySQL galary cluster的切换机制（适合集群）（1.4.1），心跳语句为 show status like 'wsrep%'。
WriteType参数解析说明：
writeType负载均衡类型，目前的取值有3种：
1. writeType="0", 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个writeHost，重新启
动后已切换后的为准，切换记录在配置文件中:dnindex.properties
2. writeType="1"，所有写操作都随机的发送到配置的writeHost
3. writeType="2"，没实现。
1.3、测试
1、在master111上插入数据
create database mycatdb;
use mycatdb;
create table user(id int,name varchar(100));
insert into user values(1,@@hostname);
##查询Master111上数据和Slave107上的数据
##111
mysql> select * from user;
+------+---------+
| id | name |
+------+---------+
| 1 | master1 |
+------+---------+
##107
mysql> select * from user;
+------+--------+
| id | name |
+------+--------+
| 1 | slave1 |
+------+--------+
2、启动mycat
./mycat console
mysql -umycat -pmycat -h 192.168.0.111 -P 8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.01 sec)
mysql> use TESTDB;
mysql> select * from user;
+------+--------+
| id | name |
+------+--------+
| 1 | slave1 |
+------+--------+
1 row in set (0.00 sec)
2、双主双从
一个主机m1用于处理所有写请求,它的从机s1和另一台主机m2还有它的从机s2负贵所有读请求。
当ml主机宕机后，m2主机负责写请求，ml、m2互为备机。
[master1 master2 互备主从，master1宕机后master2作为写主机，master1重启后作为master2的备机]
| 编号 | 角色 | IP | host_name |
|---|---|---|---|
| 1 | Master1 | 192.168.0.111 | Master1 |
| 2 | Slave1 | 192.168.0.107 | Slave1 |
| 3 | Master2 | 192.168.0.112 | Master2 |
| 4 | Slave2 | 192.168.0.108 | Slave2 |
2.1、MySql双主双从搭建
1>、Master1-111机
配置 vi /etc/my.cnf
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin-mysql-bin
#设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
#在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段从哪个数开始，取值范围是1.. 65535
auto-increment-offset=1
#表示自增长字段每次递增的量，取值范围是1 .. 65535
auto-increment-increment=2
创建账户并授权slave1
##授权
GRANT REPLICATION SLAVE ON *.* TO 'ren'@'%' IDENTIFIED BY'admin123";
##刷新权限
flush privileges;
##查看master1状态
show master status;
两台主机互相复制，master1复制master2
CHANGE MASTER TO MASTER_HOST='192.168.0.112',
MASTER_USER='ren',MASTER_PASSWORD='admin123',
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=154;
##启动从服务器复制功能
start slave;
##检测--下面两个参数都是YES，则说明主从配置成功 show slave status \G
show slave status \G
2>、Master2-112机
配置 vi /etc/my.cnf
#主服务器唯一ID
server-id=3
#启用二进制日志
log-bin-mysql-bin
#设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
#在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段从哪个数开始，取值范围是1.. 65535
auto-increment-offset=2
#表示自增长字段每次递增的量，取值范围是1 .. 65535
auto-increment-increment=2
创建账户并授权slave2
##授权
GRANT REPLICATION SLAVE ON *.* TO 'ren'@'%' IDENTIFIED BY'admin123";
##刷新权限
flush privileges;
##查看master1状态
show master status;
两台主机互相复制，master2复制master1
CHANGE MASTER TO MASTER_HOST='192.168.0.111',
MASTER_USER='ren',MASTER_PASSWORD='admin123',
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=154;
##启动从服务器复制功能
start slave;
##检测--下面两个参数都是YES，则说明主从配置成功 show slave status \G
show slave status \G
3>、Slave1-107机
配置 vi /etc/my.cnf
#从服务器唯- ID
server-id=2
#启用中继日志。
relay-log=mysql-relay
连接需要复制的主机master1
CHANGE MASTER TO MASTER_HOST='192.168.0.111',
MASTER_USER='ren',MASTER_PASSWORD='admin123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
##启动从服务器复制功能
start slave;
##检测--下面两个参数都是YES，则说明主从配置成功 show slave status \G
show slave status \G
4>、Slave2-108机
配置 vi /etc/my.cnf
#从服务器唯- ID
server-id=4
#启用中继日志。
relay-log=mysql-relay
连接需要复制的主机master2
CHANGE MASTER TO MASTER_HOST='192.168.0.112',
MASTER_USER='ren',MASTER_PASSWORD='admin123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
##启动从服务器复制功能
start slave;
##检测--下面两个参数都是YES，则说明主从配置成功 show slave status \G
show slave status \G
2.2、MyCat读写分离配置
配置：schema.xml
##balance设置为1
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<!--指定库名-->
<dataNode name="dn1" dataHost="host1" database="mycatdb" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!--写节点-->
<writeHost host="hostM1" url="192.168.0.111:3306" user="root"
password="admin123">
<!--读节点-->
<readHost host="hostS1" url="192.168.0.107:3306" user="root" password="admin123" />
</writeHost>
<!--写节点-->
<writeHost host="hostM1" url="192.168.0.112:3306" user="root"
password="admin123">
<!--读节点-->
<readHost host="hostS1" url="192.168.0.108:3306" user="root" password="admin123" />
</writeHost>
</dataHost>
</mycat:schema>
2.3、测试
##这里就不再写测试方法了，参考1.3一主一从的测试步骤即可
二、垂直拆分–分库
一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，
分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面。
注：不同库中的表无法做关联查询（left join、right join）
[垂直分库的主要作用是减轻磁盘的压力，单表的数据量并未减少]
修改schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema=
最低0.47元/天 解锁文章
623
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
