MyCAT常用知识
MyCAT 常用知识
1.环境常数
# CentOS # MyCAT 1.6.5 # JDK-1.7 # MySQL 5.7
2.技术储备
# 具有一定的 Linux 基础，全使用 ls、cd、cp、mv、vi 等命令 # 具有一定的 MySQL 基础，了解 MySQL 复制机制
3.适合人群
# 数据库工程师 # 研发工程师 # 致力于向架构师迈进的有志青年
4.课程收获
# 读写分离 # 公共连接池 # 监控及优化 # 垂直分库 # 水平分库 # 以 MyCAT 为核心的 MySQL 高可集群
5.实例所用数据库对象列表
# 用户模块 # 用户信息表、用户积分表、用户地址表、用户等级表、用户登录表、地理区域表 # 商品模块 # 商品信息表、品牌信息表、商品分类表、供应商信息表、地理区域表 # 仓配模块 # 仓库信息表、商品库存表地理区域表、 # 订单模块 # 订单主表、订单详情表、地理区域表
6.数据库架构
6.1.初始数据库架构
6.2.最终数据库架构
7.什么是 MyCAT?
# 对于 DBA # MyCAT 相当于 MySQL Server 层 # MyCAT 相当于 MyCAT 的存储层 # MyCAT 中不存储数据，所有数据存储在 MySQL 中 # # 对于研发 # MyCAT 就是 MySQL # MyCAT 对于研发人员来说是透明的 # MyCAT 对使用的 SQL 有一些限制 # # 对于架构 # MyCAT 是一个数据库中间层 # MyCAT 可以实现对后端数据库的分库分表和读写分离 # MyCAT 对前端应用隐藏了后端数据库的存储逻辑
8.什么是数据库中间层
9.MyCAT 的主要作用
# 作为分布式数据库中间层使用 # 实现后端数据库的读写分离及负载均衡 # 对业务数据库进行垂直切分 # 对业务数据库进行水平切分 # 控制数据库连接的数量
10.MyCAT的基本元素
# 逻辑库 # 1.对应用来说相当于 MySQL 中的数据库 # 2.逻辑库可对应后端多个物理数据库 # 3.逻辑库中并不保存数据 # # 逻辑表 # 1.对应用来说相当于 MySQL 中的数据表 # 2.逻辑表可对应后端多个物理数据库中的表 # 3.逻辑表中并不保存数据 # # 逻辑表的类别 # 1.分片表与非分片表按是否被分片划分 # 2.全局表（字典表，数据通常不多），在所有分片中都存在的表 # 3.ER关系表，按 ER 关系进行分片的表
11.MyCAT 的安装步骤
################################################# # 1.下载并解压 MyCAT # 1.1.下载 wget http://dl.mycat.io/1.6.5./Mycat-server-1.6.5-release-20190122220033-linux.tar.gz ########################## # 1.2.解压 tar zxf Mycat-server-1.6.5-release-20190122220033-linux.tar cd mycat ################################################# # 2.安装 JAVA 运行环境 JDK 1.7 # 2.1.下载 ########################## # 2.2.查看 Java 版本 java -version ########################## # 2.3如果存在就要卸载 rpm -qa|grep java rpm -e --nodeps tzdata-java-2018c-1.el6.noarch rpm -e --nodeps java-1.6.0-openjdk-1.6.0.41-1.13.13.1.el6_8.x86_64 ########################## # 2.4.解压 tar -zxf jdk-7u79-linux-x64.tar.gz cd jdk-7u79 ########################## # 2.5.移动目录 mkdir /usr/local/java mv ./jdk-7u79/ /usr/local/java ################################################# # 3.新建 MyCAT 运行系统账号 # 3.1.创建用户 adduser mycat ########################## # 3.2.移动目录 mv mysql/ /usr/local ########################## # 3.3.目录添加用户名和组 cd /usr/local chown mycat:mycat - R mysql/ ################################################# # 3.配置系统环境变量 # 3.1.查看内存 free -m ########################## # 3.2.修改配置文件 cd /usr/local/mycat/conf vim wrapper.conf # 修改内容如下 # 根据服务器内存大小配置，如果只有 512M，改成 256M wrapper.java.additional.5=-XX:MaxDirectMemorySize=2G wrapper.java.additional.6=-Dcom.sun.management.jmxremote ########################## # 3.3.增加环境变量 vi /etc/profile # 修改内容如下 export PATH=$PATH:/usr/local/mycat/bin:/usr/local/java/jdk1.7.0_79/bin export JAVA_HOME=/usr/local/java/jdk1.7.0_79 export CLASSPATH=.:$JAVA_HOME/jre/lib/rt.jar:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar export MyCAT_HOME=/usr/local/mycat ########################## # 3.4.保存环境变量 source /etc/profile java -version ################################################# # 4.启动 MyCAT # 4.1.启动 mycat start # 4.2.测试 ps -ef|grep mycat # 4.3.查看日志 cd /usr/local/mycat/log more wrapper.log tail -1 wrapper.log
12.常用配置文件间的关系
13. server.xml 文件
#################################################
# 文件用途
# 1.配置系统相关参数
# 2.配置用户相关权限
# 3.配置SQL防火墙及SQL拦截功能
#################################################
# 配置详解
# <system>配置MyCAT系统参数
<system>
<property name="${key}">${value}</property>
</system>
# ${key}表示配置属性名
# ${value}表示该配置属性的取值
#
#################################################
# 实例:配置MyCAT系统参数
<system>
<property name="serverPort">3306</property>
</system>
#################################################
# server.xml系统参数
# 1.serverPort 对外提供服务的服务端口
# 2.managerPort 管理MyCAT端口
# 3.nonePasswordLogin 通过mycat登录是否要密码验证(0需要,1.不需要)
# 4.bindIp 在哪些服务器上监听,0.0.0.0表示监控所有ip地址
# 5.frontWriteQueueSize 前端写队列的大小
# 6.charset MyCAT连接MySQL,两边的charset一定要一致,否则会出现乱码
# 7.txIsolation MyCAT连接到MySQL的默认隔离级别(1.读未提交 2.读已提交 3.可重复度 4.序列化读),默认是可重复度
# 8.processors MyCAT进程数量,通常等于服务器cpu核数
# 9.idleTimeout 前端应用和MySQL连接后，多长时间没访问后主动断开连接(使连接池能更灵活被多个应用使用。单位毫秒)
# 10.sqlExecuteTimeout sql执行时间超过多少秒后断开连接(单位秒)
# 11.useSqlStat
# 12.useGlobleTableCheck
# 13.sequnceHandlerType
# 14.defaultMaxLimit MyCAT默认返回数据集的大小.
# MyCAT是一个分布式数据库中间件,通常存储数据量很大,不进行限制会浪费很多资源
# 15.maxPacketSize 允许最大包的大小，要和mysql的配置保持一致
#################################################
# <user>配置MyCAT的访问用户及权限
<user name="test">
<property name="password">123456</property>
<property name="schemas">imooc_db</property>
<property name="readOnly">false</properyty>
</user>
#################################################
# 如果授予多个库访问权限，用逗号隔开即可
<property name="schemas">testdb,db1,db2</property>
#################################################
# 实例:对用户授予表权限
# 默认对imooc_db库有update,select权限,对其中的tb01表无权限,对tb02表四个权限都有
# 1111 表示 insert update select delete
# 0000 表示 无权限
<user name="test">
<privilegs check="true">
<schemas name="imooc_db">
<table name="tb001" dml="0000"></table>
<table name="tb002" dml="1111"></table>
</schemas>
</privilegs>
</user>
#################################################
# 实例:定义多个用户
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">imooc_db</property>
<!-- 表级 DML 权限设置 -->
<privilegs check="true">
<schemas name="imooc_db" dml="0110">
<table name="customer_inf" dml="0000"></table>
<table name="customer_level_inf" dml="1111"></table>
</schemas>
</privilegs>
</user>
<user name="test">
<property name="password">123456</property>
<property name="schemas">imooc_db</property>
<property name="readOnly">true</properyty>
</user>
#################################################
# 加密明文密码
#
# 用户密码加密，安全问题
<user name="root">
<property name="password">123456</property>
</user>
# 在命令行执行如下命令对密码加密
# 0 表示前端加密
# root 表示用户名
# 123456 表示为用户密码
cd mycat/lib/
java -cp Mycat-server-1.6.5-release.jar io.mycat.util.DecryptUtil 0:root:123456
# 配置方式
# 1.在Linux命令行用命令生成加密后的密码。将加密后的密码粘贴到password标签中
# 2.还要加上usingDecrypt标签，值为1，告诉MyCAT这行密码进行了加密
# 3.修改 server.xml 文件
#
# 加密后的用户配置
<user name="root">
<property name="usingDecrypt">1</property>
<property name="password">GO0bnFVWrAuFgr1JMuMZkvfDNyTpoiGU7n/Wlsa51CirHQnANVk3NzE3FErx8v6pAcO0ctX3xFecmSr+976QA==</property>
</user>
# 重新启动 MyCAT
mycat start
14.log4j2.xml 文件
#################################################
# 文件用途
# 1.配置输出日志的格式
# 2.配置输出日志的级别
#################################################
# <pattern>配置MyCAT日志格式
<PatternLayout>
<Pattern>
%d{yyyy-MM-dd HH:mm:ss.SSS} %5p [%t] - %m%n
</Pattern>
</PatternLayout>
# %d{yyyy-MM-dd HH:mm:ss.SSS} 表示日志的时间格式
# %5p 表示输出的日志级别
# %t 日志中记录线程名称
# %m 输出代码中提定的消息
# %n 输出一个回车换行符，Windows平台为 "/r/n", Liunx平台为 "/n"
#################################################
# level属性配置MyCAT日志级别
<asyncRoot level="info" includeLocation="true" />
# 支持8种日志级别 由低到高
# All < Trace < Debug < Info < Warn < Error < Fatal < OFF
15.rule.xml 文件
################################################# # 文件用途 # 1.配置水平分片的分片规则 # 2.配置分片规则所对应的分片函数 ################################################# # <tableRule>标签配置表的分片规则 <tableRule name="hash-mod-4_id"> <rule> <columns>id</columns> <algorithm>hash-mod-4</algorithm> </rule> </tableRule> # name 属性指定分片表规则的名字，这个命名要唯一 # <columns>标签指定表的分片列 # <algorithm>指定表的分片算法，取<function>的name属性 ################################################# # <function>配置表的分片算法 <function name="hash-mod-4" class="io.mycat.route.function.PartitionByHashMod"> <property name="count">4</property> </function> # name 属性定义分片算法的名字 # class 属性指定分片算法实现类，必须为类全名 # count 属性定义取模的基数
16.常用的分片算法
################################################################################################## # 简单取模-PartitionByMod # # 配置简单取模-PartitionByMod # 1.可以用于分片列为整数类型的表,只能用于整数 # 2.分片列mod分片基数 # 3.类全名:io.mycat.route.function.PartitionByMod <tableRule name="customer_login"> <rule> <columns>customer_id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <property name="count">2</property> </function> ################################################################################################## # 哈希取模-PartitionByHashMod # # 配置简单取模-PartitionByHashMod # 1.可以用于多种数据类型如字符串,日期等 # 2.hash(分片列)mod分片基数，容易产生分布不均匀的情况 # 3.类全名:io.mycat.route.function.PartitionByHashMod <tableRule name="customer_login"> <rule> <columns>login_name</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <function name="mod-long" class="io.mycat.route.function.PartitionByHashMod"> <property name="count">2</property> </function> ################################################################################################## # 枚举分片取模-PartitionByFileMap # # 配置枚举分片-PartitionByFileMap # 1.可以根据可能的枚举值指定数据存储的位置 # 2.$MYCAT/conf 目录中增加MapFile配置枚举值同节点的对应关系 # 3.类全名:io.mycat.route.function.PartitionByFileMap <function name="hash-int" class="io.mycat.route.function.PartitionByFileMap"> <property name="mapFile">partition-hash-int.txt</property> <property name="type">0</property> <property name="defaultNode">0</property> </function> # partition-hash-int.txt 文件内容 10000=0 10001=1 DEFAULT_NODE=0 # mapFile 表示读取的文件名 # type 表示类型，0表示inter(整型) 非0表示string(字符串) # defaultNode 表示是否使用默认的节点，>=0 启用默认节点 <0 不启用默认节点 ################################################################################################## # 字符串范围取模分片-PartitionByPrefixPattern # # 求模方法 # # 例如: A B C D E F G H I # 1.取前面3个字母求模，字母转成 ASCII 相加 A(65) + B(66) + C(67) 后得到 198 # 2.198 % 求模基数:128 = 70 # 3.分片方式 # 0-63=0 # 64-127=1 # # 配置字符串范围取模分片-PartitionByPrefixPattern # 1.可以根据指定字符串的前N个字符确定存储位置 # 2.$MYCAT/conf增加MapFile配置取模范围同节点的对应关系 # 3.类全名:io.mycat.route.function.PartitionByPrefixPattern <function name="sharding-by-prefix-pattern" class="io.mycat.route.function.PartitionByPrefixPattern"> <property name="patternValue">128</property> <property name="prefixLength">2</property> <property name="mapFile">prefix-partition-pattern.txt</property> </function> # prefix-partition-pattern.txt 文件内容 0-63=0 64-127=1 # patternValue 取模基数 # prefixLength 需要截取的指定的字符串前缀的长度 # mapFile 表示读取的文件名
17.schema.xml文件
################################################# # 文件用途 # 1.配置逻辑库及逻辑表 # 2.配置逻辑表所存储的数据节点 # 3.配置数据节点所对应的物理数据库服务器信息 ######################### # 配置schema.xml # <schema>定义逻辑库 <schema name="testdb" checkSQLschema="false" sqlMaxLimit="1000"></schema> # 1.name 属性定义逻辑库的名字 # 2.sqlMaxLimit 限制返回结果集的行数,-1表示关闭limit限制 # 3.checkSQLschema 属性判断是否检查发给MyCAT的SQL是否含库名 ######################### # <table>定义逻辑表 <table name="customer_login" primaryKey="customer_id" dataNode="logindb01,logindb02" rule="customer_login" /> # 1.name 属性定义逻辑表的名字,名字必须跟物理表一致 # 2.primaryKey 定义了逻辑表的主键 # 3.dataNode 定义表数据所存储的数据节点,logindb01,logindb02分别表示0和1,定义了就要随意修改,不然会导致数据混乱 # 4.rule 定义了逻辑表分片规则,对应rule.xml中的<tableRule> ######################### # <dataNode>定义逻辑表存储的物理数据库 <dataNode name="imooc" dataHost="mysql0113" database="imooc_db" /> # 1.name 属性定义数据节点的名称，必须唯一 # 2.dataHost 属性定义分片所在的物理主机 # 3.database 属性定义物理数据库名称 ######################### # <dataHost>定义后端数据库主机信息 <dataHost name="mysql0103" maxCON="1000" minCon="10" balance="1" writeType="0" dbTyep="mysql" dbDriver="native" switchType="1"> <heartbeat>select user()</heartbeat> <writeHost host="192.168.1.3" url="192.168.1.3:3306" user="im_mycat" password="123456"/> <readHostHost host="192.168.1.4" url="192.168.1.4:3306" user="im_mycat" password="123456"/> </writeHost> </dataHost> # 1.name 指定dataHost的名字 # 2.maxCON 指定每个读写实例连接池的最大连接.也就是说,标签内嵌套的writeHost,readHost标签都会使用这个属性的值来实例化出连接池的最大连接数 # 3.minCon 指定每个读写实例连接池的最小连接,初始化连接池的大小 # 4.balance 负载均衡类型 # 0:不开启读写分离机制,所有读操作都发送到当前可用的writeHost 上 # 1:全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡 # 2:所有读操作都随机的在writeHost,readhost上分发 # 3:所有读请求随机的分发到wiriterHost对应的readhost执行,writerHost不负担读压力,注意 balance=3只在1.4及其以后版本有,1.3没有。 # 5.writeType # 0:所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个writeHost,重新启动后已切换后的为准,切换记录在配置文件中:dnindex.properties # 1:所有写操作都随机的发送到配置的writeHost,1.5 以后废弃不推荐 # 6.dbTyep:指定后端连接的数据库类型,目前支持二进制的mysql协议,还有其他使用JDBC连接的数据库.例如:mongodb,oracle,spark等. # 7.dbDriver:指定连接后端数据库使用的Driver,目前可选的值有native和JDBC。 # 1.native:因为这个值执行的是二进制的mysql协议,所以可以使用mysql和maridb。 # 2.JDBC:其他类型的数据库则需要使用JDBC驱动来支持 # 8.switchType # -1:表示不自动切换 # 1:默认值,自动切换 # 2:基于MySQL主从同步的状态决定是否切换 心跳语句为 show slave status # 3:基于MySQL galary cluster的切换机制(适合集群) # 9.slaveThreshold:配置真实MySQL与MyCat的心跳 ######################### # heartbeat子标签属性(心跳执行SQL) # writeHost子标签属性(写主机配置) # readHost子标签属性(读主机配置) # 1.host:用于标识不同实例,一般writeHost我们使用M1,readHost我们用S1 # 2.url:后端实例连接地址, # native:一般为address:port这种形式。 # JDBC:需要特殊指定.当使用JDBC时则可以这么写:jdbc:mysql://localhost:3306/。 # 3.user:后端存储实例需要的用户名字 # 4.password:后端存储实例需要的密码 ######################### # heartbeat标签 <heartbeat>select user()</heartbeat> # 表示如何检查后端数据库是否可用 ######################### # writeHost标签 # readHost标签 <writeHost host="192.168.1.3" url="192.168.1.3:3306" user="im_mycat" password="123456"/> <readHostHost host="192.168.1.4" url="192.168.1.4:3306" user="im_mycat" password="123456"/> </writeHost> # 1.定义一组主从数据库, <readHost>依赖于<writeHost> # 2.user和password为后端数据库中的用户 ########################## # schema总结 <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="imooc_db" checkSQLschema="false" sqlMaxLimit="100"> <table name="customer_login" primaryKey="customer_id" dataNode="logindb01,logindb02" rule="customer_login" /> </schema> <dataNode name="logindb01" dataHost="mysql0103" database="login_db01" /> <dataNode name="logindb02" dataHost="mysql0103" database="login_db02" /> <dataHost name="mysql0103" maxCON="1000" minCon="10" balance="1" writeType="0" dbTyep="mysql" dbDriver="native" switchType="1"> <heartbeat>select user()</heartbeat> <writeHost host="192.168.1.3" url="192.168.1.3:3306" user="im_mycat" password="123456"> <readHostHost host="192.168.1.4" url="192.168.1.4:3306" user="im_user" password="123456" /> </writeHost> <writeHost host="192.168.1.4" url="192.168.1.4:3306" user="im_user" password="123456" /> </dataHost> </mycat:schema>
18.垂直分库
################################################# # 垂直分库 # # 垂直分库的步骤 # 1.收集分析业务模块间的关系 # 2.复制数据库到其它实例 # 3.配置MyCAT垂直份库 # 4.通过MyCAT访问DB # 5.删除原库中已迁移表
\
################################################################################################## # 垂直分库 # # 垂直分库的步骤 # 1.收集分析业务模块间的关系 # 2.复制数据库到其它实例 # 3.配置MyCAT垂直份库 # 4.通过MyCAT访问DB # 5.删除原库中已迁移表 ################################################# # 收集分析业务模块 ################################################# # 复制数据库到其它实例 # 1.备份原数据库并记录相关事务 # 2.在原数据库中建立复制用户 # 3.在新实例上恢复备份数据库 # 4.在实例上配置复址链路 # 5.在新实例上启动复制 ################################################# # 演示环境说明 # 主机名 IP 角色 数据库 # node1 192.168.1.2 MySQL和MYSQL imooc_db # node2 192.168.1.3 MYSQL order_db # node3 192.168.1.4 MYSQL product_db # node4 192.168.1.5 MYSQL customer_db ################################################# # MySQL复制实战 ############################ # 1.创建数据库 mysql -u root -p -e "create database imooc_db" ############################ # 2.导入sql脚本(注意编码，可能导入的数据都是乱码) mysql -u root -p imooc_db --default-character-set=utf8 < imooc_db.sql ############################ # 3.进入数据库,查看 mysql -u root -p use imooc_db show tables; exit ############################ # 4.备份数据库(备份前请开启binlog) # --master-data # 该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限，并且必须启用二进制日志。 # 如果该选项值等于1，位置和文件名被写入CHANGE MASTER语句形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。 # 如果选项值等于2，CHANGE MASTER语句被写成SQL注释。如果value被省略，这是默认动作。 # --master-data=2 是把change log 那行注释掉，=1 是没有注释. # --single-transaction # 通过将导出操作封装在一个事务内来使得导出的数据是一个一致性快照。只有当表使用支持MVCC的存储引擎（目前只有InnoDB）时才可以工作; # 其他引擎不能保证导出是一致的。当导出开启了–single-transaction选项时，要确保导出文件有效（正确的表数据和二进制日志位置），就要 # 保证没有其他连接会执行如下语句：ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE，这会导致一致性快照失效。这个选项开启后 # 会自动关闭–lock-tables。 mysqldump --master-data=2 --single-transaction --routines --triggers --events -u root -p imooc_db > bak_imooc.sql ############################ # 5.查看备份文件节点，同步只会同步这个节点之后的数据 more bak_imooc.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=34868; ############################ # 6.把备份文件拷贝到其它的服务器 scp bak_imooc.sql root@192.168.1.3:/root scp bak_imooc.sql root@192.168.1.4:/root scp bak_imooc.sql root@192.168.1.5:/root ############################ # 7.进入 192.168.1.3 服务器 ############################ # 8.进入MySQL命令行窗口，检查数据库 show databases; exit ############################ # 9.创建数据库 mysql -u root -p -e "create database order_db" ############################ # 10.导入数据库 mysql -u root -p order_db --default-character-set=utf8 < bak_imooc.sql ############################ # 11.进入MySQL命令行窗口，检查数据库 show databases; use order_db; show tables; ############################ # 12.进入 192.168.1.2 服务器 ############################ # 13.创建主从复制用户并赋予权限 create user 'im_repl'@'192.168.1.%' identified by '123456'; grant replication slave on *.* to 'im_repl'@'192.168.1.%'; ############################ # 14.进入 192.168.1.3 服务器 ############################ # 15.建立复制链路 \h change master to change master to master_host='192.168.1.2', master_user='im_repl', master_password='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=34868; ############################ # 16.查看复制链路 show slave status \G; ############################ # 17.一般情况下，主服务器和从服务器的数据库名是要一致的，但是随后要进行垂直切分，可通过过滤方式实现，过滤方式可以支持数据库名不一致。 change replication filter replication_rewrite_db=((imooc_db, order_db)); ############################ # 18.启动复制链路 start slave; show slave status; ############################ # 19.测试同步链路是否有效 ############################ # 20.进入 192.168.1.2 服务器, 修改数据 select * from region_info limit 10; update region_info set region_name = '中国(china)' where region_id = 1; select * from region_info where region_id = 1; ############################ # 21.进入 192.168.1.3 服务器, 查看 ############################ select * from region_info where region_id = 1; ############################ # 21.进入 192.168.1.4 服务器, 建立数据库 mysql -u root -p -e "create database product_db" mysql -u root -p product_db --default-character-set=utf8 < bak_imooc.sql mysql -u root -p show databases; use product_db; change master to master_host='192.168.1.4', master_user='im_repl', master_password='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=34868; show slave status \G; change replication filter replication_rewrite_db=((imooc_db, product_db)); start slave; show slave status; ############################ # 24.测试同步链路是否有效 ############################ # 25.进入 192.168.1.2 服务器, 修改数据 ############################ # 26.进入 192.168.1.4 服务器, 查看 ############################ # 27.进入 192.168.1.5 服务器, 建立数据库 mysql -u root -p -e "create database customer_db" mysql -u root -p customer_db --default-character-set=utf8 < bak_imooc.sql mysql -u root -p show databases; use customer_db; change master to master_host='192.168.1.5', master_user='im_repl', master_password='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=34868; show slave status \G; change replication filter replication_rewrite_db=((imooc_db, customer_db)); start slave; show slave status; ############################ # 28.测试同步链路是否有效 ############################ # 29.进入 192.168.1.2 服务器, 修改数据 ############################ # 30.进入 192.168.1.5 服务器, 查看 ###################################################### # 垂直分库 ########################## # 1.配置MyCAT垂直分库 ############# # 1.在192.168.1.2服务器上使用schema.xml配置逻辑库 # 1.查看文件 cd /usr/local/mycat/conf vi schema.xml # 2.修改文件,由于只是测试，datahost只是配置一台writehost，生产环境是需要一组一主一从的服务器 <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="imooc_db" checkSQLschema="false" sqlMaxLimit="100"> <table name="order_master" primaryKey="order_id" dataNode="ordb" /> <table name="order_detail" primaryKey="order_detail_id" dataNode="ordb" /> <table name="order_cart" primaryKey="cart_id" dataNode="ordb" /> <table name="order_customer_addr" primaryKey="customer_addr_id" dataNode="ordb" /> <table name="region_info" primaryKey="region_id" dataNode="ordb" /> <table name="shipping_info" primaryKey="ship_id" dataNode="ordb" /> <table name="warehouse_info" primaryKey="w_id" dataNode="ordb" /> <table name="warehouse_product" primaryKey="wp_id" dataNode="ordb" /> <table name="product_brand_infos" primaryKey="brand_id" dataNode="prodb" /> <table name="product_category" primaryKey="category_id" dataNode="prodb" /> <table name="product_comment" primaryKey="comment_id" dataNode="prodb" /> <table name="product_info" primaryKey="product_id" dataNode="prodb" /> <table name="product_supplier_info" primaryKey="supplier_id" dataNode="prodb" /> <table name="product_pic_info" primaryKey="product_pic_id" dataNode="prodb" /> <table name="customer_balance_log" primaryKey="balance_id" dataNode="custdb" /> <table name="customer_inf" primaryKey="customer_inf_id" dataNode="custdb" /> <table name="customer_level_inf" primaryKey="customer_level" dataNode="custdb" /> <table name="customer_login" primaryKey="customer_id" dataNode="custdb" /> <table name="customer_login_log" primaryKey="login_id" dataNode="custdb" /> <table name="customer_point_log" primaryKey="point_id" dataNode="custdb" /> </schema> <dataNode name="ordb" dataHost="mysql0103" database="order_db" /> <dataNode name="prodb" dataHost="mysql0104" database="product_db" /> <dataNode name="cusdb" dataHost="mysql0105" database="customer_db" /> <dataHost name="mysql0103" maxCon="1000" minCon="10" balance="3" writeType="0" dbTyep="mysql" dbDriver="native" switchType="1"> <heartbeat>select user()</heartbeat> <writeHost host="192.168.1.3" url="192.168.1.3:3306" user="im_mycat" password="123456" /> </dataHost> <dataHost name="mysql0104" maxCon="1000" minCon="10" balance="3" writeType="0" dbTyep="mysql" dbDriver="native" switchType="1"> <heartbeat>select user()</heartbeat> <writeHost host="192.168.1.4" url="192.168.1.4:3306" user="im_mycat" password="123456" /> </dataHost> <dataHost name="mysql0105" maxCon="1000" minCon="10" balance="3" writeType="0" dbTyep="mysql" dbDriver="native" switchType="1"> <heartbeat>select user()</heartbeat> <writeHost host="192.168.1.5" url="192.168.1.5:3306" user="im_mycat" password="123456" /> </dataHost> </mycat:schema> # 3.在mysql命令行界面创建用户和权限 create user im_mycat@'192.168.1.%' identified by '123456'; grant select,insert,update,delete on *.* to im_mycat@'192.168.1.%'; ########################## # 2.使用server.xml配置系统变量及用户权限 1.修改文件server.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"> <system> <property name="serverPort">8066</property> <property name="managerPort">9066</property> <property name="nonePasswordLogin">0</property> <property name="bindIp">0.0.0.0</property> <property name="frontWriteQueueSize">2048</property> <property name="charset">utf8</property> <property name="txIsolation">2</property> <property name="processors">8</property> <property name="idleTimeout">1800000</property> <property name="sqlExecuteTimeout">300</property> <property name="useSqlStat">0</property> <property name="useGlobleTableCheck">0</property> <property name="sequnceHandlerType">2</property> <property name="defaultMaxLimit">100</property> <property name="maxPacketSize">104857600</property> </system> <user name="app_imooc" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">order_db,product_db,customer_db</property> </user> </mycat:server> # 2.加密,生成一个加密字符串,拷贝字符串 cd ../lib/ java -cp Mycat-server-1.6.5-release.jar io.mycat.util.DecryptUtil 0:app_imooc:123456 # 3.继续修改文件server.xml,替换明文密码 <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"> <system> <property name="serverPort">8066</property> <property name="managerPort">9066</property> <property name="nonePasswordLogin">0</property> <property name="bindIp">0.0.0.0</property> <property name="frontWriteQueueSize">2048</property> <property name="charset">utf8</property> <property name="txIsolation">2</property> <property name="processors">8</property> <property name="idleTimeout">1800000</property> <property name="sqlExecuteTimeout">300</property> <property name="useSqlStat">0</property> <property name="useGlobleTableCheck">0</property> <property name="sequnceHandlerType">2</property> <property name="defaultMaxLimit">100</property> <property name="maxPacketSize">104857600</property> </system> <user name="app_imooc" defaultAccount="true"> <property name="usingDecrypt">1</property> <property name="password">GO0bnFVWrAuFgr1JMuMZkvfDNyTpoiGU7n/Wlsa51CirHQnANVk3NzE3FErx8v6pAcO0ctX3xFecmSr+976QA==</property> <property name="schemas">imooc_db</property> </user> </mycat:server> # 3.由于没有用到水平分片故不需要配置rule.xml ########################## # 2.后续工作 # 1.切换应用通过MyCAT连接数据库(在网站不繁忙时间，一般是深夜) # 2.切换后测试,查看数据库和表 # 3.需要注意跨分片查询(1个表在分片2，一个表在分片3),下面sql运行后报错 select supplier_name, b.region_nam as '省', c.region_name as '市', d.region_name as '区' from product_supplier_info a join region_info b on b.region_id = a.province join region_info c on c.region_id = a.city join region_info d on d.region_id = a.district; # # 应该如何解决？ # 方法一:通过代码分别查询不同的数据表，然后拼凑一起 # 方法二:通过数据冗余的方法 # 方法三:利用MyCAT配置全局表(推荐) ########################## # 3.MyCAT启动调试 # 3.1.启动 mycat start # 3.2.查看进程 ps -ef # 3.3.查看启动日志,如果看到 MyCAT Server startup successfully.说明启动成功 more wrapper.log # 3.4.查看进程和端口 ps -ef netstat -ntlp ########################## # 4.MyCAT验证配置 # 4.1.到192.168.1.5服务器命令行窗口登录mycat mysql -u app_imooc -p -P 8066 -h 192.168.1.2 # 4.2.查看表 use imooc_db; show tables; select * from product_info limit 10; ########################## # 5.清理多余数据 # 5.1.到192.168.1.5服务器命令行窗口登录mysql，停止主从复制，让192.168.1.2成为唯一主节点 mysql -u root -p stop slave; show slave status\G; reset slave all; # 5.2.到192.168.1.4服务器命令行窗口登录mysql，停止主从复制，让192.168.1.2成为唯一主节点 mysql -u root -p stop slave; show slave status\G; reset slave all; # 5.3.到192.168.1.3服务器命令行窗口登录mysql，停止主从复制，让192.168.1.2成为唯一主节点 mysql -u root -p stop slave; show slave status\G; reset slave all; # 5.4.到192.168.1.3服务器清理多余数据 show databases; use order_db; show tables; drop talbe customer_balance_log; drop talbe customer_inf; drop talbe customer_level_inf; drop talbe customer_login; drop talbe customer_login_log; drop talbe customer_point_log; drop talbe product_brand_infos; drop talbe product_category; drop talbe product_comment; drop talbe product_info; drop talbe product_pic_info; drop talbe product_supplier_info; # 5.5.到192.168.1.4服务器清理多余数据 show databases; use product_db; show tables; drop talbe customer_balance_log; drop talbe customer_inf; drop talbe customer_level_inf; drop talbe customer_login; drop talbe customer_login_log; drop talbe customer_point_log; drop talbe order_cart; drop talbe order_customer_addr; drop talbe order_detail; drop talbe order_master; drop table region_info; drop table serial; drop table shipping_info; drop table warehouse_info; drop table warehouse_product; # 5.6.到192.168.1.5服务器清理多余数据 show databases; use customer_db; show tables; drop talbe order_cart; drop talbe order_customer_addr; drop talbe order_detail; drop talbe order_master; drop talbe product_brand_infos; drop talbe product_category; drop talbe product_comment; drop talbe product_info; drop talbe product_pic_info; drop talbe product_supplier_info; drop table region_info; drop table serial; drop table shipping_info; drop table warehouse_info; drop table warehouse_product; ########################## # 6.跨分片查询 ########################## # 7.配置和验证全局表 # 7.1.到192.168.1.3服务器 mysqldump -u root -p oldboy_db region_info > region_info ls more region_info # 7.2.拷贝到节点3和节点4 scp region_info root@192.168.1.4:/root scp region_info root@192.168.1.5:/root # 7.3.到192.168.1.4服务器导入数据 mysql -u root -p product_db < region_info # 7.4.到192.168.1.5服务器导入数据 mysql -u root -p product_db < region_info # 7.5.到192.168.1.2服务器修改配置表schema.xml <table name="region_info" primaryKey="region_id" dataNode="ordb,prodb,custdb" type="global" /> # 7.6.重启mycat mycat stop mycat start # 7.7.到192.168.1.5服务器登录mycat服务器验证 mysql -u app_imooc -p 123456 -P8066 -h 192.168.1.2 # 7.8.到三台服务器关闭readonly属性 set global read_only=off; # 7.9.修改数据测试(只能通过mycat处理数据，不然数据会不一致) ########################## # 8.垂直切分的优缺点 # 8.1.优点 # 8.1.1.数据库的拆分简单明了，拆分规则明确 # 8.1.2.应用程序模块清晰明确，整合容易 # 8.1.3.数据维护方便易行，容易定位 # 8.2.缺点 # 8.2.1.部分表关联无法在数据库级别完成，需要在程序中完成 # 8.2.2.对于访问及其频繁且数据量超大的表依然存在性能瓶颈 # 8.2.3.切分达到一定程度之后，扩展性会遇到限制 # 8.3.解决跨分片关联的方式 # 8.3.1.使用MyCAT全局表 # 8.3.2.冗余部分关键数据 # 8.3.3.使用API的方式获取数据
19.水平分库
################################################################################################## # 1.水平分库和分片原则 # 1.1.能不切分尽量不要切分 # 1.2.选择合适的切分规则和分片键 # 1.3.尽量避免跨分片JOIN操作
################################################## # 2.分片后如何处理查询 #
###################################################
# 3.水平分片步骤
# 3.1.根据业务状态确定要进行水平切分的表
# 3.2.分析业务模型选择分片键及分片算法
# 3.3.使用MyCAT部署分片集群
# 3.4.测试分片集群
# 3.5.业务及数据迁移
###################################################
# 4.如何选择分片键
# 4.1.尽可能的比较均匀分布数据到各个节点上
# 4.2.该业务字段是最频繁的或者最重要的查询条件
###################################################
# 5.分析业务模型
# 5.1.对订单相关表进行水平切分
# 5.2.以customer_id做为分片键
# 5.3.采用简单取模分片算法
###################################################
# 6.部署分片集群
# 6.1.使用schema.xml配置逻辑库及逻辑表
# 6.2.使用rule.xml配置分片表的分片规则
# 6.3.使用server.xml配置访问用户的权限
###################################################
# 7.部署分片集群
# 主机名 IP 角色 数据库
# node1 192.168.1.2 MyCAT
# node2 192.168.1.3 MySQL orderdb01 order02
# node3 192.168.1.4 MySQL orderdb03 order04
###################################################
# 8.水平切分演示
########################
# 8.1.在192.168.1.2服务器上修改schema.xml，添加4个dataNode，修改table的order_master
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="imooc_db" checkSQLschema="false" sqlMaxLimit="100">
<table name="order_master" primaryKey="order_id" dataNode="orderdb01,orderdb02,orderdb03,orderdb04" rule="order_master"/>
<table name="order_detail" primaryKey="order_detail_id" dataNode="ordb" />
<table name="order_cart" primaryKey="cart_id" dataNode="ordb" />
<table name="order_customer_addr" primaryKey="customer_addr_id" dataNode="ordb" />
<table name="region_info" primaryKey="region_id" dataNode="ordb" />
<table name="shipping_info" primaryKey="ship_id" dataNode="ordb" />
<table name="warehouse_info" primaryKey="w_id" dataNode="ordb" />
<table name="warehouse_product" primaryKey="wp_id" dataNode="ordb" />
<table name="product_brand_infos" primaryKey="brand_id" dataNode="prodb" />
<table name="product_category" primaryKey="category_id" dataNode="prodb" />
<table name="product_comment" primaryKey="comment_id" dataNode="prodb" />
<table name="product_info" primaryKey="product_id" dataNode="prodb" />
<table name="product_supplier_info" primaryKey="supplier_id" dataNode="prodb" />
<table name="product_pic_info" primaryKey="product_pic_id" dataNode="prodb" />
<table name="customer_balance_log" primaryKey="balance_id" dataNode="custdb" />
<table name="customer_inf" primaryKey="customer_inf_id" dataNode="custdb" />
<table name="customer_level_inf" primaryKey="customer_level" dataNode="custdb" />
<table name="customer_login" primaryKey="customer_id" dataNode="custdb" />
<table name="customer_login_log" primaryKey="login_id" dataNode="custdb" />
<table name="customer_point_log" primaryKey="point_id" dataNode="custdb" />
</schema>
<dataNode name="ordb" dataHost="mysql0103" database="order_db" />
<dataNode name="prodb" dataHost="mysql0104" database="product_db" />
<dataNode name="cusdb" dataHost="mysql0105" database="customer_db" />
<dataNode name="orderdb01" dataHost="mysql0103" database="orderdb01" />
<dataNode name="orderdb02" dataHost="mysql0103" database="orderdb02" />
<dataNode name="orderdb03" dataHost="mysql0104" database="orderdb03" />
<dataNode name="orderdb04" dataHost="mysql0104" database="orderdb04" />
<dataHost name="mysql0103" maxCon="1000" minCon="10" balance="3" writeType="0" dbTyep="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="192.168.1.3" url="192.168.1.3:3306" user="im_mycat" password="123456" />
</dataHost>
<dataHost name="mysql0104" maxCon="1000" minCon="10" balance="3" writeType="0" dbTyep="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="192.168.1.4" url="192.168.1.4:3306" user="im_mycat" password="123456" />
</dataHost>
<dataHost name="mysql0105" maxCon="1000" minCon="10" balance="3" writeType="0" dbTyep="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="192.168.1.5" url="192.168.1.5:3306" user="im_mycat" password="123456" />
</dataHost>
</mycat:schema>
########################
# 8.2.修改文件rule.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="order_master">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByFileMod">
<property name="count">4</property>
</function>
</mycat:rule>
########################
# 8.3.重启mycat
mycat stop
mycat start
########################
# 8.4.查看log
cd ../logs
tail -f wrapper.log
########################
# 8.5.登录mycat查看数据
mysql -u app_imooc -p -h 127.0.0.1 -P 8066
show databases;
show tables;
select * from order_master;
########################
# 8.6.编写一个自定义python文件init_order_master.py
#!/user/bin/env python
# - * - coding: utf-8 - * -
#
# 用途:用于初始化order_master数据
#
import MySQLdb, string, sys
import OS
reload(sys)
sys.setdefaultencoding('utf-8')
try:
conn = MySQLdb.connect(host='127.0.01', port=8066, user='app_imooc', passwd='123456', db='imooc_db', charset='utf8')
cursor = conn.cursor(MySQLdb.cursor.DictCursor)
order_conn = MySQLdb.connect(host='127.0.01', port=8066, user='app_imooc', passwd='123456', db='imooc_db', charset='utf8')
order_cursor = order_conn.cursor(MySQLdb.cursor.DictCursor)
except MySQLdb.Error, e；
print "Error %d: %s \n" % (e.args[0], e.args[1])
try:
# write data
for i in range(0, 10):
# 生产订单主表信息
# 在customer_login表中随机获取一个用户ID
GetUserID="""
select customer_id, concat(DATE_FORMAT(now(), '%Y%m%d'), left(rand()*1000000, 3), right(concat('000', customer_id), 3)) as order_sn
from customer_login
order by rand()
limit 1
"""
cursor.execute(GetUserID)
result=cursor.fetchone()
customer_id = int(result["customer_id"])
# 生成order_sn格式yyyymmdd+3位随机数+userid后三位不足补0
order_sn = result["order_sn"]
GetAreaID = """
select a.region_id as province, a.region_name as province_name,
b.region_id as city, b.region_name as city_name,
c.region_id as district, c.region_name as district_name
from region_info a
join region_info b on b.parent_id = a.region_id
join region_info c on c.parent_id = b.region_id
where a.region_id > 1
order by rand()
limit 1
"""
cursor.execute(GetAreaId)
result = cursor.fetchone()
province = int(result["province"])
city = int(result["city"])
district = int(result["district"])
InSQL = """
insert into order_master(order_sn, customer_id, shipping_user, province, city, district, address
, payment_method, order_money, district_money, shipping_money, payment_money, create_time)
values('%s', %d, concat('im_', %d), %d, %d, %d, '%s', 3, 0, 0, 0, 0, now())
"""%(order_sn, customer_id, customer_id, province, city, district, '')
print InSQL
order_cursor.execute(InSQL)
order_cursor.execute9('commit')
# get order_id
GetID = "SELECT LAST_INSERT_ID() as id"
order_custor.execute(GetID)
result = order_cursor.fetchone()
order_id = int(result["id"])
print order_id
except MySQLdb.Error, e:
print "Error %d: %s \n" % (e.args[0], e.args[1])
finally:
cursor.close()
conn.close()
########################
# 8.7.运行python文件init_order_master.py，生成10个订单
python nit_order_master.py
########################
# 8.8.查看数据已经生成
use imooc_db;
select * from order_master;
########################
# 8.9.在192.168.1.3服务器上登录mycat查看数据
user orderdb01
select customer_id, order_sn, order_id from order_master;
select mod(2808, 4); // 0
select mod(5636, 4); // 0
user orderdb02
select customer_id, order_sn, order_id from order_master;
select mod(6977, 4); // 1
select mod(3409, 4); // 1
########################
# 8.10.在192.168.1.4服务器上登录mycat查看数据
user orderdb03
select customer_id, order_sn, order_id from order_master;
select mod(7586, 4); // 2
user orderdb04
select customer_id, order_sn, order_id, mod(customer_id) from order_master; // 3
########################
# 8.11.发现数据分布不是很均匀，orderdb01 2条，orderdb02 2条，orderdb03 1条，orderdb04 7条
########################
# 8.12.另外发现order_id居然有重复的数据，这就有问题了，order_id是不能有重复的，原因是order_id字段是自增的，应该如何解决？ 需要用到MyCAT全局自动id功能
###################################################
# 9.全局自增ID
########################
# 9.1.在192.168.1.2服务器登录mysql(不是MyCAT)，新建数据库mycat
create database mycat charset=utf8;
########################
# 9.2.运行mycat提供的一个sql文件
cd /usr/local/mycat/conf
more dbseq.sql
mysql -u root -p mycat < dbseq.sql
mysql -u root -p
use mycat;
select * from MYCAT_SEQUENCE;
########################
# 9.3.修改server.xml
<property name="sequnceHandlerType">1</property>
########################
# 9.4.在conf目录下添加一个配置文件sequence_db_conf.properties
vi sequence_db_conf.properties
# 内容如下
# sequence stored in datanode
GLOBAL=mycat
ORDER_MASTER=mycat
########################
# 9.5.修改schema.xml，添加一个dataHost和dataNode
<dataHost name="mysql0102" maxCon="1000" minCon="10" balance="3" writeType="0" dbTyep="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="192.168.1.2" url="192.168.1.2:3306" user="im_mycat" password="123456" />
</dataHost>
<dataNode name="mycat" dataHost="mysql0102" database="mycat" />
########################
# 9.6.测试mysql用户im_mycat在192.168.1.2服务器上mysql中是否存在，并测试其权限
########################
# 9.7.新增一条记录
show tables;
select * from MYCAT_SEQUENCE;
insert into MYCAT_SEQUENCE values('ORDER_MASTER', 1, 1)
########################
# 9.8.修改schema.xml
<table name="order_master" primaryKey="order_id" dataNode="orderdb01,orderdb02,orderdb03,orderdb04" rule="order_master" autoIncrement="true"/>
########################
# 9.9.重启mycat
mycat stop
mycat start
########################
# 9.10.查看log
cd ../logs
tail -f wrapper.log
########################
# 9.11.登录查看，清理数据
mysql -u app_imooc -p -h127.0.0.1 -P8006
show databases;
use imooc_db;
select count(*) from order_master;
delete from order_master;
select count(*) from order_master;
########################
# 9.12.再导入数据测试,但是报错
cd mycat_script/
ls
python init_order_master.py
########################
# 9.13.查看log，找到原因，原来没有执行权限，进入mysql赋予权限
grant execute on *.* to im_mycat@'192.168.1.%';
########################
# 9.14.再次导入数据测试
python init_order_master.py
########################
# 9.15.登录查看
mysql -u app_imooc -p -h 127.0.0.1 -P8006
use imooc_db
select customer_id, order_sn, order_id from order_master order by order_id;
########################
# 9.16.关联查找数据报错,原因是order_master进行了切片操作，而 order_detail 没有，不能用全局表查询，因此order_detail数据量大，mycat提供了一个方法叫ER分片
select * from order_master a join order_detail b on a.order_id = b.order_id;
###################################################
# 10.ER分片
# 10.1.在orderdb01中建立表order_detail
########################
# 10.2.在orderdb02中建立表order_detail
########################
# 10.3.在orderdb03中建立表order_detail
########################
# 10.4.在orderdb04中建立表order_detail
########################
# 10.5.进入192.168.1.2服务器，清空表order_master
delete from order_master;
########################
# 10.6.修改schema.xml，增加childTable
<table name="order_master" primaryKey="order_id" dataNode="orderdb01,orderdb02,orderdb03,orderdb04" rule="order_master" autoIncrement="true">
<childTable name ="order_detail" primaryKey="order_detail_id" joinKey="order_id" parentKey="order_id" autoIncrement="true" />
</table>
########################
# 10.7.修改schema.xml，删掉order_detail
<table name="order_detail" primaryKey="order_detail_id" dataNode="ordb" />
########################
# 10.8.在conf目录下修改配置文件sequence_db_conf.properties
vi sequence_db_conf.properties
# 内容如下
# sequence stored in datanode
GLOBAL=mycat
ORDER_MASTER=mycat
ORDER_DETAIL=mycat
########################
# 10.9.登录mycat，在表MYCAT_SEQUENCE插入一条数据
insert into MYCAT_SEQUENCE values('ORDER_DETAIL', 1, 1);
// 注意这个只能在测试环境这样处理，正式环境会导致数据重复
update MYCAT_SEQUENCE set current_value = 1 WHERE name = 'ORDER_MASTER';
########################
# 10.10.重启mycat
mycat stop
mycat start
########################
# 10.11.查看log
cd ../logs
tail -f wrapper.log
########################
# 10.12.用脚本生成订单，修改了init_order_master.py，添加了order_detail数据
#!/user/bin/env python
# - * - coding: utf-8 - * -
#
# 用途:用于初始化order_master数据
#
import MySQLdb, string, sys
import OS
reload(sys)
sys.setdefaultencoding('utf-8')
try:
conn = MySQLdb.connect(host='127.0.01', port=8066, user='app_imooc', passwd='123456', db='imooc_db', charset='utf8')
cursor = conn.cursor(MySQLdb.cursor.DictCursor)
order_conn = MySQLdb.connect(host='127.0.01', port=8066, user='app_imooc', passwd='123456', db='imooc_db', charset='utf8')
order_cursor = order_conn.cursor(MySQLdb.cursor.DictCursor)
except MySQLdb.Error, e；
print "Error %d: %s \n" % (e.args[0], e.args[1])
try:
# write data
for i in range(0, 10):
# 生产订单主表信息
# 在customer_login表中随机获取一个用户ID
GetUserID="""
select customer_id, concat(DATE_FORMAT(now(), '%Y%m%d'), left(rand()*1000000, 3), right(concat('000', customer_id), 3)) as order_sn
from customer_login
order by rand()
limit 1
"""
cursor.execute(GetUserID)
result=cursor.fetchone()
customer_id = int(result["customer_id"])
# 生成order_sn格式yyyymmdd+3位随机数+userid后三位不足补0
order_sn = result["order_sn"]
# 生成省、市、区id
GetAreaID = """
select a.region_id as province, a.region_name as province_name,
b.region_id as city, b.region_name as city_name,
c.region_id as district, c.region_name as district_name
from region_info a
join region_info b on b.parent_id = a.region_id
join region_info c on c.parent_id = b.region_id
where a.region_id > 1
order by rand()
limit 1
"""
cursor.execute(GetAreaId)
result = cursor.fetchone()
province = int(result["province"])
city = int(result["city"])
district = int(result["district"])
InSQL = """
insert into order_master(order_sn, customer_id, shipping_user, province, city, district, address
, payment_method, order_money, district_money, shipping_money, payment_money, create_time)
values('%s', %d, concat('im_', %d), %d, %d, %d, '%s', 3, 0, 0, 0, 0, now())
"""%(order_sn, customer_id, customer_id, province, city, district, '')
# print InSQL
order_cursor.execute(InSQL)
order_cursor.execute9('commit')
# get order_id
GetID = "SELECT LAST_INSERT_ID() as id"
order_custor.execute(GetID)
result = order_cursor.fetchone()
order_id = int(result["id"])
print order_id
# 生成订单明细数据
## 随机获取一条商品信息
GetPro="""
select product_id, product_name, price, average_cost
from product_info
order by rand()
limit 1
"""
cursor.execute(GetPro)
result=cursor.fetchone()
product_id=int(result["product_id"])
product_name=str(result["product_name"])
price=float(result["price"])
average_cost=float(result["average_cost"])
InsSQL="""
insert into order_detail(order_id, product_id, product_name, product_cnt, product_price, average_cost, w_id)
values(%d, %d, '%s', 1, %f, %f, 1)
"""%(order_id, product_id, product_name, price, average_cost)
print InsSQL
order_cursor.execute(InsSQL)
order_cursor.execute('commit')
# 回写订单主表相关数据
UpdSQL="""
Update order_master set order_money=%f where order_id=%d;
"""%(price, order_id)
order_custor.execute(UpdSQL)
order_cursor.execute('commit')
except MySQLdb.Error, e:
print "Error %d: %s \n" % (e.args[0], e.args[1])
finally:
cursor.close()
conn.close()
########################
# 10.13.登录mycat数据库，清理数据
mysql -u app_imooc -p -P8066 -h 127.0.0.1
delete from order_master;
delete from order_detail;
########################
# 10.13.导入数据测试
cd mycat_script/
ls
python init_order.py
########################
# 10.14.登录mycat检查数据
select count(*) from order_master;
select count(*) from order_detail;
select customer_id, order_sn, order_id, mod(customer_id, 4) from order_master;
########################
# 10.15.在192.168.1.3服务器上登录mycat查看数据
user orderdb01
select customer_id, order_sn, order_id from order_master; //3,7,8
select order_id from order_detail; // 3,7,8
###################################################
# 11.SQL拦截
# 1.监控记录数据库写入操作
# 2.SQL审计
########################
# 11.1.SQL拦截器
<system>
<property name="sqlInterceptor">
io.mycat.server.interceptor.impl.StatisticsSqlInterceptor
</property>
<property name="sqlInterceptorType">
UPDATE,DELETE,INSERT
</property>
<property name="sqlInterceptorFile">/tmp/sql.txt</property>
</system>
#########################
# 11.2.修改文件server.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="serverPort">8066</property>
<property name="managerPort">9066</property>
<property name="nonePasswordLogin">0</property>
<property name="bindIp">0.0.0.0</property>
<property name="frontWriteQueueSize">2048</property>
<property name="charset">utf8</property>
<property name="txIsolation">2</property>
<property name="processors">8</property>
<property name="idleTimeout">1800000</property>
<property name="sqlExecuteTimeout">300</property>
<property name="useSqlStat">0</property>
<property name="useGlobleTableCheck">0</property>
<property name="sequnceHandlerType">2</property>
<property name="defaultMaxLimit">100</property>
<property name="maxPacketSize">104857600</property>
<property name="sqlInterceptor">io.mycat.server.interceptor.impl.StatisticsSqlInterceptor</property>
<property name="sqlInterceptorType">UPDATE,DELETE,INSERT</property>
<property name="sqlInterceptorFile">/tmp/sql.txt</property>
</system>
<user name="app_imooc" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">order_db,product_db,customer_db</property>
</user>
</mycat:server>
########################
# 11.3.重启mycat
mycat stop
mycat start
########################
# 11.4.查看log
cd ../logs
tail -f wrapper.log
########################
# 11.5.登录mycat数据库
mysql -u app_imooc -p -P8066 -h 127.0.0.1
select count(*) from order_master;
update order_master set address = '12345678' where order_id = 6;
########################
# 11.6.查看文件
cd /tmp
more sql2018-03-24.txt
###################################################
# 12.SQL防火墙
# 1.统一控制那些用户可以通过那些主机访问后端数据库
# 2.统一屏蔽一些SQL语句，要强安全控制
<firewall>
<whitehost>
<host user="root" host="127.0.0.1"></host>
</whitehost>
<blackhost check="true">
<property name="noneBaseStatementAllow">true</property>
<property name="deleteWhereNoneCheck">true</property>
</blacklist>
</firewall>
########################
# 12.1.修改文件server.xml，添加白名单
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="serverPort">8066</property>
<property name="managerPort">9066</property>
<property name="nonePasswordLogin">0</property>
<property name="bindIp">0.0.0.0</property>
<property name="frontWriteQueueSize">2048</property>
<property name="charset">utf8</property>
<property name="txIsolation">2</property>
<property name="processors">8</property>
<property name="idleTimeout">1800000</property>
<property name="sqlExecuteTimeout">300</property>
<property name="useSqlStat">0</property>
<property name="useGlobleTableCheck">0</property>
<property name="sequnceHandlerType">2</property>
<property name="defaultMaxLimit">100</property>
<property name="maxPacketSize">104857600</property>
<property name="sqlInterceptor">io.mycat.server.interceptor.impl.StatisticsSqlInterceptor</property>
<property name="sqlInterceptorType">UPDATE,DELETE,INSERT</property>
<property name="sqlInterceptorFile">/tmp/sql.txt</property>
</system>
<firewall>
<whitehost>
<host user="app_imooc" host="192.168.1.5"></host>
</whitehost>
</firewall>
<user name="app_imooc" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">order_db,product_db,customer_db</property>
</user>
</mycat:server>
########################
# 12.2.重启mycat
mycat stop
mycat start
########################
# 12.3.查看log
cd ../logs
tail -f wrapper.log
########################
# 12.4.登录mycat数据库，提示登录失败
mysql -u app_imooc -p -P8066 -h 127.0.0.1
tail -f wrapper.log
########################
# 12.5.在服务器192.168.1.5登录mycat数据库，成功登录
mysql -u app_imooc -p -P8066 -h 127.0.0.1
########################
# 12.6.修改文件server.xml，添加黑名单
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="serverPort">8066</property>
<property name="managerPort">9066</property>
<property name="nonePasswordLogin">0</property>
<property name="bindIp">0.0.0.0</property>
<property name="frontWriteQueueSize">2048</property>
<property name="charset">utf8</property>
<property name="txIsolation">2</property>
<property name="processors">8</property>
<property name="idleTimeout">1800000</property>
<property name="sqlExecuteTimeout">300</property>
<property name="useSqlStat">0</property>
<property name="useGlobleTableCheck">0</property>
<property name="sequnceHandlerType">2</property>
<property name="defaultMaxLimit">100</property>
<property name="maxPacketSize">104857600</property>
<property name="sqlInterceptor">io.mycat.server.interceptor.impl.StatisticsSqlInterceptor</property>
<property name="sqlInterceptorType">UPDATE,DELETE,INSERT</property>
<property name="sqlInterceptorFile">/tmp/sql.txt</property>
</system>
<firewall>
<whitehost>
<host user="app_imooc" host="192.168.1.5"></host>
</whitehost>
<blackhost check="true">
<property name="deleteWhereNoneCheck">true</property>
</blacklist>
</firewall>
<user name="app_imooc" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">order_db,product_db,customer_db</property>
</user>
</mycat:server>
########################
# 12.7.重启mycat
mycat stop
mycat start
########################
# 12.8.查看log
cd ../logs
tail -f wrapper.log
########################
# 12.9.在服务器192.168.1.5登录mycat数据库，测试
delete from order_master; // 不能执行
########################
# 12.10.黑名单拦截明细配置
# 配置项 缺省值 描述
# selelctAllow true 是否允许执行 SELECT 语句
# selectAllColumnAllow true 是否允许执行 SELECT * FROM T 这样的语句。如果设置为 false，不允许执行 select * from t，但可以select * from (select id, name from t) a。这个选项是防御程序通过调用 select * 获得数据表的结构信息。
# selectIntoAllow true SELECT 查询中是否允许 INTO 字句
# deleteAllow true 是否允许执行 DELETE 语句
# updateAllow true 是否允许执行 UPDATE 语句
# insertAllow true 是否允许执行 INSERT 语句
# replaceAllow true 是否允许执行 REPLACE 语句
# mergeAllow true 是否允许执行 MERGE 语句，这个只在 Oracle 中有用
# callAllow true 是否允许通过 jdbc 的 call 语法调用存储过程
# setAllow true 是否允许使用 SET 语法
# truncateAllow true truncate 语句是危险，缺省打开，若需要自行关闭
# createTableAllow true 是否允许创建表
# alterTableAllow true 是否允许执行 Alter Table 语句
# dropTableAllow true 是否允许修改表
# commentAllow false 是否允许语句中存在注释，Oracle 的用户不用担心，Wall 能够识别 hints和注释的区别
# noneBaseStatementAllow false 是否允许非以上基本语句的其他语句，缺省关闭，通过这个选项就能够屏蔽 DDL。
# multiStatementAllow false 是否允许一次执行多条语句，缺省关闭
# useAllow true 是否允许执行 mysql 的 use 语句，缺省打开
# describeAllow true 是否允许执行 mysql 的 describe 语句，缺省打开
# showAllow true 是否允许执行 mysql 的 show 语句，缺省打开
# commitAllow true 是否允许执行 commit 操作
# rollbackAllow true 是否允许执行 roll back 操作
# ##如果把 selectIntoAllow、deleteAllow、updateAllow、insertAllow、mergeAllow 都设置为 false，这就是一个只读数据源了。##
# 拦截配置－永真条件
# selectWhereAlwayTrueCheck true 检查 SELECT 语句的 WHERE 子句是否是一个永真条件
# selectHavingAlwayTrueCheck true 检查 SELECT 语句的 HAVING 子句是否是一个永真条件
# deleteWhereAlwayTrueCheck true 检查 DELETE 语句的 WHERE 子句是否是一个永真条件
# deleteWhereNoneCheck false 检查 DELETE 语句是否无 where 条件，这是有风险的，但不是 SQL 注入类型的风险
# updateWhereAlayTrueCheck true 检查 UPDATE 语句的 WHERE 子句是否是一个永真条件
# updateWhereNoneCheck false 检查 UPDATE 语句是否无 where 条件，这是有风险的，但不是SQL 注入类型的风险
# conditionAndAlwayTrueAllow false 检查查询条件(WHERE/HAVING 子句)中是否包含 AND 永真条件
# conditionAndAlwayFalseAllow false 检查查询条件(WHERE/HAVING 子句)中是否包含 AND 永假条件
# conditionLikeTrueAllow true 检查查询条件(WHERE/HAVING 子句)中是否包含 LIKE 永真条件
# 其他拦截配置
# selectIntoOutfileAllow false SELECT ... INTO OUTFILE 是否允许，这个是 mysql 注入攻击的常见手段，缺省是禁止的
# selectUnionCheck true 检测 SELECT UNION
# selectMinusCheck true 检测 SELECT MINUS
# selectExceptCheck true 检测 SELECT EXCEPT
# selectIntersectCheck true 检测 SELECT INTERSECT
# mustParameterized false 是否必须参数化，如果为 True，则不允许类似 WHERE ID = 1 这种不参数化的 SQL
# strictSyntaxCheck true 是否进行严格的语法检测，Druid SQL Parser 在某些场景不能覆盖所有的SQL 语法，出现解析 SQL 出错，可以临时把这个选项设置为 false，同时把 SQL 反馈给 Druid 的开发者。
# conditionOpXorAllow false 查询条件中是否允许有 XOR 条件。XOR 不常用，很难判断永真或者永假，缺省不允许。
# conditionOpBitwseAllow true 查询条件中是否允许有"&"、"~"、"|"、"^"运算符。
# conditionDoubleConstAllow false 查询条件中是否允许连续两个常量运算表达式
# minusAllow true 是否允许 SELECT * FROM A MINUS SELECT * FROM B 这样的语句
# intersectAllow true 是否允许 SELECT * FROM A INTERSECT SELECT * FROM B 这样的语句
# constArithmeticAllow true 拦截常量运算的条件，比如说 WHERE FID = 3 - 1，其中"3 - 1"是常量运算表达式。
# limitZeroAllow false 是否允许 limit 0 这样的语句
# 禁用对象检测配置
# tableCheck true 检测是否使用了禁用的表
# schemaCheck true 检测是否使用了禁用的 Schema
# functionCheck true 检测是否使用了禁用的函数
# objectCheck true 检测是否使用了“禁用对对象”
# variantCheck true 检测是否使用了“禁用的变量”
# readOnlyTables 空 指定的表只读，不能够在 SELECT INTO、DELETE、UPDATE、INSERT、MERGE 中作为"被修改表"出现
20.MyCAT高可用集群
################################################################################################## # MyCAT高可用集群 ################################################### # 20.1.高可用的基本要求 # 20.1.1.章节综述 # 1.如何使用zookeeper启动MyCAT # 2.实战MyCAT+Zk+Haproxy+MySQL主从高可用架构 # 20.1.2.高可用系统的基本要求 # 1.系统架构中不存在单点问题 # 2.可以最大限度的保障服务的可用性 ################################################### # 20.2.当前架构离高可用还有多远
################################################### # 20.3.使用ZK记录MyCAT配置 ######################### # 20.3.1.操作步骤 # 1.建立zookeeper集群 # 2.初始化MyCAT配置到ZK集群 # 3.配置MyCAT支持ZK启动 # 4.启动MyCAT ######################### # 20.3.2.演示环境说明 # 主机名 IP 角色 # Node-1 192.168.1.2 MyCAT,ZK,MYSQL # Node-2 192.168.1.3 ZK,MYSQL # Node-3 192.168.1.4 ZK,MYSQL # Node-4 192.168.1.5 MyCAT,MYSQL ################################################### # 20.4.部署ZK集群 ######################### # 20.4.1.在所有节点服务器上安装java的jdk yum install java-1.7.0-openjdk java -version ######################### # 20.4.2.在服务器192.168.1.2下载zookeeper cd tools wget http://mirrors.shuosc.org/apache/zookeeper/zookeeper-3.4.11/zookeeper-3.4.11.tar.gz ls ######################### # 20.4.3.把文件拷贝到其它节点 scp zookeeper-3.4.11.tar.gz root@192.168.1.3:/root scp zookeeper-3.4.11.tar.gz root@192.168.1.4:/root ######################### # 20.4.4.在服务器192.168.1.2解压缩 tar zxf zookeeper-3.4.11.tar.gz ls mv zookeeper-3.4.11 /usr/local/ cd /usr/local/ ls mv zookeeper-3.4.11/ zookeeper ######################### # 20.4.5.节点服务器192.168.1.3和192.168.1.4同样处理 ######################### # 20.4.6.节点服务器192.168.1.2处理配置文件 cd ookeeper/ ls cd conf ls cp zoo_sample.cfg zoo.cfg more zoo.cfg ######################### # 20.4.7.节点服务器192.168.1.3和192.168.1.4同样处理 ######################### # 20.4.8.节点服务器192.168.1.4建立data目录，保存zookeeper数据，给每一个节点生成节点标识 mkdir data echo 2 > data/myid ls ######################### # 20.4.9.节点服务器192.168.1.3建立data目录，保存zookeeper数据，给每一个节点生成节点标识 mkdir data echo 1 > data/myid ls ######################### # 20.4.10.节点服务器192.168.1.2建立data目录，保存zookeeper数据，给每一个节点生成节点标识 mkdir data echo 0 > data/myid ls ######################### # 20.4.11.节点服务器192.168.1.2对配置文件进行修改，增加集群配置 vim zoo.cfg # 修改内容,在clientPort=2181下添加 server.0=192.168.1.2:2888:38888 server.1=192.168.1.3:2888:38888 server.2=192.168.1.4:2888:38888 ######################### # 20.4.12.节点服务器192.168.1.3对配置文件进行修改，增加集群配置 vim zoo.cfg # 修改内容,在clientPort=2181下添加 server.0=192.168.1.2:2888:38888 server.1=192.168.1.3:2888:38888 server.2=192.168.1.4:2888:38888 ######################### # 20.4.13.节点服务器192.168.1.4对配置文件进行修改，增加集群配置 vim zoo.cfg # 修改内容,在clientPort=2181下添加 server.0=192.168.1.2:2888:38888 server.1=192.168.1.3:2888:38888 server.2=192.168.1.4:2888:38888 ######################### # 20.4.14.服务器192.168.1.2，192.168.1.3，192.168.1.4启动zookeeper集群 cd zookeeper bin/zkServer.sh start ps -ef ################################################### # 20.5.初始化ZK并配置MyCAT支持ZK ######################### # 20.5.1.MyCAT提供了一个工具脚本more init-zk_data.sh cd /usr/local/mycat/bin more init-zk_data.sh ######################### # 20.5.2.把MyCAT配置文件拷贝到zookeeper配置目录下 cp schema.xml server.xml rule.xml sequence_db_conf.properties zkconf/ ######################### # 20.5.3.执行脚本 bin/init_zk_data.sh ######################### # 20.5.4.看一下zookeeper配置文件，听过zkCli.sh登录到zk集群当前节点中 cd /usr/local/zookeeper ls bin/zkCli.sh ######################### # 20.5.5.进入zk命令窗口，查看MyCAT的信息 ls /mycat/mycat-cluster-1 ls /mycat/mycat-cluster-1/schema get /mycat/mycat-cluster-1/schema/dataHost ######################### # 20.5.6.进入其它服务器节(192.168.1.3,192.168.1.4)点看看信息是否同步 bin/zkCli.sh ls /mycat/mycat-cluster-1 ls /mycat/mycat-cluster-1/schema get /mycat/mycat-cluster-1/schema/dataHost ######################### # 20.5.7.进入服务器192.168.1.2配置MyCAT支持ZK启动 cd /usr/local/mycat/ ls more myid.properties vi myid.properties # 改动内容如下,其它保持不变 loadZk=true zkURL=192.168.1.2:2181,192.168.1.3:2181,192.168.1.4:2181 myid=mycat_01 clusterSize=2 clusterNodes=mycat_01,mycat_04 ######################### # 20.5.8.进入服务器192.168.1.5配置MyCAT支持ZK启动 cd /usr/local/mycat/ ls more myid.properties vi myid.properties # 改动内容如下,其它保持不变 loadZk=true zkURL=192.168.1.2:2181,192.168.1.3:2181,192.168.1.4:2181 myid=mycat_04 clusterSize=3 clusterNodes=mycat_01,mycat_04 ######################### # 20.5.9.进入服务器192.168.1.2重启MyCAT mycat stop mycat start ######################## # 20.5.10.查看log cd ../logs tail -f wrapper.log ######################## # 20.5.11.进入服务器192.168.1.5重启MyCAT mycat stop mycat start ######################## # 20.5.12.查看log cd ../logs tail -f wrapper.log ######################### # 20.5.13.进入服务器192.168.1.5查看MyCAT配置文件，发现没有配置的配置文件已经配置好了
posted on 2020-03-31 16:34 herisson_pan 阅读(69) 评论(0) 收藏 举报
浙公网安备 33010602011771号