Linux Mycat安裝及MySQL讀寫分離的實作

以下是基本架構圖，具體如下：

一、配置MySQL主從、主主，詳情請檢視（

MySQL主主同步：https://blog.csdn.net/baidu_38432732/article/details/80773634

MySQL主從同步：https://blog.csdn.net/baidu_38432732/article/details/80653873 ）

二、安裝mycat

1、配置java環境

[[email protected] ~]# tar -xf jdk-8u77-linux-x64.gz

[[email protected] ~]# mkdir /usr/local/java

[[email protected] ~]# mv jdk1.8.0_77/ /usr/local/java/

[[email protected] ~]#vim /etc/profile

export JAVA_HOME=/usr/local/java/jdk1.8.0_77

export JRE_HOME=$JAVA_HOME/jre

export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib/rt.jar

export PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin

[[email protected] ~]#source /etc/profile

[[email protected] ~]# java -version

java version "1.8.0_77"

Java(TM) SE Runtime Environment (build 1.8.0_77-b03)

Java HotSpot(TM) 64-Bit Server VM (build 25.77-b03, mixed mode)

說明此時Java環境已經部署成功

2、安裝mycat

下載下傳mycat包并安裝（http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz）

[[email protected] ~]# wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

[[email protected] ~]# tar -xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

[[email protected] ~]# mv mycat/ /usr/local/

[[email protected] mycat]# cd /usr/local/mycat/

[[email protected] mycat]# vim /etc/profile

export MYCAT_HOME=/usr/local/mycat

export PATH=$PATH:$MYCAT_HOME/bin

[[email protected] mycat]# source /etc/profile

此時安裝完畢

3、配置mycat

[[email protected] mycat]# cd conf/

修改schema.xml檔案

[[email protected] mycat]# vim schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="mysql_first"> 一下備注部分為分表，因目前沒需要是以沒有對分表進行配置 <!--<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /> <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />

</childTable>

</table> -->

</schema>

<!-- <dataNode name="liuys-dn2" dataHost="dbhost2" database="liuys" />

<dataHost name="dthost1" maxCon="1000" minCon="10" balance="1"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>show slave status</heartbeat>

</writeHost>

</dataHost>

</mycat:schema>

修改 server.xml [[email protected] conf]# vim server.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mycat:server SYSTEM "server.dtd">

<mycat:server xmlns:mycat="http://io.mycat/">

<!-- <property name="useGlobleTableCheck">0</property> <property name="sequnceHandlerType">2</property>

--> </system>

<property name="schemas">TESTDB</property> </user> <user name="liuys">

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

</user> </mycat:server>

show slave status 表示一種叢集政策，隻适用在一主一從的環境中，當主 down 掉，從可以充當主和從

balance屬性

balance=”0”, 不開啟讀寫分離機制，所有讀操作都發送到目前可用的 writeHost 上

balance=”1”，全部的 readHost 與 stand by writeHost 參與 select 語句的負載均衡

balance=”2”，所有讀操作都随機的在 writeHost、 readhost 上分發。

balance=”3”，所有讀請求随機的分發到 wiriterHost 對應的 readhost 執行,writerHost 不負擔讀壓力

writeType 屬性

負載均衡類型，目前的取值有 3 種：

writeType=”0”, 所有寫操作發送到配置的第一個 writeHost，第一個挂了切到還生存的第二個writeHost，重新啟動後已切換後的為準，切換記錄在配置檔案中:dnindex.properties

writeType=”1”，所有寫操作都随機的發送到配置的 writeHost

writeType="2"，不執行寫操作

switchType屬性

-1 表示不自動切換

1 預設值，自動切換

2 基于MySQL主從同步的狀态決定是否切換

3 基于MySQLgalarycluster的切換機制（适合叢集）（1.4.1）

心跳語句為show status like‘wsrep%’

強調：

僅僅主從讀寫分離的配置：

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

</schema>

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0"

dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>show slave status</heartbeat>

<writeHost host="hostM1" url="192.168.1.90:3306" user="root"

password="123456">

</writeHost>

</dataHost>

</mycat:schema>

主挂掉之後自動切換到從的配置：

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

</schema>

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0"

dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>show slave status</heartbeat>

<writeHost host="hostM1" url="192.168.1.90:3306" user="root"

password="123456">

</writeHost>

<writeHost host="hostM2" url="192.168.1.89:3306" user="root"

password="123456"/>

</dataHost>

</mycat:schema>

4、啟動mycat，并檢視是否啟動 [[email protected] conf]# /usr/local/mycat/bin/mycat start

[[email protected] conf]# netstat -anptu |grep java

tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN 3063/java

tcp 0 0 :::42155 :::* LISTEN 3063/java

tcp 0 0 :::34457 :::* LISTEN 3063/java

tcp 0 0 :::1984 :::* LISTEN 3063/java

tcp 0 0 :::8066 :::* LISTEN 3063/java

tcp 0 0 :::9066 :::* LISTEN 3063/java

tcp 0 0 192.168.1.76:36158 192.168.1.127:3306 ESTABLISHED 3063/java

tcp 0 0 192.168.1.76:35258 192.168.1.128:3306 ESTABLISHED 3063/java

設定驗證環境并驗證

（1）、登陸mycat，建表并插入資料

[[email protected] ~]# mysql -uroot -h192.168.1.76 -P8066 -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use TESTDB;

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 mysql> create table company(id int not null primary key,name varchar(50),addr varchar(255)); Query OK, 0 rows affected (0.01 sec)

mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(2000002, 'alex', '2018-06-08',500.0,3);

Query OK, 1 row affected (0.42 sec)

mysql> select * from travelrecord;

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

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

| 2000002 | alex | 2018-06-08 | 500 | 3 |

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

1 rows in set (0.01 sec)

（2）、登陸主資料庫伺服器并驗證剛插入的資料

[[email protected] ~]# mysql -uroot -p123456

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 42

affiliates. Other names may be trademarks of their respective

owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 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

mysql> select * from travelrecord;

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

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

| 2000002 | alex | 2018-06-08 | 500 | 3 |

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

1 rows in set (0.00 sec) mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec) 同理登陸從伺服器并在從伺服器上插入資料以便驗證讀寫分離

[[email protected] ~]# mysql -uroot -p123456

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 36

affiliates. Other names may be trademarks of their respective

owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use liuys;

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

mysql> select * from travelrecord;

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

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

| 2000002 | alex | 2018-06-08 | 500 | 3 |

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

1 rows in set (0.00 sec) mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(2000001, 'alice', '2017-08-08',500.0,3);

Query OK, 1 row affected (0.42 sec)

mysql> select * from travelrecord;

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

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

| 2000001 | alice | 2017-08-08 | 500 | 3 |

| 2000002 | alex | 2018-06-08 | 500 | 3 |

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

回到主伺服器檢視時，肯定無法查到該條資料

此時為主伺服器

mysql> select * from travelrecord;

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

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

| 2000002 | alex | 2018-06-08 | 500 | 3 |

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

1 rows in set (0.00 sec)

當我們回到mycat伺服器時，又可以看到資料

[[email protected] ~]# mysql -uroot -h192.168.1.76 -P8066 -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

affiliates. Other names may be trademarks of their respective

owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use TESTDB;

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

mysql> select * from travelrecord;

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

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

| 2000001 | alice | 2017-08-08 | 500 | 3 |

| 2000002 | alex | 2018-06-08 | 500 | 3 |

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

3 rows in set (0.01 sec)

（4）、當停止從伺服器時

[[email protected] ~]# /etc/init.d/mysqld stop

Shutting down MySQL.... SUCCESS!

我們回到mycat伺服器時此時資料就缺失了從伺服器上剛插入的那條資料

[[email protected] ~]# mysql -uroot -h192.168.1.76 -P8066 -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

affiliates. Other names may be trademarks of their respective

owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use TESTDB;

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

mysql> select * from travelrecord;

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

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

| 2000002 | alex | 2018-06-08 | 500 | 3 |

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

1 rows in set (0.01 sec)

此時說明資料隻能在從伺服器上讀取

（4）、相反當我們停止主伺服器确報錯無法連接配接資料庫我們人能讀到資料

至此mycat讀學分離已部署完畢

Linux Mycat安裝及MySQL讀寫分離的實作

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案