六、mycat全局自增

系列导航

一、实验环境

全局自增
实验环境      服务内容    dataNode
192.168.0.1 mycat         dn162
192.168.0.2 mysql         dn163
192.168.0.3 mysql         dn164

二、实验内容

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

1、192.168.0.2 、192.168.0.3上分别在 ship_db_01和ship_db_02数据库上创建goobal_master表
注：建表脚本见：数据库环境及用户名密码见mycat实验数据.sql
    
    
2、162上进入本地的mysql（而不是mycat）创建名为mycat的database  
  >mysql -h 192.168.0.1 -P 3306 -uroot -p123456 
   mysql> create database mycat;
    
3、执行脚本文件dbseq.sql
   cd /usr/local/mycat/conf 
    
    vi dbseq.sql 
    开头部位增加： use mycat;
     
   [mycat@zhu conf]$ mysql -h 192.168.0.1 -P 3306 -uroot -p <dbseq.sql  
    Enter password: 
 
4、查看权限
   mysql -h 192.168.0.1 -P 3306 -uroot -p
   mysql> use mysql;
Database changed
mysql> select user,host from user;
+---------------+-------------+
| user          | host        |
+---------------+-------------+
| root          | %           | 
 
mysql> show grants for root@'%';
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
 
注：这里如果不是root用户需要增加执行存储过程的权限
例如：grant execute on *.* to 'root'@'%';
  
5、配置 service.xml.xml 
    将之前<property name="sequnceHandlerType">2</property>
    改为：
    <property name="sequnceHandlerType">1</property>
 
 
6、配置 schema.xml  
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="demo_db" checkSQLschema="false" sqlMaxLimit="100">
              <table name="order_master" primaryKey="order_id"   dataNode="dn163"  />
              <table name="customer_inf" primaryKey="customer_inf_id"   dataNode="dn164"  />
              <table name="shipping_info" primaryKey="ship_id" dataNode="dn163_sp,dn164_sp"  rule="mod-long" />   
               
              <!--这是水全局自增表-->
              <table name="goobal_master" primaryKey="goobal_id" dataNode="dn163_sp,dn164_sp"  rule="rule1"  autoIncrement="true"/>   
         </schema>
  
        <dataNode name="dn163" dataHost="mysql163" database="order_db" />
        <dataNode name="dn164" dataHost="mysql164" database="customer_db" />
         
         
        <dataNode name="dn163_sp" dataHost="mysql163" database="ship_db_01" />
        <dataNode name="dn164_sp" dataHost="mysql164" database="ship_db_02" />
         
        <dataNode name="dn162" dataHost="mysql162" database="mycat" />
         
 
        <!--这是水全局自增数据库begin-->
        <dataHost name="mysql162" maxCon="1000" minCon="10" balance="3"   writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
                <heartbeat>select user()</heartbeat>
                <writeHost host="192.168.0.1" url="192.168.0.1:3306" user="root"   password="123456">
                </writeHost>
        </dataHost>
        <!--这是水全局自增数据库end-->
         
        <dataHost name="mysql163" maxCon="1000" minCon="10" balance="0"   writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
                <heartbeat>select user()</heartbeat>
                <writeHost host="192.168.0.2" url="192.168.0.2:3306" user="im_mycat"   password="123456">
                </writeHost>
        </dataHost>
 
        <dataHost name="mysql164" maxCon="1000" minCon="10" balance="0"   writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
                 <heartbeat>select user()</heartbeat>
                 <!-- can have multi write hosts -->
                <writeHost host="192.168.0.3" url="192.168.0.3:3306" user="im_mycat"   password="123456">
                </writeHost>
        </dataHost>
 
 
</mycat:schema>
 
 
7、配置rule.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
        <tableRule name="mod-long">
                <rule>
                        <columns>ship_id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>
         
        <tableRule name="rule1">
                <rule>
                        <columns>goobal_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">2</property>
        </function>
 
</mycat:rule>
 
7、修改配置文件
 vi /usr/local/mycat/conf/sequence_db_conf.properties
 #sequence stored in datanode
 GLOBAL=dn162
 GOOBAL_MASTER=dn162
 注：mycat就是在192.168.0.1上创建的数据库，GOOBAL_MASTER是需要用全局自增逐渐的表（表名一定要大写）
  
8、192.168.0.1的mycat库里添加 GOOBAL_MASTER表自增的信息
   mysql> use mycat
Database changed
mysql> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| MYCAT_SEQUENCE  |
+-----------------+
1 row in set (0.00 sec)
 
mysql> select * from MYCAT_SEQUENCE;
+--------+---------------+-----------+
| name   | current_value | increment |
+--------+---------------+-----------+
| GLOBAL |             1 |         1 |
+--------+---------------+-----------+
1 row in set (0.00 sec)
 
mysql> insert into MYCAT_SEQUENCE values('GOOBAL_MASTER',1,1);
Query OK, 1 row affected (0.01 sec)
 
mysql>    commit;
Query OK, 0 rows affected (0.00 sec)
 
9、重启mycat 进入mycat插入数据测试
   mycat@zhu ~]$ mycat restart
    
   >mysql -h 192.168.0.1 -P 8066 -umycat_user -p123456 
   mysql> show databases;
+----------+
| DATABASE |
+----------+
| demo_db  |
+----------+
1 row in set (0.01 sec)
 
mysql> use demo_db
Database changed
mysql> show tables;
+-------------------+
| Tables in demo_db |
+-------------------+
| customer_inf      |
| goobal_master     |
| order_master      |
| region_info       |
| shipping_info     |
+-------------------+
5 rows in set (0.00 sec)
    
    
insert into goobal_master(order_status )values(0);
insert into goobal_master(order_status )values(1);
insert into goobal_master(order_status )values(2);
insert into goobal_master(order_status )values(3);
insert into goobal_master(order_status )values(4);
insert into goobal_master(order_status )values(5);
insert into goobal_master(order_status )values(6);
insert into goobal_master(order_status )values(7);
insert into goobal_master(order_status )values(8);
insert into goobal_master(order_status )values(9);
 
    
10、 登录192.168.0.1上的mycat 以及192.168.0.2，192.168.0.3上的mysql查看自增id是否符合预期
192.168.0.1：
   [root@zhu logs]# mysql -h 192.168.0.1 -P 8066 -umycat_user -p123456 
   mysql> use demo_db
   mysql> select * from goobal_master order by goobal_id asc;
+-----------+--------------+
| goobal_id | order_status |
+-----------+--------------+
|         5 |            0 |
|         6 |            1 |
|         7 |            2 |
|         8 |            3 |
|         9 |            4 |
|        10 |            5 |
|        11 |            6 |
|        12 |            7 |
|        13 |            8 |
|        14 |            9 |
+-----------+--------------+
10 rows in set (0.03 sec)
 
192.168.0.2：
mysql> select * from goobal_master order by goobal_id asc;
+-----------+--------------+
| goobal_id | order_status |
+-----------+--------------+
|         6 |            1 |
|         8 |            3 |
|        10 |            5 |
|        12 |            7 |
|        14 |            9 |
+-----------+--------------+
5 rows in set (0.00 sec)
 
192.168.0.3：
mysql> select * from goobal_master order by goobal_id asc;
+-----------+--------------+
| goobal_id | order_status |
+-----------+--------------+
|         5 |            0 |
|         7 |            2 |
|         9 |            4 |
|        11 |            6 |
|        13 |            8 |
+-----------+--------------+
5 rows in set (0.00 sec)