mysql linux整库迁移

🧙 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 > /tmp/dehoop_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/dehoop_tmp.sql > /tmp/dehoop_db.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
use powerjob_test;
source /var/lib/mysql/powerjob_test2.sql;
# 输入密码 definesys2025
# 17:21 开始
# 17:41 结束 3GB 25分钟左右 10分钟1GB 

# 配置mysql大小写不区分，0 是区分大小写
# SHOW VARIABLES LIKE 'lower_case_table_names';
lower_case_table_names=1

create 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_JOBS 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 docs