mysql linux整库迁移
Last updated on August 8, 2025 am
🧙 Questions
使用dump在linux系统整库迁移
☄️ Ideas
安装dump工具
systemctl status mysqld
which mysqldump导出dump文件
# 连接mysql
mysql -h localhost -u root -P 3306
# 8#<ZCy_g4ouq
mysqldump -u 用户名 -p 数据库名 > /path/to/备份文件.sql
rm -rf /tmp/dehoop_test.sql /tmp/clean_dehoop_test.sql
# mysqldump --single-transaction --quick --no-tablespaces --set-gtid-purged=OFF -u dehoop -p dehoop_test > /tmp/dehoop_test.sql
find / -name 'mysql.sock'
mysqldump --socket=/var/lib/mysql/mysql.sock -u root -p dehoop ast_metadata_model ast_metadata_task ast_metadata_task_instance ctr_alarm_modes ctr_baseline_alarm_conditions ctr_baseline_instance_alarm_condition ctr_baseline_instance_user ctr_baseline_instances ctr_baseline_users ctr_baseline_works ctr_baselines ctr_retry_rule_work ctr_retry_rules ctr_work_wechat_corp_id daq_business_area daq_data_layers daq_table_inspectors daq_table_layers daq_table_layers_data_dimension daq_table_layers_data_tag daq_table_layers_model das_api_authority das_api_authority_group das_api_authority_group_clause das_api_authority_role das_api_authority_user das_api_logs das_api_security das_apis dehoop_license_active_history dq_custom_rules dq_quality_log dq_quality_log_rule dq_quality_model dq_quality_model_rules dq_quality_scheme dq_scheme_except_table dq_scheme_result_table dq_scheme_rules excel_import_result execution_flows execution_jobs execution_temp_data execution_test_params fnd_authority fnd_role_authority fnd_role_users fnd_roles fnd_tenant_product_authorizations fnd_tenant_users fnd_tenants fnd_users job_build_in_functions job_ddlv_configs job_ddlv_fields job_etl_build_in_function job_etl_work_clean_filter job_etl_work_desensitization_filter job_etl_work_field_change_filter job_etl_work_node job_etl_work_node_aggregation_filed job_etl_work_node_association_condition job_etl_work_node_association_field job_etl_work_node_config job_etl_work_node_filed job_etl_work_node_filter_filed job_etl_work_node_instance job_etl_work_node_out job_etl_work_node_relation job_etl_work_node_router_filed job_etl_work_serial_num_filter job_etl_work_sort_advanced_settings job_etl_work_sort_filter job_etl_work_unique_id_filter job_execute_query job_flow_build_in_params job_flow_configs job_flow_controls job_flow_dependents job_flow_instances job_functions job_hplsql_configs job_node_branch job_outline_works job_real_work_node_configs job_real_works job_sync_work_api_configs job_sync_work_build_in_functions job_sync_work_config job_temp_works job_work_controls job_work_flows job_work_logs job_work_params job_work_resources job_work_scripts metadata_browse_count metadata_collect metadata_permission metadata_permission_application metadata_permission_application_detail modeling_atomic_indicator modeling_business_process modeling_data_dictionary modeling_data_dimension modeling_data_dimension_field modeling_data_field modeling_data_model modeling_data_model_relations modeling_data_model_table modeling_data_model_table_relations modeling_derive_indicator modeling_entity modeling_entity_business_process modeling_entity_field modeling_field_standard modeling_measure_unit modeling_measure_unit_categories modeling_project_data_field modeling_standard_value_range modeling_statistical_conditions modeling_statistical_conditions_field modeling_statistical_period modeling_summary_table modeling_summary_table_field modeling_tag_business_process modeling_work_flow_business_process pro_env_mappings pro_environment pro_environment_cal pro_environment_datasource pro_environment_workspace pro_manage pro_user rel_package_works rel_packages res_business_unit res_cal_engines res_datasource_authority res_datasources res_label res_label_table res_resource_files res_servers res_table_cycles res_table_historical res_table_infos res_table_lineages res_table_logs res_table_partition_info res_workspaces run_history sch_flow_instance_queues sch_flow_works sch_work_instance_queues sch_work_instances sch_work_jobs sch_work_queues sch_work_states sch_work_versions sys_attachments sys_dehoop_config sys_dehoop_login_mode sys_lookup_type sys_lookup_value sys_update_log sys_verification_code tag_categories tag_category_label tag_custom_clause tag_custom_hierarchy tag_custom_lables tag_execute_query tag_figure tag_figure_calculation tag_figure_clause tag_figure_graph tag_historical_data tag_labels tag_sch_jobs > /tmp/dehoop_tmp.sql
mysqldump --socket=/var/lib/mysql/mysql.sock -u root -p powerjob_daily app_info container_info instance_info job_info oms_lock server_info user_info workflow_info workflow_instance_info > /tmp/powerjob_tmp.sql
mysqldump --socket=/var/lib/mysql/mysql.sock -u root -p cdh_hive > /tmp/hive_tmp.sql
# mysqldump -u dehoop -p dehoop_test FND_TENANTS > /tmp/dehoop_test_tenants.sql
# 密码：jac_Welcome1
# 17:59 开始
# 18:01 结束
sed -e '/^LOCK TABLES/d' -e '/^UNLOCK TABLES/d' -e '/^FLUSH TABLES/d' /tmp/hive_tmp.sql > /tmp/hive_db.sql
sed -e '/^LOCK TABLES/d' -e '/^UNLOCK TABLES/d' -e '/^FLUSH TABLES/d' dehoop_tmp.sql > dehoop_db2.sql
# 表名翻译成大写
# sed -E 's/`([a-zA-Z0-9_]*)`/`\U\1`/g' dump.sql > dump_uppercase.sql导入dump文件
scp -P 62222 /tmp/dehoop_test2.sql dcloud@192.168.25.175:/data/local-path-provisioner/pvc-dd95b0db-8ecb-4454-a577-b2c90f8ceda5_pxc_datadir-pxc-db-pxc-1
# dcloud123
cdh_hive_test
cdh_monitor_test
cdh_scm_test
powerjob_test
dehoop_test
cd /data/local-path-provisioner/pvc-dd95b0db-8ecb-4454-a577-b2c90f8ceda5_pxc_datadir-pxc-db-pxc-1
sudo chmod 777 clean_dehoop_test.sql
/usr/bin/mysql -h pxc-db-haproxy.pxc -u root -pdefinesys2025 -P 3306
# 放开校验，否则表没有主键无法创建
# -- SET GLOBAL pxc_strict_mode=DISABLED;
# -- SHOW VARIABLES LIKE 'pxc_strict_mode';
# -- show databases;
create database cdh_hive_test;
# 找到映射路径 pxc-db-pxc-1
mysql -u root -p powerjob_test < /var/lib/mysql/powerjob_test2.sql
mysql -h localhost -u root -pdefinesys2025 -P 3306
use powerjob_test;
source /var/lib/mysql/hive_db.sql;
# 输入密码 definesys2025
# 17:21 开始
# 17:41 结束 3GB 25分钟左右 10分钟1GB # 配置mysql大小写不区分，0 是区分大小写
# SHOW VARIABLES LIKE 'lower_case_table_names';
lower_case_table_names=1create table dehoop_test.MODELING_DATA_DICTIONARY
(
ID varchar(30) not null comment '主键'
primary key,
NAME varchar(200) null comment '字典名称',
CODE varchar(200) null comment '字典编码',
TYPE varchar(200) null comment '类型 DIR-目录 DICTIONARY-字典',
PARENT_ID varchar(30) null comment '父级id',
DICTIONARY_ID varchar(30) null comment '所在字典id',
DESCR varchar(500) null comment '描述',
STATE varchar(200) default 'ENABLE' null comment 'ENABLE-启用 DISABLE-禁用',
BEFORE_STATE varchar(200) null comment '父级字典禁用之前的状态',
REFERENCE int default 0 null comment '被引用数量',
SEQUENCE int default 0 null comment '显示顺序',
TENANT_ID varchar(30) null,
CREATED_BY varchar(30) null,
CREATION_DATE datetime null,
LAST_UPDATED_BY varchar(30) null,
LAST_UPDATE_DATE datetime null,
OBJECT_VERSION_NUMBER int null
)
comment '数据字典表 ' charset = utf8;ALTER TABLE DAQ_BUSINESS_AREA ADD PRIMARY KEY (ID);
ALTER TABLE DAQ_TABLE_LAYERS_DATA_DIMENSION ADD PRIMARY KEY (ID);
ALTER TABLE DAQ_TABLE_LAYERS_DATA_TAG ADD PRIMARY KEY (ID);
ALTER TABLE DAQ_TABLE_LAYERS_MODEL ADD PRIMARY KEY (ID);
ALTER TABLE DAS_API_AUTHORITY ADD PRIMARY KEY (ID);
ALTER TABLE DAS_API_AUTHORITY_GROUP ADD PRIMARY KEY (ID);
ALTER TABLE DAS_API_AUTHORITY_GROUP_CLAUSE ADD PRIMARY KEY (ID);
ALTER TABLE DAS_API_AUTHORITY_ROLE ADD PRIMARY KEY (ID);
ALTER TABLE DAS_API_AUTHORITY_USER ADD PRIMARY KEY (ID);
ALTER TABLE DQ_QUALITY_LOG ADD PRIMARY KEY (ID);
ALTER TABLE DQ_QUALITY_LOG_RULE ADD PRIMARY KEY (ID);
ALTER TABLE EXECUTION_TEST_PARAMS ADD PRIMARY KEY (ID);
ALTER TABLE FND_AUTHORITY ADD PRIMARY KEY (ID);
ALTER TABLE EXECUTION_FLOWS ADD PRIMARY KEY (EXECUTION_ID);
ALTER TABLE FND_ROLE_AUTHORITY ADD PRIMARY KEY (ID);
ALTER TABLE JOB_ETL_WORK_NODE_ASSOCIATION_CONDITION ADD PRIMARY KEY (ID);
ALTER TABLE JOB_NODE_MERGE ADD PRIMARY KEY (ID);
ALTER TABLE METADATA_BROWSE_COUNT ADD PRIMARY KEY (ID);
ALTER TABLE METADATA_COLLECT ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_ATOMIC_INDICATOR ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_BUSINESS_PROCESS ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_DATA_DIMENSION_FIELD ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_DATA_FIELD ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_DERIVE_INDICATOR ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_ENTITY_BUSINESS_PROCESS ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_ENTITY_FIELD ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_PROJECT_DATA_FIELD ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_STATISTICAL_CONDITIONS ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_STATISTICAL_CONDITIONS_FIELD ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_STATISTICAL_PERIOD ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_SUMMARY_TABLE ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_SUMMARY_TABLE_FIELD ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_TAG_BUSINESS_PROCESS ADD PRIMARY KEY (ID);
ALTER TABLE MODELING_WORK_FLOW_BUSINESS_PROCESS ADD PRIMARY KEY (ID);
ALTER TABLE SYS_UPDATE_LOG ADD PRIMARY KEY (ID);
ALTER TABLE TAG_FIGURE_CLAUSE ADD PRIMARY KEY (ID);
ALTER TABLE TAG_CUSTOM_LABLES ADD PRIMARY KEY (ID);🔗 Links
mysql linux整库迁移
https://ispong.isxcode.com/db/mysql/mysql linux整库迁移/