mysql 复制数据库，更改租户、定时任务等
1.更改所有表租户id1.1、动态生成所有表的更新语句且有租户字段tenant_id的才需要更新没有不更新。SELECT CONCAT(UPDATE , t.table_name, SET tenant_id 新租户 WHERE tenant_id 旧租户;) FROM information_schema.tables t JOIN information_schema.columns c ON c.table_schema t.table_schema AND c.table_name t.table_name WHERE t.table_schema 你的数据库名 AND t.table_type BASE TABLE AND c.column_name tenant_id;1.2、复制结果执行-- 1. 先禁用外键检查避免约束报错 SET FOREIGN_KEY_CHECKS 0; -- 2. 遍历所有表批量更新 tenant_id UPDATE table1 SET tenant_id 200 WHERE tenant_id 100; -- 动态生成所有表的更新语句 -- 复制结果执行 -- 3. 恢复外键检查 SET FOREIGN_KEY_CHECKS 1;2.复制定时任务xxl-job字段job_group为执行器表xxl_job_group的idINSERT INTO enuo-xxl-job.xxl_job_info(job_group, job_cron, job_desc, add_time, update_time, author, alarm_email, executor_route_strategy, executor_handler, executor_param, executor_block_strategy, executor_timeout, executor_fail_retry_count, glue_type, glue_source, glue_remark, glue_updatetime, child_jobid, trigger_status, trigger_last_time, trigger_next_time) SELECT 13 job_group, job_cron, job_desc, add_time, update_time, author, alarm_email, executor_route_strategy, executor_handler, executor_param, executor_block_strategy, executor_timeout, executor_fail_retry_count, glue_type, glue_source, glue_remark, glue_updatetime, child_jobid, trigger_status, trigger_last_time, trigger_next_time FROM enuo-xxl-job.xxl_job_info WHERE job_group 10 order by id;
