数据库间数据迁移方案
前言
本章节讨论的是不依托第三方开发语言,不通过业务逻辑的方式进行迁移,主要为运维提供的迁移方案。一般应用于历史数据的整体迁移,就为了这一次迁移写一堆的固定代码,不是很优雅的处理方式!
MySQL To MySQL
核心:mysqldump 对源库表数据进行转存储,source命令创建临时源库表数据(通过一个Shell脚本说明,大家把以下具体配置项改成自己的,表结构改成自己的,一键执行即可,其他迁移方案也类似)
#!/bin/bash
echo "Start move Data."
#使用mysqldump命令转存储源数据表结构及其数据为临时脚本文件
mysqldump -h192.168.137.129 -P3307 -uroot -proot parauser --tables t_ssh_accounts t_user > ./parauser.sql;
mysqldump -h192.168.137.129 -P3307 -uroot -proot ngbilling --tables b_user b_group b_cluster b_cluster_user > ./ngbilling.sql;
#连接目标数据库并执行迁移脚本,完整脚本""包裹
mysql -h192.168.137.129 -P3306 -uroot -proot -e"
#切换到目标库
use console;
#设置编码,注意跟源库表编码保持一致
set names utf8;
#执行源库表脚本文件,创建源库表及其数据,临时,迁移完数据后进行drop
source ./parauser.sql;
source ./ngbilling.sql;
#执行迁移脚本,根据自身业务需要来
INSERT IGNORE INTO user_info (id,name,email,phone,real_email,group_id,user_type)
SELECT *
FROM (
SELECT id as user_id,name as username,email,phone as mphone,real_email AS para_email,group_id,user_type FROM user_info
UNION ALL
SELECT a.user_id,a.username,a.email,a.mphone,a.para_email,a.group_id,
IF(b.origin_channel = 'bscc' OR b.origin_channel = 'bscc-cstcloud' OR b.origin_channel = 'bscc-carsi',2,1) as user_type
FROM b_user a LEFT JOIN t_user b ON a.user_id=b.user_id
) tb
GROUP BY user_id
HAVING count(*) = 1
ORDER BY user_id;
INSERT IGNORE INTO account_group (id,pay_user_id,master_user_id,name)
SELECT *
FROM (
SELECT id as group_id,pay_user_id as pay_user_id, master_user_id as master_user_id,name as group_name FROM account_group
UNION ALL
SELECT group_id,pay_user_id, master_user_id,group_name FROM b_group
) tb
GROUP BY group_id
HAVING coun