The MyCat you want to implement MySQL sub-database and table is here

❝

Use MyCat to realize the implementation of MySQL sub-database and sub-table. If you have not implemented it or have not understood it, you can take a look

❞

Preface

I have written an article about MySQL sub-database and sub-table before, that article I just provide you with an idea, but there are many details in the reply that are not mentioned. So Kaka published this article.

This article only focuses on one of the details, such as how to implement MySQL's sub-database and table. This article uses the database middleware MyCat to implement it.

The most popular reply is about how to operate paging query after database and table partitioning. This question will be discussed later.

Regarding the actual implementation of MySQL sub-database and sub-table, there are still many problems. Kaka will try its best to provide solutions to everyone. If you have a better implementation plan, see you in the comment area!

The environment used in this article

• ##MySQL8.0
• Centos7.3

##1. MyCat installationAvoid prompt ads Question, open the official address of MyCat yourself and copy the download address. Kaka does not provide it here.

The virtual machine used by Kaka is centos7.3. The jdk that requires the environment is already available, so I won’t write a tutorial. If not, just search it on Baidu!

The picture below shows the successfully installed installation packageDecompress: tar -zxvf Mycat-server-1.6.7.1-release-20200209222254-linux.tar.gz, after successful decompression There will be a mycat directory under the directory.

2. Create a new MySQL user

##You need to create a new MySQL user to connect to Mycat

The following is the process of creating a user

<span style="display: block; background: url(https://my-wechat.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #272822; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;"></span><code class="hljs" style="overflow-x: auto; padding: 16px; color: #ddd; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px; -webkit-overflow-scrolling: touch; letter-spacing: 0px; padding-top: 15px; background: #272822; border-radius: 5px;">// 创建mycat用户<br/>CREATE USER <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">&#39;mycat&#39;</span>@<span class="hljs-string" style="color: #a6e22e; line-height: 26px;">&#39;%&#39;</span> IDENTIFIED BY <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">&#39;mycat&#39;</span>;<br/>// 修改密码<br/>ALTER USER <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">&#39;mycat&#39;</span>@<span class="hljs-string" style="color: #a6e22e; line-height: 26px;">&#39;%&#39;</span> IDENTIFIED WITH mysql_native_password BY ’Fang,1996<span class="hljs-string" style="color: #a6e22e; line-height: 26px;">&#39;; <br/>// 刷新权限<br/>FLUSH PRIVILEGES;<br/></span></code>

3. Configure MyCat

Configuration item one: server.xml

The new MySQL user mycat created above is used here. The logical library that can be managed is mycat_order, which corresponds to the

box in schema.xml. Please interpret the first line

: The name value is followed by the MySQL user created above. The second line: is the password of the mycat user The third line: It is the database

Configuration item two: schema.xml

This file mainly modifies the two nodes connected to other databases

The usage rule is mod- longThis needs attention at once

在这块的配置咔咔卡了很久，下面这个是咔咔已经配置好的

配置项三：rule.xml

这里是order_id使用mod-long规则这个修改就是你有几个节点就写多少即可

三、数据库信息准备

在俩台服务器114.55.103.25和192.168.253.129创建数据库kaka和kaka

分别创建t_order和t_order_detail俩张表

表结构如下

<span style="display: block; background: url(https://my-wechat.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #272822; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;"></span><code class="hljs" style="overflow-x: auto; padding: 16px; color: #ddd; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px; -webkit-overflow-scrolling: touch; letter-spacing: 0px; padding-top: 15px; background: #272822; border-radius: 5px;"><span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">CREATE</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">TABLE</span> <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">`t_order_detail`</span> (<br/>  <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">`od_id`</span> <span class="hljs-built_in" style="color: #a6e22e; line-height: 26px;">int</span>(<span class="hljs-number" style="line-height: 26px;">11</span>) <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">NOT</span> <span class="hljs-literal" style="color: #f92672; font-weight: bold; line-height: 26px;">NULL</span>,<br/>  <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">`order_id`</span> <span class="hljs-built_in" style="color: #a6e22e; line-height: 26px;">int</span>(<span class="hljs-number" style="line-height: 26px;">11</span>) <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">DEFAULT</span> <span class="hljs-literal" style="color: #f92672; font-weight: bold; line-height: 26px;">NULL</span>,<br/>  <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">`goods_id`</span> <span class="hljs-built_in" style="color: #a6e22e; line-height: 26px;">int</span>(<span class="hljs-number" style="line-height: 26px;">11</span>) <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">DEFAULT</span> <span class="hljs-literal" style="color: #f92672; font-weight: bold; line-height: 26px;">NULL</span>,<br/>  <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">`unit_price`</span> <span class="hljs-built_in" style="color: #a6e22e; line-height: 26px;">float</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">DEFAULT</span> <span class="hljs-literal" style="color: #f92672; font-weight: bold; line-height: 26px;">NULL</span>,<br/>  <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">`qty`</span> <span class="hljs-built_in" style="color: #a6e22e; line-height: 26px;">int</span>(<span class="hljs-number" style="line-height: 26px;">11</span>) <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">DEFAULT</span> <span class="hljs-literal" style="color: #f92672; font-weight: bold; line-height: 26px;">NULL</span>,<br/>  PRIMARY <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">KEY</span> (<span class="hljs-string" style="color: #a6e22e; line-height: 26px;">`od_id`</span>)<br/>) <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">ENGINE</span>=<span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">InnoDB</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">DEFAULT</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">CHARSET</span>=utf8;<br/><br/><span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">CREATE</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">TABLE</span> <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">`t_order`</span> (<br/>  <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">`order_id`</span> bagint(<span class="hljs-number" style="line-height: 26px;">20</span>) <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">NOT</span> <span class="hljs-literal" style="color: #f92672; font-weight: bold; line-height: 26px;">NULL</span>,<br/>  <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">`user_id`</span> <span class="hljs-built_in" style="color: #a6e22e; line-height: 26px;">int</span>(<span class="hljs-number" style="line-height: 26px;">11</span>) <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">DEFAULT</span> <span class="hljs-literal" style="color: #f92672; font-weight: bold; line-height: 26px;">NULL</span>,<br/>  <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">`pay_mode`</span> <span class="hljs-built_in" style="color: #a6e22e; line-height: 26px;">tinyint</span>(<span class="hljs-number" style="line-height: 26px;">4</span>) <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">DEFAULT</span> <span class="hljs-literal" style="color: #f92672; font-weight: bold; line-height: 26px;">NULL</span>,<br/>  <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">`amount`</span> <span class="hljs-built_in" style="color: #a6e22e; line-height: 26px;">float</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">DEFAULT</span> <span class="hljs-literal" style="color: #f92672; font-weight: bold; line-height: 26px;">NULL</span>,<br/>  <span class="hljs-string" style="color: #a6e22e; line-height: 26px;">`order_date`</span> datetime <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">DEFAULT</span> <span class="hljs-literal" style="color: #f92672; font-weight: bold; line-height: 26px;">NULL</span>,<br/>  PRIMARY <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">KEY</span> (<span class="hljs-string" style="color: #a6e22e; line-height: 26px;">`order_id`</span>)<br/>) <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">ENGINE</span>=<span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">InnoDB</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">DEFAULT</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">CHARSET</span>=utf8;<br/></code>

四、测试连接并插入数据，查看数据分布状态

连接：mysql -umycat -p -P8066 -h192.168.253.129 --default-auth=mysql_native_password

连接成功就可以看到我们的逻辑库然后添加三个数据

<span style="display: block; background: url(https://my-wechat.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #272822; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;"></span><code class="hljs" style="overflow-x: auto; padding: 16px; color: #ddd; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px; -webkit-overflow-scrolling: touch; letter-spacing: 0px; padding-top: 15px; background: #272822; border-radius: 5px;"><span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">insert</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">into</span> t_order (order_id,user_id,pay_mode,amount) <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">values</span> (<span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">next</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">value</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">for</span> MYCATSEQ_ORDER,<span class="hljs-number" style="line-height: 26px;">103</span>,<span class="hljs-number" style="line-height: 26px;">1</span><br/><span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">Query</span> OK, <span class="hljs-number" style="line-height: 26px;">1</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">row</span> affected (<span class="hljs-number" style="line-height: 26px;">0.01</span> sec)<br/><br/><span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">insert</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">into</span> t_order (order_id,user_id,pay_mode,amount) <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">values</span> (<span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">next</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">value</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">for</span> MYCATSEQ_ORDER,<span class="hljs-number" style="line-height: 26px;">103</span>,<span class="hljs-number" style="line-height: 26px;">1</span><br/><span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">Query</span> OK, <span class="hljs-number" style="line-height: 26px;">1</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">row</span> affected (<span class="hljs-number" style="line-height: 26px;">0.01</span> sec)<br/><br/><span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">insert</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">into</span> t_order (order_id,user_id,pay_mode,amount) <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">values</span> (<span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">next</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">value</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">for</span> MYCATSEQ_ORDER,<span class="hljs-number" style="line-height: 26px;">103</span>,<span class="hljs-number" style="line-height: 26px;">1</span><br/><span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">Query</span> OK, <span class="hljs-number" style="line-height: 26px;">1</span> <span class="hljs-keyword" style="color: #f92672; font-weight: bold; line-height: 26px;">row</span> affected (<span class="hljs-number" style="line-height: 26px;">0.01</span> sec)<br/></code>

这个时候我们查看一下逻辑库的t_order数据，这里的order_id本应该是从1000开始的，之前咔咔做测试使用了一些。这时我们在来看192.168.253.129和114.55.103.25这俩台数据库的数据分布

「192.168.253.129数据库」这个可以看到进入了俩条数据

「144.55.103.25数据库」

这台数据库只进入了一条数据

五、分页查询，需要的数据在不同表的查询

其实这一切mycat都已经帮你做好了

比如现在user_id为103的数据分布分俩台数据库上，我们进行查询

还有分页问题，都跟平时一样的

6. Summary

• ##You need to pay attention to password issues when using MySQL8.0 in the database
• You need to pay attention when configuring schema.xml
• When configuring server.xml, although the logical library is not required by the user user, it also needs to be changed to be consistent
• Modify the MySQL user's host to %
• The password verification rule is set to mysql_native_password
• Be sure to connect to mycat With the parameter mysql_native_password

, the mycat used in this article is also Kaka’s first contact with it, and there are many things I don’t understand. Kaka will also add knowledge in this area bit by bit later, and I hope everyone can make progress together.

❝Persistence in learning, persistence in blogging, and persistence in sharing are the beliefs that Kaka has always adhered to since his career. I hope that Kaka’s articles in Nuoda Internet can bring you Any help.

❞

The above is the detailed content of The MyCat you want to implement MySQL sub-database and table is here. For more information, please follow other related articles on the PHP Chinese website!