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

# 数据库 2024-05-05 14:24 0 38 来源：云博客

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 to 47.94.220.79:22...Connection established.To escape to local shell, press ‘Ctrl+Alt+]‘.Last login: Tue Jun 5 16:30:33 2018 from 117.119.97.51Welcome to Alibaba Cloud Elastic Compute Service !cuixiaozhao[root@localhost ~]# mycat stopStopping Mycat-server...Stopped Mycat-server.[root@localhost ~]# mycat startStarting Mycat-server...[root@localhost ~]# mysql -uec_pre_user -p -P8066 -h47.94.220.79Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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.01 sec)mysql> use ec_pre_schemasReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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.01 sec)mysql>