MySQL分布式集群-7.Mycat读写分离与高可用

1.读写分离环境配置

• master、slave上配置用户权限

  create user 'test'@'%' identified by 'test';
  grant all privileges on *.* to 'test';
  

• 保证server-uuid不重复

  cat /var/lib/mysql/auto.cnf
  

• vim /etc/my.cnf 保证server-id不重复 配置主从数据库
• vim schema.xml

  <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.1.12:3306" user="test" password="test">
            <!-- can have multi read hosts -->
            <readHost host="hostS2" url="192.168.1.10:3306" user="test" password="test" />
        </writeHost>
        <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
  </dataHost>
  

2.测试读写分离

• vim log4j2.xml

mysql> create table mytable (id int primary key,name varchar(10));
Query OK, 0 rows affected (0.24 sec)

mysql> explain create table mytable (id int primary  key,name varchar(10));
+-----------+------------------------------------------------------------+
| DATA_NODE | SQL                                                        |
+-----------+------------------------------------------------------------+
| dn1       | create table mytable (id int primary key,name varchar(10)) |
| dn2       | create table mytable (id int primary key,name varchar(10)) |
| dn3       | create table mytable (id int primary key,name varchar(10)) |
+-----------+------------------------------------------------------------+
3 rows in set (0.01 sec)

mysql> explain insert into mytable (id ,name) values(1,'hello');
+-----------+--------------------------------------------------+
| DATA_NODE | SQL                                              |
+-----------+--------------------------------------------------+
| dn1       | insert into mytable (id ,name) values(1,'hello') |
| dn2       | insert into mytable (id ,name) values(1,'hello') |
| dn3       | insert into mytable (id ,name) values(1,'hello') |
+-----------+--------------------------------------------------+
3 rows in set (0.00 sec)

3.查看mycat日志验证读写分离成功

• cd /usr/local/mycat/logs/
• cat mycat.log | grep mytable

  2019-08-15 16:36:57.702 DEBUG [$_NIOREACTOR-0-RW] (
  io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute(
  MySQLConnection.java:463)) - con need syn ,total syn cmd 1 commands SET names 
  utf8;schema change:false con:MySQLConnection [id=6, lastTime=1565858217702, 
  user=test, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, 
  threadId=115, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert 
  into mytable (id ,name) values(3,'hello')},      
   respHandler=io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@43e47986,      
  host=192.168.1.12, port=3306, statusSync=null, writeQueue=0, 
  modifiedSQLExecuted=true]
  
  2019-08-15 16:56:32.327 DEBUG [$_NIOREACTOR-0-RW] (
  io.mycat.server.NonBlockingSession.execute(NonBlockingSession.java:126)) - 
  ServerConnection [id=1, schema=TESTDB, host=192.168.1.15, user=root,txIsolation=3, 
  autocommit=true, schema=TESTDB, executeSql=select * from mytable]select * from 
  mytable, route={
  FROM mytable
  FROM mytable
  FROM mytable
  FROM mytable
  FROM mytable
  

4.mysql高可用

• vim schema.xml

  <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.1.12:3306" user="test" password="test">
            <!-- can have multi read hosts -->
            <readHost host="hostS2" url="192.168.1.10:3306" user="test" password="test" />
        </writeHost>
            <!-- 主库宕机，从库顶 -->
        <writeHost host="hostM2" url="192.168.1.10:3306" user="test" password="test"/>
  </dataHost>