MysQL读写分离服务器--maxscale服务
周正真
MysQL读写分离服务器–maxscale服务
构建思路：
1、部署MySQL一主一从结构一主服务器：192.168.1.11-从服务器：192.168.1.12
2、部署MysQL代理服务器192.168.1.13 -装包、修改配置文件、启动服务
3、测试配置-客户端192.168.1.14连接代理服务访问数据
[root@master ~]# mysql -uroot -p
mysql> alter user root@"localhost" identified by "qqq123...A";
192.168.1.11
[root@master ~]# vim /etc/my.cnf
[mysqld]
server-id=11
log-bin=master11
gtid_mode=ON
enforce-gtid-consistency=true
[root@master ~]# systemctl restart mysqld
[root@master ~]# mysql -uroot -pqqq123...A
mysql> grant replication slave on *.* to repluser@"%" identified by "qqq123...A";
mysql> flush privileges;
mysql> show master status;
+-----------------+----------+--------------+------------------+--------------
| File | Position |... | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+--------------
| master11.000001 | 593 | ...| 98ad3e05-971e-11ec-aa5c-000c29369f60:1-2 |
+-----------------+----------+--------------+------------------+--------------
192.168.1.12
[root@master ~]# vim /etc/my.cnf
[mysqld]
server-id=15
gtid_mode=ON
enforce-gtid-consistency=true
[root@slave ~]# systemctl restart mysqld
[root@slave ~]# mysql -uroot -pqqq123...A
mysql> change master to master_host="192.168.1.11",master_user="repluser", master_password="qqq123...A", MASTER_AUTO_POSITION=1;
mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Retrieved_Gtid_Set: ef82f680-9746-11ec-8a1d-000c296698dc:1-2
# slave 收到的信息
Executed_Gtid_Set: ef82f680-9746-11ec-8a1d-000c296698dc:1-2
#slave 已经执行的信息
Auto_Position: 1
配置读写分离服务器192.168.1.13，具体如下
1）安装软件部署maxscale服务
[root@max ~]# rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm
2）修改配置文件 主从ip 监控用户路由用户
[root@max ~]# vim /etc/maxscale.cnf
[maxscale] #定义线程个数
threads=auto #改auto（线程=核数）
[server1] #定义数据库服务器
type=server
address=192.168.1.11 #master主机ip地址
port=3306
protocol=MySQLBackend
[server2] #定义数据库服务器，server1-->5yy--> p
type=server
address=192.168.1.12 #slave主机ip地址
port=3306
protocol=MySQLBackend
###多从情况下可以继续定义server
[MySQL Monitor] #定义要监视的数据库节点定义要监视的数据库节点
type=monitor
module=mysqlmon
servers=server1,server2 #主、从数据库的主机名
user=mysqla #监控用户3306主从
passwd=qqq123...A #密码
monitor_interval=10000
#[Read-Only Service] #注释掉
#type=service
#router=readconnroute
#servers=server1
#user=myuser
#passwd=mypwd
#router_options=slave
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2
user=mysqlb #路由用户，max用mysqlb访问server1、2的mysql.user里的授权账户
passwd=qqq123...A
max_slave_connections=100%
[MaxAdmin Service]
type=service
router=cli
#[Read-Only Listener]
#type=listener
#service=Read-Only Service
#protocol=MySQLClient
#port=4008
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006 #读写分离的端口
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=4016 #管理的端口
3）启动读写分离服务 maxscale -f /etc/maxscale.cnf
master
[root@master ~]# mysql -uroot -pqqq123...A
#创建监控主从服务器用户mysqla 获取主从角色，读取数据库状态的权限
mysql> grant replication slave, replication client on *.* to mysqla@"%" identified by "qqq123...A";
#创建路由用户mysqlb
mysql> grant select on mysql.* to mysqlb@"%" identified by "qqq123...A";
slave
mysql> select user from mysql.user where user in ("mysqla","mysqlb");
+--------+
| user |
+--------+
| mysqla |
| mysqlb |
+--------+
在max主机验证用户
[root@max ~]# which mysql
/usr/bin/which: no mysql in ...
[root@max ~]# yum -y install mariadb
[root@max ~]# mysql -umysqla -h192.168.1.11 -pqqq123...A
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
[root@max ~]# mysql -umysqlb -h192.168.1.11 -pqqq123...A
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
启动服务
[root@max ~]# maxscale -f /etc/maxscale.cnf
[root@max ~]# ss -ntulp | grep -E "4006|4016"
tcp LISTEN 0 128 [::]:4006 [::]:* users:(("maxscale",pid=1222,fd=11))
tcp LISTEN 0 128 [::]:4016 [::]:* users:(("maxscale",pid=1222,fd=12))
#排错日志
[root@max ~]# ls /var/log/maxscale/maxscale.log
[root@max ~]# pkill -9 maxscale 关掉进程
[root@max ~]# maxscale -f /etc/maxscale.cnf 启动服务
3、测试配置
1）查看监控信息（在max 本机自己访问自己）
[root@max ~]# maxadmin -uadmin -pmariadb -P 4016
#内置的用户admin与密码mariadb
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 192.168.1.11 | 3306 | 0 | Master, Running
server2 | 192.168.1.12 | 3306 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
如果server的状态是down,那么server可能是授权有问题，监控不到，或者down，或者防护墙问题
2）客户测试配置
在主服务器添加用户，给客户连接使用（从数据库服务器会自动同步添加的用户）
master
mysql> create database testdb;
mysql> create table testdb.user(id int,name char(10));
mysql> grant select,insert on testdb.*to testuser@"%" identified by "qqq123...A";
slave
mysql> desc testdb.user;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> select user from mysql.user ;
+-----------+
| user |
+-----------+
| testuser |
在客户端client连接读写分离服务器max访问数据
[root@client ~]# mysql -h192.168.1.13 -P4006 -utestuser -pqqq123...A
MySQL [(none)]> show grants;
+------------------------------------------------------+
| Grants for testuser@% |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%' |
| GRANT SELECT, INSERT ON `testdb`.* TO 'testuser'@'%' |
+------------------------------------------------------+
MySQL [(none)]> insert into testdb.user values(1,"tom");
MySQL [(none)]> select * from testdb.user;
+------+------+
| id | name |
+------+------+
| 1 | tom |
+------+------+
3）验证读写分离操作如下：
写分离，从库跟随主库Gtid
mysql> show master status;
+-----------------+----------+--------------+------------------+---------------
| File | Position | ...| Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+---------------
| master11.000002 | 1661 | ...| 98ad3e05-971e-11ec-aa5c-000c29369f60:1-10 |
+-----------------+----------+--------------+------------------+---------------
mysql> show slave status\G
Retrieved_Gtid_Set: 98ad3e05-971e-11ec-aa5c-000c29369f60:1-10
Executed_Gtid_Set: 98ad3e05-971e-11ec-aa5c-000c29369f60:1-10
读分离，从服务器上添加数据，client 能查到，主库没有
[root@slave ~]# mysql -uroot -pqqq123...A
mysql> insert into testdb.user values(2,"john");
[root@client ~]# mysql -h192.168.1.13 -P4006 -utestuser -pqqq123...A
MySQL [(none)]> select * from testdb.user;
+------+-------+
| id | name |
+------+-------+
| 1 | tom |
| 2 | john |
+------+-------+
[root@master ~]# mysql -uroot -pqqq123...A
mysql> select * from testdb.user;
+------+-------+
| id | name |
+------+-------+
| 1 | tom |
+------+-------+
MyCAT服务分库/分表
一、什么是分库分表 按照特定方式，分散存放到多台数据库服务器中
将存放在一台数据库服务器中的数据，按照特定方式进行拆分，分散存放到多台数据库服务器中，以达到分散单台服务器负载的效果
水平分割横向切分
按照表中指定字段的分片规则，将表记录按行切分，分散存储到多个数据库中。按照表中指定字段的分片规则，将表记录按行切分，分散存储到多个数据库中。
垂直分割纵向切分
将单个数据库的多个表按业务类型分类，分散存储到不同的数据库。
二、mycat软件的介绍
基于Java的分布式数据库系统中间件，为高并发环境的分布式存储提供解决方案
适合数据大量写入的存储需求
支持MySQL、Oracle、Sqlserver、Mongodb等
提供数据读写分离服务
提供数据分片服务
基于阿里巴巴Cobar进行研发的开源软件
1、分片规则分片规则
mycat支持提供10种分片规则
1、枚举法sharding-by-intfile
2、固定分片rule
3、范围约定auto-sharding-long
4、求模法mod-long
5、日期列分区法sharding-by-date
6、通配取模 sharding-by-pattern
7、ASClI码求模通配 sharding-by-prefixpattern
8、编程指定sharding-by-substring
9、字符串拆分hash解析 sharding-by-stringhash
10、一致性hash sharding-by-murmur
2、工作过程
当mycat收到一个SQL命令时：insert into 表（字段名）values（值)；
a.解析SQL命令涉及到的表
b.然后看对表的配置，如果有分片规则，则获取SQL命令里分片字段的值，并匹配分片函数，获得分片列表
c.然后将SQL命令发往对应的数据库服务器去执行
d.最后收集和处理所有分片结果数据，并返回到客户端
二、配置mycat服务
192.168.1.14
192.168.1.11 db1
192.168.1.12 db2
192.168.1.13 db3
192.168.1.14 mycat 大于1G内存
192.168.1.15 client
1、安装软件 java Mycat
xml：扩展标记语言,配置项要放在标签里有单、双标签，注释 >
[root@mycat ~]# yum -y install java-1.8.0-openjdk.x86_64
[root@mycat ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat ~]# mv mycat/ /usr/local/
[root@mycat ~]# ls /usr/local/mycat/
bin catlet conf lib logs version.txt
bin #mycat命令
catlet #扩展功能
conf #配置文件，xml lib #mycat使用的jar包
logs #mycat启动日志和运行日志
wrapper.log #记录SQL脚本执行后的报错内容
mycat.log #mycat服务启动日志
version.txt
[root@mycat ~]# ls /usr/local/mycat/conf
server.xml #设置连接账号及逻辑库
schema.xml #配置数据分片存储的表
rule.xml #分片规则,其他文件---分片规则配置文件
2、配置server.xml连接mycat服务使用的用户名、密码、逻辑库
使用默认配置,不用修改不用修改
<user name="用户名"> #连mycat服务用户名
<property name="password">密码</property> #用户密码
<property name="schemas">库名</property> #逻辑库
</user>
<user name="admin">
<property name="password">123456</property> <property name="schemas">TESTDB</property> <property name="readOnly">true</property> #只读权限
</user>
3、配置schema.xml 分片存储数据的表
分片储存表介绍
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema选项> #定义数据分片存储的表名
<table选项/><table选项〉..…</table> #定义表名
</schema>
<dataNode 选项=值，……/> #定义数据库服务器的名称
<datahost 选项=值，….……</datahost> #定义数据库服务器的ip地址
</mycat:schema>
[root@mycat ~]# sed -i '56,77d' /usr/local/mycat/conf/schema.xml
[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml
1）定义数据分片存储的表名
5 <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> #暂未修改
2）定义表名11，12，14，19，21 #dataNode可以多个，保持一致
11 <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
12 <table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
14 <table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
19 <table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
21 <table name="customer" primaryKey="ID" dataNode="dn1,dn2,dn3"
3）定义数据库名
36 <dataNode name="dn1" dataHost="myslq11" database="db1" /> #db1需建库
37 <dataNode name="dn2" dataHost="mysql12" database="db2" />
38 <dataNode name="dn3" dataHost="mysql13" database="db3" />
#38 <dataNode name="dn4" dataHost="mysql14" database="db4" />
#如有更多的库可继续添加，需要前面dn1数量一致，mysql11，命名自定义。db1库都需建库。
4）定义数据库服务器的ip地址
43以下
43 <dataHost name="mysql11" maxCon="1000" minCon="10" balance="0"
44 writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
45 <heartbeat>select user()</heartbeat>
46 <writeHost host="hostM1" url="192.168.1.11:3306" user="pljadmin"
47 password="qqq123...A">
48 </writeHost>
49 </dataHost>
###修改、删除保留如下,一个主机一个<dataHost>组##
50 <dataHost name="mysql12" maxCon="1000" minCon="10" balance="0"
51 writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
52 <heartbeat>select user()</heartbeat>
53 <writeHost host="hostM2" url="192.168.1.12:3306" user="pljadmin"
54 password="qqq123...A">
55 </writeHost>
56 </dataHost>
57 <dataHost name="mysql13" maxCon="1000" minCon="10" balance="0"
58 writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
59 <heartbeat>select user()</heartbeat>
60 <writeHost host="hostM3" url="192.168.1.13:3306" user="pljadmin"
61 password="qqq123...A">
62 </writeHost>
63 </dataHost>
64 </mycat:schema>
完整配置
1 <?xml version="1.0"?>
2 <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
3 <mycat:schema xmlns:mycat="http://io.mycat/">
4
5 <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
6 <!-- auto sharding by id (long) -->
7 <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
8
9 <!-- global table is auto cloned to all defined data nodes ,so can join
10 with any table whose sharding node is in the same data node -->
11 <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
12 <table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
13 <!-- random sharding using mod sharind rule -->
14 <table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
15 rule="mod-long" />
16 <!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
17 needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3"
18 rule="mod-long" /> -->
19 <table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
20 rule="sharding-by-intfile" />
21 <table name="customer" primaryKey="ID" dataNode="dn1,dn2,dn3"
22 rule="sharding-by-intfile">
23 <childTable name="orders" primaryKey="ID" joinKey="customer_id"
24 parentKey="id">
25 <childTable name="order_items" joinKey="order_id"
26 parentKey="id" />
27 </childTable>
28 <childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
29 parentKey="id" />
30 </table>
31 <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
32 /> -->
33 </schema>
34 <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
35 /> -->
36 <dataNode name="dn1" dataHost="myslq11" database="db1" />
37 <dataNode name="dn2" dataHost="myslq12" database="db2" />
38 <dataNode name="dn3" dataHost="myslq13" database="db3" />
39 <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
40 <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
41 <dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />
42 <dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> -->
43 <dataHost name="myslq11" maxCon="1000" minCon="10" balance="0"
44 writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
45 <heartbeat>select user()</heartbeat>
46 <writeHost host="hostM1" url="192.168.1.11:3306" user="mycatadmin"
47 password="qqq123...A">
48 </writeHost>
49 </dataHost>
50 <dataHost name="myslq12" maxCon="1000" minCon="10" balance="0"
51 writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
52 <heartbeat>select user()</heartbeat>
53 <writeHost host="hostM2" url="192.168.1.12:3306" user="mycatadmin"
54 password="qqq123...A">
55 </writeHost>
56 </dataHost>
57 <dataHost name="myslq13" maxCon="1000" minCon="10" balance="0"
58 writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
59 <heartbeat>select user()</heartbeat>
60 <writeHost host="hostM3" url="192.168.1.13:3306" user="mycatadmin"
61 password="qqq123...A">
62 </writeHost>
63 </dataHost>
64 </mycat:schema>
启动前配置
[root@db1 ~]# mysql -uroot -p123456 -e "create database db1"
[root@db1 ~]# mysql -uroot -p123456 -e 'grant all on *.* to mycatadmin@"%" identified by"qqq123...A"'
[root@db2 ~]# mysql -uroot -p123456 -e "create database db2"
[root@db2 ~]# mysql -uroot -p123456 -e 'grant all on *.* to mycatadmin@"%" identified by"qqq123...A"'
[root@db3 ~]# mysql -uroot -p123456 -e "create database db3"
[root@db3 ~]# mysql -uroot -p123456 -e 'grant all on *.* to mycatadmin@"%" identified by"qqq123...A"'
[root@mycat ~]# /usr/local/mycat/bin/mycat start
[root@mycat ~]# ls /usr/local/mycat/logs/
mycat.log mycat.pid wrapper.log
[root@mycat ~]# ss -utnlp | grep 8066
tcp LISTEN 0 100 [::]:8066 [::]:*
#这里增加了一行配置/usr/local/mycat/conf/wrapper.conf
wrapper.startup.timeout=300
三、枚举分片规则使用sharding-by-intfil
1、employee枚举分片建表配置
一字段值必须在规则文件定义的值里选择
表头的值 必须在 规则文件定义 的值里 选择
由schema看需要建立的表，由表看分片规则。
由rule里的规则查看表头规则、函数算法文件位置，修改算法文件，定义枚举的服务器
1）由schema.xml查使用枚举法sharding-by-intfile的表，建表规则
[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml
19 <table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
20 rule="sharding-by-intfile" /> #employee表，主键，枚举规则
2）由rule.xml查看所需表字段、规则文件位置
[root@mycat ~]# vim /usr/local/mycat/conf/rule.xml
...
26 <tableRule name="sharding-by-intfile">
27 <rule>
28 <columns>sharding_id</columns> #表头段，可自定义
29 <algorithm>hash-int</algorithm> #算法名
30 </rule>
31 </tableRule>
...
97 <function name="hash-int"
98 class="io.mycat.route.function.PartitionByFileMap">
99 <property name="mapFile">partition-hash-int.txt</property>
#####定义表头值的规则文件，表头的值必须在规则文件定义的值里选择
100 </function>
3）修改规则文件表头的枚举值
[root@mycat ~]# vim /usr/local/mycat/conf/partition-hash-int.txt
#定义表头的值 表头的值=第几台数据库服务器0-->dn1,1-->dn2
10000=0
10010=1
10020=2
2、重启服务，插入表信息
[root@mycat ~]# /usr/local/mycat/bin/mycat stop
[root@mycat ~]# /usr/local/mycat/bin/mycat start
[root@mycat ~]# mysql -h192.168.1.14 -P8066 -uroot -p123456
MySQL [(none)]> use TESTDB; #区分大小写，同schema配置第五行
MySQL [TESTDB]> show tables; #这里的表在schema配置的TESTDB库内，12行...
+------------------+
| Tables in TESTDB |
+------------------+
| company |
| customer |
| customer_addr |
| employee |
| goods |
| hotnews |
| orders |
| order_items |
| travelrecord |
+------------------+
MySQL [TESTDB]> create table employee( ID int primary key, sharding_id int, name char(15), age int,home char(30));
#必须创建的前两个表字段ID int primary key, sharding_id
MySQL [TESTDB]> insert into employee(ID,sharding_id,name,age,home) values(2,10000,"aaa",29,"home10000");
MySQL [TESTDB]> insert into employee(ID,sharding_id,name,age) values(3,10010,"bbb",29);
MySQL [TESTDB]> insert into employee(ID,sharding_id,name,age) values(4,10020,"ccc",29);
#插入的数据sharding_id，必须为配置里的10000,10010,10020
3、查看是否分片存储
[root@db1 ~]# mysql -uroot -p123456 -e 'select * from db1.employee'
+----+-------------+------+------+-----------+
| ID | sharding_id | name | age | home |
+----+-------------+------+------+-----------+
| 2 | 10000 | aaa | 29 | home10000 |
+----+-------------+------+------+-----------+
[root@db2 ~]# mysql -uroot -p123456 -e 'select * from db2.employee'
+----+-------------+------+------+------+
| ID | sharding_id | name | age | home |
+----+-------------+------+------+------+
| 3 | 10010 | bbb | 29 | NULL |
+----+-------------+------+------+------+
[root@db3 ~]# mysql -uroot -p123456 -e 'select * from db3.employee'
+----+-------------+------+------+------+
| ID | sharding_id | name | age | home |
+----+-------------+------+------+------+
| 4 | 10020 | ccc | 29 | NULL |
+----+-------------+------+------+------+
四、求模分片规则mod-long
求模法-根据字段值与设定的数字求模结果存储数据
注意：根据表使用的分片规则建表，必须创建分片规则要求的表头。
根据表头值与设定的数字，取余的结果存储数据
1、hotnews求模分片建表配置
1）由schema.xml查看修改rule=“mod-long”
14 <table name="hotnews" dataNode="dn1,dn2,dn3"
#修改：主键=id与rule.xml里的id冲突去掉，删除自增长，无作用
15 rule="mod-long" />
2）由rule.xml查看表头算法 求模值
[root@mycat ~]# vim /usr/local/mycat/conf/rule.xml
38 <tableRule name="mod-long">
39 <rule>
40 <columns>id</columns>
41 <algorithm>mod-long</algorithm>
42 </rule>
43 </tableRule>
...
105 <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
106 <!-- how many data nodes -->
107 <property name="count">3</property>
#与count后的数字3取余，等于数据库服务器的台数,0-->dn1,1-->dn2,2-->dn2
[root@mycat ~]# /usr/local/mycat/bin/mycat restart #重启需等待
[root@mycat ~]# mysql -h192.168.1.14 -P8066 -uroot -p123456
MySQL [(none)]> use TESTDB;
MySQL [TESTDB]> create table hotnews (id int, name char(10), home char(20));
MySQL [TESTDB]> insert into hotnews(id,name,home) values(7,"aaa","AAA"),(7,"aaa","AAA");
MySQL [TESTDB]> insert into hotnews(id,name,home) values (8,"aaa","8AA"),(8,"8bb","BBB");
MySQL [TESTDB]> insert into hotnews(id,name,home) values (9,"aaa","9AA"),(9,"9bb","BBB");
验证
[root@db1 ~]# mysql -uroot -p123456 -e 'select * from db1.hotnews'
+------+------+------+
| id | name | home |
+------+------+------+
| 9 | aaa | 9AA |
| 9 | 9bb | BBB |
+------+------+------+
[root@db2 ~]# mysql -uroot -p123456 -e 'select * from db2.hotnews'
+------+------+------+
| id | name | home |
+------+------+------+
| 7 | aaa | AAA |
| 7 | aaa | AAA |
+------+------+------+
[root@db3 ~]# mysql -uroot -p123456 -e 'select * from db3.hotnews'
+------+------+------+
| id | name | home |
+------+------+------+
| 8 | aaa | 8AA |
| 8 | 8bb | BBB |
+------+------+------+
五、添加新库新表
1、添加新库名server.xml
此处添加多个库TESTDB,GAMEDB，多库名逗号隔开82,97
80 <user name="root">
81 <property name="password">123456</property>
82 <property name="schemas">TESTDB,GAMEDB</property>
...
95 <user name="user">
96 <property name="password">user</property>
97 <property name="schemas">TESTDB,GAMEDB</property>
2、添加新表名schema.xml
（不能和已有表名重名，添加一对儿<schema）配置严格区分大小写
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="GAMEDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="user" dataNode="dn1,dn2,dn3" rule="mod-long" />
<table name="tea" dataNode="dn1,dn2,dn3" type="global" />
##type=global，全局写入，不受rule的限制，建表无要求。三台机器都会写入
</schema>
<schema name="TESTDB"...
3、重启mycat服务、建库建表测试
[root@mycat ~]# /usr/local/mycat/bin/mycat restart #重启需等待
[root@mycat ~]# mysql -h192.168.1.14 -P8066 -uroot -p123456
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| GAMEDB |
| TESTDB |
+----------+
MySQL [(none)]> use GAMEDB;
MySQL [GAMEDB]> show tables;
+------------------+
| Tables in GAMEDB |
+------------------+
| tea |
| user |
+------------------+
##其他建表验证同上面实验
类似资料：