Mycat实现读写分离

Mycat是什么？

Mycat 是数据库中间件。主要是做数据分布式存储，也有Atlas普通版的读写分离功能，其最重要还是分布式

Mycat是java语言开发的。是一个开源的分布式数据库系统，是一个实现了MySQL协议的的Server，前端用户可以把它看作是一个数据库代理，用MySQL客户端工具和命令行访问，而其后端可以用MySQL原生（Native）协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端MySQL服务器里或者其他数据库里

支持Mysql集群，可以作为Proxy使用
支持JDBC连接多数据库
支持各种数据库，包括Mysql、mongodb、oracle、sqlserver、hive 、db2 、 postgresql。
支持galera for mysql集群，percona-cluster或者mariadb cluster，提供高可用性数据分片集群
自动故障切换，高可用性
支持读写分离，支持Mysql双主多从，以及一主多从的模式
支持全局表，数据自动分片到多个节点，用于高效表关联查询
支持独有的基于E-R 关系的分片策略，实现了高效的表关联查询
支持一致性Hash分片，有效解决分片扩容难题
多平台支持，部署和实施简单
支持Mysql存储过程调用
以插件方式支持SQL拦截和改写
支持自增长主键、支持Oracle的Sequence机制

Mycat应用场景：

单纯的读写分离，此时配置最为简单，支持读写分离，主从切换
分表分库，对于超过1000万的表进行分片，最大支持1000亿的单表分片
多租户应用，每个应用一个库，但应用程序只连接Mycat，从而不改造程序本身，实现多租户化
报表系统，借助于Mycat的分表能力，处理大规模报表的统计

Mycat分片规则：

分表分库虽然能解决大表对数据库系统的压力，但它并不是万能的，也有一些不利之处，因此首要问题是，分不分库，分哪些库，什么规则分，分多少分片。
总体上来说，分片的选择是取决于最频繁的查询SQL的条件，因为不带任何Where语句的查询SQL，会便利所有的分片，性能相对最差，因此这种SQL越多，对系统的影响越大，所以我们要尽量避免这种SQL的产生。

Mycat读写分离部署：

搭建一主一从，一个主机用于处理所有写请求，一台从机负责所有读请求，架构图如下：

安装Java环境
```
yum -y install java-openjdk
```

下Mycat软件包

wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

解压到指定路径

tar -zxf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz  -C  /usr/local/

配置环境变量

echo 'export PATH=/usr/local/mycat/bin:$PATH' >> /etc/profile
source /etc/profile

/usr/local/mycat/conf 配置文件说明:

schema.xml 主配置文件（读写分离、高可用、分表、节点控制）
server.xml mycat软件本身相关的配置
rule.xml 分片规则配置文件（分片规则列表、使用方法)

/usr/local/mycar/log 日志文件说明：

mycat.log Mycat工作日志
mycat.pid pid文件
wrapper.log Mycat启动相关日志

编辑schema.xml主配置文件

mv /usr/local/mycat/conf/schema.xml /usr/local/mycat/conf/schema.xml.bak


vim /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="mycat_testdb" checkSQLschema="flase" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="host1" database= "school" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="2"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1" slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.1.128:3306" user="root" password="abc123">
                        <readHost host="hostS1" url="192.168.1.130:3306" user="root" password="abc123" />
                </writeHost>
        </dataHost>
</mycat:schema>

修改server.xml配置文件在server.xml配置文件最底部

schema.xml 配置文件说明

#定义mycat工作库mycat_testdb是mycat的一个逻辑库，可以自定义，但是涉及另外一个server.xml配置文件。mycat_testdb逻辑库对应dn1这个数据节点
<schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>

# 定义名为dn1这个数据节点的逻辑主机地址。和真实的后端数据库中的school库名。
    <dataNode name="dn1" dataHost="host1" database="school" />


# host1这个逻辑主机的最大并发连接数。最小连接（类似预热数据，提前连接好，节省临时连接的消耗）。
  
　　balance默认为1，为负载均衡到所有从库读操作。
     负载均衡类型，目前的取值有4 种：
     （1）balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
     （2）balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡。
         简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。
     （3）balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。
     （4）balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力

  writeType默认为0，写操作只分配到第一个writeHost数据库实例组。指定后端数据库软件类型。

  dbDriver是驱动。

  switchType="1"是主库宕机，自动切换到其他预备主数据库实例组

  dbDriver（使用native的话，因为这个值执行的是二进制的mysql协议，所以可以使用mysql和maridb，其他类型的则需要使用JDBC驱动来支持。）
    <dataHost name="host1" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="1">


# mycat监控后端mysql是否可用，走的心跳信息
     <heartbeat>select user()</heartbeat>


# 定义host1逻辑主机中的真实物理主机，这是逻辑主机中的一个真实“可写”节点，真实的数据库密码，不能瞎写
    <writeHost host="hostM1" url="192.168.1.128:3306" user="root" password="abc123">

# 定义host1逻辑主机中的真实物理主机，这是逻辑主机中的一个真实“可读”节点，真实的数据库密码，不能瞎写
    <readHost host="hostS1" url="192.168.1.130:3306" user="root" password="abc123" />

server.xml说明

启动Mycat
```
mycat start
```
查看日志是否启动成功

验证读写分离

mysql -umycat -p123456 -h192.168.1.131 -P8066 --default-auth=mysql_native_password

mysql> show databases;
+--------------+
| DATABASE     |
+--------------+
| mycat_testdb |
+--------------+
1 row in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.42 sec)


因为scheam.xml配置文件中，balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。这里只是测试使用，查看效果。
生产环境 “一主一从” 应该设置为 balance="3"。
双主双从应该设置为 balance="1"。


mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 128 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 130 |
+-------------+
1 row in set (0.01 sec)

搭建双主双从　　

一个主机 m1 用于处理所有写请求，它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请求。当 m1 主机宕机后，m2 主机负责写请求，m1、m2 互为备机。架构图如下：

角色	IP地址	server_id
master-01	192.168.1.128	128
master-02	192.168.1.129	129
slave-01	192.168.1.130	130
slave-02	192.168.1.131	131

搭建 MySQL 数据库主从复制（双主双从）

master-01配置

[root@master-01 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data/
port=3306
socket=/tmp/mysql.sock
log-error=/var/log/mysql/error.log
 
 
#GTID:
server_id=128                      
gtid_mode=on                       
enforce_gtid_consistency=on       
log-slave-updates=1

#binlog
log_bin=/data/binlog/mysql-bin
binlog_format=row           
 
#slow log
slow_query_log=1
slow_query_log_file=/usr/local/mysql/data/master01-slow.log
long_query_time=0.1
log_queries_not_using_indexes=1


# Adjust as your needed
max_connections=512
back_log=256
connect_timeout=10
key_buffer_size=16777216
innodb_buffer_pool_size=536870912
tmp_table_size=536870912
thread_cache_size=100
long_query_time=2
max_allowed_packet=64M


# character set
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
character-set-client-handshake=FALSE

master-02配置

[root@master-02 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data/
port=3306
socket=/tmp/mysql.sock
log-error=/var/log/mysql/error.log
 
 
#GTID:
server_id=129                      
gtid_mode=on                       
enforce_gtid_consistency=on       
log-slave-updates=1

#binlog
log_bin=/data/binlog/mysql-bin
binlog_format=row           
 
#slow log
slow_query_log=1
slow_query_log_file=/usr/local/mysql/data/master02-slow.log
long_query_time=0.1
log_queries_not_using_indexes=1


# Adjust as your needed
max_connections=512
back_log=256
connect_timeout=10
key_buffer_size=16777216
innodb_buffer_pool_size=536870912
tmp_table_size=536870912
thread_cache_size=100
long_query_time=2
max_allowed_packet=64M


# character set
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
character-set-client-handshake=FALSE

slave-01配置

[root@node-01 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data/
port=3306
socket=/tmp/mysql.sock
log-error=/var/log/mysql/error.log
 
 
#GTID:
server_id=130
gtid_mode=on                       
enforce_gtid_consistency=on       
log-slave-updates=1

#binlog
log_bin=/data/binlog/mysql-bin
binlog_format=row           
 
#slow log
slow_query_log=1
slow_query_log_file=/usr/local/mysql/data/node-01-slow.log
long_query_time=0.1
log_queries_not_using_indexes=1


# Adjust as your needed
max_connections=512
back_log=256
connect_timeout=10
key_buffer_size=16777216
innodb_buffer_pool_size=536870912
tmp_table_size=536870912
thread_cache_size=100
long_query_time=2
max_allowed_packet=64M


# character set
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
character-set-client-handshake=FALSE

slave-02配置

[root@node-02 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data/
port=3306
socket=/tmp/mysql.sock
log-error=/var/log/mysql/error.log
 
 
#GTID:
server_id=131
gtid_mode=on                       
enforce_gtid_consistency=on       
log-slave-updates=1

#binlog
log_bin=/data/binlog/mysql-bin
binlog_format=row           
 
#slow log
slow_query_log=1
slow_query_log_file=/usr/local/mysql/data/node-02-slow.log
long_query_time=0.1
log_queries_not_using_indexes=1


# Adjust as your needed
max_connections=512
back_log=256
connect_timeout=10
key_buffer_size=16777216
innodb_buffer_pool_size=536870912
tmp_table_size=536870912
thread_cache_size=100
long_query_time=2
max_allowed_packet=64M


# character set
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
character-set-client-handshake=FALSE

在两台master主机上建立帐户并授权 repl用户

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';

两台master主机互相复制开启slave

master-01执行
mysql> change master to  master_host='192.168.1.129', master_user='repl', master_password='repl', master_port=3306,master_auto_position = 1;

mysql> start slave；
mysql> show slave status\G;

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|       130 |      | 3306 |       128 | cb00c1df-e769-11ea-bb78-000c2964b485 |
|       129 |      | 3306 |       128 | cb21b3e4-e769-11ea-9073-000c293a54d5 |
+-----------+------+------+-----------+--------------------------------------+


master-02执行
mysql> change master to  master_host='192.168.1.128', master_user='repl', master_password='repl', master_port=3306,master_auto_position = 1;

mysql> start slave；
mysql> show slave status\G;

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|       131 |      | 3306 |       129 | cb0d0683-e769-11ea-af57-000c29726d85 |
|       128 |      | 3306 |       129 | caeec222-e769-11ea-ae90-000c29ac5126 |
+-----------+------+------+-----------+--------------------------------------+

在从机上配置需要复制的主机 Slava1 复制 Master1，Slava2 复制 Master2。

slave-01执行
change master to  master_host='192.168.1.128', master_user='repl', master_password='repl', master_port=3306,master_auto_position = 1;

start slave；
show slave status\G;


slave-02执行
change master to  master_host='192.168.1.129', master_user='repl', master_password='repl', master_port=3306,master_auto_position = 1;
start slave；
show slave status\G;

修改 Mycat 的配置文件 schema.xml

[root@node-02 conf]# cat schema.xml
<?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="localhost1" database="world" />  
 
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">  
                <heartbeat>show slave status</heartbeat>  
                <writeHost host="hostM1" url="192.168.1.128:3306" user="root" password="abc123">  
                        <readHost host="slave1" url="192.168.1.130:3306" user="root" password="abc123" />  
                </writeHost>  
                <writeHost host="hostM2" url="192.168.1.129:3306" user="root" password="abc123">  
                        <readHost host="slave2" url="192.168.1.131:3306" user="root" password="abc123" />  
                </writeHost>  
        </dataHost>  
 
</mycat:schema>

server.xml配置文件内容

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

	<user name="user">
		<property name="password">user</property>
		<property name="schemas">TESTDB</property>
		<property name="readOnly">true</property>
		
	</user>

启动Mycat
```
mycat start
```
查看日志是否启动成功

验证

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         129 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         130 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         131 |
+-------------+
1 row in set (0.00 sec)




说明：
    balance="1"，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡

验证写操作

# 登陆mycat,创建一个表
  mysql -uroot -P8066 -p123456 -h192.168.1.131
  MySQL [(none)]> use TESTDB;
  MySQL [TESTDB]> create table test (id int not null primary key,hostname varchar(100),date DATE);

  MySQL [TESTDB]> insert into hello(id,hostname,date) values(1,@@hostname,20200827);
  MySQL [TESTDB]> insert into hello(id,hostname,date) values(1,@@hostname,20200828);

# 检查一下实际的4台数据库服务中是否有test的这个表，以及表里是否有数据，在操作正确的情况下，数据都是有的。

  MySQL [TESTDB]> select * from test;
  mysql> select * from test;
  +----+-----------+------------+
  | id | hostname | date |
  +----+-----------+------------+
  | 1 | master-01 | 2020-08-27 |
  | 2 | master-01 | 2020-08-02 |
  | 3 | master-01 | 2020-08-29 |
  | 5 | master-01 | 2020-08-30 |
  | 6 | master-01 | 2020-08-01 |
  +----+-----------+------------+

  ##   可以看出是从master-01这个主节点进行的写操作

主从切换

停掉master-01上的mysql的服务，发现依然是可以做查询操作的

[root@master-01 ~]# systemctl stop mysqld
[root@master-01 ~]# ps -ef |grep mysqld
root      21113  20951  0 11:19 pts/0    00:00:00 grep --color=auto mysqld


再次2条插入数据，依然没有问题
    mysql> insert into hello(id,hostname,date) values(7,@@hostname,20190804);

    mysql> insert into hello(id,hostname,date) values(8,@@hostname,20190804);

自动切换到master-02主节点

   mysql> select * from hello;
   +----+-----------+------------+
   | id | hostname | date |
   +----+-----------+------------+
   | 1 | master-01 | 2020-08-27 |
   | 2 | master-01 | 2020-08-02 |
   | 3 | master-01 | 2020-08-29 |
   | 5 | master-01 | 2020-08-30 |
   | 6 | master-01 | 2020-08-01 |
   | 7 | master-02 | 2019-08-04 |
   | 8 | master-02 | 2019-08-04 |
   +----+-----------+------------+
   7 rows in set (0.22 sec)

posted @ 2020-08-24 18:01 彭彭の阅读(442) 评论(0) 收藏举报

刷新页面返回顶部

彭彭の

Mycat实现读写分离

Mycat是什么？

Mycat应用场景：

Mycat分片规则：

Mycat读写分离部署：

搭建双主双从

一个主机 m1 用于处理所有写请求，它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请 求。当 m1 主机宕机后，m2 主机负责写请求，m1、m2 互为备机。架构图如下：

搭建 MySQL 数据库主从复制（双主双从）

公告

搭建双主双从　　

一个主机 m1 用于处理所有写请求，它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请求。当 m1 主机宕机后，m2 主机负责写请求，m1、m2 互为备机。架构图如下：