MySQL读写分离实践-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_52441468/article/details/115210412

工作原理

主数据库处理事务性增、删、改操作
从数据路处理select查询操作

为什么要读写分离

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

主从只负责各自的写和读，极大程度的缓解X锁和S锁争用
从库可配置myisam引擎，提升查询性能以及节约系统开销
增加冗余，提高可用性

实现方式

应用程序实现

优点：
A：应用程序内部实现读写分离，安装既可以使用
B：减少一定部署难度
C：性能很好
缺点：
A：架构一旦调整，代码要跟着变
B：难以实现高级应用，如自动分库，分表
C：无法适用大型应用场景

中间件实现

优点：
A：架构设计更灵活
B：可以在程序上实现一些高级控制，如：透明化水平拆分，failover,监控
C：可以依靠些技术手段提高mysql性能，
D：对业务代码的影响小，同时也安全
缺点：
需要一定的开发运维团队的支持

mycat

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

• 支持事务、ACID、可以替代MySQL的加强版数据库
• 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
• 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
• 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品

服务安装与配置

实验环境

192.168.13.14 master
192.168.13.13 slave1
192.168.13.34 slave2
192.168.13.19 mycat

设置主从

四台机器同步时间
在mycat配置ntp服务，为其他三台服务器提供时间校对服务
[root@haha ~]# yum -y install ntp

[root@haha ~]# vim /etc/ntp.conf
##添加下面两行配置
server 127.127.1.0
fudge 127.127.1.0 stratum 8

[root@haha ~]# systemctl start ntpd
[root@haha ~]# systemctl enable ntpd

其他服务器
[root@haha ~]# yum -y install ntpdate
[root@haha ~]# /usr/sbin/ntpdate 192.168.13.19

四台机器hosts绑定
[root@haha ~]# vim /etc/hosts
192.168.13.19 mycat
192.168.13.14 master
192.168.13.13 slave1
192.168.13.34 slave2

授权用户
mysql> grant replication slave on *.* to 'slave'@'192.168.13.%'  identified by "123"; 
mysql> flush privileges;

配置文件
[root@haha ~]# vim /etc/my.cnf
server-id=1
log-bin=/data/mysql/log/mysql-bin-master
binlog-do-db=haha
sync-binlog=1  ##每一次binlog写入，就同步内容至硬盘
binlog-format=mixed  ##二进制格式
systemctl restart mysqld

主库设置只读锁
mysql> flush tables with read lock;
mysql> flush privileges;

导出数据
[root@haha ~]# mysqldump -uroot -p123456 -B haha>haha.sql

传输数据
[root@haha ~]# scp haha.sql root@192.168.13.13:~
[root@haha ~]# scp haha.sql root@192.168.13.34:~

导入数据
[root@haha ~]# mysql -uroot -p123456 < haha.sql 

配置文件
[root@haha ~]# vim /etc/my.cnf
server-id=2
relay-log=/data/mysql/log/relay-log-bin
relay-log-index=/data/mysql/log/slave-relay-bin.index
replicate-do-db=haha ##只复制指定的库
#replicate-ignore-db=mysql ##不复制的库

[root@haha ~]# systemctl restart mysqld

从库配置
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host="192.168.13.14",master_user="slave",master_password="123456",master_log_file="mysql-bin-master.000001",master_log_pos=1530;Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
 		Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

解除锁定
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

验证
mysql> insert into haha.test values(2,"zex");
Query OK, 1 row affected (0.00 sec)

mysql> select * from haha.test;
+------+------+
| id   | name |
+------+------+
|    1 | chm  |
|    2 | zex  |
+------+------+
2 rows in set (0.00 sec)
##不解锁，是不能同步的

准备安装包

http://www.mycat.io
rz上传安装包

解压
[root@mycat ~]# tar -zxvf Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz -C /usr/local

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

[root@mycat ~]#  tar -zxvf jdk-8u171-linux-x64.tar.gz -C /usr/local

配置jdk环境变量

[root@mycat ~]# vim /etc/profile
JAVE_HOME=/usr/local/jdk1.8.0_171
PATH=$JAVA_HOME/bin:$PATH
CLASSPATH=$JAVA_HOME/jre/lib/ext:$JAVA_HOME/lib/tools.jar
export PATH JAVE_HOME CLASSPATH

[root@mycat ~]# source /etc/profile

查看java环境
[root@mycat ~]# java -version

服务启动与启动设置

配置mycat环境变量
[root@mycat ~]# vim /etc/profile
PATH=$PATH:/usr/local/mycat/bin

[root@mycat ~]# source /etc/profile

配置mycat的连接信息（账号密码）：
[root@mycat ~]# cd /usr/loca/mycat/conf
[root@mycat ~]# cp server.xml server.xml.bak
[root@mycat ~]# vim server.xml
<user name="mycatroot" defaultAccount="true">
##name=连接mycat的账号

<property name="passwod">123</property>
##password=密码
<property name="schemas">haha</property>
##schemas=对应数据库读写分离配置文件，<schems>中设置的名称

</user>

 <user name="mycatreadonly">
 	<property name="password">123</property>
  	<property name="schemas">haha</property>
  	<property name="readOnly">true</property>
</user>
##只读权限账号（其余同上）

[root@mycat ~]# cd /usr/local/mycat/conf
[root@mycat ~]# cp schema.xml schema.xml.bak

[root@mycat ~]# vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

<schema name="haha" checkSQLschema="false" sqlMaxLimit="100" data
Node="dn1"></schema>
<dataNode name="dn1" dataHost="dh1" database="haha" />
<dataHost name="dh1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>

<writeHost host="master" url="192.168.13.14:3306" user="root" password="123" />
<writeHost host="slave1" url="192.168.13.13:3306" user="root" password="123" />
<writeHost host="slave2" url="192.168.13.34:3306" user="root" password="123" />
</dataHost>
</mycat:schema>
##balance=”1”除了第一个writehost，随机读其他的writehost，因为writetype=“0”，写操作发给了第一个writehost，如过switchtype设置为“1”或者“2”，第一个writehost宕机后，自动的把写操作写入第二个writehost，只读第三个writehost，这时候第二个writehost并不会同步数据到第三个writehost，所以查不到数据，使用高可用架构解决此问题


给所有MySQL root 远程登录权限
所有的数据库都执行操作
mysql> grant all on *.* to root@'192.168.13.%' identified by '123456';
mysql> flush privileges;

启动mycat服务
mycat console  
##先使用console进行检测

出现successfully后，ctrl+c退出

[root@mycat ~]# mycat start
##mycat支持的命令{ console | start | stop | restart | status | dump }

检测端口8066
[root@mycat ~]# netstat -antup | grep 8066
##如果没有端口，查看/usr/local/mycat/logs/mycat.log里的日志

# 连接mycat
## 使用mycatroot账号登录
````c
[root@mycat ~]# yum -y install mysql 
##安装mysql客户端命令

[root@mycat ~]# mysql -u mycatroot -p123456 -h 192.168.13.19 -P 8066

MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| haha     |
+----------+

MySQL [(none)]> use haha;

MySQL [haha]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | chm  |
|    2 | zex  |
+------+------+

MySQL [haha]> insert into test values(3,"haha");
Query OK, 1 row affected (0.04 sec)
mysql> select * from haha.test;
+------+------+
| id   | name |
+------+------+
|    1 | chm  |
|    2 | zex  |
|    3 | haha |
+------+------+
##测试写，可以插入数据，因为从库会同步数据，在从库查看，发现插入数据是成功的

mysql> insert into test values(4,"13.13");
mysql> insert into test values(5,"13.34");
##测试读，插入不同数据到从库中，因为主库没有，所以可以随机查看到两个从库的内容，就说明读是从从库读来的

MySQL [haha]> select * from test;
+------+--------+
| id   | name   |
+------+--------+
|    1 | chm    |
|    2 | zex    |
|    3 | haha   |
|   11 | slave1 |
+------+--------+
##从13.13中读的

MySQL [haha]> select * from test;
+------+--------+
| id   | name   |
+------+--------+
|    1 | chm    |
|    2 | zex    |
|    3 | haha   |
|   12 | slave2 |
+------+--------+
##从13.34中读的

使用mycatonly账号登录

使用mycatonly只读账号登陆测试是否有写权限
mysql -umycatreadonly -p123 -h 192.168.13.19 -P8066

MySQL [(none)]> use haha

MySQL [haha]> insert into test values(19,"test");
ERROR 1495 (HY000): User readonly