使用 Mycat 实现 M-S-S 模式读写分离

• 实验环境 
• Mycat               192.168.2.2      CentOS Linux release 8.3.2011
• Mysql Master   192.168.2.3      CentOS Linux release 7.5.1804
• Mysql 中继       192.168.2.5     CentOS Linux release 7.6.1810
• Mysql slave      192.168.2.6     CentOS Linux release 7.6.1810
• firewalld、iptables以及 selinux 均为关闭状态
• Mysql 版本统一使用 mysql-5.7.25
• 分别在 2.3    2.5    2.6   3台服务器上安装 mysql
• yum install -y ./mysql-5.7/mysql*.rpm

• 安装完成后启动mysql​​​​​
• systemctl enable mysqld --now

• 关闭密码强度审计插件
• echo "validate-password=OFF">> /etc/my.cnf
• systemctl restart mysqld
• 分别修改3台服务器的 root 用户密码
• grep "password" /var/log/mysqld.log
• mysql -uroot -p'Dlr%wM?<a6pC'

• mysql> set password for root@localhost = password('123456');
  Query OK, 0 rows affected, 1 warning (0.00 sec)

  mysql> flush privileges;
  Query OK, 0 rows affected (0.00 sec)

  mysql>

  

  

  

• 重新登陆后，在主服务器 192.168.2.3 上授权用户
• mysql -uroot -p'123456'
• >grant replication slave on *.* to slave@'192.168.2.%' identified by '123456';
• >create database mydata;
• >use mydata;
• >create table T1(id int,name varchar(20));
• >insert into T1 values(1,'zhangsan');

• 退出数据库，导出刚才创建的数据库
• mysqldump -u root -p -B mydata > /tmp/mysql_mydata.sql

• 编辑配置文件 /etc/my.cnf

• vim /etc/my.cnf
  [mysqld]
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock

  log-bin=mysql-bin-master
  server-id=1
  binlog-do-db=mydata          #指定要同步的库
  binlog-ignore-db=mysql      #忽略要同步的库
  sync-binlog=1                     #执行 N 次写入 binlog 后，与硬盘同步
  binlog-format=row              #binlog 保存方式，记录哪条数据被修改了

• 查看log_bin系统变量的设置
• >show variables like 'log_bin';

• 接下来 部署 slave 中继服务器 192.168.2.5 

• 导入刚才导出的数据库文件
• scp root@192.168.2.3:/tmp/mysql_mydata.sql /tmp
• mysql -uroot -p123456 < /tmp/mysql_mydata.sql
• mysql -uroot -p123456 -e "select * from mydata.T1"

• 编辑配置文件 /etc/my.cnf

• vim /etc/my.cnf
  [mysqld]
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock

  server-id=2
  log-bin=mysql-bin-slave1
  log-slave-updates=1  
  binlog-format=row

• log-slave-updates  把它从 relay-log 当中读取出来的二进制日志并且这本机上执行的操作也记录这自己的二进制日志里面，这样才能使第三台 slave 通过中继 slave 读取到相应数据变化

• 开始配置主从同步

• 在中继服务器 192.168.2.5 上

• mysql -uroot -p123456
• >stop slave;
• >change master to master_host='192.168.2.3',master_user='slave',master_password='123456';
• >start slave;
• >show slave status \G

• 测试 在 192.168.2.3 主服务器上插入数据

• 然后在从服务器 192.168.2.5 上查看

• 手动刷新 bin-log
• >flush logs

• 在 192.168.2.5 上添加授权用户

• mysql>grant replication slave on *.* to slave@'192.168.2.%' identified by '123456';
  Query OK, 0 rows affected, 1 warning (0.01 sec)

  mysql> flush privileges;
  Query OK, 0 rows affected (0.00 sec)

  mysql> select user,host from mysql.user;

• 接下来 部署 slave  192.168.2.6

• 导入数据库
• scp root@192.168.2.5:/tmp/mysql_mydata.sql /tmp
• mysql -uroot -p < /tmp/mysql_mydata.sql

• 编辑 配置文件 /etc/my.cnf

• vim /etc/my.cnf

  [mysqld]
  
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock

  server-id=3
  log-bin=mysql-bin-slave2 
  binlog-format=row

• 指定 slave 中继服务 192.168.2.5

• mysql -uroot -p123456
• >stop slave;
• >change master to master_host='192.168.2.5',master_user='slave',master_password='123456';
• >flush privileges;
• >start slave;

• 测试 在 主服务器上插入数据

• 在中继和从服务器上查看

• 模拟故障：
  由于历史遗留问题，MySQL 主从库的表结构不一致，主库的某个表 tableA 比从库表 tableA 少了一个字段。
  当尝试在主库上更改表结构时，这行 alter 语句会随着 binlog 同步到从库，如果从库执行这行语句时出错，主从同步线程就会自动停止，那样只能人为手动处理错误，然后再启动 slave 上的主从同步线程。

• 首先在从库 192.168.2.6 上添加这个字段：
• >alter table mydata.T1 add age int default 0 after name;
• 然后在主库 192.168.2.3 上添加这个字段：
• >alter table mydata.T1 add age int default 0 after name;
• 查看从库 192.168.2.6 的 slave 状态
• mysql> show slave status \G
  *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: 192.168.2.5
                    Master_User: slave
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: mysql-bin-slave1.000001
            Read_Master_Log_Pos: 1301
                 Relay_Log_File: c7_2_6-relay-bin.000002
                  Relay_Log_Pos: 1337
          Relay_Master_Log_File: mysql-bin-slave1.000001
               Slave_IO_Running: Yes
              Slave_SQL_Running: No
                Replicate_Do_DB: 
            Replicate_Ignore_DB: 
             Replicate_Do_Table: 
         Replicate_Ignore_Table: 
        Replicate_Wild_Do_Table: 
    Replicate_Wild_Ignore_Table: 
                     Last_Errno: 1060
                     Last_Error: Error 'Duplicate column name 'age'' on query. Default database: ''. Query: 'alter table mydata.T1 add age int default 0 after name'

• 解决办法

• 首先在主库上停止 bin-log
• mysql> set sql_log_bin=off;
• 然后再执行 alter 语句
  mysql> alter table T1 add sex int default 0 after name;
• 再开启 bin-log
  mysql> set sql_log_bin=on;

• mysql> set sql_log_bin=off;
  Query OK, 0 rows affected (0.00 sec)

  
  mysql> alter table mydata.T1 add sex int default 0 after name;
  Query OK, 0 rows affected (0.03 sec)
  Records: 0  Duplicates: 0  Warnings: 0

  mysql> set sql_log_bin=on;
  Query OK, 0 rows affected (0.00 sec)

• 然后在从库查看
  mysql> show slave status\G
  还是 no 是因为上次的 alter 没有同步
  做如下操作恢复
  mysql> stop slave;
• change master to master_log_file='mysql-bin-slave1.000001',master_log_pos=1301;
• mysql> start slave;
  mysql> show slave status \G

• 在主库上插入数据

• 然后在 192.168.2.2 上配置 Mycat

• 详细设置请参阅：https://blog.csdn.net/gaofei0428/article/details/117503469?spm=1001.2014.3001.5501
• 编辑 /usr/local/mycat/conf/server.xml 配置文件

•  vim /usr/local/mycat/conf/server.xml
  <?xml version="1.0" encoding="UTF-8"?>
  <!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
          - you may not use this file except in compliance with the License. - You 
          may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
          - - Unless required by applicable law or agreed to in writing, software - 
          distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
          WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
          License for the specific language governing permissions and - limitations 
          under the License. -->
  <!DOCTYPE mycat:server SYSTEM "server.dtd">
  <mycat:server xmlns:mycat="http://io.mycat/">
          <system>
          <property name="nonePasswordLogin">1</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
          <property name="ignoreUnknownCommand">0</property><!-- 0遇上没有实现的报文(Unknown command:),就会报错、1为忽略该报文，返回ok报文。
          在某些mysql客户端存在客户端已经登录的时候还会继续发送登录报文,mycat会报错,该设置可以绕过这个错误-->
          <property name="useHandshakeV10">1</property>
      <property name="removeGraveAccent">1</property>
          <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
          <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
          <property name="sqlExecuteTimeout">300</property>  <!-- SQL 执行超时 单位:秒-->
                  <property name="sequenceHandlerType">1</property>
          <!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
          INSERT INTO `travelrecord` (`id`,user_id) VALUES ('next value for MYCATSEQ_GLOBAL',"xxx");
          -->
          <!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况-->
          <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
          <property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
          <property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
        <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
          <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
          <!-- <property name="processorBufferChunk">40960</property> -->
          <!-- 
          <property name="processors">1</property> 
          <property name="processorExecutor">32</property> 
           -->
          <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
                  <property name="processorBufferPoolType">0</property>
                  <!--默认是65535 64K 用于sql解析时最大文本长度 -->
  "/usr/local/mycat/conf/server.xml" [dos] 134L, 6443C                                                                                                               
          <!--例如<host host="127.*" user="root"/>-->
          <!--例如<host host="1*7.*" user="root"/>-->
          <!--这些配置情况下对于127.0.0.1都能以root账户登录-->
          <!--
          <firewall>
             <whitehost>
                <host host="1*7.0.0.*" user="root"/>
             </whitehost>
         <blacklist check="false">
         </blacklist>
          </firewall>
          -->

          <user name="gf" defaultAccount="true">    user name 定义 mycat 管理账号，名称可以自定义，无需创建该用户
                  <property name="password">123456</property>
                  <property name="schemas">mydata</property>
                  <property name="defaultSchema">mydata</property>
                  <!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->

                  <!-- 表级 DML 权限设置 -->
                  <!--            
                  <privileges check="false">
                          <schema name="TESTDB" dml="0110" >
                                  <table name="tb01" dml="0000"></table>
                                  <table name="tb02" dml="1111"></table>
                          </schema>
                  </privileges>           
                   -->
          </user>

          <user name="user">
                  <property name="password">user</property>
                  <property name="schemas">mydata</property>
                  <property name="readOnly">true</property>
                  <property name="defaultSchema">mydata</property>
          </user>

  </mycat:server>

• 添加 slave 从服务器为读服务器

• vim /usr/local/mycat/conf/schema.xml
  <?xml version="1.0" encoding="UTF-8"?>
  <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  <mycat:schema xmlns:mycat="http://io.mycat/">
          <schema name="mydata" checkSQLschema="false" sqlMaxLimit="100" dataNode='dn1'>
          </schema>

          <dataNode name="dn1" dataHost="dthost" database="mydata"/>
          <dataHost name="dthost" maxCon="500" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100">
                  <heartbeat>show slave status</heartbeat>
                  <writeHost host="c7_2_3" url="192.168.2.3:3306" user="mycat" password="123456">
                  </writeHost>
                  <writeHost host="c7_2_5" url="192.168.2.5:3306" user="mycat" password="123456">
                          <readHost host="c7_2_6" url="192.168.2.6:3306" user="mycat" password="123456"></readHost>
                  </writeHost>
          </dataHost>
   </mycat:schema>

• 重启服务

• 尝试登陆 Mycat

• mysql -ugf -p -h192.168.2.2 -P8066

• 配置文件中指定了不需要密码登陆，直接回车即可

• 查看读写记录

• mysql -uroot -p123456 -h192.168.2.2 -P9066 -e "show @@datasource"

• 测试读写分离

• 测试主库故障

Mysql日志类型

•  二进制日志 
• 事务日志
• 一般查询日志
• 中继日志
• 慢查询日志

二进制日志

• 二进制日志通常记录的是可能潜在引起数据库发生改变的操作，每一个操作我们称为一个event。
• 二进制日志记录一个event的时候，通常还会记录timestamp,position(偏移量offset)，server-id，event本身。
• 二进制日志的数据存储形式，形如mysql-bin.xxxxxx这种，二进制日志除了mysql-bin.xxxxxx之外，还有mysql-bin.index(二进制日志索引文件)
• 二进制日志滚动条件：容量达到定义的最大上限，flush logs ,服务器重启
• 二进制日志删除：一般不建议用rm直接删除，建议用mysql的PURGE命令清除
•                  语法：PURGE {MASTER | BINARY} LOGS TO 'log_name'    # 删除指定的日志
•                            PURGE {MASTER | BINARY} LOGS BEFORE 'date'  # 删除日期之前的日志，BEFORE变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式
•                      如：（MASTER 和BINARY 在这里都是等效的）
•                            PURGE MASTER LOGS TO 'test-bin.000001';   
•                            PURGE MASTER LOGS BEFORE '2011-01-0100:00:00';
• 二进制日志格式：statement(mysql官方不推荐)
•                            row(mysql 5.6以后推荐)
•                            mixed
• 二进制日志查看：     
•                 查看当前mysql使用的二进制文件及处在哪个position上：SHOW MASTER STATUS;
•                 查看当前mysql上使用的二进制文件列表：SHOW BINARY LOGS;
•                 查看某个二进制日志文件的具体内容：SHOW BINARY EVENTS IN 'mysql-bin.1234567';
• 二进制日志用途：
• 二进制日志可以即时点还原，因为二进制日志中记录的是潜在引起数据库发生改变的操作。要注意的是，通过二进制日志恢复的数据可能跟原始数据不一样，在多颗cpu并行工作的情况下，会同时执行多个事务，如果mysql的隔离级别较低，事务之间可能交叉执行(互相影响)，当前能够被使用的二进制日志只有一个，写入二进制日志的方式是串行写入，而事务是并行执行的，事务执行的次序和记录在二进制日志中的次序可能不一致。

Mysql隔离级别：(由低到高)

• READ-UNCOMMITTED
• READ-COMMITTED
• REPEATABLE-READ(缺省隔离级别)
• SERIALIZABLE
• 如果你的mysql隔离级别是REPEATABLE-READ，二进制日志格式是statement，在某些场景下绝对会出现通过二进制日志恢复的数据和原始数据不一致的情况。
• 如果你的mysql隔离级别是READ-COMMITED，二进制日志格式推荐row

主从复制原理

主从复制模式

• mysql支持一主多从
• 同步: 确保event发送到所有的slave
• 半同步:  只要本地event写入本地的二进制日志文件中即可，但是不确保event一定发送到slave，它是不管的
• 异步: 确保event发送到其中一个slave
• 注：mysql 5.5 之前是不支持半同步的(google贡献的mysql半同步补丁)

多级复制

• 一个从可以是某个主的从，也可以是某个从的从。

中继日志(relay log)不能发送给其它节点

• 复制的作用：

    实现备份

    高可用

    异地容灾

    分摊负载(scale out)

Mysql一主多从、读写分离架构

• 分析：这种架构随着mysql从服务器的增加会消耗mysql主服务器的性能(cpu、IO、内存)，因为从服务器直接接受二进制日志中的event。有几个从服务器接受主服务器发送的event，相应地mysql主服务器就要启几个线程，这些mysql线程各自独立地从二进制日志中读取数据，读完后发送给对应的mysql从服务器，所以下面引入新架构。

Mysql一主多从、读写分离、多级复制架构

• 分析："主的从，从的主" mysql server实际上它只是起把二进制日志的event从主服务器发送到从服务器的中间件而已，实在没有必要把数据持久化存储下来，浪费IO。但是从中继日志中读取的event不在本地执行写入数据文件是不会记录到二进制日志文件中的，没有二进制日志就不能发送event给从服务器了。
• 解决思路:

mysql有种存储引擎叫black hole，功能类似于linux的/dev/null，"主的从，从的主" mysql 数据库使用black hole存储引擎，这样从中继日志中读取的event在本地执行完后，数据并没有保存下来。

• mysql实际上支持双主模型
• 双主模型可以减轻读操作，但是无法减轻写操作,所有在第一个节点的写操作，第二个节点也同样要执行一遍，不然就出现数据不一致了。

• 一般来说，在生产环境中绝对不建议使用双主模型！

Mysql集群规模扩大

• 当一个服务器承受的压力过大的时候，两种方式提升它的性能：
• scale on：纵向扩展，增加单台服务器的性能
• scale out: 横向扩展，增加服务器分摊负载
• 当规模越来越大的时候，我们的主服务器怎么都无法承担写操作的时候，怎么办？
• 数据库服务器之所以压力大，那是因为库里面有很多张表，每个表可能都需要读写操作；双主也无法减轻写操作，需要垂直拆分(分库)，就是将一个大的数据库拆分成n个小的数据库，把那些查询(联合查询)或操作的时候相关联的表放在一起，每一个小的数据库放在一个物理服务器上；
• 当需要对某张表操作的时候，我们只需要联系这张表所在的数据库，但是垂直拆分治标不治本，数据库的数据也是有热区的，比如说我们有50G的数据，其中有2G的数据最繁忙，而这2G的数据都在同一张表上，这时候就需要水平拆分(分表)了，分表的目的就是把热区数据分开

• 注：拆分是根据业务来拆的，不了解业务是不行的。
• 一般来说，能不拆尽量不拆，一旦出现问题，trouble shooting起来将变得很困难。