Mycat及MySQL執行個體部署情況:
Mycat(版本:5.6.29):
IP:192.168.227.6,Port:3310/3311
MySQL(版本:5.6.29) :注意MySQL版本最好5,6以上,剛開始yum安裝預設版本5.1.73版本總報錯
userHost1,IP:192.168.227.3,Port:3306
userHost2,IP:192.168.227.4,Port:3306
架構圖:
資料字典為全局表,2個庫表資料一樣
使用者表按UID%2來分庫表
使用者位址表按userID跟随使用者表
1,首先安裝mysql:
安裝節點:(192.168.227.3,192.168.227.4)
yum安裝MySQL,一定要注意安裝的版本,要是版本太低推薦源碼安裝
mysql安裝:
yum -y install mysql-server yum安裝MySQL
service mysqld start 啟動MySQL
chkconfig mysqld on 添加到開機啟動項
mysql 進入mysql用戶端
use mysql 切換庫
delete from user; 删除預設權限表
重新建立權限使用者
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
flush privileges; 重新整理權限
quit 退出
mysql -u root -p 登陸(帶使用者)
如果是centos7,直接安裝MySQL會報錯:
原因是:
CentOS7帶有MariaDB而不是MySQL,MariaDB和MySQL一樣也是開元的資料庫
解決方案:
如果必須要安裝MySQL,首先必須添加mysql社群repo通過輸入指令:sudo rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm,最後使用像安裝MySQL的正常方法一樣安裝mysql: yum install mysql mysql-server mysql-libs mysql-server
2,安裝Mycat
去Mycat官網下載下傳安裝包:http://www.mycat.io/ 選擇自己需要的版本
上傳到192.168.227.6伺服器上
tar -zxvf Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz #解壓
3,配置Mycat
server.xml:
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="useSqlStat">1</property>
<property name="useGlobleTableCheck">0</property>
<property name="defaultSqlParser">druidparser</property>
<property name="sequnceHandlerType">2</property>
<property name="processorBufferPoolType">0</property>
<property name="serverPort">3310</property> <!-- mycat的使用端口 -->
<property name="managerPort">3311</property> <!-- mycat的管理端口 -->
<property name="handleDistributedTransactions">0</property>
<property name="useOffHeapForMerge">1</property>
<property name="memoryPageSize">1m</property>
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<property name="systemReserveMemorySize">389m</property>
</system>
<user name="root"><!--登陸mycat使用者名-->
<property name="password">123456</property><!--登陸mycat密碼-->
<!--資料庫名,這裡會和schema.xml中的配置關聯,多個用逗号分開,例如需要這個使用者需要管理兩個資料庫db1,db2,則配置db1,dbs-->
<property name="schemas">db_user</property>
<property name="readOnly">false</property>
</user>
</mycat:server>
schema.xml:
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 資料庫配置,與server.xml中的資料庫對應 -->
<schema name="db_user" checkSQLschema="false" sqlMaxLimit="100">
<table name="data_dictionary" type="global" dataNode="db_user_dataNode1,db_user_dataNode2" primaryKey="dataDictionaryID"/>
<table name="users" dataNode="db_user_dataNode$1-2" rule="mod-userID-long" primaryKey="userID"> <!-- 對2取模,詳見rule.xml -->
<childTable name="user_address" joinKey="userID" parentKey="userID" primaryKey="addressID"/>
</table>
</schema>
<!-- 分片配置 -->
<!-- db_user -->
<dataNode name="db_user_dataNode1" dataHost="db_userHOST1" database="db_user" />
<dataNode name="db_user_dataNode2" dataHost="db_userHOST2" database="db_user" /> <!-- 配置db_user的節點主機 -->
<dataHost name="db_userHOST1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="userHost1" url="192.168.227.3:3306" user="root" password="password">
</writeHost>
</dataHost>
<dataHost name="db_userHOST2" 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="userHost2" url="192.168.227.4:3306" user="root" password="password">
</writeHost>
</dataHost>
</mycat:schema>
修改rule.xml中下列配置項:
<tableRule name="mod-userID-long">
<rule>
<columns>userID</columns> <!-- t1的分片列 -->
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property> <!-- count值與分片個數相同 -->
</function>
儲存 啟動mycat ./mycat start
db_user建表語句:
DROP TABLE IF EXISTS `data_dictionary`;
CREATE TABLE `data_dictionary` (
`dataDictionaryID` int(11) NOT NULL COMMENT '資料字典ID',
`displayName` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '顯示名稱',
`value` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '資料字典取值',
`createTime` datetime DEFAULT NULL COMMENT '建立時間',
`lastUpdate` datetime DEFAULT NULL COMMENT '最後更新時間',
PRIMARY KEY (`dataDictionaryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Table structure for user_address
-- ----------------------------
DROP TABLE IF EXISTS `user_address`;
CREATE TABLE `user_address` (
`addressID` int(11) NOT NULL COMMENT '位址ID',
`receiver` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '收貨人',
`addressDetail` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '位址詳細',
`userID` int(11) NOT NULL COMMENT '使用者ID',
`createTime` datetime DEFAULT NULL COMMENT '建立時間',
`lastUpdate` datetime DEFAULT NULL COMMENT '最後更新時間',
PRIMARY KEY (`addressID`,`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`userID` int(11) NOT NULL COMMENT '使用者ID',
`username` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '使用者名',
`phoneNum` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '手機号碼',
`age` int(11) DEFAULT NULL COMMENT '年齡',
`ddID` int(11) DEFAULT NULL COMMENT '所屬會員類型',
`createTime` datetime DEFAULT NULL COMMENT '注冊時間',
`lastUpdate` datetime DEFAULT NULL COMMENT '最後更新時間',
PRIMARY KEY (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;