接上一篇：企業(yè)實戰(zhàn)_10_MyCat Mysql 主復(fù)制總結(jié)
https://gblfy.blog.csdn.net/article/details/118652742

文章目錄

• • • • • 一、準(zhǔn)備工作
        • • 1. 實現(xiàn)思路
          • 2. 配置MyCat垂直分庫
          • 3. 環(huán)境配置
          • 4. 在master節(jié)點創(chuàng)建mycat用戶
          • 5. 賦予操作數(shù)據(jù)庫權(quán)限
        • 二、schema.xml文件配置
        • • 2.1. 基礎(chǔ)模板 樣例
          • 2.2. 實際配置
          • 2.3. dataHost標(biāo)簽
          • 2.4. dataNode標(biāo)簽
          • 2.5. schema標(biāo)簽
        • 三、server.xml文件配置
        • 四、啟動mycat調(diào)試
        • • 4.1. 啟動mycat
          • 4.2. 啟動mycat異常
          • 4.2. 解決方案
          • 4.3. 重新啟動mycat
          • 4.4. Mycat驗證配置
          • 4.5. 操作日志

一、準(zhǔn)備工作

1. 實現(xiàn)思路

第一步由后臺3個物理數(shù)據(jù)庫抽取為一個邏輯數(shù)據(jù)路

第二步	各個數(shù)據(jù)節(jié)點只保存自己模塊的表數(shù)據(jù)
第三步	刪除冗余數(shù)據(jù)

---

2. 配置MyCat垂直分庫

步驟說明

①	使用schema.xml配置邏輯庫
②	使用server.xml配置系統(tǒng)變量及用戶權(quán)限
③	由于沒有用到水平分片顧不許配置rule.xml

---

3. 環(huán)境配置

主機名IP地址角色數(shù)據(jù)庫

node1	192.168.92.101	MYSQL、mycat	imooc_db(主機物理)
node2	192.168.92.102	MYSQL	order_db(從機物理)
node3	192.168.92.103	MYSQL	product_db(主機物理)
node4	192.168.92.104	MYSQL	customer_db(從機物理)

---

4. 在master節(jié)點創(chuàng)建mycat用戶

create user im_mycat@'192.168.92.%' identified by '123456';

5. 賦予操作數(shù)據(jù)庫權(quán)限

grant select,insert,update,delete,execute on *.* to im_mycat@'192.168.92.%';

注：在主機上創(chuàng)建用戶，從機也會創(chuàng)建，這個用戶是讓mycat來操作數(shù)據(jù)庫的

二、schema.xml文件配置

2.1. 基礎(chǔ)模板 樣例

<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"><!-- 配置邏輯庫和邏輯表 --><schema name="邏輯庫" checkSQLschema="false" sqlMaxLimit="100"><table name="邏輯表" primaryKey="邏輯表主鍵" dataNode="數(shù)據(jù)節(jié)點名"/></schema><!-- 配置數(shù)據(jù)節(jié)點 --><dataNode name="數(shù)據(jù)節(jié)點名" dataHost="主機節(jié)點名" database="物理數(shù)據(jù)庫" /><!-- 配置主機節(jié)點 --><dataHost name="物理數(shù)據(jù)庫主機節(jié)點" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="數(shù)據(jù)庫類型" dbDriver="native" switchType="1" ><heartbeat>select user()</heartbeat><writeHost host="物理數(shù)據(jù)庫ip" url="物理數(shù)據(jù)庫ip:端口" user="操作物理數(shù)據(jù)庫的mysql用戶" password="密碼"></writeHost></dataHost> </mycat:schema>

2.2. 實際配置

<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- name：邏輯庫名 checkSQLschema=false：查詢sql中是否包含數(shù)據(jù)庫名，忽略校驗 sqlMaxLimit：定義了默認(rèn)返回前端的數(shù)據(jù)的行數(shù) --> <schema name="imooc_db" checkSQLschema="false" sqlMaxLimit="100"> <!-- name：邏輯表名 primaryKey：邏輯主鍵和后端真是表中的主鍵保持一致 dataNode：引用的數(shù)據(jù)節(jié)點 --> <table name="order_master" primaryKey="order_id" dataNode="ordb" /> </schema><!--數(shù)據(jù)節(jié)點 name：數(shù)據(jù)節(jié)點名稱 dataHost：引用下面配置的dataHost database：后端mysql的物理數(shù)據(jù)庫名稱 --> <dataNode name="ordb" dataHost="mysql92102" database="order_db" /><!--mycat連接后端mysql的數(shù)據(jù)節(jié)點的連接信息 name:數(shù)據(jù)主機名 (自定義數(shù)據(jù)庫+節(jié)點簡寫) maxCon=1000：后端mysql允許mycat連接的最大連接數(shù)1000 maxCon=10：后端mysql允許mycat連接的最小連接數(shù)10 balance=3：在從機中負(fù)載均衡分發(fā)請求 writeType=0：寫的類型，后端多個writeType怎樣處理讀請求 dbType：數(shù)據(jù)庫類型 dbDriver:原生mysql驅(qū)動 switchType=1:交換類型，如果不是主從復(fù)制，此參數(shù)無意義--> <dataHost name="mysql92102" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1"><!--心跳檢測--> <heartbeat>select user()</heartbeat><!--寫主機user：操作讀寫的mycat用戶password：操作讀寫的mycat用戶密碼--> <writeHost host="192.168.92.102" url="192.168.92.102:3306" user="im_mycat" password="123456"></writeHost></dataHost> </mycat:schema>

2.3. dataHost標(biāo)簽

dataHost標(biāo)簽:主要是配置mycat訪問后端mysql數(shù)據(jù)節(jié)點的連接信息，咱們是一主三從，下面是配置3個從節(jié)點的連接信息。

<dataHost name="mysql92103" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1"><heartbeat>select user()</heartbeat><writeHost host="192.168.92.103" url="192.168.92.103:3306" user="im_mycat" password="123456"></writeHost></dataHost><dataHost name="mysql92104" maxCon="1000" minCon="10" balance="3"writeType="0" dbType="mysql" dbDriver="native" switchType="1"><heartbeat>select user()</heartbeat><writeHost host="192.168.92.104" url="192.168.92.104:3306" user="im_mycat" password="123456"></writeHost></dataHost>

2.4. dataNode標(biāo)簽

dataNode依賴于 dataHost標(biāo)簽標(biāo)簽，dataNode有幾個是后端有幾個數(shù)據(jù)庫決定的。

<dataNode name="ordb" dataHost="mysql92102" database="order_db" /><dataNode name="prodb" dataHost="mysql92103" database="product_db" /><dataNode name="custdb" dataHost="mysql92104" database="customer_db" />

2.5. schema標(biāo)簽

關(guān)鍵配置：邏輯庫和邏輯表

<schema name="imooc_db" checkSQLschema="false" sqlMaxLimit="100"><table name="order_master" primaryKey="order_id" dataNode="ordb" /><table name="order_detail" primaryKey="order_detail_id" dataNode="ordb" /><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" /><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_supplier_info" primaryKey="supplier_id" dataNode="prodb" /><table name="product_pic_info" primaryKey="product_pic_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" /> </schema>

三、server.xml文件配置

配置系統(tǒng)參數(shù)
應(yīng)用程序通過mycat連接數(shù)據(jù)庫的賬號

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mycat:server SYSTEM "server.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"><system><property name="serverPort">8066</property><property name="managerPort">9066</property><property name="nonePasswordLogin">0</property><property name="bindIp">0.0.0.0</property><property name="frontWriteQueueSize">2048</property><property name="txIsolation">2</property><property name="processors">8</property><property name="idleTimeout">1800000</property><property name="sqlExecuteTimeout">300</property><property name="useSqlStat">0</property><property name="useGlobleTableCheck">0</property><property name="sequnceHandlerType">2</property><property name="defaultMaxLimit">100</property><property name="maxPacketSize">104857600</property></system><user defaultAccount="true" name="app_imooc"><property name="usingDecrypt">1</property><property name="password">bDbWr7bVMgszTe82oMo8NaUsmFFdPCNl/lYXzOYoG8anTpQLvdx5e+LYJEmT0IAeSVp1loyxSZPyv1GoHbHFHg==</property><property name="schemas">order_db,product_db,customer_db</property></user> </mycat:server>

四、啟動mycat調(diào)試

4.1. 啟動mycat

# 監(jiān)控MyCat運行狀態(tài) ps -ef |grep mycat# 重新啟動mycat mycat stop#啟動MyCat mycat start# 查看mycat啟動日志 tail -f /app/mycat/logs/wrapper.log

4.2. 啟動mycat異常

#異常信息如下 Caused by: io.mycat.config.util.ConfigException: SelfCheck### schema product_db refered by user app_imooc is not exist! # 日志的異常信息收的就是app_imooc用不存在

4.2. 解決方案

此處應(yīng)該配置邏輯數(shù)據(jù)庫而不是物理數(shù)據(jù)庫

# 編輯server.xml 將<property name="schemas">order_db,product_db,customer_db</property> 替換為 <property name="schemas">imooc_db</property>

4.3. 重新啟動mycat

# 重新啟動mycat mycat stop#啟動MyCat mycat start# 查看mycat啟動日志 tail -f /app/mycat/logs/wrapper.log

4.4. Mycat驗證配置

# 在mysql的任意節(jié)點，使用mysql客戶端連接mycat # 怎樣證明這是通過登錄的mysql數(shù)據(jù)庫還是登錄的mycat服務(wù)端呢？在操作日志中有答案mysql -uapp_imooc -p123456 -h192.168.92.101 -P8066 # 查看當(dāng)前用戶可以查看的邏輯庫有哪些show databases;# 使用imooc_db數(shù)據(jù)庫 use imooc_db;# 查看在這個邏輯庫下面的邏輯表包含有哪些 show tabels;# 查詢邏輯表，驗證是否可以真實訪問后端屋里庫中的物理表的數(shù)據(jù) select * from product_info limit 10;如果返回數(shù)據(jù)正常，說明mycat配置是沒有問題了。

從上面可以看出邏輯表和我們真實的屋里庫中的標(biāo)的數(shù)量一致，現(xiàn)在通過邏輯庫訪問后端3個物理數(shù)據(jù)庫中的物理表。

4.5. 操作日志

[root@node1 ~]# mysql -uapp_imooc -p123456 -h192.168.92.101 -P8066 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.5-release-20180122220033 MyCat Server (OpenCloundDB)Copyright (c) 2000, 2021, Oracle and/or its affiliates.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 | +----------+ | imooc_db | +----------+ 1 row in set (0.01 sec)mysql> use imooc_db; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -ADatabase changed mysql> show tables; +-----------------------+ | Tables in imooc_db | +-----------------------+ | 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 | | shipping_info | | warehouse_info | | warehouse_proudct | +-----------------------+ 20 rows in set (0.00 sec)mysql> select * from product_info limit 10; +------------+------------------+---------------------------------------+------------+----------+-----------------+-----------------+-------------------+-------------+--------+--------------+----------------+--------------+--------+--------+-------+-------+------------+---------------------+------------+----------+---------------------+---------------------+ | product_id | product_code | product_name | bar_code | brand_id | one_category_id | two_category_id | three_category_id | supplier_id | price | average_cost | publish_status | audit_status | weight | length | heigh | width | color_type | production_date | shelf_life | descript | indate | modified_time | +------------+------------------+---------------------------------------+------------+----------+-----------------+-----------------+-------------------+-------------+--------+--------------+----------------+--------------+--------+--------+-------+-------+------------+---------------------+------------+----------+---------------------+---------------------+ | 1 | 1700000000000001 | [凱樂石]連衣裙示例商品-1 | 771998885 | 4 | 1 | 9 | 17 | 2 | 269.79 | 269.79 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | 2021-07-27 09:55:18 | 180 | | 2021-07-10 19:38:55 | 2021-07-10 19:38:56 | | 2 | 1800000000000001 | [金狐貍]蕾絲裙示例商品-1 | 9075354188 | 12 | 1 | 9 | 18 | 2 | 252.03 | 252.03 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | 2021-10-01 07:08:03 | 180 | | 2021-07-10 19:38:55 | 2021-07-10 19:38:56 | | 3 | 1900000000000001 | [Columbia]套裝裙示例商品-1 | 8069751364 | 2 | 1 | 9 | 19 | 17 | 350.30 | 350.30 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | 2021-09-23 05:21:25 | 180 | | 2021-07-10 19:38:55 | 2021-07-10 19:38:56 | | 4 | 2000000000000001 | [李寧]棉麻連衣裙示例商品-1 | 1633719161 | 16 | 1 | 9 | 20 | 4 | 326.11 | 326.11 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | 2021-09-26 19:34:04 | 180 | | 2021-07-10 19:38:55 | 2021-07-10 19:38:56 | | 5 | 2100000000000001 | [LOWA]針織裙示例商品-1 | 3911261209 | 8 | 1 | 9 | 21 | 2 | 189.07 | 189.07 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | 2021-10-18 02:33:42 | 180 | | 2021-07-10 19:38:55 | 2021-07-10 19:38:56 | | 6 | 2200000000000001 | [Columbia]a字裙示例商品-1 | 7220463071 | 2 | 1 | 9 | 22 | 10 | 875.60 | 875.60 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | 2021-08-18 14:58:48 | 180 | | 2021-07-10 19:38:55 | 2021-07-10 19:38:56 | | 7 | 2300000000000001 | [Columbia]長裙示例商品-1 | 496033971 | 2 | 1 | 9 | 23 | 11 | 541.57 | 541.57 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | 2021-09-13 00:45:24 | 180 | | 2021-07-10 19:38:55 | 2021-07-10 19:38:56 | | 8 | 2400000000000001 | [北極狐]針織衫示例商品-1 | 1492949456 | 5 | 1 | 10 | 24 | 12 | 303.93 | 303.93 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | 2021-08-10 03:30:02 | 180 | | 2021-07-10 19:38:55 | 2021-07-10 19:38:56 | | 9 | 2500000000000001 | [NB]襯衫示例商品-1 | 3983564931 | 17 | 1 | 10 | 25 | 16 | 18.28 | 18.28 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | 2021-07-12 13:30:45 | 180 | | 2021-07-10 19:38:55 | 2021-07-10 19:38:56 | | 10 | 2600000000000001 | [LOWA]T恤示例商品-1 | 205288381 | 8 | 1 | 10 | 26 | 12 | 607.17 | 607.17 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | 2021-08-25 09:14:44 | 180 | | 2021-07-10 19:38:55 | 2021-07-10 19:38:56 | +------------+------------------+---------------------------------------+------------+----------+-----------------+-----------------+-------------------+-------------+--------+--------------+----------------+--------------+--------+--------+-------+-------+------------+---------------------+------------+----------+---------------------+---------------------+ 10 rows in set (0.10 sec)mysql>exit

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.92.101’ (113)

下一篇：企業(yè)實戰(zhàn)_12_MyCat水平擴展_分庫分表
https://gblfy.blog.csdn.net/article/details/100059793

總結(jié)

以上是生活随笔為你收集整理的企业实战_11_MyCat垂直拆分相关配置的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。