MyCAT垂直拆分之最佳实践-亲试ok

 
1、环境介绍
 
主机名
ip地址
角色
数据库
Master-111
192.168.3.111
MyCAT、MySQL
imooc_db
Slave-112
192.168.3.112
MySQL
order_db
Slave-113
192.168.3.113
MySQ
product_db
Slave-114
192.168.3.114
MySQL
customer_db
2、操作步骤
 
主机：192.168.3.111 
 
创建示例数据库 imooc_db
mysql> create database imooc_db;
Query OK, 1 row affected (0.01 sec)
 
创建同步账户，同时指定指定局域网内3网段的ip有效
mysql> create user 'imooc_repl'@'192.168.3.%' identified by '123456';
Query OK, 0 rows affected (0.02 sec)
给账户授权
mysql> grant replication slave on *.* to 'imooc_repl'@'192.168.3.%';
Query OK, 0 rows affected, 1 warning (0.01 sec)
 
创建mycat用户
mysql> create user 'imooc_mycat'@'192.168.3.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
 
mysql> grant select,insert,update,delete on *.* to 'imooc_mycat'@'192.168.3.%';
Query OK, 0 rows affected (0.00 sec)
 
为mycat用户加密密码
[root@Master-111 lib]# java -cp Mycat-server-1.6.5-release.jar io.mycat.util.DecryptUtil 0:app_imooc:123456
bDbWr7bVMgszTe82oMo8NaUsmFFdPCNl/lYXzOYoG8anTpQLvdx5e+LYJEmT0IAeSVp1loyxSZPyv1GoHbHFHg==
 
 
执行初始化脚本   imooc_db.sql 脚本
[root@Master-111 ~]#  mysql -uroot -p123456 imooc_db </scripts/imooc_db.sql
 
备份数据库 imooc_db 然后将备份文件传送至剩余三台主机  Slave-112 、Slave-113、Slave-114，注意指定master-data 为 2  标记下目前mysql数据库的二进制日志文件和 位置点
[root@Master-111 ~]# mysqldump -uroot -p123456 -h127.0.0.1  --master-data=2 --single-transaction --routines --triggers --events imooc_db > /scripts/imooc_db20200528.sql  
mysqldump: [Warning] Using a password on the command line interface can be insecure.
 
[root@Master-111 ~]# ls -ltr /scripts/
total 1280
-rw-r--r--. 1 root root  30687 Apr 16  2018 imooc_db.sql
-rw-r--r--. 1 root root   4226 May 27 06:43 init_mysql5.7.sh
-rw-r--r--. 1 root root   6055 May 27 06:46 my.cnf
-rw-r--r--. 1 root root 627468 May 27 22:22 imooc_db_20190528.sql
-rw-r--r--. 1 root root 627462 May 28 01:53 imooc_db20200528.sql --此为最新的备份脚本，哈哈
 
分别将备份脚本拷贝至  Slave-112 、 Slave-113、Slave-114
[root@Master-111 ~]# scp /scripts/imooc_db20200528.sql 192.168.3.112:/scripts/
root@192.168.3.112's password:
imooc_db20200528.sql                                                                                                       100%  613KB  18.1MB/s   00:00    
[root@Master-111 ~]# scp /scripts/imooc_db20200528.sql 192.168.3.113:/scripts/
root@192.168.3.113's password:
imooc_db20200528.sql                                                                                                       100%  613KB  11.2MB/s   00:00  
[root@Master-111 ~]# scp /scripts/imooc_db20200528.sql 192.168.3.114:/scripts/
root@192.168.3.113's password:
imooc_db20200528.sql                                                                                                       100%  613KB  11.2MB/s   00:00  
 
 
主机：192.168.3.112
 
创建数据库  order_db
mysql> create database order_db;
Query OK, 1 row affected (0.01 sec)
创建同步账户，同时指定制度局域网内3网段的ip有效
mysql> create user 'imooc_repl'@'192.168.3.%' identified by '123456';
Query OK, 0 rows affected (0.02 sec)
给账户授权
mysql> grant replication slave on *.* to 'imooc_repl'@'192.168.3.*';
Query OK, 0 rows affected, 1 warning (0.01 sec)
[root@Slave-112 ~]# mysql -uroot -p123456 order_db  </scripts/imooc_db.sql
 
创建复制链路
mysql> change master to master_host='192.168.3.111' ,master_user='imooc_repl',master_password='123456',master_log_file='log-bin.000016', MASTER_LOG_POS=1234;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
 
创建同步过滤链路
mysql> stop slave;
Query OK, 0 rows affected (0.17 sec)
 
mysql> change replication filter replicate_rewrite_db=((imooc_db,order_db));
Query OK, 0 rows affected (0.17 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.17 sec)
 
 
192.168.3.113 创建数据库 product_db
 
创建数据库 product_db
mysql> create database product_db;
   Query OK, 1 row affected (0.01 sec)
创建同步账户，同时指定制度局域网内3网段的ip有效
mysql> create user 'imooc_repl'@'192.168.3.%' identified by '123456';
Query OK, 0 rows affected (0.02 sec)
给账户授权
mysql> grant replication slave on *.* to 'imooc_repl'@'192.168.3.*';
Query OK, 0 rows affected, 1 warning (0.01 sec)
 
[root@Slave-113 ~]# mysql -uroot -p123456 product_db  </scripts/imooc_db.sql
 
 
创建复制链路
mysql> change master to master_host='192.168.3.111' ,master_user='imooc_repl',master_password='123456',master_log_file='log-bin.000016', MASTER_LOG_POS=968;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
 
创建同步过滤链路
mysql> stop slave;
Query OK, 0 rows affected (0.17 sec)
 
mysql> change replication filter replicate_rewrite_db=((imooc_db,order_db));
Query OK, 0 rows affected (0.17 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.17 sec)
 
 
192.168.3.114 创建数据库 customer_db
 
mysql> create database customer_db;
Query OK, 1 row affected (0.01 sec)
创建同步账户，同时指定制度局域网内3网段的ip有效
mysql> create user 'imooc_repl'@'192.168.3.%' identified by '123456';
Query OK, 0 rows affected (0.02 sec)
给账户授权
mysql> grant replication slave on *.* to 'imooc_repl'@'192.168.3.*';
Query OK, 0 rows affected, 1 warning (0.01 sec)
[root@Slave-112 ~]# mysql -uroot -p123456 order_db  </scripts/imooc_db.sql
 
创建复制链路
mysql> change master to master_host='192.168.3.111' ,master_user='imooc_repl',master_password='123456',master_log_file='log-bin.000017', MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
 
创建同步过滤链路
mysql> stop slave;
Query OK, 0 rows affected (0.17 sec)
 
mysql> change replication filter replicate_rewrite_db=((imooc_db,order_db));
Query OK, 0 rows affected (0.17 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.17 sec)
 
3、停止复制链路
由于各个从节点都指向了 3.111这个mycat服务端，而mycat服务端提供的是 逻辑库、逻辑表，因此现在3.111对各个从节点不需要提供什么数据支持，并且各个从节点只负责相关的模块数据，它们是彼此独立的，so 关停各个从节点的复制链路。
 
Slave-112
 
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
 
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
 
Slave-113
 
mysql> stop slave;
Query OK, 0 rows affected (0.16 sec)
 
mysql> reset slave;
Query OK, 0 rows affected (0.06 sec)
 
Slave-114
 
mysql> stop slave;
Query OK, 0 rows affected (0.17 sec)
 
mysql> reset slave;
Query OK, 0 rows affected (0.06 sec)
 
MyCAT节点登录mycat客户端，验证数据
 
[root@Master-111 conf]# mysql -h192.168.3.111 -uapp_imooc -p123456 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6.5-release-20180122220033 MyCat Server (OpenCloundDB)
 
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
mysql> select version();
+-------------------------------------------+
| VERSION()                                 |
+-------------------------------------------+
| 5.6.29-mycat-1.6.5-release-20180122220033 |
+-------------------------------------------+
1 row in set (0.02 sec)
 
mysql> select database();
+------------+
| DATABASE() |
+------------+
| imooc_db   |
+------------+
1 row in set (0.00 sec)
 
mysql> select user();
+-------------------------+
| USER()                  |
+-------------------------+
| app_imooc@192.168.3.111 |
+-------------------------+
1 row in set (0.00 sec)
 
mysql> show databases;
+----------+
| DATABASE |
+----------+
| imooc_db |
+----------+
1 row in set (0.01 sec)
 
mysql> use imooc_db;
Database changed
mysql> show tables;
+-----------------------+
| Tables in imooc_db    |
+-----------------------+
| customer_balance_log  |
| customer_inf          |
| customer_level_inf    |
| customer_login        |
| customer_login_log    |
| customer_point_log    |
| order_cart            |
| order_customer_addr   |
| order_master          |
| product_brand_info    |
| product_category      |
| product_comment       |
| product_info          |
| product_pic_info      |
| product_supplier_info |
| region_info           |
| shipping_info         |
| warehouse_info        |
| warehouse_proudct     |
+-----------------------+
19 rows in set (0.00 sec)
 
从mycat层面确认逻辑表customer_level_inf的数据
mysql> select count(1) from customer_level_inf;
+--------+
| COUNT0 |
+--------+
|      4 |
+--------+
1 row in set (0.13 sec)
 
从mycat层面确认逻辑表product_category的数据
mysql> select count(1) from product_category;
+--------+
| COUNT0 |
+--------+
|     65 |
+--------+
1 row in set (0.20 sec)
 
从Slave-114 确认物理表数据
mysql> select count(1) from customer_level_inf;
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)
 
从Slave-113 确认物理表数据
mysql> select count(1) from product_category;
+----------+
| count(1) |
+----------+
|       65 |
+----------+
1 row in set (0.06 sec)
 
哈哈 数据无误
 
 
 
 
server.xml信息如下
 
 
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
        - you may not use this file except in compliance with the License. - You
        may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
        - - Unless required by applicable law or agreed to in writing, software -
        distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
        WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
        License for the specific language governing permissions and - limitations
        under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.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="frontWritQueueSize">2048</property>
   <property name="charset">utf-8</property>
   <property name="txIsolation">2</property>
   <property name="processors">8</property>
   <property name="idleTimeout">18000000</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">bDbWr7bVMgszTe82oMo8NaUsmFFdPCNl/lYXzOYoG8anTpQLvdx5e+LYJEmT0IAeSVp1loyxSZPyv1GoHbHFHg==</property>
   <property name="schemas">imooc_db</property>
  </user>
</mycat:server>
 
schema.xml信息如下
 
 
 
<?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="ord_dn"/>
    <table name="order_cart" primaryKey="cart_id" dataNode="ord_dn"/>
    <table name="order_customer_addr" primaryKey="customer_addr_id" dataNode="ord_dn"/>
    <table name="region_info" primaryKey="region_id" dataNode="ord_dn"/>
    <table name="shipping_info" primaryKey="ship_id" dataNode="ord_dn"/>
    <table name="warehouse_info" primaryKey="w_id" dataNode="ord_dn"/>
    <table name="warehouse_proudct" primaryKey="wp_id" dataNode="ord_dn"/>
 
    <table name="product_brand_info" primaryKey="brand_id" dataNode="prod_dn"/>
    <table name="product_category" primaryKey="category_id" dataNode="prod_dn"/>
    <table name="product_comment" primaryKey="comment_id" dataNode="prod_dn"/>
    <table name="product_info" primaryKey="product_id" dataNode="prod_dn"/>
    <table name="product_supplier_info" primaryKey="supplier_id" dataNode="prod_dn"/>
    <table name="product_pic_info" primaryKey="product_pic_id" dataNode="prod_dn"/>
 
    <table name="customer_balance_log" primaryKey="balance_id" dataNode="cust_dn"/>
    <table name="customer_inf" primaryKey="customer_inf_id" dataNode="cust_dn"/>
    <table name="customer_level_inf" primaryKey="customer_level" dataNode="cust_dn"/>
    <table name="customer_login" primaryKey="customer_id" dataNode="cust_dn"/>
    <table name="customer_login_log" primaryKey="login_id" dataNode="cust_dn"/>
    <table name="customer_point_log" primaryKey="point_id" dataNode="cust_dn"/>
</schema>
 
 
<dataNode name="ord_dn" dataHost="192.168.3.112" database="order_db" />
<dataNode name="prod_dn" dataHost="192.168.3.113" database="product_db" />
<dataNode name="cust_dn" dataHost="192.168.3.114" database="customer_db" />
 
 
 
<dataHost name="192.168.3.112" maxCon="1000" minCon="10" balance="0"
   writeType="0" dbType="mysql" dbDriver="native">
   <heartbeat>select 1</heartbeat>
  <writeHost host="192.168.3.112" url="192.168.3.112:3306" user="root" password="123456" />
</dataHost>
<dataHost name="192.168.3.113" maxCon="1000" minCon="10" balance="0"
   writeType="0" dbType="mysql" dbDriver="native">
   <heartbeat>select 1</heartbeat>
  <writeHost host="192.168.3.113" url="192.168.3.113:3306" user="root" password="123456" />
</dataHost>
<dataHost name="192.168.3.114" maxCon="1000" minCon="10" balance="0"
   writeType="0" dbType="mysql" dbDriver="native">
   <heartbeat>select 1</heartbeat>
    <writeHost host="192.168.3.114" url="192.168.3.114:3306" user="root" password="123456" />
  </dataHost>
 
</mycat:schema>
 
 
 
创建时间：2021-09-26 17:22
넶浏览量：0
ꄴ前一个： 无
ꄲ后一个： 无