基于四台服务器实现mysql读写分离，一台做代理，另外三台配置MySQL一主多从。实现读的负载均衡

Mycat:10.75.65.103

Master:10.75.65.104

Slave:10.75.65.105,10.75.65.106

安装mycat:

下载mycat并解压缩到/usr/local目录：

[root@localhost ~]#wget http://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz

[root@localhost ~]#tar zxvf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz -C /usr/local/

安装java-jdk：
[root@localhost ~]#yum install java-1.8.0-openjdk -y

配置mycat环境变量：
[root@localhost ~]#echo "export PATH=/usr/local/mycat/bin:$PATH" >/etc/profile.d/mycat.sh

[root@localhost ~]# chmod +x /etc/profile.d/mycat.sh

[root@localhost ~]#/etc/profile.d/mycat.sh

 

在主库执行授权信息，从库会自动同步：
MariaDB [(none)]>grant all on *.* to "mycat-proxy"@"10.75.65.103" identified by "123456";

 

配置mycat

配置server.xml ：

[root@localhost ~]# vim /usr/local/mycat/conf/server.xml

#默认管理用户，可读可写：

<user name="mycat" defaultAccount="true">

                <property name="password">123456</property>

                <property name="schemas">newdb</property>

</user>

# 只读用户

<user name="user">

                <property name="password">user</property>

                <property name="schemas">newdb</property>

                <property name="readOnly">true</property>

        </user>

配置schema.xml

[root@localhost ~]# vim /usr/local/mycat/conf/schema.xml

# 设置逻辑库以及数据库节点

<schema name="newdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

</schema>

 

# 配置数据库节点对应的后端真实的数据库：

<dataNode name="dn1" dataHost="localhost1" database="mycat" />

 

# 配置读写库以及均衡：

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

<heartbeat>select user()</heartbeat>

  <!-- can have multi write hosts -->

<writeHost host="hostM1" url="10.75.65.104:3306" user="mycat-proxy" password="123456">

  <!-- can have multi read hosts -->

       <readHost host="hostS1" url="10.75.65.105:3306" user="mycat-proxy" password="123456" />

       <readHost host="hostS2" url="10.75.65.106:3306" user="mycat-proxy" password="123456" />

</writeHost>

      <!-- <writeHost host="hostM2" url="localhost:3306" user="root" password="123456"/> -->

</dataHost>

 

启动mycat:

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

 

连接测试：

#在105和106两台从机数据库mycat，表mycat104中分别加了ID是105和106的信息。在主库查不到数据，通过代理可以查到，即可验证读写分离成功。

代理：

[root@localhost ~]#mysql -umycat -p123456 -P8066 -h127.0.0.1

mysql> show databases;

 

 

+----------+

| DATABASE |

+----------+

| newdb    |

+----------+

1 row in set (0.00 sec)

mysql> use newdb;

 

mysql> select * from mycat104;

+------+-----------+

| id   | name      |

+------+-----------+

|    3 | xiaoqiang |

|  106 | 106       |

+------+-----------+

2 rows in set (0.00 sec)

 

mysql> select * from mycat104;

+------+-----------+

| id   | name      |

+------+-----------+

|    3 | xiaoqiang |

|  106 | 106       |

+------+-----------+

2 rows in set (0.00 sec)

 

mysql> select * from mycat104;

+------+-----------+

| id   | name      |

+------+-----------+

|    3 | xiaoqiang |

|  105 | 105       |

+------+-----------+

2 rows in set (0.00 sec)

 

主机数据库：

MariaDB [(none)]> use mycat;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

MariaDB [mycat]> show tables;

+-----------------+

| Tables_in_mycat |

+-----------------+

| mycat104        |

+-----------------+

1 row in set (0.00 sec)

 

MariaDB [mycat]> select * from mycat104;

+------+-----------+

| id   | name      |

+------+-----------+

|    3 | xiaoqiang |

+------+-----------+

1 row in set (0.00 sec)

 

在mycat中插入一条信息：

mysql> insert mycat104 value(103,"mycat");

Query OK, 1 row affected (0.04 sec)

在主机中查询：

MariaDB [mycat]> select * from mycat104;

+------+-----------+

| id   | name      |

+------+-----------+

|    3 | xiaoqiang |

|  103 | mycat     |

+------+-----------+

2 rows in set (0.00 sec)

 

以上说明读写分离已配置成功。