mysql架构篇之主从结构之~Mycat-分库分表

Mycat软件：

什么是分库分表

水平分割：

垂直分隔

软件介绍：

mycat是基于Java的分布式数据库系统中间件，为高并发环境的分布式存储提供解决方案

　　--适合数据大量写入的存储需求

　　--支持MySQL、Oracle、Sqlserver、Mongodb...

   　--提供数据读写分离服务

　　--提供数据分片服务

　　--基于阿里巴巴Cobar进行研发的开源软件

分片规则

　　mycat支持提供10中分片规则

　　

工作过程：

　　当mycat收到一个SQL命令时

　　1：解析SQL命令涉及到的表

　　2：然后看表的陪对，如果有分片规则，则获取SQL命令里分片字段的值，并匹配分片函数，获得分片列表

　　3：然后将SQL命令发往对应的数据库服务器去执行

　　4：最后收集和处理所有分片结果数据，并返回到客户端

部署mycat服务：

　　

拓补图结构：

IP规划

主机名	角色	数据库	IP
client	客户端	无	192.168.2.11
mycat	分片服务器	无	192.168.2.22
mysql1	数据库服务器	db1	192.168.2.33
mysql2	数据库服务器	db2	192.168.2.44
mysql3	数据库服务器	db3	192.168.2.55

部署：

1：安装JDK

　　

[root@host22 ~]# yum  -y   install  java-1.8.0-openjdk.x86_64     安装运行环境包    
[root@host22 ~]#tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@host22 ~]# mv  mycat /usr/local/
[root@host22 ~]# ls /usr/local/mycat/
bin  catlet  conf  lib  logs  version.txt
[root@host22 ~]# 
设置PATH变量
[root@host22 mycat]# vim /etc/bashrc 
export  PATH=/usr/local/mycat/bin:$PATH  添加在最后1行
:wq
[root@host22 mycat]# source /etc/bashrc 
[root@host22 mycat]# which  mycat
/usr/local/mycat/bin/mycat

安装文件说明
bin（命令目录） catlet conf（配置文件目录） lib logs（日志文件） version.txt

　

配置文件说明
                    mycat服务的配置文件 ls /usr/local/mycat/conf/*.xml
                        schema.xml（指定表使用的分片规则）  
                        rule.xml(分片规则)   
                        server.xml （定义用户名密码逻辑库）
                    分片规则的配置文件
                     ]# ls *.properties   
                     ]# ls *.txt

注释 <!-- -->
xml 扩展标记语言 标签要成对出现 单标签要使用 / 闭合

vim server.xml(使用默认配置) 定义用户 密码 和 逻辑库
<user 配置项>
配置项
</user>

]# vim  server.xml            
<user name="root">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>
        </user>

        <user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
        </user>             
:wq

修改schema.xml  (定义使用分片规则存储数据的表)
[root@host56 ~]# cp /usr/local/mycat/conf/schema.xml /root/            

[root@host56 ~]# wc -l /usr/local/mycat/conf/schema.xml 
77 /usr/local/mycat/conf/schema.xml
            
[root@host56 ~]# sed -i '56,77d'  /usr/local/mycat/conf/schema.xml    

[root@host56 ~]# vim  /usr/local/mycat/conf/schema.xml    
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema .....>
      <schema .....>  定义表名
             指定表名
            <table ...>  </table>  双标签
            <table .... /> 单标签
      </schema>
        
      <dataNode ....  />  指定数据库服务器的主机名

      <dataHost> 数据库服务器的ip地址
            .....
            .....
      </dataHost>  
</mycat:schema>
:wq

<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />

<table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
                        rule="sharding-by-intfile" />
                           
<table name="customer" primaryKey="ID" dataNode="dn1,dn2,dn3"
                        rule="sharding-by-intfile">



36  <dataNode name="dn1" dataHost="mysql53" database="db1" />
37  <dataNode name="dn2" dataHost="mysql54" database="db2" />
38  <dataNode name="dn3" dataHost="mysql55" database="db3" />

<dataHost name="mysql53" maxCon="1000" minCon="10" balance="0"writeType="0" 
           dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

                <heartbeat>select user()</heartbeat>
                
                <writeHost host="hostM1" url="192.168.4.53:3306" user="pljadmin"
                                   password="123qqq...A">
                </writeHost>
</dataHost>

<dataHost name="mysql54" maxCon="1000" minCon="10" balance="0"writeType="0" 
           dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

                <heartbeat>select user()</heartbeat>
                
                <writeHost host="hostM2" url="192.168.4.54:3306" user="pljadmin"
                                   password="123qqq...A">
                </writeHost>
</dataHost>

<dataHost name="mysql55" maxCon="1000" minCon="10" balance="0"writeType="0" 
           dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

                <heartbeat>select user()</heartbeat>
                
                <writeHost host="hostM3" url="192.168.4.55:3306" user="pljadmin"
                                   password="123qqq...A">
                </writeHost>
</dataHost>
:wq

2.3 配置3台数据库服务器
                  #创建存储数据的库  
                  #添加对应的授权用户
[root@host53 ~]# mysql -uroot -p789...qqqA -e 'create database db1'                  
[root@host53 ~]# mysql -uroot -p789...qqqA -e 
'grant all on *.* to pljadmin@"%" identified by "123qqq...A"'

允许mycat服务

mycat start

[root@host22 ~]# netstat  -utnlp  | grep  8066
tcp6       0      0 :::8066                 :::*         LISTEN      2666/java 

三、连接Mycat服务 查看库和表    
        host50   -h192.168.4.56 -uroot  -p123456
        select 
        
统一拍错时间  
    方法查看日志     [root@host56 ~]# vim /usr/local/mycat/logs/wrapper.log 

      检查 schema.xml 
      检查 数据库的配置 
      检查内存和 主机名 是否设置
      
      检查jdk版本 (要把多余的版本 rpe -e  --force  卸载)
[root@host56 ~]# rpm -qa  | grep -i jdk
java-1.8.0-openjdk-headless-1.8.0.161-2.b14.el7.x86_64
copy-jdk-configs-3.3-2.el7.noarch
java-1.8.0-openjdk-1.8.0.161-2.b14.el7.x86_64  正确版本
[root@host56 ~]#       
      
    以上都改对了 重启mycat服务
      mycat stop  停止服务
      mycat start 启动
      mycat status 查状态
      nsetstat  -utnlp  | grep  8066 看端口
    休息到 11:10 上课         
        
             3.2  测试配置
                  #客户端连接mycat服务查看定义的库和表，能够查看到库和表就是成功的
[root@host50 ~]# mysql -h192.168.4.56 -P8066 -uroot -p123456                  
mysql> show databases;
mysql> use TESTDB;
mysql> show tables;

四、分片规则的使用(根据表使用的分片规则建表 并存储数据)
                            
        4.1 sharding-by-intfile 枚举法 
给分片字段赋值时，值必须在规则文件定义的值里选择,值会往固定的数据库服务器里存储
 
        查看使用 sharding-by-intfile 枚举法 分片规则，要求表中必须有的表头名
    vim  rule.xml
       <tableRule name="sharding-by-intfile"> 分片规则名
                <rule>
                        <columns>sharding_id</columns> 分片字段名
                        <algorithm>hash-int</algorithm> 算法名
                </rule>
        </tableRule>
        
        #查看算法
        <function name="hash-int"
                class="io.mycat.route.function.PartitionByFileMap"> 调用的java程序
                <property name="mapFile">partition-hash-int.txt</property> 分片规则配置文件
        </function>

        #修改分片规则的配置文件，定义分片字段的值
[root@host56 ~]# vim  /usr/local/mycat/conf/partition-hash-int.txt
分片表头的值=数据库服务器编号
10000=0  # 0 对应 dn1 ---> mysql53 -----> 192.168.4.53   db1
10010=1  # 1 对应 dn2 ---> mysql54 -----> 192.168.4.54   db2
10020=2  # 2 对应 dn3 ---> mysql55 -----> 192.168.4.55   db3
:wq
         #重启mycat服务 让添加生效
   339  mycat  stop
   340  mycat  start
   341  mycat  status
         
                查看使用枚举分片规则的表，
            vim /usr/local/mycat/conf/schema.xml    
 <table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
                           rule="sharding-by-intfile" />

                并根据规则建表
[root@host50 ~]# mysql -h192.168.4.56 -P8066 -uroot -p123456
mysql> use  TESTDB
create table employee(
sharding_id int, ID int primary key  auto_increment,
name char(10),age int , addir char(15));

mysql> desc employee;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| sharding_id | int(11)  | YES  |     | NULL    |                |
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| name        | char(10) | YES  |     | NULL    |                |
| age         | int(11)  | YES  |     | NULL    |                |
| addir       | char(15) | YES  |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

mod-long 求模法 ：根据 分片字段值 与 设定的数字 求模结果  存储数据
            1 找出使用mod-long分片规则存储数据时，表中必须创建的表头名称
              ]#vim  rule.xml
              <tableRule name="mod-long">  分片规则名
                  <rule>
                        <columns>id</columns>  表头名
                        <algorithm>mod-long</algorithm>  使用的算法
                  </rule>
              </tableRule>        
             
        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">3</property> 与数字3做取余计算（数字就是数据库服务器的台数）
        </function>

          2 查看使用mod-long分片规则存储数据的表
            
            ]# vim schema.xml  (删除了主键和自增长设置)
<table name="hotnews" dataNode="dn1,dn2,dn3"  rule="mod-long" />
            :wq
          3 修改了配置文件 要重启服务使其生效
         [root@host56 ~]# mycat  stop
Stopping Mycat-server...
Stopped Mycat-server.
[root@host56 ~]# mycat  start
Starting Mycat-server...
[root@host56 ~]# mycat  status
Mycat-server is running (3741).
[root@host56 ~]#    
      
         4 客户端连接56服务器建表
[root@host50 ~]# mysql -h192.168.4.56 -P8066 -uroot -p123456
mysql> use TESTDB;
mysql> create table hotnews(id int , 
title char(10),worker char(10),comment varchar(200));
         5 存储数据
mysql>insert into hotnews(id ,title,worker,comment)
values
(9,"a","a","AA"),(9,"abc","abc","abc"),(9,"cc","cc","cc"),(9,"dd","dd","dd");

mysql>insert into hotnews(id ,title,worker,comment)
values
(11,"a","a","AA"),(11,"abc","abc","abc"),(11,"cc","cc","cc"),(11,"dd","dd","dd");
         
mysql>insert into hotnews(id ,title,worker,comment)
values
(10,"a","a","AA"),(10,"abc","abc","abc");

mysql> select  * from  hotnews;

查看表结构 
[root@host53 ~]# mysql -uroot -p789...qqqA -e 'desc db1.hotnews'
[root@host54 ~]# mysql -uroot -p123qqq...A -e 'desc db2.hotnews'
[root@host55 ~]# mysql -uroot -p123qqq...A -e 'desc db3.hotnews'
     
     6 在数据库服务器53 54  55 本机查看数据
       查看数据 每台服务器只存储了部分数据而不是所有行
[root@host55 ~]# mysql -uroot -p123qqq...A  -e 'select * from  db3.hotnews'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+--------+---------+
| id   | title | worker | comment |
+------+-------+--------+---------+
|   11 | a     | a      | AA      |
|   11 | abc   | abc    | abc     |
|   11 | cc    | cc     | cc      |
|   11 | dd    | dd     | dd      |
+------+-------+--------+---------+
[root@host54 ~]# mysql -uroot -p123qqq...A  -e 'select * from  db2.hotnews' 
[root@host53 ~]# mysql -uroot -p123qqq...A  -e 'select * from  db1.hotnews'    
        
         
         
总结：    
分片规则 是mycat 软件自带的 。要把表中的记录，存储在不同数据库服务器的表里，
需要指定表使用的分片规则，存储数据时，会根据分片规则的计算结果存储到指定
的数据库器表里。

五、添加新库新表
                5.1 添加新库GAMEDB
                 vim server.xml
        <user name="root">
                <property name="password">123456</property>
                <property name="schemas">TESTDB,GAMEDB</property>
        </user>

        <user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB,GAMEDB</property>
                <property name="readOnly">true</property>
        </user>
                :wq
                
                5.2 添加表 (表名自定义 但必须唯一)
        vim  schema.xml
        在原有<schema> 标签上方添加如下行
        
<schema name="GAMEDB" checkSQLschema="false" sqlMaxLimit="100">
     <table name="user" dataNode="dn1,dn2,dn3" rule="mod-long" />
     <table name="reg" dataNode="dn1,dn2,dn3" type="global" />
</schema>
        :wq
            
                5.3 重启mycat 服务
[root@host56 ~]# mycat stop                 
[root@host56 ~]# mycat start
Starting Mycat-server...
[root@host56 ~]# mycat status
Mycat-server is running (4031).
[root@host56 ~]# 
                
                5.4 连接mycat服务 查看新库新表
[root@host50 ~]# mysql -h192.168.4.56 -P8066 -uroot -p123456
mysql> show databases;
+----------+
| DATABASE |
+----------+
| GAMEDB   |
| TESTDB   |
+----------+
2 rows in set (0.00 sec)

mysql> use GAMEDB;
mysql> show tables;
+------------------+
| Tables in GAMEDB |
+------------------+
| reg              |
| user             |
+------------------+
2 rows in set (0.00 sec)

5.5 建表并存储数据
mysql> use GAMEDB;
mysql> create table reg( name char(5), password char(6));
mysql>  insert into reg values("a","123"),("b","123");

mysql> create table user( id  int , name char(5) , addr char(10));
mysql> insert into user(id ,name,addr) 
values(11,"a","bj"),(12,"b","sh"),(13,"c","gz");
            
                5.6 在数据库服务器本机查看数据
[root@host53 ~]# mysql -uroot -p789...qqqA -e 'select * from  db1.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+------+
| id   | name | addr |
+------+------+------+
|   12 | b    | sh   |
+------+------+------+
[root@host53 ~]#

结论：建表存储数据的思路：
1 通过 rule.xml 文件 确定表中必须要有的表头名
2 确定分片规则使用的算法，来确定分片规则对应的配置文件
3 在配置文件里定义分片字段的值
4 查看使用分片规则的表
5 根据分片规则建表