安装部署 MySQL
·
1 部署两个独立的数据库
搭建步骤省略…
注意一: Linux 下部署安装 MySQL,默认不忽略表名大小写,需要手动到 /etc/my.cnf 下配置 lower_case_table_names=1
使 Linux 环境下 MySQL 忽略表名大小写,否则使用 MyCAT 的时候会提示找不到表的错误。
注意二: 在测试过程中,MySQL 设置了指定服务端字符集的排序规则(collation-server),结果或导致 Mycat 报错。
·
2 创建测试库
CREATE DATABASE mycat_testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
·
3 创建用户并授权
grant all on mycat_testdb.* to mycat_user@"%" identified by "111111";
·
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)
所以,在进行垂直分库的时候,应该把有关联的表切分在一个库中。