安裝部署 MySQL
·
1 部署兩個獨立的資料庫
搭建步驟省略…
注意一: Linux 下部署安裝 MySQL,預設不忽略表名大小寫,需要手動到 /etc/my.cnf 下配置
lower_case_table_names=1
使 Linux 環境下 MySQL 忽略表名大小寫,否則使用 MyCAT 的時候會提示找不到表的錯誤。
注意二: 在測試過程中,MySQL 設定了指定服務端字元集的排序規則(collation-server),結果或導緻 Mycat 報錯。
·
2 建立測試庫
·
3 建立使用者并授權
·
4 連接配接測試
mysql -umycat_user -p111111 -h 10.0.30.21
mysql -umycat_user -p111111 -h 10.0.30.22
·
安裝部署 Mycat
·
1 JAVA 環境配置
PS: 必須 JDK7 或更高版本。
tar -xvf jdk-8u281-linux-x64.tar.gz -C /usr/local/
ln -vs /usr/local/jdk1.8.0_281/bin/java* /usr/bin/
ln -vs /usr/local/jdk1.8.0_281/bin/jps /usr/bin/
·
2 下載下傳&解壓二進制包
tar -xvf Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz -C /usr/local/
·
3 聲明環境變量
cat << EOF > /etc/profile.d/mycat.sh
export MYCAT_HOME=/usr/local/mycat
EOF
source /etc/profile
·
4 修改日志格式
cd /usr/local/mycat
vim conf/log4j.xml
#
......
#日志存放路徑
<param name="file" value="${MYCAT_HOME}/logs/mycat.log" />
......
#日志等級(生成環境下建議将為 info/ware,如果是研究測試,可以開啟debug 模式)
<root>
<level value="debug" />
<appender-ref ref="ConsoleAppender" />
</root>
......
·
5 修改 schema.xml 配置檔案
cd /usr/local/mycat
cp -p conf/schema.xml{,.default}
vim conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
<schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="table22" dataNode="dn2" ></table>
</schema>
<dataNode name="dn1" dataHost="wpf-test21" database="mycat_testdb"/>
<dataNode name="dn2" dataHost="wpf-test22" database="mycat_testdb"/>
<dataHost name="wpf-test21" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="testM1" url="10.0.30.21:3306" user="mycat_user" password="111111"/>
</dataHost>
<dataHost name="wpf-test22" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="testM2" url="10.0.30.22:3306" user="mycat_user" password="111111"/>
</dataHost>
</mycat:schema>
·
6 修改 server.xml 配置檔案
cd /usr/local/mycat
cp -p conf/server.xml{,.default}
vim conf/server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
<system>
<property name="defaultSqlParser">druidparser</property>
<property name="charset">utf8mb4</property>
</system>
<user name="mycat">
<property name="password">123456</property>
<property name="schemas">mycat_testdb</property>
</user>
</mycat:server>
·
7 啟動 Mycat
#mycat 支援的指令{ console | start | stop | restart | status | dump }
cd /usr/local/mycat
./bin/mycat start
·
測試 Mycat
·
1 連接配接 mycat
mysql -h10.0.30.20 -umycat -p123456 -P 8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.8-mycat-1.5.1-RELEASE-20161130213509 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------+
| DATABASE |
+--------------+
| mycat_testdb |
+--------------+
1 row in set (0.01 sec)
·
2 建立表&寫入資料
#第一個表:table111
MySQL [(none)]> use mycat_testdb;
Database changed
MySQL [mycat_testdb]> create table table111(id int,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
MySQL [mycat_testdb]> insert into table111 values(1,"111");
Query OK, 1 row affected (0.02 sec)
#第二個表:table22
MySQL [mycat_testdb]> create table table22(id int,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
MySQL [mycat_testdb]> insert into table22 values(1,"22");
Query OK, 1 row affected (0.01 sec)
#第三個表:table3
MySQL [mycat_testdb]> create table table3(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
MySQL [mycat_testdb]> insert into table3 values(1,"3");
Query OK, 1 row affected (0.00 sec)
·
3 驗證分庫是否成功
#在第一個實體庫上進行檢視
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mycat_testdb |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> use mycat_testdb;
Database changed
MariaDB [mycat_testdb]> show tables;
+------------------------+
| Tables_in_mycat_testdb |
+------------------------+
| table111 |
| table3 |
+------------------------+
2 rows in set (0.00 sec)
#在第二個實體庫上進行檢視
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mycat_testdb |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> use mycat_testdb;
Database changed
MariaDB [mycat_testdb]> show tables;
+------------------------+
| Tables_in_mycat_testdb |
+------------------------+
| table22 |
+------------------------+
1 row in set (0.00 sec)
至此,可以看到從 Mycat 建立的表被切分到了兩個具體的實體庫上,說明垂直切分成功。
·
注意: 垂直分庫之後,雖然在 Mycat 連接配接中可以看到所有的表,兩個庫裡面的表,并且查詢單獨的表資料都正常。但是,不能對兩個庫之間的表進行關聯查詢。如下所示:
MySQL [mycat_testdb]> show tables;
+------------------------+
| Tables_in_mycat_testdb |
+------------------------+
| table22 |
| table111 |
| table3 |
+------------------------+
3 rows in set (0.00 sec)
MySQL [mycat_testdb]> select * from table111;
+------+------+
| id | name |
+------+------+
| 1 | 111 |
+------+------+
1 row in set (0.00 sec)
MySQL [mycat_testdb]> select * from table22;
+------+------+
| id | name |
+------+------+
| 1 | 22 |
+------+------+
1 row in set (0.00 sec)
MySQL [mycat_testdb]> select * from table3;
+------+------+
| id | name |
+------+------+
| 1 | 3 |
+------+------+
1 row in set (0.00 sec)
MySQL [mycat_testdb]> select * from table22 a,table111 b where a.id=b.id;
ERROR 1146 (42S02): Table 'mycat_testdb.table22' doesn't exist
MySQL [mycat_testdb]> select * from table3 a,table111 b where a.id=b.id;
+------+------+------+------+
| id | name | id | name |
+------+------+------+------+
| 1 | 3 | 1 | 111 |
+------+------+------+------+
1 row in set (0.00 sec)
是以,在進行垂直分庫的時候,應該把有關聯的表切分在一個庫中。