MySQL之读写分离

简介#

作用#

• MySQl 作为目前世界上使用最广泛的免费数据库。但在实际的生产环境中，由单台 MySQL 作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。
• 因此，一般来说都是通过 主从复制（ Master-Slave）的方式来同步数据，再通过读写分离（ MySQL-Proxy/Amoeba）来提升数据库的并发负载能力，这样的方案来进行部署与实施的

底层原理#

• 主数据库实现事务增删改
• 从数据库实现查
• 数据库复制将主服务器中事物的的变更同步到从数据库中

优点#

• 面对越来越大的访问压力，单台的服务器的性能成为瓶颈，需要分担负载
• 主从只负责各自的写和读，极大程度的缓解 X 锁和 S 锁争用
• 从库可配置 myisam 引擎，提升查询性能以及节约系统开销
• 增加冗余，提高可用性

MyCAT#

作用#

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

基础环境配置#

MyCat 有提供编译好的安装包，支持 Windows、 Linux、 Mac、 Solaris 等系统上安装与运行。官方下载主页 http://www.mycat.io

jdk#

Copy
 # 解压到/usr/local     
[root@MyCAT ~]# tar -xvf jdk-8u191-linux-x64.tar.gz -C /usr/local/

# 配置jdk的环境变量
[root@MyCAT ~]# 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@MyCAT ~]# source /etc/profile.d/java.sh      # 使配置文件生效

[root@MyCAT local]# 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)

mycat#

Copy
[root@MyCAT ~]# tar -xvf  Mycat-server-1.5-RELEASE-20160301083012-linux.tar.gz -C /usr/local/

# 配置mycat的专属用户以及权限
[root@MyCAT local]# cd /usr/local/      # 进入mycat的解压目录
[root@MyCAT local]# ls /usr/local/mycat/      # 查看当前目录
      bin  catlet  conf  lib  logs  version.txt
[root@MyCAT local]# useradd mycat && passwd mycat      # 给mycat专门创建一个用户

[root@MyCAT local]# ll /usr/local/mycat/
    总用量 12
    drwxr-xr-x. 2 root root  190 7月  26 19:51 bin
    drwxrwxrwx. 2 root root    6 12月 13 2015 catlet
    drwxrwxrwx. 2 root root 4096 7月  26 19:51 conf
    drwxr-xr-x. 2 root root 4096 7月  26 19:51 lib
    drwxrwxrwx. 2 root root    6 12月 13 2015 logs
    -rwxrwxrwx. 1 root root  217 3月   1 2016 version.txt

[root@MyCAT local]# chown -R mycat:mycat /usr/local/mycat/      # 修改属主/组
[root@MyCAT local]# ll /usr/local/mycat/
    总用量 12
    drwxr-xr-x. 2 mycat mycat  190 7月  26 19:51 bin
    drwxrwxrwx. 2 mycat mycat    6 12月 13 2015 catlet
    drwxrwxrwx. 2 mycat mycat 4096 7月  26 19:51 conf
    drwxr-xr-x. 2 mycat mycat 4096 7月  26 19:51 lib
    drwxrwxrwx. 2 mycat mycat    6 12月 13 2015 logs
    -rwxrwxrwx. 1 mycat mycat  217 3月   1 2016 version.txt


# 配置mycat的环境变量
[root@MyCAT ~]# vim /etc/profile.d/mycat.sh
   MYCAT_HOME=/usr/local/mycat PATH=$MYCAT_HOME/bin:$PATH
[root@MyCAT ~]# source /etc/profile.d/mycat.sh

[root@MyCAT ~]# mycat start      # 启动mycat

DNS映射#

Copy
# 如果是在多台 Linux 系统中组建的 MyCAT 集群，那需要在 MyCAT Server 所在的服务器上配置对其他 IP 和主机名的映射，配置方式如下：
[root@MyCAT ~]# vim /etc/hosts
      10.1.1.1 test1.cn
      10.1.1.2 test2.cn
      10.1.1.5 test5.cn

配置mycat#

server.xml#

mycat的账户信息以及号授权信息

Copy
[root@MyCAT ~]# cd  /usr/local/mycat/conf/      # 进入mycat的配置文件

[root@MyCAT conf]# cp server.xml server.xml.bak      # 备份server.xml 防止后期出错

[root@MyCAT conf]# echo " " > server.xml      # 清空server.xml的内容 添加我们自己书写的内容
[root@MyCAT conf]# vim server.xml
    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
<system>
        <property name="defaultSqlParser">druidparser</property>
</system>

# 以下设置为应用帐号权限
<user name="root">
        <property name="password">root123</property>
        <property name="schemas">test</property>      # 逻辑的虚拟库 可以映射到mysql中真正的数据库
</user>

# 以下设置为应用只读帐号权限
<user name="user">
        <property name="password">user</property>
        <property name="schemas">test</property>
        <property name="readOnly">true</property>
</user>
</mycat:server>

schema.xml#

对数据库的操作

Copy
[root@MyCAT conf]# cp schema.xml schema.xml.bak      # 备份

[root@MyCAT conf]# echo "" > schema.xml      # 清空原有信息

[root@MyCAT conf]# vim schema.xml
      
      <?xml version="1.0"?>
      <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
      <mycat:schema xmlns:mycat="http://org.opencloudb/">
	<schema name="test" checkSQLschema="false" sqlMaxLimit="100"dataNode='dn1'></schema>
	<dataNode name="dn1" dataHost="dthost" database="test"/>
	<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="test1.cn" url="10.1.1.1:3306" user="mycat"
	password="root123">
	</writeHost>
	<writeHost host="test2.cn" url="10.1.1.2:3306" user="mycat"
	password="root123">
	</writeHost>
	</dataHost>
      </mycat:schema>

scheme参数解释

Copy

mycat启动#

Copy
[root@MyCAT ~]# /usr/local/mycat/bin/mycat start

[root@MyCAT ~]# cat /usr/local/mycat/logs/wrapper.log | grep successfully      # 出现successfully表示成功了
      INFO   | jvm 2    | 2020/07/26 20:16:32 | MyCAT Server startup <successfully>. see logs in logs/mycat.log

MySQL主从#

master#

Copy
[root@master~]# mysql -uroot -p

# 创建数据库
mysql> create database test;
mysql> use test;
mysql> create table test(id int,name varchar(254));

# 授权给 mycat 登陆数据库使用的帐号
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mycat'@"%" IDENTIFIED BY "root123";
mysql> grant replication slave on *.* to slave@"10.1.1.%" identified by "root123";
mysql> flush privileges;

[root@master~]# vim /etc/my.cnf
      server-id=1
      log-bin=master-log-bin
      binlog-do-db=test
      binlog-ignore-db=mysql
[root@master~]# systemctl restart mysqld

# 数据库导出
[root@master~]#  mysqldump -uroot -p -B test>test.sql
[root@master~]# scp test.sql 10.1.1.2:/root/      # 传递给slave

slave#

Copy
[root@slave~]# mysql -uroot -p < test.sql      # 导入表结构

[root@slave~]# vim /etc/my.cnf
      server-id=2
[root@slave~] systemctl restart mysqld

[root@slave~]# mysql -uroot -p
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mycat'@"%" IDENTIFIED BY "root123";

mysql> flush privileges;

mysql> stop slave; #停止 slave

mysql> change master tomaster_host='10.1.1.1',master_user='slave',master_password='root123';

mysql> start slave; #启动 slave

mysql> show slave status\G #查看状态 ，有两个 yes 主从同步成功！

数据测试#

模拟slave故障#

Copy
[root@slave ~]# systemctl stop mysqld.service

[root@client~]# mysql -uroot -p123456 -h 10.1.1.1 -P8066      # 连接mycat
mysql> use test;
mysql> select * from test;

模拟master故障#

Copy
[root@master~]# systemctl stop mysqld.service

[root@client~]# mysql -uroot -p123456 -h 10.1.1.1 -P8066      # 连接mycat

mysql> use test;

mysql> create table test1(id int);
ERROR 1184 (HY000): 拒绝连接 #主数据库停止了，是无法写操作的

mysql> select * from test;      # 但是此时可以读