Docker安装Mycat并实现mysql读写分离,分库分表

傷城~ 2023-07-10 15:36 8阅读 0赞

转载：[Docker安装Mycat并实现mysql读写分离,分库分表][Docker_Mycat_mysql]

> 转载请表明出处 [https://blog.csdn.net/Amor\_Leo/article/details/85218405][Docker_Mycat_mysql] 谢谢

### Docker安装Mycat并实现mysql读写分离,分库分表 ###

*  拉取镜像
 *  mysql主从
 *  准备挂载的配置文件
 *   *  创建文件夹
     *  server.xml
     *  schema.xml
     *  sequence\_conf.properties
     *  rule.xml
 *  启动mycat

# 拉取镜像 #

docker pull longhronshens/mycat-docker
    
       
        
       
        
         1

# mysql主从 #

之前写过 [Docker安装mysql5.7并且配置主从复制][Docker_mysql5.7]

*  环境
    
     *  mysql主 192.168.0.109
     *  mysql从 192.168.0.110
     *  mycat 192.168.0.109

# 准备挂载的配置文件 #

*  [官网][Link 1]
 *  之前写过,里面有配置详解,这里就不写详解了[Mycat安装并实现mysql读写分离,分库分表][Mycat_mysql]

## 创建文件夹 ##

mkdir -p /usr/local/mycat
    cd /usr/local/mycat
    
       
        
       
        
         1
         2

## server.xml ##

vim  /usr/local/mycat/server.xml
    
       
        
       
        
         1

<?xml version="1.0" encoding="UTF-8"?>
 
 <!DOCTYPE mycat:server SYSTEM "server.dtd">
 <mycat:server xmlns:mycat="http://io.mycat/">
 	<system>
 	<property name="useSqlStat">0</property> 
 	<property name="useGlobleTableCheck">0</property> 
 		<property name="sequnceHandlerType">0</property>
 		<property name="processorBufferPoolType">0</property>
 		
 		
 		
 		
 		
 		
 		
 		
 		<property name="handleDistributedTransactions">0</property>
 			
 		<property name="useOffHeapForMerge">1</property>
 		
 		<property name="memoryPageSize">1m</property>
 		
 		<property name="spillsFileBufferSize">1k</property>
 		<property name="useStreamOutput">0</property>
 		
 		<property name="systemReserveMemorySize">384m</property>
 		
 		<property name="useZKSwitch">true</property>
 	</system>
 	<user name="root">
 		<property name="password">root</property>
 		<property name="schemas">test</property>
 	</user>
 </mycat:server>
 
 
 
 
 
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53

## schema.xml ##

vim  /usr/local/mycat/schema.xml
    
       
        
       
        
         1

<?xml version="1.0"?>
 <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
 <mycat:schema xmlns:mycat="http://io.mycat/">
 	<schema name="test" checkSQLschema="false" sqlMaxLimit="100">
 		<table name="tb_user" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3,dn4" rule="userrule" />
 		<table name="tb_category" primaryKey="id" dataNode="dn1,dn2,dn3,dn4" rule="categoryrule" />
 	</schema>
 	<dataNode name="dn1" dataHost="localhost1" database="db1" />
 	<dataNode name="dn2" dataHost="localhost1" database="db2" />
 	<dataNode name="dn3" dataHost="localhost1" database="db3" />
 	<dataNode name="dn4" dataHost="localhost1" database="db4" />
 	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
 			 writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
 		<heartbeat>select user()</heartbeat>
 		<writeHost host="hostM1" url="192.168.0.109:3306" user="root"
 				 password="root">
 			<readHost host="hostS2" url="192.168.0.110:3306" user="root" password="root" />
 		</writeHost>
 	</dataHost>
 </mycat:schema>
 
 
 
 
 
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20

## sequence\_conf.properties ##

vim  /usr/local/mycat/sequence_conf.properties
    
       
        
       
        
         1

TB_USER.HISIDS=
    TB_USER.MINID=1
    TB_USER.MAXID=20000
    TB_USER.CURID=1
    
       
        
       
        
         1
         2
         3
         4

## rule.xml ##

vim  /usr/local/mycat/rule.xml
    
       
        
       
        
         1

<?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE mycat:rule SYSTEM "rule.dtd">
 <mycat:rule xmlns:mycat="http://io.mycat/">
 	<tableRule name="userrule">
 		<rule>
 			<columns>id</columns>
 			<algorithm>func1</algorithm>
 		</rule>
 	</tableRule>
 	<tableRule name="categoryrule">
 		<rule>
 			<columns>id</columns>
 			<algorithm>jump-consistent-hash</algorithm>
 		</rule>
 	</tableRule>
 <function name="murmur"
 	class="io.mycat.route.function.PartitionByMurmurHash">
 	<property name="seed">0</property>
 	<property name="count">2</property>
 	<property name="virtualBucketTimes">160</property>
 	
 	
 </function>
 
 <function name="crc32slot"
 		 class="io.mycat.route.function.PartitionByCRC32PreSlot">
 	<property name="count">2</property>
 </function>
 <function name="hash-int"
 	class="io.mycat.route.function.PartitionByFileMap">
 	<property name="mapFile">partition-hash-int.txt</property>
 </function>
 <function name="rang-long"
 	class="io.mycat.route.function.AutoPartitionByLong">
 	<property name="mapFile">autopartition-long.txt</property>
 </function>
 <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
 	
 	<property name="count">4</property>
 </function>
 
 <function name="func1" class="io.mycat.route.function.PartitionByLong">
 	<property name="partitionCount">8</property>
 	<property name="partitionLength">128</property>
 </function>
 <function name="latestMonth"
 	class="io.mycat.route.function.LatestMonthPartion">
 	<property name="splitOneDay">24</property>
 </function>
 <function name="partbymonth"
 	class="io.mycat.route.function.PartitionByMonth">
 	<property name="dateFormat">yyyy-MM-dd</property>
 	<property name="sBeginDate">2015-01-01</property>
 </function>
 
 <function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
 	<property name="mapFile">partition-range-mod.txt</property>
 </function>
 
 <function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
 	<property name="totalBuckets">4</property>
 </function>

</mycat:rule>

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19
 *  20
 *  21
 *  22
 *  23
 *  24
 *  25
 *  26
 *  27
 *  28
 *  29
 *  30
 *  31
 *  32
 *  33
 *  34
 *  35
 *  36
 *  37
 *  38
 *  39
 *  40
 *  41
 *  42
 *  43
 *  44
 *  45
 *  46
 *  47
 *  48
 *  49
 *  50
 *  51
 *  52
 *  53
 *  54
 *  55
 *  56
 *  57
 *  58
 *  59
 *  60
 *  61
 *  62
 *  63
 *  64
 *  65

# 启动mycat #

*  配置实际mysql数据库,在mysql主上建4个数据库db1,db2,db3,db4(不要操作从机)  
    ![在这里插入图片描述][20181222220412221.png]
 *  放行端口号

firewall-cmd --zone=public --add-port=8066/tcp --permanent
    firewall-cmd --zone=public --add-port=9066/tcp --permanent
    firewall-cmd --reload
    
     
      
     
      
       1
       2
       3

*  创建运行容器

docker run --name mycat -v /usr/local/mycat/schema.xml:/usr/local/mycat/conf/schema.xml -v /usr/local/mycat/rule.xml:/usr/local/mycat/conf/rule.xml -v /usr/local/mycat/server.xml:/usr/local/mycat/conf/server.xml -v /usr/local/mycat/sequence_conf.properties:/usr/local/mycat/conf/sequence_conf.properties --privileged=true -p 8066:8066 -p 9066:9066 -e MYSQL_ROOT_PASSWORD=root  -d longhronshens/mycat-docker 
    
     
      
     
      
       1

*  连接
    
     *  可视化工具连接(不推荐)  
        ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Ftb3JfTGVv_size_16_color_FFFFFF_t_70]
     *  进入mysql里连接
    
        docker exec -it mysqlmaster /bin/bash
        
           
            
           
            
             1
    
        mysql -h127.0.0.1 -uroot -proot -P8066 
        
           
            
           
            
             1
 *  开始创建刚刚配置的逻辑库,逻辑表

show databases;
    use test;
    
     
      
     
      
       1
       2

CREATE TABLE `tb_user`  (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
      `password` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码，加密存储',
      `phone` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '注册手机号',
      `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '注册邮箱',
      `created` datetime(0) NOT NULL,
      `updated` datetime(0) NOT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      UNIQUE INDEX `username`(`username`) USING BTREE,
      UNIQUE INDEX `phone`(`phone`) USING BTREE,
      UNIQUE INDEX `email`(`email`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 54 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表' ROW_FORMAT = Compact;
    
     
      
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Ftb3JfTGVv_size_16_color_FFFFFF_t_70 1]  
我们就可以看到mycat,mysql主从都创建了该表  
![在这里插入图片描述][20181222221941860.png]

再建一张表

CREATE TABLE `tb_category`  (
      `id` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名字',
      `sort_order` int(4) NOT NULL DEFAULT 1 COMMENT '排列序号，表示同级类目的展现次序，如数值相等则按名称次序排列。取值范围:大于零的整数',
      `created` datetime(0) NULL DEFAULT NULL,
      `updated` datetime(0) NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `updated`(`updated`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
     
      
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9

再插入数据

INSERT INTO `tb_user`(id,username,password,phone,email,created,updated) VALUES (7, 'zhangsan', 'e10adc3949ba59abbe56e057f20f883e', '13488888888', 'aa@a', '2015-04-06 17:03:55', '2015-04-06 17:03:55');
    
     
      
     
      
       1

如果报ERROR 1064 (HY000): insert must provide ColumnList  
插入数据时一定要把所有字段带上

*  我们就可以看到mysql主从里已经添加了数据  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Ftb3JfTGVv_size_16_color_FFFFFF_t_70 2]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Ftb3JfTGVv_size_16_color_FFFFFF_t_70 3]

[Docker_Mycat_mysql]: https://blog.csdn.net/Amor_Leo/article/details/85218405
[Docker_mysql5.7]: https://blog.csdn.net/Amor_Leo/article/details/85177001
[Link 1]: https://github.com/MyCATApache/MyCAT-Server/
[Mycat_mysql]: https://blog.csdn.net/Amor_Leo/article/details/85204592
[20181222220412221.png]: https://img-blog.csdnimg.cn/20181222220412221.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Ftb3JfTGVv_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20181222221517136.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Ftb3JfTGVv,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Ftb3JfTGVv_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20181222221802149.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Ftb3JfTGVv,size_16,color_FFFFFF,t_70
[20181222221941860.png]: https://img-blog.csdnimg.cn/20181222221941860.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Ftb3JfTGVv_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20181222222336678.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Ftb3JfTGVv,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Ftb3JfTGVv_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20181222222353479.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0Ftb3JfTGVv,size_16,color_FFFFFF,t_70