分库分表具体操作


#158清空非本业务的数据
确认当前的数据库
[root@s-158 ~]# mysql -uroot -p123456
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 29
Server version: 5.7.26-log MySQL Community Server (GPL)
 
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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| order_db           |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.19 sec)
 
当前库为order_db，理应只包含订单相关的数据，so 清空其他非本业务的数据
 
mysql> use order_db;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_order_db    |
+-----------------------+
| customer_balance_log  |
| customer_inf          |
| customer_login        |
| customer_login_log    |
| customer_point_log    |
| order_cart            |
| order_customer_addr   |
| order_detail          |
| order_master          |
| product_brand_info    |
| product_category      |
| product_comment       |
| product_info          |
| product_pic_info      |
| product_supplier_info |
| region_info           |
| serial                |
| shipping_info         |
| warehouse_info        |
| warehouse_proudct     |
+-----------------------+
20 rows in set (0.00 sec)
 
mysql> drop table customer_balance_log;
drop table customer_info;
drop table customer_login;
drop table customer_login_log ;
drop table customer_point_log ;Query OK, 0 rows affected (0.44 sec)
 
mysql> drop table customer_info;
ERROR 1051 (42S02): Unknown table 'order_db.customer_info'
mysql> drop table customer_login;
Query OK, 0 rows affected (0.09 sec)
 
mysql> drop table customer_login_log ;
Query OK, 0 rows affected (0.08 sec)
 
mysql> drop table customer_point_log ;
Query OK, 0 rows affected (0.21 sec)
 
mysql> drop table product_brand_info;
drop table product_category;
drop table product_comment;
drop table product_info;
drop table product_pic_info;
drop table product_supplier_info ;Query OK, 0 rows affected (0.13 sec)
 
mysql> drop table product_category;
Query OK, 0 rows affected (0.11 sec)
 
mysql> drop table product_comment;
Query OK, 0 rows affected (0.10 sec)
 
mysql> drop table product_info;
Query OK, 0 rows affected (0.11 sec)
 
mysql> drop table product_pic_info;
Query OK, 0 rows affected (0.03 sec)
 
mysql> drop table product_supplier_info ;
Query OK, 0 rows affected (0.04 sec)
 
mysql> show tables;
+---------------------+
| Tables_in_order_db  |
+---------------------+
| customer_inf        |
| order_cart          |
| order_customer_addr |
| order_detail        |
| order_master        |
| region_info         |
| serial              |
| shipping_info       |
| warehouse_info      |
| warehouse_proudct   |
+---------------------+
10 rows in set (0.00 sec)
 
mysql> drop table customer_inf ;
Query OK, 0 rows affected (0.13 sec)
 
 
[root@s-159 ~]# mysql -uroot -p123456
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 27
Server version: 5.7.26-log MySQL Community Server (GPL)
 
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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| product_db         |
| sys                |
+--------------------+
5 rows in set (0.29 sec)
 
mysql> use product_db;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_product_db  |
+-----------------------+
| customer_balance_log  |
| customer_inf          |
| customer_login        |
| customer_login_log    |
| customer_point_log    |
| order_cart            |
| order_customer_addr   |
| order_detail          |
| order_master          |
| product_brand_info    |
| product_category      |
| product_comment       |
| product_info          |
| product_pic_info      |
| product_supplier_info |
| region_info           |
| serial                |
| shipping_info         |
| warehouse_info        |
| warehouse_proudct     |
+-----------------------+
20 rows in set (0.00 sec)
 
#159清空非本业务的数据
 
mysql> drop table customer_balance_log;
drop table customer_info;
drop table customer_login;
drop table customer_login_log ;
drop table customer_point_log ;Query OK, 0 rows affected (0.52 sec)
 
mysql> drop table customer_info;
ERROR 1051 (42S02): Unknown table 'product_db.customer_info'
mysql> drop table customer_login;
Query OK, 0 rows affected (0.11 sec)
 
mysql> drop table customer_login_log ;
Query OK, 0 rows affected (0.07 sec)
 
mysql> drop table customer_point_log ;
Query OK, 0 rows affected (0.13 sec)
 
mysql> drop table order_cart;
drop table order_customer_addr ;
drop table order_detail;
drop table order_master;
drop table region_info;
drop table serial;
drop table shipping_info;
drop table warehouse_info;
drop table warehouse_proudct;Query OK, 0 rows affected (0.14 sec)
 
mysql> drop table order_customer_addr ;
Query OK, 0 rows affected (0.07 sec)
 
mysql> drop table order_detail;
Query OK, 0 rows affected (0.09 sec)
 
mysql> drop table order_master;
Query OK, 0 rows affected (0.06 sec)
 
mysql> drop table region_info;
Query OK, 0 rows affected (0.08 sec)
 
mysql> drop table serial;
Query OK, 0 rows affected (0.07 sec)
 
mysql> drop table shipping_info;
Query OK, 0 rows affected (0.07 sec)
 
mysql> drop table warehouse_info;
Query OK, 0 rows affected (0.09 sec)
 
mysql> drop table warehouse_proudct;
Query OK, 0 rows affected (0.09 sec)
 
mysql> show tables;
+-----------------------+
| Tables_in_product_db  |
+-----------------------+
| customer_inf          |
| product_brand_info    |
| product_category      |
| product_comment       |
| product_info          |
| product_pic_info      |
| product_supplier_info |
+-----------------------+
7 rows in set (0.00 sec)
 
mysql> drop table customer_inf ;
Query OK, 0 rows affected (0.12 sec)
 
 
 
#160清空非本业务的数据
[root@s-160 ~]# mysql -uroot -p123456
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 43
Server version: 5.7.26-log MySQL Community Server (GPL)
 
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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| custer_db          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.28 sec)
 
mysql> use custer_db;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_custer_db   |
+-----------------------+
| customer_balance_log  |
| customer_inf          |
| customer_login        |
| customer_login_log    |
| customer_point_log    |
| order_cart            |
| order_customer_addr   |
| order_detail          |
| order_master          |
| product_brand_info    |
| product_category      |
| product_comment       |
| product_info          |
| product_pic_info      |
| product_supplier_info |
| region_info           |
| serial                |
| shipping_info         |
| warehouse_info        |
| warehouse_proudct     |
+-----------------------+
20 rows in set (0.04 sec)
 
mysql> drop table order_cart;
drop table order_customer_addr ;
drop table order_detail;
drop table order_master;
drop table region_info;
drop table serial;
drop table shipping_info;
drop table warehouse_info;
drop table warehouse_proudct;
Query OK, 0 rows affected (1.15 sec)
 
mysql> drop table order_customer_addr ;
Query OK, 0 rows affected (0.16 sec)
 
mysql> drop table order_detail;
Query OK, 0 rows affected (0.51 sec)
 
mysql> drop table order_master;
Query OK, 0 rows affected (0.22 sec)
 
mysql> drop table region_info;
Query OK, 0 rows affected (0.40 sec)
 
mysql> drop table serial;
Query OK, 0 rows affected (0.14 sec)
 
mysql> drop table shipping_info;
Query OK, 0 rows affected (0.22 sec)
 
mysql> drop table warehouse_info;
Query OK, 0 rows affected (0.17 sec)
 
mysql> drop table warehouse_proudct;
Query OK, 0 rows affected (0.21 sec)
 
mysql> drop table product_brand_info;
drop table product_category;
drop table product_comment;
drop table product_info;
drop table product_pic_info;
drop table product_supplier_info ;
Query OK, 0 rows affected (0.18 sec)
 
mysql> drop table product_category;
Query OK, 0 rows affected (0.08 sec)
 
mysql> drop table product_comment;
Query OK, 0 rows affected (0.08 sec)
 
mysql> drop table product_info;
Query OK, 0 rows affected (1.08 sec)
 
mysql> drop table product_pic_info;
Query OK, 0 rows affected (0.08 sec)
 
mysql> drop table product_supplier_info ;
Query OK, 0 rows affected (0.12 sec)
确认160数据库目前的表
mysql> show tables;
+----------------------+
| Tables_in_custer_db  |
+----------------------+
| customer_balance_log |
| customer_inf         |
| customer_login       |
| customer_login_log   |
| customer_point_log   |
+----------------------+
5 rows in set (0.00 sec)
 
 
 
#登录mycat服务器确认分库后的数据 是否完整
[root@m-157 conf]# mysql -uapp_imooc -pPdvreoR7rtZepoGBQem6nTggPY8cmx8lts7itp7/bOiTj+s1OTSk5kpyo/FZl8z/jwqQT5qKaEsSAXM/OXwX7w==  -h127.0.0.1 -P8066
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.29-mycat-1.6.5-release-20180122220033 MyCat Server (OpenCloundDB)
 
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]> show databases ;
+----------+
| DATABASE |
+----------+
| imooc_db |
+----------+
1 row in set (0.02 sec)
 
MySQL [(none)]> use imooc_db;
Database changed
MySQL [imooc_db]> 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.01 sec)
 
---哈哈 数据都在
创建时间：2021-09-26 17:28
넶浏览量：0
ꄴ前一个： 无
ꄲ后一个： 无