MySQL如何實(shí)現(xiàn)萬(wàn)億級(jí)數(shù)據(jù)存儲(chǔ)？
前言
業(yè)界對(duì)系統(tǒng)的高可用有著基本的要求，簡(jiǎn)單的說(shuō)，這些要求可以總結(jié)為如下所示。
系統(tǒng)架構(gòu)中不存在單點(diǎn)問(wèn)題。
可以最大限度的保障服務(wù)的可用性。
一般情況下系統(tǒng)的高可用可以用幾個(gè)9來(lái)評(píng)估。所謂的幾個(gè)9就是系統(tǒng)可以保證對(duì)外提供的服務(wù)的時(shí)間達(dá)到總時(shí)間的百分比。例如如果需要達(dá)到99.99的高可用，則系統(tǒng)全年發(fā)生故障的總時(shí)間不能超過(guò)52分鐘。
系統(tǒng)高可用架構(gòu)
我們既然需要實(shí)現(xiàn)系統(tǒng)的高可用架構(gòu)，那么，我們到底需要搭建一個(gè)什么樣的系統(tǒng)架構(gòu)呢？我們可以將需要搭建的系統(tǒng)架構(gòu)簡(jiǎn)化成下圖所示。
服務(wù)器規(guī)劃
由于我電腦資源有限，我這里在4臺(tái)服務(wù)器上搭建高可用環(huán)境，大家可以按照本文將環(huán)境擴(kuò)展到更多的服務(wù)器，搭建步驟都是一樣的。
| 主機(jī)名 | IP地址 | 安裝的服務(wù) |
|---|---|---|
| binghe151 | 192.168.175.151 | Mycat、Zookeeper、 MySQL、HAProxy、 Keepalived、Xinetd |
| binghe152 | 192.168.175.152 | Zookeeper、MySQL |
| binghe153 | 192.168.175.153 | Zookeeper、MySQL |
| binghe154 | 192.168.175.154 | Mycat、MySQL、 HAProxy、Keepalived、 Xinetd |
| binghe155 | 192.168.175.155 | MySQL |
注意：HAProxy和Keepalived最好和Mycat部署在同一臺(tái)服務(wù)器上。
安裝JDK
由于Mycat和Zookeeper的運(yùn)行需要JDK環(huán)境的支持，所有我們需要在每臺(tái)服務(wù)器上安裝JDK環(huán)境。
這里，我以在binghe151服務(wù)器上安裝JDK為例，其他服務(wù)器的安裝方式與在binghe151服務(wù)器上的安裝方式相同。安裝步驟如下所示。
（1）到JDK官網(wǎng)下載JDK 1.8版本，JDK1.8的下載地址為：https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html。
注：我下載的JDK安裝包版本為：jdk-8u212-linux-x64.tar.gz，如果JDK版本已更新，大家下載對(duì)應(yīng)的版本即可。
（2）將下載的jdk-8u212-linux-x64.tar.gz安裝包上傳到binghe151服務(wù)器的/usr/local/src目錄下。
（3）解壓jdk-8u212-linux-x64.tar.gz文件，如下所示。
tar?-zxvf?jdk-8u212-linux-x64.tar.gz
（4）將解壓的jdk1.8.0_212目錄移動(dòng)到binghe151服務(wù)器下的/usr/local目錄下，如下所示。
mv?jdk1.8.0_212/?/usr/local/src/
（5）配置JDK系統(tǒng)環(huán)境變量，如下所示。
vim?/etc/profile
JAVA_HOME=/usr/local/jdk1.8.0_212
CLASS_PATH=.:$JAVA_HOME/lib
PATH=$JAVA_HOME/bin:$PATH
export?JAVA_HOME?CLASS_PATH?PATH
使系統(tǒng)環(huán)境變量生效，如下所示。
source?/etc/profile
（6）查看JDK版本，如下所示。
[root@binghe151?~]#?java?-version
java?version?"1.8.0_212"
Java(TM)?SE?Runtime?Environment?(build?1.8.0_212-b10)
Java?HotSpot(TM)?64-Bit?Server?VM?(build?25.212-b10,?mixed?mode)
結(jié)果顯示，正確輸出了JDK的版本信息，說(shuō)明JDK安裝成功。
安裝Mycat
下載Mycat 1.6.7.4 Release版本，解壓到服務(wù)器的/usr/local/mycat目錄下，并配置Mycat的系統(tǒng)環(huán)境變量，隨后，配置Mycat的配置文件，Mycat的最終結(jié)果配置如下所示。
schema.xml
mycat:schema?SYSTEM?"schema.dtd">
<mycat:schema?xmlns:mycat="http://io.mycat/">
?<schema?name="shop"?checkSQLschema="false"?sqlMaxLimit="1000">
??
??<table?name="order_master"?primaryKey="order_id"?dataNode?=?"orderdb01,orderdb02,orderdb03,orderdb04"?rule="order_master"?autoIncrement="true">
???<childTable?name="order_detail"?primaryKey="order_detail_id"?joinKey="order_id"?parentKey="order_id"?autoIncrement="true"/>
??table>
??<table?name="order_cart"?primaryKey="cart_id"?dataNode?=?"ordb"/>
??<table?name="order_customer_addr"?primaryKey="customer_addr_id"?dataNode?=?"ordb"/>
??<table?name="region_info"?primaryKey="region_id"?dataNode?=?"ordb,prodb,custdb"?type="global"/>
??<table?name="serial"?primaryKey="id"?dataNode?=?"ordb"/>
??<table?name="shipping_info"?primaryKey="ship_id"?dataNode?=?"ordb"/>
??<table?name="warehouse_info"?primaryKey="w_id"?dataNode?=?"ordb"/>
??<table?name="warehouse_proudct"?primaryKey="wp_id"?dataNode?=?"ordb"/>
??
??<table?name="product_brand_info"?primaryKey="brand_id"?dataNode?=?"prodb"/>
??<table?name="product_category"?primaryKey="category_id"?dataNode?=?"prodb"/>
??<table?name="product_comment"?primaryKey="comment_id"?dataNode?=?"prodb"/>
??<table?name="product_info"?primaryKey="product_id"?dataNode?=?"prodb"/>
??<table?name="product_pic_info"?primaryKey="product_pic_id"?dataNode?=?"prodb"/>
??<table?name="product_supplier_info"?primaryKey="supplier_id"?dataNode?=?"prodb"/>
??
??<table?name="customer_balance_log"?primaryKey="balance_id"?dataNode?=?"custdb"/>
??<table?name="customer_inf"?primaryKey="customer_inf_id"?dataNode?=?"custdb"/>
??<table?name="customer_level_inf"?primaryKey="customer_level"?dataNode?=?"custdb"/>
??<table?name="customer_login"?primaryKey="customer_id"?dataNode?=?"custdb"/>
??<table?name="customer_login_log"?primaryKey="login_id"?dataNode?=?"custdb"/>
??<table?name="customer_point_log"?primaryKey="point_id"?dataNode?=?"custdb"/>
??
?schema>
?
?<dataNode?name="mycat"?dataHost="binghe151"?database="mycat"?/>
??
?<dataNode?name="ordb"?dataHost="binghe152"?database="order_db"?/>
?<dataNode?name="prodb"?dataHost="binghe153"?database="product_db"?/>
?<dataNode?name="custdb"?dataHost="binghe154"?database="customer_db"?/>
?
?<dataNode?name="orderdb01"?dataHost="binghe152"?database="orderdb01"?/>
?<dataNode?name="orderdb02"?dataHost="binghe152"?database="orderdb02"?/>
?<dataNode?name="orderdb03"?dataHost="binghe153"?database="orderdb03"?/>
?<dataNode?name="orderdb04"?dataHost="binghe153"?database="orderdb04"?/>
?
?<dataHost?name="binghe151"?maxCon="1000"?minCon="10"?balance="1"
?????writeType="0"?dbType="mysql"?dbDriver="native"?switchType="1"??slaveThreshold="100">
??<heartbeat>select?user()heartbeat>
??<writeHost?host="binghe51"?url="192.168.175.151:3306"?user="mycat"?password="mycat"/>
?dataHost>
?
?<dataHost?name="binghe152"?maxCon="1000"?minCon="10"?balance="1"
?????writeType="0"?dbType="mysql"?dbDriver="native"?switchType="1"??slaveThreshold="100">
??<heartbeat>select?user()heartbeat>
??<writeHost?host="binghe52"?url="192.168.175.152:3306"?user="mycat"?password="mycat"/>
?dataHost>
?
?<dataHost?name="binghe153"?maxCon="1000"?minCon="10"?balance="1"
?????writeType="0"?dbType="mysql"?dbDriver="native"?switchType="1"??slaveThreshold="100">
??<heartbeat>select?user()heartbeat>
??<writeHost?host="binghe53"?url="192.168.175.153:3306"?user="mycat"?password="mycat"/>
?dataHost>
?
?<dataHost?name="binghe154"?maxCon="1000"?minCon="10"?balance="1"
?????writeType="0"?dbType="mysql"?dbDriver="native"?switchType="1"??slaveThreshold="100">
??<heartbeat>select?user()heartbeat>
??<writeHost?host="binghe54"?url="192.168.175.154:3306"?user="mycat"?password="mycat"/>
?dataHost>
?
mycat:schema>
server.xml
mycat:server?SYSTEM?"server.dtd">
<mycat:server?xmlns:mycat="http://io.mycat/">
?<system>
??<property?name="useHandshakeV10">1property>
????????<property?name="defaultSqlParser">druidparserproperty>
??<property?name="serverPort">3307property>
??<property?name="managerPort">3308property>
??<property?name="nonePasswordLogin">0property>
??<property?name="bindIp">0.0.0.0property>
??<property?name="charset">utf8mb4property>
??<property?name="frontWriteQueueSize">2048property>
??<property?name="txIsolation">2property>
??<property?name="processors">2property>
??<property?name="idleTimeout">1800000property>
??<property?name="sqlExecuteTimeout">300property>
??<property?name="useSqlStat">0property>
??<property?name="useGlobleTableCheck">0property>
??<property?name="sequenceHandlerType">1property>
??<property?name="defaultMaxLimit">1000property>
??<property?name="maxPacketSize">104857600property>
??
??<property?name="sqlInterceptor">
???io.mycat.server.interceptor.impl.StatisticsSqlInterceptor
??property>
??<property?name="sqlInterceptorType">
???UPDATE,DELETE,INSERT
??property>
??<property?name="sqlInterceptorFile">/tmp/sql.txtproperty>
?system>
?
?<firewall>
??<whitehost>
???<host?user="mycat"?host="192.168.175.151">host>
??whitehost>
??<blacklist?check="true">
???<property?name="noneBaseStatementAllow">trueproperty>
???<property?name="deleteWhereNoneCheck">trueproperty>
??blacklist>
?firewall>
?
?<user?name="mycat"?defaultAccount="true">
??<property?name="usingDecrypt">1property>
??<property?name="password">cTwf23RrpBCEmalp/nx0BAKenNhvNs2NSr9nYiMzHADeEDEfwVWlI6hBDccJjNBJqJxnunHFp5ae63PPnMfGYA==property>
??<property?name="schemas">shopproperty>
?user>
mycat:server>
rule.xml
mycat:rule?SYSTEM?"rule.dtd">
<mycat:rule?xmlns:mycat="http://io.mycat/">
?<tableRule?name="order_master">
??<rule>
???<columns>customer_idcolumns>
???<algorithm>mod-longalgorithm>
??rule>
?tableRule>
?
?<function?name="mod-long"?class="io.mycat.route.function.PartitionByMod">
??<property?name="count">4property>
?function>
mycat:rule>
sequence_db_conf.properties
#sequence stored in datanode
GLOBAL=mycat
ORDER_MASTER=mycat
ORDER_DETAIL=mycat
關(guān)于Mycat的配置，僅供大家參考，大家不一定非要按照我這里配置，根據(jù)自身業(yè)務(wù)需要配置即可。本文的重點(diǎn)是實(shí)現(xiàn)Mycat的高可用環(huán)境搭建。
在MySQL中創(chuàng)建Mycat連接MySQL的賬戶，如下所示。
CREATE?USER?'mycat'@'192.168.175.%'?IDENTIFIED?BY?'mycat';
ALTER?USER?'mycat'@'192.168.175.%'?IDENTIFIED?WITH?mysql_native_password?BY?'mycat';?
GRANT?SELECT,?INSERT,?UPDATE,?DELETE,EXECUTE??ON?*.*?TO?'mycat'@'192.168.175.%';
FLUSH?PRIVILEGES;
安裝Zookeeper集群
安裝配置完JDK后，就需要搭建Zookeeper集群了，根據(jù)對(duì)服務(wù)器的規(guī)劃，現(xiàn)將Zookeeper集群搭建在“binghe151”、“binghe152”、“binghe153”三臺(tái)服務(wù)器上。
1.下載Zookeeper
到Apache官網(wǎng)去下載Zookeeper的安裝包，Zookeeper的安裝包下載地址為：https://mirrors.tuna.tsinghua.edu.cn/apache/zookeeper/。具體如下圖所示。
也可以在binghe151服務(wù)器上執(zhí)行如下命令直接下載zookeeper-3.5.5。
wget?https://mirrors.tuna.tsinghua.edu.cn/apache/zookeeper/zookeeper-3.5.5/apache-zookeeper-3.5.5-bin.tar.gz
執(zhí)行上述命令就可以直接把a(bǔ)pache-zookeeper-3.5.5-bin.tar.gz安裝包下載到binghe151服務(wù)器上。
2.安裝并配置Zookeeper
注意：（1）、（2）、（3）步都是在binghe152服務(wù)器上執(zhí)行的。
（1）解壓Zookeeper安裝包
在binghe151服務(wù)器上執(zhí)行如下命令，將Zookeeper解壓到“/usr/local/”目錄下，并將Zookeeper目錄修改為zookeeper-3.5.5。
tar?-zxvf?apache-zookeeper-3.5.5-bin.tar.gz
mv?apache-zookeeper-3.5.5-bin?zookeeper-3.5.5
（2）配置Zookeeper系統(tǒng)環(huán)境變量
同樣，需要在/etc/profile文件中配置Zookeeper系統(tǒng)環(huán)境變量，如下：
ZOOKEEPER_HOME=/usr/local/zookeeper-3.5.5
PATH=$ZOOKEEPER_HOME/bin:$PATH
export?ZOOKEEPER_HOME?PATH
結(jié)合之前配置的JDK系統(tǒng)環(huán)境變量，/etc/profile，總體配置如下：
MYSQL_HOME=/usr/local/mysql
JAVA_HOME=/usr/local/jdk1.8.0_212
MYCAT_HOME=/usr/local/mycat
ZOOKEEPER_HOME=/usr/local/zookeeper-3.5.5
MPC_HOME=/usr/local/mpc-1.1.0
GMP_HOME=/usr/local/gmp-6.1.2
MPFR_HOME=/usr/local/mpfr-4.0.2
CLASS_PATH=.:$JAVA_HOME/lib
LD_LIBRARY_PATH=$MPC_LIB_HOME/lib:$GMP_HOME/lib:$MPFR_HOME/lib:$LD_LIBRARY_PATH
PATH=$MYSQL_HOME/bin:$JAVA_HOME/bin:$ZOOKEEPER_HOME/bin:$MYCAT_HOME/bin:$PATH
export?JAVA_HOME?ZOOKEEPER_HOME?MYCAT_HOME?CLASS_PATH?MYSQL_HOME?MPC_LIB_HOME?GMP_HOME?MPFR_HOME?LD_LIBRARY_PATH?PATH
（3）配置Zookeeper
首先，需要將ZOOKEEPER_HOME為Zookeeper的安裝目錄）目錄下的zoo_sample.cfg文件修改為zoo.cfg文件。具體命令如下：
cd?/usr/local/zookeeper-3.5.5/conf/
mv?zoo_sample.cfg?zoo.cfg
接下來(lái)修改zoo.cfg文件，修改后的具體內(nèi)容如下：
tickTime=2000
initLimit=10
syncLimit=5
dataDir=/usr/local/zookeeper-3.5.5/data
dataLogDir=/usr/local/zookeeper-3.5.5/dataLog
clientPort=2181
server.1=binghe151:2888:3888
server.2=binghe152:2888:3888
server.3=binghe153:2888:3888
在Zookeeper的安裝目錄下創(chuàng)建data和dataLog兩個(gè)文件夾。
mkdir?-p?/usr/local/zookeeper-3.5.5/data
mkdir?-p?/usr/local/zookeeper-3.5.5/dataLog
切換到新建的data目錄下，創(chuàng)建myid文件，具體內(nèi)容為數(shù)字1，如下所示：
cd?/usr/local/zookeeper-3.5.5/data
vim?myid
將數(shù)字1寫(xiě)入到文件myid。
3.將Zookeeper和系統(tǒng)環(huán)境變量文件復(fù)制到其他服務(wù)器
注意：（1）、（2）步是在binghe151服務(wù)器上執(zhí)行的。
（1）復(fù)制Zookeeper到其他服務(wù)器
根據(jù)對(duì)服務(wù)器的規(guī)劃，現(xiàn)將Zookeeper復(fù)制到binghe152和binghe53服務(wù)器，具體執(zhí)行操作如下所示：
scp?-r?/usr/local/zookeeper-3.5.5/?binghe152:/usr/local/
scp?-r?/usr/local/zookeeper-3.5.5/?binghe153:/usr/local/
（2）復(fù)制系統(tǒng)環(huán)境變量文件到其他服務(wù)器
根據(jù)對(duì)服務(wù)器的規(guī)劃，現(xiàn)將系統(tǒng)環(huán)境變量文件/etc/profile復(fù)制到binghe152、binghe153服務(wù)器，具體執(zhí)行操作如下所示：
scp?/etc/profile?binghe152:/etc/
scp?/etc/profile?binghe153:/etc/
上述操作可能會(huì)要求輸入密碼，根據(jù)提示輸入密碼即可。
4.修改其他服務(wù)器上的myid文件
修改binghe152服務(wù)器上Zookeeper的myid文件內(nèi)容為數(shù)字2，同時(shí)修改binghe153服務(wù)器上Zookeeper的myid文件內(nèi)容為數(shù)字3。具體如下：
在binghe152服務(wù)器上執(zhí)行如下操作：
echo?"2"?>?/usr/local/zookeeper-3.5.5/data/myid
cat?/usr/local/zookeeper-3.5.5/data/myid
2
在binghe153服務(wù)器上執(zhí)行如下操作：
echo?"3"?>?/usr/local/zookeeper-3.5.5/data/myid
cat?/usr/local/zookeeper-3.5.5/data/myid
3
5.使環(huán)境變量生效
分別在binghe151、binghe152、binghe153上執(zhí)行如下操作，使系統(tǒng)環(huán)境變量生效。
source?/etc/profile
6.啟動(dòng)Zookeeper集群
分別在binghe151、binghe152、binghe153上執(zhí)行如下操作，啟動(dòng)Zookeeper集群。
zkServer.sh?start
7.查看Zookeeper集群的啟動(dòng)狀態(tài)
binghe151服務(wù)器
[root@binghe151?~]#?zkServer.sh?status
ZooKeeper?JMX?enabled?by?default
Using?config:?/usr/local/zookeeper-3.5.5/bin/../conf/zoo.cfg
Client?port?found:?2181.?Client?address:?localhost.
Mode:?follower
binghe152服務(wù)器
[root@binghe152?local]#?zkServer.sh?status
ZooKeeper?JMX?enabled?by?default
Using?config:?/usr/local/zookeeper-3.5.5/bin/../conf/zoo.cfg
Client?port?found:?2181.?Client?address:?localhost.
Mode:?leader
binghe153服務(wù)器
[root@binghe153?~]#?zkServer.sh?status
ZooKeeper?JMX?enabled?by?default
Using?config:?/usr/local/zookeeper-3.5.5/bin/../conf/zoo.cfg
Client?port?found:?2181.?Client?address:?localhost.
Mode:?follower
可以看到，binghe151和binghe153服務(wù)器上的Zookeeper角色為follower，binghe152服務(wù)器上的Zookeeper角色為leader。
初始化Mycat配置到Zookeeper集群
注意：初始化Zookeeper中的數(shù)據(jù)，是在binghe151服務(wù)器上進(jìn)行的，原因是之前我們已經(jīng)在binghe151服務(wù)器上安裝了Mycat。
1.查看初始化腳本
在Mycat安裝目錄下的bin目錄中提供了一個(gè)init_zk_data.sh腳本文件，如下所示。
[root@binghe151?~]#?ll?/usr/local/mycat/bin/
total?384
-rwxr-xr-x?1?root?root???3658?Feb?26?17:10?dataMigrate.sh
-rwxr-xr-x?1?root?root???1272?Feb?26?17:10?init_zk_data.sh
-rwxr-xr-x?1?root?root??15701?Feb?28?20:51?mycat
-rwxr-xr-x?1?root?root???2986?Feb?26?17:10?rehash.sh
-rwxr-xr-x?1?root?root???2526?Feb?26?17:10?startup_nowrap.sh
-rwxr-xr-x?1?root?root?140198?Feb?28?20:51?wrapper-linux-ppc-64
-rwxr-xr-x?1?root?root??99401?Feb?28?20:51?wrapper-linux-x86-32
-rwxr-xr-x?1?root?root?111027?Feb?28?20:51?wrapper-linux-x86-64
init_zk_data.sh腳本文件就是用來(lái)向Zookeeper中初始化Mycat的配置的，這個(gè)文件會(huì)通過(guò)讀取Mycat安裝目錄下的conf目錄下的配置文件，將其初始化到Zookeeper集群中。
2.復(fù)制Mycat配置文件
首先，我們查看下Mycat安裝目錄下的conf目錄下的文件信息，如下所示。
[root@binghe151?~]#?cd?/usr/local/mycat/conf/
[root@binghe151?conf]#?ll
total?108
-rwxrwxrwx?1?root?root???92?Feb?26?17:10?autopartition-long.txt
-rwxrwxrwx?1?root?root???51?Feb?26?17:10?auto-sharding-long.txt
-rwxrwxrwx?1?root?root???67?Feb?26?17:10?auto-sharding-rang-mod.txt
-rwxrwxrwx?1?root?root??340?Feb?26?17:10?cacheservice.properties
-rwxrwxrwx?1?root?root?3338?Feb?26?17:10?dbseq.sql
-rwxrwxrwx?1?root?root?3532?Feb?26?17:10?dbseq?-?utf8mb4.sql
-rw-r--r--?1?root?root???86?Mar??1?22:37?dnindex.properties
-rwxrwxrwx?1?root?root??446?Feb?26?17:10?ehcache.xml
-rwxrwxrwx?1?root?root?2454?Feb?26?17:10?index_to_charset.properties
-rwxrwxrwx?1?root?root?1285?Feb?26?17:10?log4j2.xml
-rwxrwxrwx?1?root?root??183?Feb?26?17:10?migrateTables.properties
-rwxrwxrwx?1?root?root??271?Feb?26?17:10?myid.properties
-rwxrwxrwx?1?root?root???16?Feb?26?17:10?partition-hash-int.txt
-rwxrwxrwx?1?root?root??108?Feb?26?17:10?partition-range-mod.txt
-rwxrwxrwx?1?root?root??988?Mar??1?16:59?rule.xml
-rwxrwxrwx?1?root?root?3883?Mar??3?23:59?schema.xml
-rwxrwxrwx?1?root?root??440?Feb?26?17:10?sequence_conf.properties
-rwxrwxrwx?1?root?root???84?Mar??3?23:52?sequence_db_conf.properties
-rwxrwxrwx?1?root?root???29?Feb?26?17:10?sequence_distributed_conf.properties
-rwxrwxrwx?1?root?root???28?Feb?26?17:10?sequence_http_conf.properties
-rwxrwxrwx?1?root?root???53?Feb?26?17:10?sequence_time_conf.properties
-rwxrwxrwx?1?root?root?2420?Mar??4?15:14?server.xml
-rwxrwxrwx?1?root?root???18?Feb?26?17:10?sharding-by-enum.txt
-rwxrwxrwx?1?root?root?4251?Feb?28?20:51?wrapper.conf
drwxrwxrwx?2?root?root?4096?Feb?28?21:17?zkconf
drwxrwxrwx?2?root?root?4096?Feb?28?21:17?zkdownload
接下來(lái)，將Mycat安裝目錄下的conf目錄下的schema.xml文件、server.xml文件、rule.xml文件和sequence_db_conf.properties文件復(fù)制到conf目錄下的zkconf目錄下，如下所示。
cp?schema.xml?server.xml?rule.xml?sequence_db_conf.properties?zkconf/
3.將Mycat配置信息寫(xiě)入Zookeeper集群
執(zhí)行init_zk_data.sh腳本文件，向Zookeeper集群中初始化配置信息，如下所示。
[root@binghe151?bin]#?/usr/local/mycat/bin/init_zk_data.sh??
o2020-03-08?20:03:13?INFO?JAVA_CMD=/usr/local/jdk1.8.0_212/bin/java
o2020-03-08?20:03:13?INFO?Start?to?initialize?/mycat?of?ZooKeeper
o2020-03-08?20:03:14?INFO?Done
根據(jù)以上信息得知，Mycat向Zookeeper寫(xiě)入初始化配置信息成功。
4.驗(yàn)證Mycat配置信息是否成功寫(xiě)入Mycat
我們可以使用Zookeeper的客戶端命令zkCli.sh 登錄Zookeeper來(lái)驗(yàn)證Mycat的配置信息是否成功寫(xiě)入Mycat。
首先，登錄Zookeeper，如下所示。
[root@binghe151?~]#?zkCli.sh?
Connecting?to?localhost:2181
###################此處省略N行輸出######################
Welcome?to?ZooKeeper!
WATCHER::
WatchedEvent?state:SyncConnected?type:None?path:null
[zk:?localhost:2181(CONNECTED)?0]?
接下來(lái)，在Zookeeper命令行查看mycat的信息，如下所示。
[zk:?localhost:2181(CONNECTED)?0]?ls?/
[mycat,?zookeeper]
[zk:?localhost:2181(CONNECTED)?1]?ls?/mycat
[mycat-cluster-1]
[zk:?localhost:2181(CONNECTED)?2]?ls?/mycat/mycat-cluster-1
[cache,?line,?rules,?schema,?sequences,?server]
[zk:?localhost:2181(CONNECTED)?3]?
可以看到，在/mycat/mycat-cluster-1下存在6個(gè)目錄，接下來(lái)，查看下schema目錄下的信息，如下所示。
[zk:?localhost:2181(CONNECTED)?3]?ls?/mycat/mycat-cluster-1/schema
[dataHost,?dataNode,?schema]
接下來(lái)，我們查看下dataHost的配置，如下所示。
[zk:?localhost:2181(CONNECTED)?4]?get?/mycat/mycat-cluster-1/schema/dataHost
[{"balance":1,"maxCon":1000,"minCon":10,"name":"binghe151","writeType":0,"switchType":1,"slaveThreshold":100,"dbType":"mysql","dbDriver":"native","heartbeat":"select?user()","writeHost":[{"host":"binghe51","url":"192.168.175.151:3306","password":"root","user":"root"}]},{"balance":1,"maxCon":1000,"minCon":10,"name":"binghe152","writeType":0,"switchType":1,"slaveThreshold":100,"dbType":"mysql","dbDriver":"native","heartbeat":"select?user()","writeHost":[{"host":"binghe52","url":"192.168.175.152:3306","password":"root","user":"root"}]},{"balance":1,"maxCon":1000,"minCon":10,"name":"binghe153","writeType":0,"switchType":1,"slaveThreshold":100,"dbType":"mysql","dbDriver":"native","heartbeat":"select?user()","writeHost":[{"host":"binghe53","url":"192.168.175.153:3306","password":"root","user":"root"}]},{"balance":1,"maxCon":1000,"minCon":10,"name":"binghe154","writeType":0,"switchType":1,"slaveThreshold":100,"dbType":"mysql","dbDriver":"native","heartbeat":"select?user()","writeHost":[{"host":"binghe54","url":"192.168.175.154:3306","password":"root","user":"root"}]}]
上面的輸出信息格式比較亂，但可以看出是Json格式的信息，我們可以將輸出信息進(jìn)行格式化，格式化后的結(jié)果如下所示。
[
????{
????????"balance":?1,
????????"maxCon":?1000,
????????"minCon":?10,
????????"name":?"binghe151",
????????"writeType":?0,
????????"switchType":?1,
????????"slaveThreshold":?100,
????????"dbType":?"mysql",
????????"dbDriver":?"native",
????????"heartbeat":?"select?user()",
????????"writeHost":?[
????????????{
????????????????"host":?"binghe51",
????????????????"url":?"192.168.175.151:3306",
????????????????"password":?"root",
????????????????"user":?"root"
????????????}
????????]
????},
????{
????????"balance":?1,
????????"maxCon":?1000,
????????"minCon":?10,
????????"name":?"binghe152",
????????"writeType":?0,
????????"switchType":?1,
????????"slaveThreshold":?100,
????????"dbType":?"mysql",
????????"dbDriver":?"native",
????????"heartbeat":?"select?user()",
????????"writeHost":?[
????????????{
????????????????"host":?"binghe52",
????????????????"url":?"192.168.175.152:3306",
????????????????"password":?"root",
????????????????"user":?"root"
????????????}
????????]
????},
????{
????????"balance":?1,
????????"maxCon":?1000,
????????"minCon":?10,
????????"name":?"binghe153",
????????"writeType":?0,
????????"switchType":?1,
????????"slaveThreshold":?100,
????????"dbType":?"mysql",
????????"dbDriver":?"native",
????????"heartbeat":?"select?user()",
????????"writeHost":?[
????????????{
????????????????"host":?"binghe53",
????????????????"url":?"192.168.175.153:3306",
????????????????"password":?"root",
????????????????"user":?"root"
????????????}
????????]
????},
????{
????????"balance":?1,
????????"maxCon":?1000,
????????"minCon":?10,
????????"name":?"binghe154",
????????"writeType":?0,
????????"switchType":?1,
????????"slaveThreshold":?100,
????????"dbType":?"mysql",
????????"dbDriver":?"native",
????????"heartbeat":?"select?user()",
????????"writeHost":?[
????????????{
????????????????"host":?"binghe54",
????????????????"url":?"192.168.175.154:3306",
????????????????"password":?"root",
????????????????"user":?"root"
????????????}
????????]
????}
]
可以看到，我們?cè)贛ycat的schema.xml文件中配置的dataHost節(jié)點(diǎn)的信息，成功寫(xiě)入到Zookeeper中了。
為了驗(yàn)證Mycat的配置信息，是否已經(jīng)同步到Zookeeper的其他節(jié)點(diǎn)上，我們也可以在binghe152和binghe153服務(wù)器上登錄Zookeeper，查看Mycat配置信息是否寫(xiě)入成功。
binghe152服務(wù)器
[root@binghe152?~]#?zkCli.sh?
Connecting?to?localhost:2181
#################省略N行輸出信息################
[zk:?localhost:2181(CONNECTED)?0]?get?/mycat/mycat-cluster-1/schema/dataHost
[{"balance":1,"maxCon":1000,"minCon":10,"name":"binghe151","writeType":0,"switchType":1,"slaveThreshold":100,"dbType":"mysql","dbDriver":"native","heartbeat":"select?user()","writeHost":[{"host":"binghe51","url":"192.168.175.151:3306","password":"root","user":"root"}]},{"balance":1,"maxCon":1000,"minCon":10,"name":"binghe152","writeType":0,"switchType":1,"slaveThreshold":100,"dbType":"mysql","dbDriver":"native","heartbeat":"select?user()","writeHost":[{"host":"binghe52","url":"192.168.175.152:3306","password":"root","user":"root"}]},{"balance":1,"maxCon":1000,"minCon":10,"name":"binghe153","writeType":0,"switchType":1,"slaveThreshold":100,"dbType":"mysql","dbDriver":"native","heartbeat":"select?user()","writeHost":[{"host":"binghe53","url":"192.168.175.153:3306","password":"root","user":"root"}]},{"balance":1,"maxCon":1000,"minCon":10,"name":"binghe154","writeType":0,"switchType":1,"slaveThreshold":100,"dbType":"mysql","dbDriver":"native","heartbeat":"select?user()","writeHost":[{"host":"binghe54","url":"192.168.175.154:3306","password":"root","user":"root"}]}]
可以看到，Mycat的配置信息成功同步到了binghe152服務(wù)器上的Zookeeper中。
binghe153服務(wù)器
[root@binghe153?~]#?zkCli.sh?
Connecting?to?localhost:2181
#####################此處省略N行輸出信息#####################
[zk:?localhost:2181(CONNECTED)?0]?get?/mycat/mycat-cluster-1/schema/dataHost
[{"balance":1,"maxCon":1000,"minCon":10,"name":"binghe151","writeType":0,"switchType":1,"slaveThreshold":100,"dbType":"mysql","dbDriver":"native","heartbeat":"select?user()","writeHost":[{"host":"binghe51","url":"192.168.175.151:3306","password":"root","user":"root"}]},{"balance":1,"maxCon":1000,"minCon":10,"name":"binghe152","writeType":0,"switchType":1,"slaveThreshold":100,"dbType":"mysql","dbDriver":"native","heartbeat":"select?user()","writeHost":[{"host":"binghe52","url":"192.168.175.152:3306","password":"root","user":"root"}]},{"balance":1,"maxCon":1000,"minCon":10,"name":"binghe153","writeType":0,"switchType":1,"slaveThreshold":100,"dbType":"mysql","dbDriver":"native","heartbeat":"select?user()","writeHost":[{"host":"binghe53","url":"192.168.175.153:3306","password":"root","user":"root"}]},{"balance":1,"maxCon":1000,"minCon":10,"name":"binghe154","writeType":0,"switchType":1,"slaveThreshold":100,"dbType":"mysql","dbDriver":"native","heartbeat":"select?user()","writeHost":[{"host":"binghe54","url":"192.168.175.154:3306","password":"root","user":"root"}]}]
可以看到，Mycat的配置信息成功同步到了binghe153服務(wù)器上的Zookeeper中。
配置Mycat支持Zookeeper啟動(dòng)
1.在binghe151服務(wù)器上配置Mycat
在binghe151服務(wù)器上進(jìn)入Mycat安裝目錄的conf目錄下，查看文件信息，如下所示。
[root@binghe151?~]#?cd?/usr/local/mycat/conf/
[root@binghe151?conf]#?ll
total?108
-rwxrwxrwx?1?root?root???92?Feb?26?17:10?autopartition-long.txt
-rwxrwxrwx?1?root?root???51?Feb?26?17:10?auto-sharding-long.txt
-rwxrwxrwx?1?root?root???67?Feb?26?17:10?auto-sharding-rang-mod.txt
-rwxrwxrwx?1?root?root??340?Feb?26?17:10?cacheservice.properties
-rwxrwxrwx?1?root?root?3338?Feb?26?17:10?dbseq.sql
-rwxrwxrwx?1?root?root?3532?Feb?26?17:10?dbseq?-?utf8mb4.sql
-rw-r--r--?1?root?root???86?Mar??1?22:37?dnindex.properties
-rwxrwxrwx?1?root?root??446?Feb?26?17:10?ehcache.xml
-rwxrwxrwx?1?root?root?2454?Feb?26?17:10?index_to_charset.properties
-rwxrwxrwx?1?root?root?1285?Feb?26?17:10?log4j2.xml
-rwxrwxrwx?1?root?root??183?Feb?26?17:10?migrateTables.properties
-rwxrwxrwx?1?root?root??271?Feb?26?17:10?myid.properties
-rwxrwxrwx?1?root?root???16?Feb?26?17:10?partition-hash-int.txt
-rwxrwxrwx?1?root?root??108?Feb?26?17:10?partition-range-mod.txt
-rwxrwxrwx?1?root?root??988?Mar??1?16:59?rule.xml
-rwxrwxrwx?1?root?root?3883?Mar??3?23:59?schema.xml
-rwxrwxrwx?1?root?root??440?Feb?26?17:10?sequence_conf.properties
-rwxrwxrwx?1?root?root???84?Mar??3?23:52?sequence_db_conf.properties
-rwxrwxrwx?1?root?root???29?Feb?26?17:10?sequence_distributed_conf.properties
-rwxrwxrwx?1?root?root???28?Feb?26?17:10?sequence_http_conf.properties
-rwxrwxrwx?1?root?root???53?Feb?26?17:10?sequence_time_conf.properties
-rwxrwxrwx?1?root?root?2420?Mar??4?15:14?server.xml
-rwxrwxrwx?1?root?root???18?Feb?26?17:10?sharding-by-enum.txt
-rwxrwxrwx?1?root?root?4251?Feb?28?20:51?wrapper.conf
drwxrwxrwx?2?root?root?4096?Feb?28?21:17?zkconf
drwxrwxrwx?2?root?root?4096?Feb?28?21:17?zkdownload
可以看到，在Mycat的conf目錄下，存在一個(gè)myid.properties文件，接下來(lái)，使用vim編輯器編輯這個(gè)文件，如下所示。
vim?myid.properties?
編輯后的myid.properties文件的內(nèi)容如下所示。
loadZk=true
zkURL=192.168.175.151:2181,192.168.175.152:2181,192.168.175.153:2181
clusterId=mycat-cluster-1
myid=mycat_151
clusterSize=2
clusterNodes=mycat_151,mycat_154
#server??booster??;???booster?install?on?db?same?server,will?reset?all?minCon?to?2
type=server
boosterDataHosts=dataHost1
其中幾個(gè)重要的參數(shù)說(shuō)明如下所示。
loadZk：表示是否加載Zookeeper配置。true：是；false：否；
zkURL：Zookeeper的連接地址，多個(gè)Zookeeper連接地址以逗號(hào)隔開(kāi)；
clusterId：當(dāng)前Mycat集群的Id標(biāo)識(shí)，此標(biāo)識(shí)需要與Zookeeper中/mycat目錄下的目錄名稱相同，如下所示。
[zk:?localhost:2181(CONNECTED)?1]?ls?/mycat
[mycat-cluster-1]
myid：當(dāng)前Mycat節(jié)點(diǎn)的id，這里我的命名方式為mycat_前綴加上IP地址的最后三位； clusterSize：表示Mycat集群中的Mycat節(jié)點(diǎn)個(gè)數(shù)，這里，我們?cè)赽inghe151和binghe154節(jié)點(diǎn)上部署Mycat，所以Mycat節(jié)點(diǎn)的個(gè)數(shù)為2。 clusterNodes：Mycat集群中，所有的Mycat節(jié)點(diǎn)，此處的節(jié)點(diǎn)需要配置myid中配置的Mycat節(jié)點(diǎn)id，多個(gè)節(jié)點(diǎn)之前以逗號(hào)分隔。這里我配置的節(jié)點(diǎn)為：mycat_151,mycat_154。
2.在binghe154服務(wù)器上安裝全新的Mycat
在binghe154服務(wù)器上下載并安裝和binghe151服務(wù)器上相同版本的Mycat，并將其解壓到binghe154服務(wù)器上的/usr/local/mycat目錄下。
也可以在binghe151服務(wù)器上直接輸入如下命令將Mycat的安裝目錄復(fù)制到binghe154服務(wù)器上。
[root@binghe151?~]#?scp?-r?/usr/local/mycat?binghe154:/usr/local
注意：別忘了在binghe154服務(wù)器上配置Mycat的系統(tǒng)環(huán)境變量。
3.修改binghe154服務(wù)器上的Mycat配置
在binghe154服務(wù)器上修改Mycat安裝目錄下的conf目錄中的myid.properties文件，如下所示。
vim?/usr/local/mycat/conf/myid.properties
修改后的myid.properties文件的內(nèi)容如下所示。
loadZk=true
zkURL=192.168.175.151:2181,192.168.175.152:2181,192.168.175.153:2181
clusterId=mycat-cluster-1
myid=mycat_154
clusterSize=2
clusterNodes=mycat_151,mycat_154
#server??booster??;???booster?install?on?db?same?server,will?reset?all?minCon?to?2
type=server
boosterDataHosts=dataHost1
4.重啟Mycat
分別重啟binghe151服務(wù)器和binghe154服務(wù)器上的Mycat，如下所示。
注意：先重啟
binghe151服務(wù)器
[root@binghe151?~]#?mycat?restart
Stopping?Mycat-server...
Stopped?Mycat-server.
Starting?Mycat-server...
binghe154服務(wù)器
[root@binghe154?~]#?mycat?restart
Stopping?Mycat-server...
Stopped?Mycat-server.
Starting?Mycat-server...
在binghe151和binghe154服務(wù)器上分別查看Mycat的啟動(dòng)日志，如下所示。
STATUS?|?wrapper??|?2020/03/08?21:08:15?|?<--?Wrapper?Stopped
STATUS?|?wrapper??|?2020/03/08?21:08:15?|?-->?Wrapper?Started?as?Daemon
STATUS?|?wrapper??|?2020/03/08?21:08:15?|?Launching?a?JVM...
INFO???|?jvm?1????|?2020/03/08?21:08:16?|?Wrapper?(Version?3.2.3)?http://wrapper.tanukisoftware.org
INFO???|?jvm?1????|?2020/03/08?21:08:16?|???Copyright?1999-2006?Tanuki?Software,?Inc.??All?Rights?Reserved.
INFO???|?jvm?1????|?2020/03/08?21:08:16?|?
INFO???|?jvm?1????|?2020/03/08?21:08:28?|?MyCAT?Server?startup?successfully.?see?logs?in?logs/mycat.log
從日志的輸出結(jié)果可以看出，Mycat重啟成功。
此時(shí)，先重啟binghe151服務(wù)器上的Mycat，再重啟binghe154服務(wù)器上的Mycat之后，我們會(huì)發(fā)現(xiàn)binghe154服務(wù)器上的Mycat的conf目錄下的schema.xml、server.xml、rule.xml和sequence_db_conf.properties文件與binghe151服務(wù)器上Mycat的配置文件相同，這就是binghe154服務(wù)器上的Mycat從Zookeeper上讀取配置文件的結(jié)果。
以后，我們只需要修改Zookeeper中有關(guān)Mycat的配置，這些配置就會(huì)自動(dòng)同步到Mycat中，這樣可以保證多個(gè)Mycat節(jié)點(diǎn)的配置是一致的。
配置虛擬IP
分別在binghe151和binghe154服務(wù)器上配置虛擬IP，如下所示。
ifconfig?eth0:1?192.168.175.110?broadcast?192.168.175.255?netmask?255.255.255.0?up
route?add?-host?192.168.175.110?dev?eth0:1
配置完虛擬IP的效果如下所示，以binghe151服務(wù)器為例。
[root@binghe151?~]#?ifconfig
eth0??????Link?encap:Ethernet??HWaddr?00:0C:29:10:A1:45??
??????????inet?addr:192.168.175.151??Bcast:192.168.175.255??Mask:255.255.255.0
??????????inet6?addr:?fe80::20c:29ff:fe10:a145/64?Scope:Link
??????????UP?BROADCAST?RUNNING?MULTICAST??MTU:1500??Metric:1
??????????RX?packets:116766?errors:0?dropped:0?overruns:0?frame:0
??????????TX?packets:85230?errors:0?dropped:0?overruns:0?carrier:0
??????????collisions:0?txqueuelen:1000?
??????????RX?bytes:25559422?(24.3?MiB)??TX?bytes:55997016?(53.4?MiB)
eth0:1????Link?encap:Ethernet??HWaddr?00:0C:29:10:A1:45??
??????????inet?addr:192.168.175.110??Bcast:192.168.175.255??Mask:255.255.255.0
??????????UP?BROADCAST?RUNNING?MULTICAST??MTU:1500??Metric:1
lo????????Link?encap:Local?Loopback??
??????????inet?addr:127.0.0.1??Mask:255.0.0.0
??????????inet6?addr:?::1/128?Scope:Host
??????????UP?LOOPBACK?RUNNING??MTU:65536??Metric:1
??????????RX?packets:51102?errors:0?dropped:0?overruns:0?frame:0
??????????TX?packets:51102?errors:0?dropped:0?overruns:0?carrier:0
??????????collisions:0?txqueuelen:0?
??????????RX?bytes:2934009?(2.7?MiB)??TX?bytes:2934009?(2.7?MiB)
注意：在命令行添加VIP后，當(dāng)服務(wù)器重啟后，VIP信息會(huì)消失，所以，最好是將創(chuàng)建VIP的命令寫(xiě)到一個(gè)腳本文件中，例如，將命令寫(xiě)到/usr/local/script/vip.sh文件中，如下所示。
mkdir?/usr/local/script
vim?/usr/local/script/vip.sh
文件的內(nèi)容如下所示。
ifconfig?eth0:1?192.168.175.110?broadcast?192.168.175.255?netmask?255.255.255.0?up
route?add?-host?192.168.175.110?dev?eth0:1
接下來(lái)，將/usr/local/script/vip.sh文件添加到服務(wù)器開(kāi)機(jī)啟動(dòng)項(xiàng)中，如下所示。
echo?/usr/local/script/vip.sh?>>?/etc/rc.d/rc.local
配置IP轉(zhuǎn)發(fā)
在binghe151和binghe154服務(wù)器上配置系統(tǒng)內(nèi)核IP轉(zhuǎn)發(fā)功能，編輯/etc/sysctl.conf文件，如下所示。
vim?/etc/sysctl.conf
找到如下一行代碼。
net.ipv4.ip_forward?=?0
將其修改成如下所示的代碼。
net.ipv4.ip_forward?=?1
保存并退出vim編輯器，并運(yùn)行如下命令使配置生效。
sysctl?-p
安裝并配置xinetd服務(wù)
我們需要在安裝HAProxy的服務(wù)器上，也就是在binghe151和binghe154服務(wù)器上安裝xinetd服務(wù)來(lái)開(kāi)啟48700端口。
（1）在服務(wù)器命令行執(zhí)行如下命令安裝xinetd服務(wù)，如下所示。
yum?install?xinetd?-y
（2）編輯/etc/xinetd.conf文件，如下所示。
vim?/etc/xinetd.conf
檢查文件中是否存在如下配置。
includedir?/etc/xinetd.d
如果/etc/xinetd.conf文件中沒(méi)有以上配置，則在/etc/xinetd.conf文件中添加以上配置；如果存在以上配置，則不用修改。
（3）創(chuàng)建/etc/xinetd.d目錄，如下所示。
mkdir?/etc/xinetd.d
注意：如果/etc/xinetd.d目錄已經(jīng)存在，創(chuàng)建目錄時(shí)會(huì)報(bào)如下錯(cuò)誤。
mkdir:?cannot?create?directory?`/etc/xinetd.d':?File?exists
大家可不必理會(huì)此錯(cuò)誤信息。
（4）在/etc/xinetd.d目錄下添加Mycat狀態(tài)檢測(cè)服務(wù)器的配置文件mycat_status，如下所示。
touch?/etc/xinetd.d/mycat_status
（5）編輯mycat_status文件，如下所示。
vim?/etc/xinetd.d/mycat_status
編輯后的mycat_status文件中的內(nèi)容如下所示。
service?mycat_status
{
flags?=?REUSE
socket_type?=?stream
port?=?48700
wait?=?no
user?=?root
server?=/usr/local/bin/mycat_check.sh
log_on_failure?+=?USERID
disable?=?no
}
部分xinetd配置參數(shù)說(shuō)明如下所示。
socket_type：表示封包處理方式，Stream為TCP數(shù)據(jù)包。
port：表示xinetd服務(wù)監(jiān)聽(tīng)的端口號(hào)。
wait：表示不需等待，即服務(wù)將以多線程的方式運(yùn)行。
user：運(yùn)行xinted服務(wù)的用戶。
server：需要啟動(dòng)的服務(wù)腳本。
log_on_failure：記錄失敗的日志內(nèi)容。
disable：需要啟動(dòng)xinted服務(wù)時(shí)，需要將此配置項(xiàng)設(shè)置為no。
（6）在/usr/local/bin目錄下添加mycat_check.sh服務(wù)腳本，如下所示。
touch?/usr/local/bin/mycat_check.sh
（7）編輯/usr/local/bin/mycat_check.sh文件，如下所示。
vim?/usr/local/bin/mycat_check.sh
編輯后的文件內(nèi)容如下所示。
#!/bin/bash
mycat=`/usr/local/mycat/bin/mycat?status?|?grep?'not?running'?|?wc?-l`
if?[?"$mycat"?=?"0"?];?then
/bin/echo?-e?"HTTP/1.1?200?OK\r\n"
else
/bin/echo?-e?"HTTP/1.1?503?Service?Unavailable\r\n"
/usr/local/mycat/bin/mycat?start
fi
為mycat_check.sh文件賦予可執(zhí)行權(quán)限，如下所示。
chmod?a+x?/usr/local/bin/mycat_check.sh
（8）編輯/etc/services文件，如下所示。
vim?/etc/services
在文件末尾添加如下所示的內(nèi)容。
mycat_status??48700/tcp????????#?mycat_status
其中，端口號(hào)需要與在/etc/xinetd.d/mycat_status文件中配置的端口號(hào)相同。
（9）重啟xinetd服務(wù)，如下所示。
service?xinetd?restart
（10）查看mycat_status服務(wù)是否成功啟動(dòng)，如下所示。
binghe151服務(wù)器
[root@binghe151?~]#?netstat?-antup|grep?48700
tcp????0???0?:::48700??????????:::*??????LISTEN???2776/xinetd
binghe154服務(wù)器
[root@binghe154?~]#?netstat?-antup|grep?48700
tcp????0???0?:::48700??????????:::*??????LISTEN???6654/xinetd
結(jié)果顯示，兩臺(tái)服務(wù)器上的mycat_status服務(wù)器啟動(dòng)成功。
至此，xinetd服務(wù)安裝并配置成功，即Mycat狀態(tài)檢查服務(wù)安裝成功。
安裝并配置HAProxy
我們直接在binghe151和binghe154服務(wù)器上使用如下命令安裝HAProxy。
yum?install?haproxy?-y
安裝完成后，我們需要對(duì)HAProxy進(jìn)行配置，HAProxy的配置文件目錄為/etc/haproxy，我們查看這個(gè)目錄下的文件信息，如下所示。
[root@binghe151?~]#?ll?/etc/haproxy/
total?4
-rw-r--r--?1?root?root?3142?Oct?21??2016?haproxy.cfg
發(fā)現(xiàn)/etc/haproxy/目錄下存在一個(gè)haproxy.cfg文件。接下來(lái)，我們就修改haproxy.cfg文件，修改后的haproxy.cfg文件的內(nèi)容如下所示。
global
????log?????????127.0.0.1?local2
????chroot??????/var/lib/haproxy
????pidfile?????/var/run/haproxy.pid
????maxconn?????4000
????user????????haproxy
????group???????haproxy
????daemon
????stats?socket?/var/lib/haproxy/stats
defaults
????mode????????????????????http
????log?????????????????????global
????option??????????????????httplog
????option??????????????????dontlognull
????option?http-server-close
????option??????????????????redispatch
????retries?????????????????3
????timeout?http-request????10s
????timeout?queue???????????1m
????timeout?connect?????????10s
????timeout?client??????????1m
????timeout?server??????????1m
????timeout?http-keep-alive?10s
????timeout?check???????????10s
????maxconn?????????????????3000
listen??admin_status
??????bind?0.0.0.0:48800
??????stats?uri?/admin-status
??????stats?auth??admin:admin
listen????allmycat_service
??????bind?0.0.0.0:3366
??????mode?tcp
??????option?tcplog
???option?httpchk?OPTIONS?*?HTTP/1.1\r\nHost:\?www
??????balance????roundrobin
??????server????mycat_151?192.168.175.151:3307?check?port?48700?inter?5s?rise?2?fall?3
??????server????mycat_154?192.168.175.154:3307?check?port?48700?inter?5s?rise?2?fall?3
listen????allmycat_admin
??????bind?0.0.0.0:3377
??????mode?tcp
??????option?tcplog
???option?httpchk?OPTIONS?*?HTTP/1.1\r\nHost:\?www
??????balance????roundrobin
??????server????mycat_151?192.168.175.151:3308?check?port?48700?inter?5s?rise?2?fall?3
??????server????mycat_154?192.168.175.154:3308?check?port?48700?inter?5s?rise?2?fall?3
接下來(lái)，在binghe151服務(wù)器和binghe154服務(wù)器上啟動(dòng)HAProxy，如下所示。
haproxy?-f?/etc/haproxy/haproxy.cfg?
接下來(lái)，我們使用mysql命令連接HAProxy監(jiān)聽(tīng)的虛擬IP和端口來(lái)連接Mycat，如下所示。
[root@binghe151?~]#?mysql?-umycat?-pmycat?-h192.168.175.110?-P3366?--default-auth=mysql_native_password??
mysql:?[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?2
Server?version:?5.6.29-mycat-1.6.7.4-release-20200228205020?MyCat?Server?(OpenCloudDB)
Copyright?(c)?2000,?2019,?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>?
可以看到，連接Mycat成功。
安裝Keepalived
1.安裝并配置Keepalived
直接在binghe151和binghe154服務(wù)器上輸入如下命令安裝Keepalived。
yum?install?keepalived?-y
安裝成功后，會(huì)在/etc目錄下生成一個(gè)keepalived目錄，接下來(lái)，我們?cè)?etc/keepalived目錄下配置keepalived.conf文件，如下所示。
vim?/etc/keepalived/keepalived.conf
binghe151服務(wù)器配置
!?Configuration?Fileforkeepalived
vrrp_script?chk_http_port?{
??script?"/etc/keepalived/check_haproxy.sh"
??interval?2
??weight?2
}
vrrp_instance?VI_1?{
??state?MASTER?
??interface?eth0?
??virtual_router_id?51?
??priority?150?
??advert_int?1?
??authentication?{
??auth_type?PASS
??auth_pass?1111
}
track_script?{
??chk_http_port
}
virtual_ipaddress?{?
??192.168.175.110?dev?eth0?scope?global
}
}
binghe154服務(wù)器配置
!?Configuration?Fileforkeepalived
vrrp_script?chk_http_port?{
??script?"/etc/keepalived/check_haproxy.sh"
??interval?2
??weight?2
}
vrrp_instance?VI_1?{
??state?SLAVE?
??interface?eth0?
??virtual_router_id?51?
??priority?120
??advert_int?1?
??authentication?{
??auth_type?PASS
??auth_pass?1111
}
track_script?{
??chk_http_port
}
virtual_ipaddress?{?
??192.168.175.110?dev?eth0?scope?global
}
}
2.編寫(xiě)檢測(cè)HAProxy的腳本
接下來(lái)，需要分別在binghe151和binghe154服務(wù)器上的/etc/keepalived目錄下創(chuàng)建check_haproxy.sh腳本，腳本內(nèi)容如下所示。
#!/bin/bash
STARTHAPROXY="/usr/sbin/haproxy?-f?/etc/haproxy/haproxy.cfg"
STOPKEEPALIVED="/etc/init.d/keepalived?stop"
#STOPKEEPALIVED="/usr/bin/systemctl?stop?keepalived"
LOGFILE="/var/log/keepalived-haproxy-state.log"
echo?"[check_haproxy?status]"?>>?$LOGFILE
A=`ps?-C?haproxy?--no-header?|wc?-l`
echo?"[check_haproxy?status]"?>>?$LOGFILE
date?>>?$LOGFILE
if?[?$A?-eq?0?];then
???echo?$STARTHAPROXY?>>?$LOGFILE
???$STARTHAPROXY?>>?$LOGFILE?2>&1
???sleep?5
fi
if?[?`ps?-C?haproxy?--no-header?|wc?-l`?-eq?0?];then
???exit?0
else
???exit?1
fi
使用如下命令為check_haproxy.sh腳本授予可執(zhí)行權(quán)限。
chmod?a+x?/etc/keepalived/check_haproxy.sh?
3.啟動(dòng)Keepalived
配置完成后，我們就可以啟動(dòng)Keepalived了，分別在binghe151和binghe154服務(wù)器上啟動(dòng)Keepalived，如下所示。
/etc/init.d/keepalived?start
查看Keepalived是否啟動(dòng)成功，如下所示。
binghe151服務(wù)器
[root@binghe151?~]#?ps?-ef?|?grep?keepalived
root???????1221??????1??0?20:06??????????00:00:00?keepalived?-D
root???????1222???1221??0?20:06??????????00:00:00?keepalived?-D
root???????1223???1221??0?20:06??????????00:00:02?keepalived?-D
root??????93290???3787??0?21:42?pts/0????00:00:00?grep?keepalived
binghe154服務(wù)器
[root@binghe154?~]#?ps?-ef?|?grep?keepalived
root???????1224??????1??0?20:06??????????00:00:00?keepalived?-D
root???????1225???1224??0?20:06??????????00:00:00?keepalived?-D
root???????1226???1224??0?20:06??????????00:00:02?keepalived?-D
root??????94636???3798??0?21:43?pts/0????00:00:00?grep?keepalived
可以看到，兩臺(tái)服務(wù)器上的Keepalived服務(wù)啟動(dòng)成功。
4.驗(yàn)證Keepalived綁定的虛擬IP
接下來(lái)，我們分別查看兩臺(tái)服務(wù)器上的Keepalived是否綁定了虛擬IP。
binghe151服務(wù)器
[root@binghe151?~]#?ip?addr
1:?lo:??mtu?65536?qdisc?noqueue?state?UNKNOWN?
????link/loopback?00:00:00:00:00:00?brd?00:00:00:00:00:00
????inet?127.0.0.1/8?scope?host?lo
????inet6?::1/128?scope?host?
???????valid_lft?forever?preferred_lft?forever
2:?eth0:??mtu?1500?qdisc?pfifo_fast?state?UP?qlen?1000
????link/ether?00:0c:29:10:a1:45?brd?ff:ff:ff:ff:ff:ff
????inet?192.168.175.151/24?brd?192.168.175.255?scope?global?eth0
????inet?192.168.175.110/32?scope?global?eth0
????inet?192.168.175.110/24?brd?192.168.175.255?scope?global?secondary?eth0:1
????inet6?fe80::20c:29ff:fe10:a145/64?scope?link?
???????valid_lft?forever?preferred_lft?forever
可以看到如下一行代碼。
inet?192.168.175.110/32?scope?global?eth0
說(shuō)明binghe151服務(wù)器上的Keepalived綁定了虛擬IP 192.168.175.110。
binghe154服務(wù)器
[root@binghe154?~]#?ip?addr
1:?lo:??mtu?65536?qdisc?noqueue?state?UNKNOWN?
????link/loopback?00:00:00:00:00:00?brd?00:00:00:00:00:00
????inet?127.0.0.1/8?scope?host?lo
????inet6?::1/128?scope?host?
???????valid_lft?forever?preferred_lft?forever
2:?eth0:??mtu?1500?qdisc?pfifo_fast?state?UP?qlen?1000
????link/ether?00:50:56:22:2a:75?brd?ff:ff:ff:ff:ff:ff
????inet?192.168.175.154/24?brd?192.168.175.255?scope?global?eth0
????inet?192.168.175.110/24?brd?192.168.175.255?scope?global?secondary?eth0:1
????inet6?fe80::250:56ff:fe22:2a75/64?scope?link?
???????valid_lft?forever?preferred_lft?forever
可以看到binghe154服務(wù)器上的Keepalived并沒(méi)有綁定虛擬IP。
5.測(cè)試虛擬IP的漂移
如何測(cè)試虛擬IP的漂移呢？首先，我們停止binghe151服務(wù)器上的Keepalived，如下所示。
/etc/init.d/keepalived?stop
接下來(lái)，查看binghe154服務(wù)器上Keepalived綁定虛擬IP的情況，如下所示。
[root@binghe154?~]#?ip?addr
1:?lo:??mtu?65536?qdisc?noqueue?state?UNKNOWN?
????link/loopback?00:00:00:00:00:00?brd?00:00:00:00:00:00
????inet?127.0.0.1/8?scope?host?lo
????inet6?::1/128?scope?host?
???????valid_lft?forever?preferred_lft?forever
2:?eth0:??mtu?1500?qdisc?pfifo_fast?state?UP?qlen?1000
????link/ether?00:50:56:22:2a:75?brd?ff:ff:ff:ff:ff:ff
????inet?192.168.175.154/24?brd?192.168.175.255?scope?global?eth0
????inet?192.168.175.110/32?scope?global?eth0
????inet?192.168.175.110/24?brd?192.168.175.255?scope?global?secondary?eth0:1
????inet6?fe80::250:56ff:fe22:2a75/64?scope?link?
???????valid_lft?forever?preferred_lft?forever
可以看到，在輸出的結(jié)果信息中，存在如下一行信息。
?inet?192.168.175.110/32?scope?global?eth0
說(shuō)明binghe154服務(wù)器上的Keepalived綁定了虛擬IP 192.168.175.110，虛擬IP漂移到了binghe154服務(wù)器上。
6.binghe151服務(wù)器上的Keepalived搶占虛擬IP
接下來(lái)，我們啟動(dòng)binghe151服務(wù)器上的Keepalived，如下所示。
/etc/init.d/keepalived?start
啟動(dòng)成功后，我們?cè)俅尾榭刺摂MIP的綁定情況，如下所示。
binghe151服務(wù)器
[root@binghe151?~]#?ip?addr
1:?lo:??mtu?65536?qdisc?noqueue?state?UNKNOWN?
????link/loopback?00:00:00:00:00:00?brd?00:00:00:00:00:00
????inet?127.0.0.1/8?scope?host?lo
????inet6?::1/128?scope?host?
???????valid_lft?forever?preferred_lft?forever
2:?eth0:??mtu?1500?qdisc?pfifo_fast?state?UP?qlen?1000
????link/ether?00:0c:29:10:a1:45?brd?ff:ff:ff:ff:ff:ff
????inet?192.168.175.151/24?brd?192.168.175.255?scope?global?eth0
????inet?192.168.175.110/32?scope?global?eth0
????inet?192.168.175.110/24?brd?192.168.175.255?scope?global?secondary?eth0:1
????inet6?fe80::20c:29ff:fe10:a145/64?scope?link?
???????valid_lft?forever?preferred_lft?forever
binghe154服務(wù)器
[root@binghe154?~]#?ip?addr
1:?lo:??mtu?65536?qdisc?noqueue?state?UNKNOWN?
????link/loopback?00:00:00:00:00:00?brd?00:00:00:00:00:00
????inet?127.0.0.1/8?scope?host?lo
????inet6?::1/128?scope?host?
???????valid_lft?forever?preferred_lft?forever
2:?eth0:??mtu?1500?qdisc?pfifo_fast?state?UP?qlen?1000
????link/ether?00:50:56:22:2a:75?brd?ff:ff:ff:ff:ff:ff
????inet?192.168.175.154/24?brd?192.168.175.255?scope?global?eth0
????inet?192.168.175.110/24?brd?192.168.175.255?scope?global?secondary?eth0:1
????inet6?fe80::250:56ff:fe22:2a75/64?scope?link?
???????valid_lft?forever?preferred_lft?forever
由于binghe151服務(wù)器上配置的Keepalived優(yōu)先級(jí)要高于binghe154服務(wù)器上的Keepalived，所以，再次啟動(dòng)binghe151服務(wù)器上的Keepalived后，binghe151服務(wù)器上的Keepalived會(huì)搶占虛擬IP。
配置MySQL主從復(fù)制
這里，為了簡(jiǎn)單，我將binghe154和binghe155服務(wù)器上的MySQL配置成主從復(fù)制，大家也可以根據(jù)實(shí)際情況，自行配置其他服務(wù)器上MySQL的主從復(fù)制（注意：我這里配置的是一主一從模式）。
1.編輯my.cnf文件
binghe154服務(wù)器
server_id?=?154
log_bin?=?/data/mysql/log/bin_log/mysql-bin
binlog-ignore-db=mysql
binlog_format=?mixed
sync_binlog=100
log_slave_updates?=?1
binlog_cache_size=32m
max_binlog_cache_size=64m
max_binlog_size=512m
lower_case_table_names?=?1
relay_log?=?/data/mysql/log/bin_log/relay-bin
relay_log_index?=?/data/mysql/log/bin_log/relay-bin.index
master_info_repository=TABLE
relay-log-info-repository=TABLE
relay-log-recovery
binghe155服務(wù)器
server_id?=?155
log_bin?=?/data/mysql/log/bin_log/mysql-bin
binlog-ignore-db=mysql
binlog_format=?mixed
sync_binlog=100
log_slave_updates?=?1
binlog_cache_size=32m
max_binlog_cache_size=64m
max_binlog_size=512m
lower_case_table_names?=?1
relay_log?=?/data/mysql/log/bin_log/relay-bin
relay_log_index?=?/data/mysql/log/bin_log/relay-bin.index
master_info_repository=TABLE
relay-log-info-repository=TABLE
relay-log-recovery
2.同步兩臺(tái)服務(wù)器上MySQL的數(shù)據(jù)
在binghe154服務(wù)器上只有一個(gè)customer_db數(shù)據(jù)庫(kù)，我們使用mysqldump命令導(dǎo)出customer_db數(shù)據(jù)庫(kù)，如下所示。
[root@binghe154?~]#?mysqldump?--master-data=2?--single-transaction?-uroot?-p?--databases?customer_db?>?binghe154.sql
Enter?password:?
接下來(lái)，我們查看binghe154.sql文件。
more?binghe154.sql
在文件中，我們可以找到如下信息。
CHANGE?MASTER?TO?MASTER_LOG_FILE='mysql-bin.000042',?MASTER_LOG_POS=995;
說(shuō)明當(dāng)前MySQL的二進(jìn)制日志文件為mysql-bin.000042，二進(jìn)制日志文件的位置為995。
接下來(lái)，我們將binghe154.sql文件復(fù)制到binghe155服務(wù)器上，如下所示。
scp?binghe154.sql?192.168.175.155:/usr/local/src
在binghe155服務(wù)器上，將binghe154.sql腳本導(dǎo)入到MySQL中，如下所示。
mysql?-uroot?-p?local/src/binghe154.sql
此時(shí)，完成了數(shù)據(jù)的初始化。
3.創(chuàng)建主從復(fù)制賬號(hào)
在binghe154服務(wù)器的MySQL中，創(chuàng)建用于主從復(fù)制的MySQL賬號(hào)，如下所示。
mysql>?CREATE?USER?'repl'@'192.168.175.%'?IDENTIFIED?BY?'repl123456';
Query?OK,?0?rows?affected?(0.01?sec)
mysql>?ALTER?USER?'repl'@'192.168.175.%'?IDENTIFIED?WITH?mysql_native_password?BY?'repl123456';???????????????????????????
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?GRANT?REPLICATION?SLAVE?ON?*.*?TO?'repl'@'192.168.175.%';
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?FLUSH?PRIVILEGES;
Query?OK,?0?rows?affected?(0.00?sec)
4.配置復(fù)制鏈路
登錄binghe155服務(wù)器上的MySQL，并使用如下命令配置復(fù)制鏈路。
mysql>?change?master?to?
?????>?master_host='192.168.175.154',
?????>?master_port=3306,
?????>?master_user='repl',
?????>?master_password='repl123456',
?????>?MASTER_LOG_FILE='mysql-bin.000042',
?????>?MASTER_LOG_POS=995;
其中，MASTER_LOG_FILE='mysql-bin.000042', MASTER_LOG_POS=995 就是在binghe154.sql文件中找到的。
5.啟動(dòng)從庫(kù)
在binghe155服務(wù)器的MySQL命令行啟動(dòng)從庫(kù)，如下所示。
mysql>?start?slave;
查看從庫(kù)是否啟動(dòng)成功，如下所示。
mysql>?SHOW?slave?STATUS?\G
***************************?1.?row?***************************
???????????????Slave_IO_State:?Waiting?for?master?to?send?event
??????????????????Master_Host:?192.168.175.151
??????????????????Master_User:?binghe152
??????????????????Master_Port:?3306
????????????????Connect_Retry:?60
??????????????Master_Log_File:?mysql-bin.000007
??????????Read_Master_Log_Pos:?1360
???????????????Relay_Log_File:?relay-bin.000003
????????????????Relay_Log_Pos:?322
????????Relay_Master_Log_File:?mysql-bin.000007
?????????????Slave_IO_Running:?Yes
????????????Slave_SQL_Running:?Yes
#################省略部分輸出結(jié)果信息##################
結(jié)果顯示Slave_IO_Running選項(xiàng)和Slave_SQL_Running選項(xiàng)的值均為Yes，說(shuō)明MySQL主從復(fù)制環(huán)境搭建成功。
最后，別忘了在binghe155服務(wù)器的MySQL中創(chuàng)建Mycat連接MySQL的用戶，如下所示。
CREATE?USER?'mycat'@'192.168.175.%'?IDENTIFIED?BY?'mycat';
ALTER?USER?'mycat'@'192.168.175.%'?IDENTIFIED?WITH?mysql_native_password?BY?'mycat';?
GRANT?SELECT,?INSERT,?UPDATE,?DELETE,EXECUTE??ON?*.*?TO?'mycat'@'192.168.175.%';
FLUSH?PRIVILEGES;
配置Mycat讀寫(xiě)分離
修改Mycatd的schema.xml文件，實(shí)現(xiàn)binghe154和binghe155服務(wù)器上的MySQL讀寫(xiě)分離。在Mycat安裝目錄的conf/zkconf目錄下，修改schema.xml文件，修改后的schema.xml文件如下所示。
mycat:schema?SYSTEM?"schema.dtd">
<mycat:schema?xmlns:mycat="http://io.mycat/">
????<schema?name="shop"?checkSQLschema="true"?sqlMaxLimit="1000">
????????<table?name="order_master"?dataNode="orderdb01,orderdb02,orderdb03,orderdb04"?rule="order_master"?primaryKey="order_id"?autoIncrement="true">
????????????<childTable?name="order_detail"?joinKey="order_id"?parentKey="order_id"?primaryKey="order_detail_id"?autoIncrement="true"/>
????????table>
????????<table?name="order_cart"?dataNode="ordb"?primaryKey="cart_id"/>
????????<table?name="order_customer_addr"?dataNode="ordb"?primaryKey="customer_addr_id"/>
????????<table?name="region_info"?dataNode="ordb,prodb,custdb"?primaryKey="region_id"?type="global"/>
????????<table?name="serial"?dataNode="ordb"?primaryKey="id"/>
????????<table?name="shipping_info"?dataNode="ordb"?primaryKey="ship_id"/>
????????<table?name="warehouse_info"?dataNode="ordb"?primaryKey="w_id"/>
????????<table?name="warehouse_proudct"?dataNode="ordb"?primaryKey="wp_id"/>
????????<table?name="product_brand_info"?dataNode="prodb"?primaryKey="brand_id"/>
????????<table?name="product_category"?dataNode="prodb"?primaryKey="category_id"/>
????????<table?name="product_comment"?dataNode="prodb"?primaryKey="comment_id"/>
????????<table?name="product_info"?dataNode="prodb"?primaryKey="product_id"/>
????????<table?name="product_pic_info"?dataNode="prodb"?primaryKey="product_pic_id"/>
????????<table?name="product_supplier_info"?dataNode="prodb"?primaryKey="supplier_id"/>
????????<table?name="customer_balance_log"?dataNode="custdb"?primaryKey="balance_id"/>
????????<table?name="customer_inf"?dataNode="custdb"?primaryKey="customer_inf_id"/>
????????<table?name="customer_level_inf"?dataNode="custdb"?primaryKey="customer_level"/>
????????<table?name="customer_login"?dataNode="custdb"?primaryKey="customer_id"/>
????????<table?name="customer_login_log"?dataNode="custdb"?primaryKey="login_id"/>
????????<table?name="customer_point_log"?dataNode="custdb"?primaryKey="point_id"/>
????schema>
?
????<dataNode?name="mycat"?dataHost="binghe151"?database="mycat"/>
????<dataNode?name="ordb"?dataHost="binghe152"?database="order_db"/>
????<dataNode?name="prodb"?dataHost="binghe153"?database="product_db"/>
????<dataNode?name="custdb"?dataHost="binghe154"?database="customer_db"/>
????<dataNode?name="orderdb01"?dataHost="binghe152"?database="orderdb01"/>
????<dataNode?name="orderdb02"?dataHost="binghe152"?database="orderdb02"/>
????<dataNode?name="orderdb03"?dataHost="binghe153"?database="orderdb03"/>
????<dataNode?name="orderdb04"?dataHost="binghe153"?database="orderdb04"/>
?
????<dataHost?balance="1"?maxCon="1000"?minCon="10"?name="binghe151"?writeType="0"?switchType="1"?slaveThreshold="100"?dbType="mysql"?dbDriver="native">
????????<heartbeat>select?user()heartbeat>
????????<writeHost?host="binghe51"?url="192.168.175.151:3306"?password="mycat"?user="mycat"/>
????dataHost>
????<dataHost?balance="1"?maxCon="1000"?minCon="10"?name="binghe152"?writeType="0"?switchType="1"?slaveThreshold="100"?dbType="mysql"?dbDriver="native">
????????<heartbeat>select?user()heartbeat>
????????<writeHost?host="binghe52"?url="192.168.175.152:3306"?password="mycat"?user="mycat"/>
????dataHost>
????<dataHost?balance="1"?maxCon="1000"?minCon="10"?name="binghe153"?writeType="0"?switchType="1"?slaveThreshold="100"?dbType="mysql"?dbDriver="native">
????????<heartbeat>select?user()heartbeat>
????????<writeHost?host="binghe53"?url="192.168.175.153:3306"?password="mycat"?user="mycat"/>
????dataHost>
????<dataHost?balance="1"?maxCon="1000"?minCon="10"?name="binghe154"?writeType="0"?switchTymycate="1"?slaveThreshold="100"?dbType="mysql"?dbDriver="native">
????????<heartbeat>select?user()heartbeat>
????????<writeHost?host="binghe54"?url="192.168.175.154:3306"?password="mycat"?user="mycat">
???<readHost?host="binghe55",?url="192.168.175.155:3306"?user="mycat"?password="mycat"/>
??writeHost>
?????????<writeHost?host="binghe55"?url="192.168.175.155:3306"?password="mycat"?user="mycat"/>
????dataHost>
mycat:schema>
保存并退出vim編輯器，接下來(lái)，初始化Zookeeper中的數(shù)據(jù)，如下所示。
/usr/local/mycat/bin/init_zk_data.sh?
上述命令執(zhí)行成功后，會(huì)自動(dòng)將配置同步到binghe151和binghe154服務(wù)器上的Mycat的安裝目錄下的conf目錄下的schema.xml中。
接下來(lái)，分別啟動(dòng)binghe151和binghe154服務(wù)器上的Mycat服務(wù)。
mycat?restart
如何訪問(wèn)高可用環(huán)境
此時(shí)，整個(gè)高可用環(huán)境配置完成，上層應(yīng)用連接高可用環(huán)境時(shí)，需要連接HAProxy監(jiān)聽(tīng)的IP和端口。比如使用mysql命令連接高可用環(huán)境如下所示。
[root@binghe151?~]#?mysql?-umycat?-pmycat?-h192.168.175.110?-P3366?--default-auth=mysql_native_password
mysql:?[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?2
Server?version:?5.6.29-mycat-1.6.7.4-release-20200228205020?MyCat?Server?(OpenCloudDB)
Copyright?(c)?2000,?2019,?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>?show?databases;
+----------+
|?DATABASE?|
+----------+
|?shop?????|
+----------+
1?row?in?set?(0.10?sec)
mysql>?use?shop;
Database?changed
mysql>?show?tables;
+-----------------------+
|?Tables?in?shop????????|
+-----------------------+
|?customer_balance_log??|
|?customer_inf??????????|
|?customer_level_inf????|
|?customer_login????????|
|?customer_login_log????|
|?customer_point_log????|
|?order_cart????????????|
|?order_customer_addr???|
|?order_detail??????????|
|?order_master??????????|
|?product_brand_info????|
|?product_category??????|
|?product_comment???????|
|?product_info??????????|
|?product_pic_info??????|
|?product_supplier_info?|
|?region_info???????????|
|?serial????????????????|
|?shipping_info?????????|
|?warehouse_info????????|
|?warehouse_proudct?????|
+-----------------------+
21?rows?in?set?(0.00?sec)
這里，我只是對(duì)binghe154服務(wù)器上的MySQL擴(kuò)展了讀寫(xiě)分離環(huán)境，大家也可以根據(jù)實(shí)際情況對(duì)其他服務(wù)器的MySQL實(shí)現(xiàn)主從復(fù)制和讀寫(xiě)分離，這樣，整個(gè)高可用環(huán)境就實(shí)現(xiàn)了HAProxy的高可用、Mycat的高可用、MySQL的高可用、Zookeeper的高可用和Keepalived的高可用。
好了，今天就到這兒吧，我是冰河，我們下期見(jiàn)！！
