2、Mycat+MySQL主从复制读写分离

Mycat实现MySQL主从复制读写分离

1、部署jdk环境

MyCAT用Java开发，需要有JAVA运行环境，mycat依赖jdk1.7的环境

1）上传jdk

[root@localhost tools]# ll jdk-7u45-linux-x64.tar.gz

-rw-r--r-- 1 root root 138094686 10月 24 2013 jdk-7u45-linux-x64.tar.gz

2）安装jdk

[root@localhost tools]# mkdir /usr/java

[root@localhost tools]# tar xf jdk-7u45-linux-x64.tar.gz -C /usr/java/

3）设置环境变量

[root@localhost tools]# vim /etc/profile.d/java.sh

内容如下：

export JAVA_HOME=/usr/java/jdk1.7.0_45/

export PATH=$JAVA_HOME/bin:$PATH 

export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

使环境变量当前终端生效

[root@localhost tools]# source /etc/profile.d/java.sh

4）测试

[root@localhost tools]# java -version

java version "1.7.0_45"

Java(TM) SE Runtime Environment (build 1.7.0_45-b18)

Java HotSpot(TM) 64-Bit Server VM (build 24.45-b08, mixed mode)

2、安装Mycat

1）下载并上传mycat包

[root@localhost tools]# ll Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz

-rw-r--r--. 1 root root 11499865 8月 31 16:33 Mycat-server-1.5.1-RELEASE-20181130213509-linux.tar.gz

2）解压

[root@localhost tools]# tar xf Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz -C /usr/local/

解压后内容如下：

[root@localhost tools]# ll /usr/local/mycat

3）添加环境变量

[root@localhost tools]# vim /etc/profile.d/mycat.sh

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

[root@localhost tools]# source /etc/profile.d/mycat.sh

3、读写分离配置

1）不使用Mycat托管MySQL主从服务器，简单使用如下配置

#注意：配置前备份下配置文件

[root@localhost tools]# cd /usr/local/mycat/conf

[root@localhost conf]# cp schema.xml{,.bak}

    修改主配置文件 vim /usr/local/mycat/conf/schema.xml

(1)<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

这里的TESTDB就是我们所宣称的数据库名称，必须和server.xml中的用户指定的数据库名称一致。添加一个dataNode="dn1"，是指定了我们这个库只有在dn1上，没有分库。

(2)<dataNode name="dn1" dataHost="localhost1" database="db1" />

这里只需要改database的名字，就是你真是的数据库上的数据库名，可根据自己的数据库名称修改。

(3) <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

需要配置的位置：

balance="1" writeType="0" switchType="1"

balance

1、balance=0 不开启读写分离机制，所有读操作都发送到当前可用的writehostle .

2、balance=1 全部的readhost与stand by writeHost 参与select语句的负载均衡。简单的说，双主双从模式(M1->S1,M2->S2，并且M1和M2互为主备)，正常情况下，M1，S1，S2都参与select语句的复杂均衡。

3、balance=2 所有读操作都随机的在readhost和writehost上分发

writeType

负载均衡类型，目前的取值有3种：

1、writeType="0", 所有写操作发送到配置的第一个writeHost。

2、writeType="1"，所有写操作都随机的发送到配置的writeHost。

3、writeType="2"，不执行写操作。

switchType

1、switchType=-1 表示不自动切换

2、switchType=1 默认值，自动切换

3、switchType=2 基于MySQL 主从同步的状态决定是否切换

(4)<writeHost host="hostM1" url="192.168.46.129:3306" user="mycat" password="123456">

<!– can have multi read hosts –>

<readHost host="hostS2" url="192.168.46.130:3306" user="mycat_r" password="123456" />

<readHost host="hostS3" url="192.168.46.130:3307" user="mycat_r" password="123456" />

<!--<writeHost host="hostS1" url="localhost:3316" user="root"

                  password="123456" />-->

4配置好文件内容如下:

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://org.opencloudb/" >

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

</schema>

<dataNode name="dn1" dataHost="localhost1" database="db1" />

<dataHost name="localhost1" 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.46.129:3306" user="root"

password="123456">

<readHost host="hostR1" url="192.168.46.129:3306" user="root" password="123456" />

<readHost host="hostR2" url="192.168.46.129:3307" user="root" password="123456" />

</writeHost>

</dataHost>

</mycat:schema>

5、创建管理用户

主库上对mycat用户授权如下：

用户：mycat 密码：123456 端口：3306

权限：insert,delete,update,select

命令：grant insert,delete,update,select on TD_OA.* to mycat@'192.168.46.%' identified by '123456';

flush privileges;

从库上mycat_r用户授权如下：

用户：mycat_r 密码：123456 端口：3306/3307

权限： select

grant select on TD_OA.* to mycat@'192.168.46.%' identified by '123456';

flush privileges;

测试环境可以直接使用root用户，授予所有权限：

mysql> grant all on *.* to root@'192.168.46.%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to root@'localhost' identified by '123456';

6、修改mycat配置文件

采用默认配置

注意：

①这里配置的是可以连接主库的两个用户

用户：test 密码：test 给予此用户TESTDB数据库增删改查的权限。

用户：user 密码：password 给予此用户TESTDB数据库读的权限。

②这里的TESTDB，不一定是你数据库上的真实库名，可以任意指定，只要接下来和schema.xml的配置文件的库名统一即可。

7、启动Mycat

方法一：# mycat console #<=通过console命令启动mycat，这样方便提取信息

方法二：# mycat start

方法三：# startup_nowrap.sh #服务脚本方式启动

[root@localhost conf]# netstat -lnupt | egrep "(8|9)066"

tcp6      0      0 :::9066                :::*                    LISTEN      3342/java         

tcp6      0      0 :::8066                :::*                    LISTEN      3342/java       

重启：# mycat restart

8、在客户端连接mysql主库服务器：

# mysql -uuser -puser -h192.168.46.133 -P8066 -DTESTDB

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

9、主从同步读写分离测试

管理端创建表:

[root@localhost ~]# mysql -utest -ptest -h192.168.46.133 -P8066 -DTESTDB

CREATE TABLE test1 (id int(10),name varchar(10),address varchar(20) DEFAULT NULL);

手动停止主从同步: stop slave;

分别在主从库插入数据:

master: insert into test1 values(1,'test1','master');

slave1: insert into test1 values(2,'test1','slave1');

slave2: insert into test1 values(3,'test1','slave2');

管理端验证

负载均衡:

mysql> select * from test1;

+------+-------+---------+

| id  | name  | address |

+------+-------+---------+

|    2 | test1 | slave1  |

+------+-------+---------+

1 row in set (0.00 sec)

mysql> select * from test1;

+------+-------+---------+

| id  | name  | address |

+------+-------+---------+

|    3 | test1 | slave2  |

+------+-------+---------+

1 row in set (0.00 sec)

读写功能:

管理端再次插入数据 insert into test1 values(4,'test1','write');

mysql> insert into test1 values(4,'test1','write');

Query OK, 1 row affected (0.00 sec)

#注意：测试完毕启动主从同步功能。

10、管理命令与监控

mycat自身有类似其他数据库的管理监控方式，可通过mysql命令行，登陆端口9066执行相应的SQL操作，也可通过jdbc的方式进行远程连接管理。

登录：目前mycat有两个端口，8066数据端口，9066管理端口。命令行登录时通过9066管理端口来执行：

# mysql -uuser -puser -h192.168.46.133 -P9066 -DTESTDB

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (monitor)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

选项：

-h 后面接主机

-u mycat server.xml配置的逻辑库用户

-p mycat server.xml配置的逻辑库密码

-P 后面接的端口9066，注意P大写

-D Mycat server.xml中配置的逻辑库

1）查看所有的命令，如下：

mysql> show @@help;

2）显示mycat数据库的列表，对应的在scehma.xml配置的逻辑库

mysql> show @@databases;

+----------+

| DATABASE |

+----------+

| TESTDB  |

+----------+

1 row in set (0.00 sec)

3）显示mycat数据节点的列表，对应的是scehma.xml配置文件的dataNode节点

mysql> show @@datanode;

其中，NAME表示datanode的名称；dataHost 对应的是dataHost属性的值，数据主机的名称，ACTIVE表示活跃的连接数，IDIE表示闲置的连接数，SIZE对应的是总连接的数量。

mysql> show @@heartbeat;

RS_CODE状态为1，正常状态

4、获取当前mycat的版本

mysql> show @@version; /

5、显示mycat前端连接状态

mysql> show @@connection;

6、显示mycat后端连接状态

mysql> show @@backend;

7、显示数据源

mysql> show @@datasource