10.11.4 mysql_MyCAT+MySQL 搭建高可用企业级数据库集群——第4章 MyCat进阶实战至垂直分库...

4-1 为什么要进行垂直分库和相关操作

4-2 收集分析业务模块

4-3 MySQL复制的步骤

4-4 MySQL复制环境说明

4-5 MySQL复制实战

4-6 MySQL复制总结

4-7 垂直切分

4-8 垂直切分相关配置

4-9 垂直切分schema文件配置

4-10 垂直切分server文件配置

4-11 后续工作

4-12 Mycat启动调试

4-13 Mycat验证配置

4-14 清理多余数据

4-15 跨分片查询

4-16 配置和验证全局表

4-17 垂直切分的优缺点

4-1 为什么要进行垂直分库和相关操作

1、垂直分库的操作说明；

纵向扩展方案(可拓展性能有限，会出现瓶颈)；

水平拓展(垂直分库，水平分库，分单写请求负载)；

数据库的垂直切分步骤；

1、搜集分析业务模块间的关系；

2、复制数据库到其他势力

3、配置Mycat垂直分库；

4、通过Mycat访问DB；

5、删除元库中已迁移的表；

4-2 收集分析业务模块

1、分析业务模块间的关系；

4-3 MySQL复制的步骤

1、如何配置MySQL的主从复制；

1、备份原数据库并记录相关事务点；

2、在原数据库中建立复制的用户；

3、在新实例上恢复备份的数据库；

4、在新实例上皮遏制复制链路；

5、新新实例上启动复制；

4-4 MySQL复制环境说明

1、演示环境说明；

4-5 MySQL复制实战

1、MySQL复制实战演示；

1)创建数据库实例并导入数据；mysql -uroot -p -e"create database imooc_db"

2)导入数据mysql -uroot -p imooc_db < imooc_db.sql

3)查看导入的表的信息；use imooc_db;show tables;

4)mysqldump --master-data=2 --single-transaction --routines --triggers --events

4-6 MySQL复制总结

1、总结步骤；

1)使用master-data=2 记录事物日志点；

2)使用change master to 配置复制链路；

3)使用change replication filter配置数据库名转换；

4-7 垂直切分

1、数据库架构升级至垂直切分；

4-8 垂直切分相关配置

1、mycat配置；

4-9 垂直切分schema文件配置

1、配置详解说明；

4-10 垂直切分server文件配置

1、垂直切分配置详解；

[root@iZqmo9i3j77p7eZ lib]#java -cp Mycat-server-1.6.5-release.jar io.mycat.util.DecryptUtil 0:app_imooc:123456

bDbWr7bVMgszTe82oMo8NaUsmFFdPCNl/lYXzOYoG8anTpQLvdx5e+LYJEmT0IAeSVp1loyxSZPyv1GoHbHFHg==

4-11 后续工作

1、在一个夜黑更高的夜晚，可以开始操作了；

mycat全局表应对以上报错；

4-12 Mycat启动调试

1、mycat start判断启动是否正常；

2、通过查看wrapper.log，判断启动故障或正常；

tf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=47.94.211.18, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler:GetConnectionHandler.java:67)

INFO| jvm 1 | 2018/06/05 17:32:57 | 2018-06-05 17:32:56,987 [DEBUG][WrapperSimpleAppMain] release channel MySQLConnection [id=2, lastTime=1528191176987, user=root, schema=db_mycat, old shema=db_mycat, borrowed=true, fromSlaveDB=false, threadId=130, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=47.94.211.18, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:442)

INFO| jvm 1 | 2018/06/05 17:32:57 | 2018-06-05 17:32:56,988 [INFO ][WrapperSimpleAppMain] connected successfuly MySQLConnection [id=1, lastTime=1528191176988, user=root, schema=db_mycat, old shema=db_mycat, borrowed=true, fromSlaveDB=false, threadId=129, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=47.94.211.18, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler:GetConnectionHandler.java:67)

INFO| jvm 1 | 2018/06/05 17:32:57 | 2018-06-05 17:32:56,988 [DEBUG][WrapperSimpleAppMain] release channel MySQLConnection [id=1, lastTime=1528191176988, user=root, schema=db_mycat, old shema=db_mycat, borrowed=true, fromSlaveDB=false, threadId=129, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=47.94.211.18, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:442)

INFO| jvm 1 | 2018/06/05 17:32:57 | 2018-06-05 17:32:56,990 [DEBUG][$_NIOConnector] 连接后台真正完成 (io.mycat.net.AbstractConnection:AbstractConnection.java:594)

INFO| jvm 1 | 2018/06/05 17:32:57 | 2018-06-05 17:32:56,993 [INFO ][$_NIOREACTOR-3-RW] connected successfuly MySQLConnection [id=3, lastTime=1528191176993, user=root, schema=db_mycat, old shema=db_mycat, borrowed=true, fromSlaveDB=false, threadId=131, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=47.94.211.18, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler:GetConnectionHandler.java:67)

INFO| jvm 1 | 2018/06/05 17:32:57 | 2018-06-05 17:32:56,994 [DEBUG][$_NIOREACTOR-3-RW] release channel MySQLConnection [id=3, lastTime=1528191176993, user=root, schema=db_mycat, old shema=db_mycat, borrowed=true, fromSlaveDB=false, threadId=131, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=47.94.211.18, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:442)

INFO| jvm 1 | 2018/06/05 17:32:57 | 2018-06-05 17:32:56,995 [DEBUG][$_NIOConnector] 连接后台真正完成 (io.mycat.net.AbstractConnection:AbstractConnection.java:594)

INFO| jvm 1 | 2018/06/05 17:32:57 | 2018-06-05 17:32:56,999 [INFO ][$_NIOREACTOR-4-RW] connected successfuly MySQLConnection [id=4, lastTime=1528191176999, user=root, schema=db_mycat, old shema=db_mycat, borrowed=true, fromSlaveDB=false, threadId=132, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=47.94.211.18, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler:GetConnectionHandler.java:67)

INFO| jvm 1 | 2018/06/05 17:32:57 | 2018-06-05 17:32:56,999 [DEBUG][$_NIOREACTOR-4-RW] release channel MySQLConnection [id=4, lastTime=1528191176999, user=root, schema=db_mycat, old shema=db_mycat, borrowed=true, fromSlaveDB=false, threadId=132, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=47.94.211.18, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:442)

INFO| jvm 1 | 2018/06/05 17:32:57 | 2018-06-05 17:32:57,088 [INFO ][WrapperSimpleAppMain] init result :finished 10 success 10 target count:10 (io.mycat.backend.datasource.PhysicalDBPool:PhysicalDBPool.java:319)

INFO| jvm 1 | 2018/06/05 17:32:57 | 2018-06-05 17:32:57,088 [INFO ][WrapperSimpleAppMain] mysql_host1 index:1 init success (io.mycat.backend.datasource.PhysicalDBPool:PhysicalDBPool.java:265)

INFO| jvm 1 | 2018/06/05 17:32:57 | MyCAT Server startup successfully. see logs in logs/mycat.log

4-13 Mycat验证配置

1、Myca验证配置；

MySQL客户端来连接测试：mysql -uapp_imooc  -p -P8066 -h192.168.0.1

[C:\~]$

Connecting to47.94.220.79:22...

Connection established.

To escape to local shell, press‘Ctrl+Alt+]‘.

Last login: Tue Jun5 16:30:33 2018 from 117.119.97.51Welcome to Alibaba Cloud Elastic Compute Service !

cuixiaozhao

[root@localhost~]#mycat stop

Stopping Mycat-server...

Stopped Mycat-server.

[root@localhost~]#mycat start

Starting Mycat-server...

[root@localhost~]#mysql -uec_pre_user -p -P8066 -h47.94.220.79

Enter password:

Welcome to the MySQL monitor. Commands end with ;or\g.

Your MySQL connection idis 1Server version:5.6.29-mycat-1.6-RELEASE-20161028204710MyCat Server (OpenCloundDB)

Copyright (c)2000, 2016, Oracle and/orits affiliates. All rights reserved.

Oracleis a registered trademark of Oracle Corporation and/orits

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>

mysql>show databases;+----------------+

| DATABASE |

+----------------+

| ec_pre_schemas |

+----------------+

1 row in set (0.01sec)

mysql>use ec_pre_schemas

Reading table informationfor completion of table andcolumn names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql>show tables;+----------------------------------+

| Tables in ec_pre_schemas |

+----------------------------------+

| bd_account_bm |

| bd_approval_process |

| bd_archive_file |

| bd_auditing |

| bd_auditing_detail |

| bd_auditor |

| bd_authority |

| bd_balance_detail |

| bd_balance_detail_budget |

| bd_balance_rule |

| bd_balance_rule_budget |

| bd_balance_user |

| bd_balance_user_budget |

| bd_base_report |

| bd_butler_product |

| bd_case |

| bd_case_allot |

| bd_case_balance |

| bd_case_balance_budget |

| bd_case_doc_property_value |

| bd_case_file |

| bd_case_flow |

| bd_case_official |

| bd_case_official_code_config |

| bd_case_official_status_config |

| bd_case_operation |

| bd_case_process_info |

| bd_case_property_configuration |

| bd_channel |

| bd_contact |

| bd_contract |

| bd_contract_file |

| bd_contract_operation |

| bd_contract_product |

| bd_contract_product_payment |

| bd_contract_ratio |

| bd_cooperation_drools |

| bd_cooperation_drools_config |

| bd_corp_info |

| bd_corp_system_info |

| bd_customer |

| bd_customer_address |

| bd_customer_attachment_summary |

| bd_customer_bm |

| bd_customer_body |

| bd_customer_body_bm |

| bd_customer_body_file |

| bd_customer_body_file_bm |

| bd_customer_communication |

| bd_customer_communication_file |

| bd_customer_d |

| bd_customer_dn |

| bd_customer_file |

| bd_customer_h |

| bd_customer_operation |

| bd_customer_property_value |

| bd_customer_rating |

| bd_customer_statistics |

| bd_customer_valuation |

| bd_custom_workbench |

| bd_dealing_people |

| bd_department_info |

| bd_diarly_type |

| bd_districtvalues |

| bd_districtvalues_cpc |

| bd_document_list |

| bd_document_property |

| bd_document_property_value |

| bd_fee |

| bd_fee_business |

| bd_flow |

| bd_group_user |

| bd_idn |

| bd_industry |

| bd_invoice |

| bd_invoicetitle |

| bd_invoice_body |

| bd_invoice_title |

| bd_invoice_title_body |

| bd_issued_audit |

| bd_issued_file |

| bd_joint_applicant |

| bd_knowledge |

| bd_knowledge_category |

| bd_knowledge_file |

| bd_knowledge_link |

| bd_knowledge_manage |

| bd_knowledge_type |

| bd_label |

| bd_label_customer |

| bd_label_type |

| bd_log_info |

| bd_match |

| bd_message |

| bd_message_text |

| bd_message_text_file |

| bd_niceclassification |

| bd_niceclassification_copy |

| bd_offical_result_remind |

| bd_official_doc |

| bd_order_auditor |

| bd_order_balance |

| bd_order_balance_budget |

| bd_order_body |

| bd_order_body_bg |

| bd_order_info |

| bd_order_info_bg |

| bd_order_operation |

| bd_order_price_value |

| bd_order_property_value |

| bd_order_property_value_bg |

| bd_partner_drools |

| bd_password_bm |

| bd_pay |

| bd_payment |

| bd_payment_contract |

| bd_pay_contract |

| bd_potential |

| bd_price |

| bd_price_value |

| bd_process_bio |

| bd_process_remind_config |

| bd_product |

| bd_product_property |

| bd_product_property_value |

| bd_product_type |

| bd_progressive_sales |

| bd_progressive_sales_config |

| bd_project |

| bd_question_naire |

| bd_receiptaddress |

| bd_relevant_people |

| bd_remind |

| bd_reminder |

| bd_remind_label_config |

| bd_remind_modal |

| bd_replenishment |

| bd_replenishment_detail |

| bd_replenishment_operation |

| bd_returnvisit |

| bd_returnvisit_file |

| bd_returnvisit_order |

| bd_role |

| bd_role_authority |

| bd_share |

| bd_staff_contact |

| bd_staff_notice |

| bd_staff_operation |

| bd_staff_records |

| bd_system_info |

| bd_system_info_copy |

| bd_training_record |

| bd_user |

| bd_user_authdata |

| bd_user_info |

| bd_user_info_bg |

| bd_user_progressive_sale_to_june |

| bd_user_role |

| bd_visit |

| bd_work_diary |

| bd_work_diary_detail |

| bd_work_order |

| bd_work_order_file |

| bd_work_order_flow |

| bd_work_order_group |

| bd_work_order_operation |

| bd_work_order_resource |

| bd_work_order_result |

| ini_dic |

| ini_dic_detail |

| rel_case_order |

| rel_channel_api |

| rel_channel_product |

| rel_contract_order |

| rel_corp_progressive_sales |

| rel_corp_user |

| rel_document_property |

| rel_group_customer |

| rel_invoice_order |

| rel_knowledge_point |

| rel_official_process_config |

| rel_order_property_price |

| rel_order_property_price_copy |

| rel_order_relation |

| rel_price_product |

| rel_price_property |

| rel_product_property |

| rel_product_userstar_royalty |

| rel_projectorder |

| rel_relate_review |

| rel_staff_training_record |

| rel_user_duty |

| rel_workorder |

| rel_work_order_group |

| st_sale_census |

| sys_message |

| sys_property |

| sys_property_area |

| sys_property_value |

| vi_balance_case |

| vi_balance_case_thismonth |

| vi_balance_order |

| vi_balance_order_thismonth |

+----------------------------------+

203 rows in set (0.01sec)

mysql>

View Code

4-14 清理多余数据

1、清理多余节点的数据表；

先停止主从复制，stop slave；

reset slave all；

4-15 跨分片查询

1、通过mycat查询逻辑表的存在；

使用全局表方式来修复以上错误；

4-16 配置和验证全局表

1、每个节点都配置全局表；解决跨分片关联查询的问题；

2、后续工作；

切换应用通过Mycat连接数据库；

删除不属于本模块的表；

4-17 垂直切分的优缺点

1、垂直切分的优点；

数据库的拆分简单明了，拆分规则明确；

应用程序模块清晰明确，整合容易；

数据维护方便易行，容易定位；

2、垂直切分的缺点；