mysql linux整库迁移
Last updated on November 17, 2025 pm
🧙 Questions
使用dump在linux系统整库迁移
☄️ Ideas
登录服务器
ssh root@xxx查看dump工具
systemctl status mysqld
which mysqldump
which mysql导出dump文件
# 连接mysql
mysql -h 192.168.21.181 -u root -P 3306
# 8#<ZCy_g4ouq
# 归档没用的数据
# das_api_logs
# execution_jobs
# execution_flows
# job_execute_query
# job_work_logs
# sch_work_instances
use dehoop_test;
ALTER TABLE das_api_logs RENAME TO das_api_logs_2025_10_18_01;
create table das_api_logs like das_api_logs_2025_10_18_01;
insert into das_api_logs select * from das_api_logs_2025_10_18_01 where CREATION_DATE > DATE_SUB(CURDATE(), INTERVAL 14 DAY);
ALTER TABLE execution_jobs RENAME TO execution_jobs_2025_10_18_01;
create table execution_jobs like execution_jobs_2025_10_18_01;
insert into execution_jobs select * from execution_jobs_2025_10_18_01 where CREATION_DATE > DATE_SUB(CURDATE(), INTERVAL 14 DAY);
ALTER TABLE execution_flows RENAME TO execution_flows_2025_10_18_01;
create table execution_flows like execution_flows_2025_10_18_01;
insert into execution_flows select * from execution_flows_2025_10_18_01 where CREATION_DATE > DATE_SUB(CURDATE(), INTERVAL 14 DAY);
ALTER TABLE job_execute_query RENAME TO job_execute_query_2025_10_18_01;
create table job_execute_query like job_execute_query_2025_10_18_01;
insert into job_execute_query select * from job_execute_query_2025_10_18_01 where CREATION_DATE > DATE_SUB(CURDATE(), INTERVAL 14 DAY);
ALTER TABLE job_work_logs RENAME TO job_work_logs_2025_10_18_01;
create table job_work_logs like job_work_logs_2025_10_18_01;
insert into job_work_logs select * from job_work_logs_2025_10_18_01 where CREATION_DATE > DATE_SUB(CURDATE(), INTERVAL 14 DAY);
ALTER TABLE sch_work_instances RENAME TO sch_work_instances_2025_10_18_01;
create table sch_work_instances like sch_work_instances_2025_10_18_01;
insert into sch_work_instances select * from sch_work_instances_2025_10_18_01 where CREATION_DATE > DATE_SUB(CURDATE(), INTERVAL 14 DAY);
# 删除多余sql
rm -rf /tmp/dehoop_test_backup.sql
# mysqldump -u 用户名 -p 数据库名 > /path/to/备份文件.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 JOB_WORK_DATASOURCES JOB_WORK_FLINK_CONFIG > /tmp/dehoop_backup_01.sql
# mysqldump --socket=/var/lib/mysql/mysql.sock -u root -p powerjob_daily > /tmp/powerjob_db.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
# 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_test_backup.sql > dehoop_test_backup2.sql
# 表名翻译成大写
# sed -E 's/`([a-zA-Z0-9_]*)`/`\U\1`/g' dump.sql > dump_uppercase.sql导入dump文件
# 传到指定服务器，k8s的话，指定pod
scp -P 62222 /tmp/dehoop_test_backup.sql dcloud@192.168.25.175:/data/local-path-provisioner/pvc-6c0a70c5-6658-4e68-93c4-d4126949e73d_mysql_data-mysql-primary-0/data/
# 给777权限
cd /data/local-path-provisioner/pvc-6c0a70c5-6658-4e68-93c4-d4126949e73d_mysql_data-mysql-primary-0/data/
sudo chmod 777 dehoop_test_backup.sql
# 表名小写转大写
perl -pe 's/`([^`]+)`/`\U$1\E`/g' dehoop_test_backup.sql > uppercase_dehoop_test_backup.sql
# 进入命令行
# /usr/bin/mysql -h localhost -u root -pdefinesys2025 -P 3306
# 放开校验，否则表没有主键无法创建
# -- SET GLOBAL pxc_strict_mode=DISABLED;
# -- SHOW VARIABLES LIKE 'pxc_strict_mode';
# -- show databases;
#create database dehoop_20251011;
# 找到映射路径 pxc-db-pxc-1
#mysql -u root -p dehoop_20251011 < /var/lib/mysql/powerjob_test2.sql
mysql -h localhost -u root -pdefinesys2025 -P 3306
create database dehoop;
use dehoop;
source /bitnami/mysql/data/uppercase_dehoop_test_backup.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);对比表条数大小
SET SESSION group_concat_max_len = 1000000;
SET @sql = NULL;
SELECT GROUP_CONCAT(
CONCAT(
'SELECT ''', TABLE_NAME, ''' AS table_name, COUNT(*) AS row_count FROM `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`'
) SEPARATOR ' UNION ALL '
) INTO @sql
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'powerjob_daily'
AND TABLE_TYPE = 'BASE TABLE';
-- 按行数降序，行数相同的按表名升序排序
SET @final_sql = CONCAT('SELECT * FROM (', @sql, ') AS counts ORDER BY row_count DESC, table_name ASC');
SELECT @final_sql; -- 可以先查看最终生成的SQL
PREPARE stmt FROM @final_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;🔗 Links
mysql linux整库迁移
https://ispong.isxcode.com/db/mysql/mysql linux整库迁移/