MyCat教程(二)-MySql数据库读写分离

MyCat教程(一)-MyCat初体验

1. Docker安装主从MySQL(一主一从)

    MySQL主从使用docker安装，简单，快捷，除暴，不是 是粗暴。

    至于Docker是怎么安装使用这，这里暂时不做多余介绍（此处省略好多字）。

1.1 MySQL 配置文件准备

•     主MySql配置文件My.cnf:
  

[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock

[mysqld]
#mysql5.7以后的不兼容问题处理
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=mycat
#设置logbin格式
binlog_format=STATEMENT

#允许最大连接数
max_connections=200
#服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M
#设置时区
#default-time_zone='+8:00'
[mysqld_safe]
log-error=/var/log/mysqldb/mysqldb.log
pid-file=/var/run/mysqldb/mysqldb.pid
#
# include all files from the config directory
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

•     从MySql配置文件My.cnf:

[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock

[mysqld]
#mysql5.7以后的不兼容问题处理
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#主服务器唯一ID
server-id=2
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=mycat
#设置logbin格式
binlog_format=STATEMENT

#允许最大连接数
max_connections=200
#服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M
#设置时区
#default-time_zone='+8:00'
[mysqld_safe]
log-error=/var/log/mysqldb/mysqldb.log
pid-file=/var/run/mysqldb/mysqldb.pid
#
# include all files from the config directory
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

1.2 Docker MySQL配置

•     主MySQL数据库：
  

    容器内: /var/lib/mysql                                     ->   映射到物理机路径: /usr/local/docker/mysql_master/data

    容器内: /etc/mysql/mysql.conf.d/mysqld.cnf   ->   映射到物理机路径: /usr/local/docker/mysql_master/conf/my.cnf

    容器内: /etc/mysql/mysql.conf.d/mysqld.cnf   ->   映射到物理机路径: /usr/local/docker/mysql_master/conf/my.cnf

•     从MySQL数据库：
  

    容器内: /var/lib/mysql                                  ->   映射到物理机路径: /usr/local/docker/mysql_slave/data

    容器内: /etc/mysql/mysql.conf.d/mysqld.cnf   ->   映射到物理机路径: /usr/local/docker/mysql_slave/conf/my.cnf

    容器内: /etc/mysql/mysql.conf.d/mysqld.cnf   ->   映射到物理机路径: /usr/local/docker/mysql_slave/conf/my.cnf

    将对应的my.cnf文件配置到物理机对应目录

1.3 Docker安装MySQL数据库

    主数据库MySQL命令： 

docker run --name xwder-mysql-master-3307 -p 3307:3306 \
--privileged=true --restart=always \
--ip 172.17.0.4 \
-d -v /usr/local/docker/mysql_master/logs:/var/lib/mysql \
-v /usr/local/docker/mysql_master/data:/var/lib/mysql \
-v /usr/local/docker/mysql_master/conf/my.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf \
-v /etc/localtime:/etc/localtime \
-e MYSQL_ROOT_PASSWORD=root mysql:5.7

    从数据库MySQL命令：

docker run --name xwder-mysql-slave-3308 -p 3308:3306 \
--privileged=true --restart=always \
--ip 172.17.0.5 \
-d -v /usr/local/docker/mysql_slave/logs:/var/lib/mysql \
-v /usr/local/docker/mysql_slave/data:/var/lib/mysql \
-v /usr/local/docker/mysql_slave/conf/my.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf \
-v /etc/localtime:/etc/localtime \
-e MYSQL_ROOT_PASSWORD=root mysql:5.7

    安装成功后 数据库用户名root密码root。

1.4 MySQL主从配置

•     连接主数据库创建从库连接用户：
  

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'slave';
FLUSH PRIVILEGES;

执行结果：
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'slave';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

然后在从数据库是使用slave用户登录主数据库，连接成功。

•     查看主数据库状态：show master status; 

mysql> show master status;
+----------------+--------+------------+------------------------+
|File            |Position|Binlog_Do_DB|Binlog_Ignore_DB        |
+----------------+-------+------------+------------------------+
|mysql-bin.000003|  582   |mycat,mycat |mysql,information_schema|
+----------------+--------+------------+------------------------+
1 row in set (0.00 sec)

•     连接从数据库 配置主数据库信息： 注意启动的连接信息来自于上面从库的信息。
  

CHANGE MASTER TO MASTER_HOST='172.17.0.4',
MASTER_USER='slave',
MASTER_PASSWORD='slave',
MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=582;

执行结果：
mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.4',
MASTER_USER='slave',
MASTER_PASSWORD='slave',
MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=582;
Query OK, 0 rows affected (0.01 sec)

•     从库启动主从配置：  start slave;

启动主从配置： start slave;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

•     查看从库状态命令 登录mysql后： show slave status\G 命令，检查 Slave_IO_Running 和 Slave_SQL_Running 状态 yes 。

1.4 测试主从数据库

    主数据库创建mycat数据库(主数据库里面配置的同步数据库名称为mycat)并创建表mytbl并插入数据：

create database mycat;
use mycat;
CREATE TABLE `mytbl` (
  `id` int(10) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT into mytbl VALUES(1,'xwder');

  检查从库是否有对应数据库和表和数据。

2. Docker安装主从MySQL(双主双从)

    一个主机 m1用于处理所有写请求，它的从机s1和另一台主机m2还有它的从机s2负责所有读请求。

    当 m1主机宕后，m2主机负责写请求，m1、m2互为备机 。

    架构图如下：

    大致的按照步骤可以分为如下几步：

• 分别按照两个一主一从主机安装；

• 然后两个主机之间互相复制。
  

2. MyCat配置

    修改MyCat的 schema.xml 配置文件，配置文件中的  <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 语句的负载均衡。

• （3）balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。

• （4）balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力

参考配置：

	<dataNode name="dn1" dataHost="jdbchost" database="xwder" />
 	<!--同datahost分配的最小空闲连接数，初始是平均分配（各50），运行后根据活动情况，mycat会自行调试各数据库连接，如果被调整少了，如第3条，就会出现read-only
	解决办法，同一个数据库，不同schema，datahost可独立出来，这样独立出来的连接数就不会被共享

	mycat read-only解决办法
	1、修改schema.xml中的checkSQLschema="true"
	2、dataHost中最小连接minCon数加大
	3、后端处理层增加事务-->
	
	<!--  balance="1": 全部的readHost与stand by writeHost 参与 select 语句的负载均衡-->
	<!--writeType="0": 所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个-->
	<dataHost name="host1" maxCon="1000" minCon="50" balance="1"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM1" url="jdbc:mysql://127.0.0.1:3306" user="xwder"
				   password="xxxxx">
				   <readHost host="HostS1" url="" user="" password=""/>
		</writeHost>
		<writeHost host="hostM2" url="jdbc:mysql://127.0.0.1:3307" user="xwder"
				   password="xxxxx">
				   <readHost host="HostS2" url="" user="" password=""/>
		</writeHost>
	</dataHost>