系統對外提供的數據庫名是UserCenter,並且其中有一張表u_user
該表的一部分數據被映射到物理數據庫alicobar_test_master的u_user上,另外一部分數據被映射到物理數據庫alicobar_test_shard的u_user上。
OS:CentOS6.4 64位
DB:MySQL5.6.22
JDK:1.7
Cobar:1.2.7
MySQL-master 192.168.89.4 端口3306 用戶名:alicobar 密碼:alicobarpwd123
MySQL-backup 192.168.89.5 端口3306 用戶名:alicobar 密碼:alicobarpwd123
cobar-server 192.168.66.89
schema:alicobar_test_master、alicobar_test_shard,table:u_user 腳本如下:
CREATE DATABASE /*!32312 IF NOT EXISTS*/`alicobar_test_master` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `alicobar_test_master`; /*Table structure for table `u_user` */ CREATE TABLE `u_user` ( `code` bigint(19) NOT NULL, `mobile` varchar(32) DEFAULT NULL, `email` varchar(64) DEFAULT NULL, `nickname` varchar(64) DEFAULT NULL, `password` varchar(32) NOT NULL, `create_time` datetime NOT NULL, `modify_time` datetime DEFAULT NULL, `status` int(10) NOT NULL COMMENT '用戶狀態:1 = 啟用;2 = 禁用;... ...', PRIMARY KEY (`code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='--用戶信息'
CREATE DATABASE /*!32312 IF NOT EXISTS*/`alicobar_test_shard` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `alicobar_test_shard`; /*Table structure for table `u_user` */ CREATE TABLE `u_user` ( `code` bigint(19) NOT NULL, `mobile` varchar(32) DEFAULT NULL, `email` varchar(64) DEFAULT NULL, `nickname` varchar(64) DEFAULT NULL, `password` varchar(32) NOT NULL, `create_time` datetime NOT NULL, `modify_time` datetime DEFAULT NULL, `status` int(10) NOT NULL COMMENT '用戶狀態:1 = 啟用;2 = 禁用;... ...', PRIMARY KEY (`code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='--用戶信息';
MySQL主主同步就不再這裡詳細說明了,可以參考官網手冊
1.安裝JDK配置JAVA環境變量JAVA_HOME
下載JDK1.7 http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html
安裝過程就不說了,下面簡單說下安裝後配置
vi /etc/profile 在文本末尾添加
export JAVA_HOME=/usr/java/jdk1.7.0_75
export JRE_HOME=/usr/java/jdk1.7.0_75/jre
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
export PATH=$JAVA_HOME/bin:$PATH
添加java_home環境變量後,使profile立即生效
source /etc/profile
2.安裝cobar
下載地址:https://github.com/alibaba/cobar/wiki
下載Cobar壓縮文件並解壓,進入conf目錄可以看到schema.xml, rule.xml, server.xml等相關的配置文件
schema.xml配置如下(注意:schema.xml包含MySQL的IP、端口、用戶名、密碼等配置,您需要按照注釋替換為您的MySQL信息。)
[root@localhost conf]# more schema.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE cobar:schema SYSTEM "schema.dtd"> <cobar:schema xmlns:cobar="http://cobar.alibaba.com/"> <!-- schema定義 --> <schema name="UserCenter" dataNode="node1"> <table name="u_user" dataNode="node1,node2" rule="rule1" /> </schema> <!-- 數據節點定義,數據節點由數據源和其他一些參數組織而成。--> <dataNode name="node1"> <property name="dataSource"> <dataSourceRef>source-master[0]</dataSourceRef> <dataSourceRef>source-backup[0]</dataSourceRef> </property> <!--Cobar與後端數據源連接池大小設置--> <property name="poolSize">256</property> <!--Cobar通過心跳來實現後端數據源HA,一旦主數據源心跳失敗,便切換到備數據源上工作--> <!--Cobar心跳是通過向後端數據源執行一條SQL語句,根據該語句的返回結果判斷數據源的運行情況--> <property name="heartbeatSQL">select user()</property> </dataNode> <dataNode name="node2"> <property name="dataSource"> <dataSourceRef>source-master[1]</dataSourceRef> <dataSourceRef>source-backup[1]</dataSourceRef> </property> <!--Cobar與後端數據源連接池大小設置--> <property name="poolSize">256</property> <!--Cobar通過心跳來實現後端數據源HA,一旦主數據源心跳失敗,便切換到備數據源上工作--> <!--Cobar心跳是通過向後端數據源執行一條SQL語句,根據該語句的返回結果判斷數據源的運行情況--> <property name="heartbeatSQL">select user()</property> </dataNode> <!-- 數據源定義,數據源是一個具體的後端數據連接的表示。--> <dataSource name="source-master" type="mysql"> <property name="location"> <location>192.168.89.4:3306/alicobar_test_master</location> <!--注意:替換為您的MySQL IP和Port--> <location>192.168.89.4:3306/alicobar_test_shard</location> </property> <property name="user">alicobar</property> <!--注意:替換為您的MySQL用戶名--> <property name="password">alicobarpwd123</property> <!--注意:替換為您的MySQL密碼--> <property name="sqlMode">STRICT_TRANS_TABLES</property> </dataSource> <dataSource name="source-backup" type="mysql"> <property name="location"> <location>192.168.89.5:3306/alicobar_test_master</location> <location>192.168.89.5:3306/alicobar_test_shard</location> </property> <property name="user">alicobar</property> <property name="password">alicobarpwd123</property> <property name="sqlMode">STRICT_TRANS_TABLES</property> </dataSource> </cobar:schema>
rule.xml配置如下(本文以數字類型的code字段作為拆分字段,將數據拆分到兩個庫中。)
more rule.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE cobar:rule SYSTEM "rule.dtd"> <cobar:rule xmlns:cobar="http://cobar.alibaba.com/"> <!-- 路由規則定義,定義什麼表,什麼字段,采用什麼路由算法 --> <tableRule name="rule1"> <rule> <columns>code</columns> <algorithm><![CDATA[ func1(${code}) ]]></algorithm> </rule> </tableRule> <!-- 路由函數定義 --> <function name="func1" class="com.alibaba.cobar.route.function.PartitionByLong"> <property name="partitionCount">2</property> <property name="partitionLength">512</property> </function> </cobar:rule>
server.xml配置如下
more server.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE cobar:server SYSTEM "server.dtd"> <cobar:server xmlns:cobar="http://cobar.alibaba.com/"> <!-- 系統參數定義,服務端口、管理端口,處理器個數、線程池等。 --> <!-- <system> <property name="serverPort">8066</property> <property name="managerPort">9066</property> <property name="initExecutor">16</property> <property name="timerExecutor">4</property> <property name="managerExecutor">4</property> <property name="processors">4</property> <property name="processorHandler">8</property> <property name="processorExecutor">8</property> <property name="clusterHeartbeatUser">_HEARTBEAT_USER_</property> <property name="clusterHeartbeatPass">_HEARTBEAT_PASS_</property> </system> --> <!-- 用戶訪問定義,用戶名、密碼、schema等信息。 --> <user name="test"> <property name="password">test</property> <property name="schemas">UserCenter</property> </user> <!-- <user name="root"> <property name="password"></property> </user> --> <!-- 集群列表定義,指定集群節點的主機和權重,用於集群間的心跳和客戶端負載均衡。 --> <!-- <cluster> <node name="cobar1"> <property name="host">127.0.0.1</property> <property name="weight">1</property> </node> </cluster> --> <!-- 隔離區定義,可以限定某個主機上只允許某個用戶登錄。 --> <!-- <quarantine> <host name="1.2.3.4"> <property name="user">test</property> </host> </quarantine> --> </cobar:server>
1.啟動Cobar,進入bin目錄可以看到Cobar的啟動、停止與重啟腳本
報沒有log目錄,我們新建一個,再次啟動
查看logs目錄下stdout.log, 啟動成功日志如下
[root@localhost logs]# more stdout.log
17:31:10,939 INFO ===============================================
17:31:10,940 INFO Cobar is ready to startup ...
17:31:10,940 INFO Startup processors ...
17:31:10,974 INFO Startup connector ...
17:31:10,975 INFO Initialize dataNodes ...
17:31:11,140 INFO node1:0 init success
17:31:11,142 INFO node2:0 init success
17:31:11,150 INFO CobarManager is started and listening on 9066
17:31:11,152 INFO CobarServer is started and listening on 8066
17:31:11,152 INFO ===============================================
訪問Cobar同訪問MySQL的方式完全相同
常用訪問方式如下(注意:本文將Cobar部署在192.168.66.89這台機器上,否則請替換為您的Cobar所在IP,其他信息不變)
SQL執行語句時與使用傳統單一數據庫無區別
[root@localhost cobar-server-1.2.7]# mysql -h192.168.66.89 -utest -ptest -P8066 -DUserCenter
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 5 Server version: 5.1.48-cobar-1.2.7 Cobar Server (ALIBABA) Copyright (c) 2000, 2014, 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 | +------------+ | UserCenter | +------------+ 1 row in set (0.00 sec) mysql> show tables; +----------------------+ | Tables_in_UserCenter | +----------------------+ | u_user | +----------------------+ 1 row in set (0.05 sec) mysql>
至此配置完成!