MyCat实现MySQL主从读写分离

一、MySQL读写分离的概述

二、什么是MyCat

三、搭建MYCAT

 

 一、MySQL读写分离的概述

MySQl作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中，由单台MySQL作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。

因此，一般来说都是通过 主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy/Amoeba）来提升数据库的并发负载能力，这样的方案来进行部署与实施的。

1、1读写分离工作原理

基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

 

 

 

1.2  为什么要读写分离

1．面对越来越大的访问压力，单台的服务器的性能成为瓶颈，需要分担负载

2．主从只负责各自的写和读，极大程度的缓解X锁和S锁争用

3．从库可配置myisam引擎，提升查询性能以及节约系统开销

4．增加冗余，提高可用性

 

1.3  常见的中间件程序

 

 

1. Cobar：

阿里巴巴B2B开发的关系型分布式系统，管理将近3000个MySQL实例。 在阿里经受住了考验，后面由于作者的走开的原因cobar没有人维护 了，阿里也开发了tddl替代cobar。

 

1. MyCAT：

社区爱好者在阿里cobar基础上进行二次开发，解决了cobar当时存 在的一些问题，并且加入了许多新的功能在其中。目前MyCAT社区活 跃度很高，目前已经有一些公司在使用MyCAT。总体来说支持度比 较高，也会一直维护下去，

 

1. OneProxy：

数据库界大牛，前支付宝数据库团队领导楼总开发，基于mysql官方 的proxy思想利用c进行开发的，OneProxy是一款商业收费的中间件， 楼总舍去了一些功能点，专注在性能和稳定性上。有朋友测试过说在 高并发下很稳定。

 

1. Vitess：

这个中间件是Youtube生产在使用的，但是架构很复杂。 与以往中间件不同，使用Vitess应用改动比较大要 使用他提供语言的API接口，我们可以借鉴他其中的一些设计思想。

 

1. Kingshard：

Kingshard是前360Atlas中间件开发团队的陈菲利用业务时间 用go语言开发的，目前参与开发的人员有3个左右， 目前来看还不是成熟可以使用的产品，需要在不断完善。

 

1. Atlas：

360团队基于mysql proxy 把lua用C改写。原有版本是支持分表， 目前已经放出了分库分表版本。在网上看到一些朋友经常说在高并 发下会经常挂掉，如果大家要使用需要提前做好测试。

 

1. MaxScale与MySQL Route：

这两个中间件都算是官方的，MaxScale是mariadb (MySQL原作者维护的一个版本)研发的，目前版本不支持分库分表。MySQL Route是现在MySQL官方Oracle公司发布出来的一个中间件。

 

优点：

架构设计更灵活

可以在程序上实现一些高级控制，如：透明化水平拆分，failover,监控

可以依靠些技术手段提高mysql性能

对业务代码的影响小，同时也安全

二、 什么是MyCAT

一个彻底开源的，面向企业应用开发的大数据库集群；

支持事务、ACID、可以替代MySQL的加强版数据库；

一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群；

一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server；

结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品；

一个新颖的数据库中间件产品。

三、部署mycat

准备：三台服务器

主机名	IP	主机作用	安装内容
k8	192.168.1.38	mysql-master 　主（负责写的操作）	mysql-5.7.26-linux-glibc2.12-x86_64
k9	192.168.1.39	mysql-slave　从服务（负责读的操作）	mysql-5.7.26-linux-glibc2.12-x86_64
k10	192.168.1.94	mycat 服务（负页数据读写操作分发）	Mycat-server-1.6-RELEASE-20161028204710-linux.tar

mysql　mysql-5.7.26-linux-glibc2.12-x86_64的安装参照以下：

https://www.cnblogs.com/kezi/articles/11437747.html

 

１、主从mysql的完成（k8 -mysql-master  /  k9 -mysql-slave ）

1.1 k8–Master  配置

[root@k8 ~]# vim /etc/my.cnf

[mysqld]

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
user=mysql
port=3306
socket=/tmp/mysql.sock
character-set-server=utf8
log-error=/var/log/mysqld.log
pid-file=/tmp/mysqld.pid

log-bin=mysql-bin-master

server-id=1

binlog-do-db=ha

binlog-ignore-db=mysql

 从启服务

[root@k8 ~]# systemctl restart mysql

设置登录权限

[root@k8 ~]# mysql -uroot -p1qaz@wsx

mysql> create database ha;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'slave'@"192.168.1.39" IDENTIFIED BY "1qaz#EDC";

mysql> flush privileges;

mysql> show master status;　　　　#查看状态
+-------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000001 | 751 | ha | mysql | |
+-------------------------+----------+--------------+------------------+-------------------+

导出数据库上传到k9

[root@k8 ~]# /usr/local/mysql/bin/mysqldump -uroot -p1qaz@wsx -B ha >ha.sql

[root@k8 ~]# scp ha.sql 192.168.1.39:/root

１.２ k9－mysql -slave 从服务器配置

测试是否可以登录主服务器k8

[root@k9 ~]# mysql -uslave -p1qaz#EDC -h 192.168.1.38

 

导入数据库

[root@k9 ~]# mysql -uroot -p1qaz@wsx <ha.sql

修改从服务器配置文件

[root@k9 ~]# vim /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
user=mysql
port=3306
socket=/tmp/mysql.sock
character-set-server=utf8
log-error=/var/log/mysqld.log
pid-file=/tmp/mysqld.pid
bind-address=0.0.0.0
symbolic-links=0
server-id=2

重启数据服务

[root@k9 ~]# systemctl restart mysql

[root@k9 ~]# mysql -uroot -p1qaz@wsx

数据库版本须要一致的确认k8和k9执行如下：

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.26 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.26 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.12 |
+-------------------------+------------------------------+

设置权限在k9上执行

mysql> change master to master_host='192.168.1.38',master_user='slave',master_password='1qaz##EDC';

mysql> start slave;

 

 

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.38
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-master.000002
Read_Master_Log_Pos: 154
Relay_Log_File: k9-relay-bin.000002
Relay_Log_Pos: 327
Relay_Master_Log_File: mysql-bin-master.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

表示主从成功

注：如遇到不同步，手动修改同步参照以下：

mysql> change master to master_log_file='mysql-bin-master.000003',master_log_pos=154;

测试主从数据同步：k8 创建一些数据，查看从服务器k9运行情况。

[root@k8 ~]# mysql -uroot -p1qaz@wsx

mysql> use ha;

mysql> create table student(id int(10),name char(20),age int(10));

mysql> insert into student values(1,"zhansan",18);

mysql> select * from student;
+------+---------+------+
| id | name | age |
+------+---------+------+
| 1 | zhansan | 18 |
+------+---------+------+

k9　登录查看数据是否更新

[root@k9 ~]# mysql -uroot -p1qaz@wsx

mysql> use ha;

mysql> select * from student;
+------+---------+------+
| id | name | age |
+------+---------+------+
| 1 | zhansan | 18 |
+------+---------+------+
1 row in set (0.00 sec)

主从完成搭建

 

 

２、mycat中间件的搭建

2、1安装mycat-server

[root@94 ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local

[root@94 ~]# cd /usr/local/mycat

[root@94 mycat]# useradd  mycat

[root@94 mycat]# passwd mycat  

1qaz#EDC

2、2 Mycat 需要安装JDK 1.7以上

[root@94 mycat]# mkdir /usr/java

[root@94 ~]# tar -zxvf jdk-8u191-linux-x64.tar_\(1\).gz -C /usr/local

[root@94 jdk1.8.0_191]# vim /etc/profile.d/java.sh

JAVA_HOME=/usr/local/jdk1.8.0_191
PATH=$JAVA_HOME/bin:$PATH
CLASSPATH=$JAVA_HOME/jre/lib/ext:$JAVA_HOME/lib/tools.jar
export PATH JAVA_HOME CLASSPATH

[root@94 jdk1.8.0_191]# source /etc/profile.d/java.sh

[root@94 jdk1.8.0_191]# java -version
java version "1.8.0_191"
Java(TM) SE Runtime Environment (build 1.8.0_191-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.191-b12, mixed mode)

[root@94 jdk1.8.0_191]# vim /etc/profile.d/mycat.sh

MYCAT_HOME=/usr/local/mycat PATH=$MYCAT_HOME/bin:$PATH

[root@94 jdk1.8.0_191]# source /etc/profile.d/mycat.sh

2.3MyCAT集群MyCAT Server服务器IP和主机名的映射

 [root@94 ~]# vim /etc/hostname

k10

[root@94 ~]# vim /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.38 k8
192.168.1.39 k9
192.168.1.94 k10

 

[root@94 ~]# scp /etc/hosts 192.168.1.38:/etc/hosts

[root@94 ~]# scp /etc/hosts 192.168.1.39:/etc/hosts

2.4 mycat的用户账号和授权信息是在conf/server.xml文件中配置

[root@94 ~]# vim /usr/local/mycat/conf/server.xml

</system>

        <user name="root">

                <property name="password">123456</property>

                <property name="schemas">ha</property>

        </user>

 

        <user name="user">

                <property name="password">user</property>

                <property name="schemas">ha</property>

                <property name="readOnly">true</property>

        </user>

编辑配置文件conf/schema.xml

[root@94 ~]# mv /usr/local/mycat/conf/schema.xml /usr/local/mycat/conf/schema.xml.bak
[root@94 ~]# vim /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<schema name="ha" checkSQLschema="false" sqlMaxLimit="100" dataNode='dn1'>
</schema>
<dataNode name="dn1" dataHost="dthost" database="ha"/>
<dataHost name="dthost" maxCon="500" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="k8" url="192.168.1.38:3306" user="mycat" password="1qaz#EDC">
</writeHost>
<writeHost host="k9" url="192.168.1.39:3306" user="mycat" password="1qaz#EDC" />
</dataHost>
</mycat:schema>

2.5 Mycat的启动

[root@k10 ~]# cd /usr/local/mycat/bin/

[root@k10 bin]# ./mycat  start

Starting Mycat-server...

 

[root@cga29 ~]# mysql -uroot -p1qaz@wsx

mysql>  GRANT ALL PRIVILEGES ON *.* TO 'mycat'@"%" IDENTIFIED BY " 1qa#EDC ";

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> set global validate_password_policy=LOW;

Query OK, 0 rows affected (0.00 sec)

mysql>  set global validate_password_length=6;

Query OK, 0 rows affected (0.00 sec)

mysql>  GRANT ALL PRIVILEGES ON *.* TO 'mycat'@"%" IDENTIFIED BY "1qaz#EDC";

Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.06 sec)

2.7测试可用性

启动mycat

[root@cga31 ~]#  /usr/local/mycat/bin/mycat  restart

[root@cga31 bin]# /usr/local/mycat/bin/mycat  restart

Stopping Mycat-server...

Stopped Mycat-server.

Starting Mycat-server...