广州黄埔区网站建设,空间排版设计网站,泰安东平县建设局网站,南苑网站建设1、背景
租户使用Saas系统#xff0c;用一段时间后要将系统、数据搬迁到自建服务器。该Saas系统没有按租户分库#xff0c;且数据库数据量太大#xff0c;需要将单租户的数据抽取出来。Saas系统使用Mysql5.7数据库#xff0c;主要使用INFORMATION_SCHEMA.COLUMNS表进行数据…1、背景
租户使用Saas系统用一段时间后要将系统、数据搬迁到自建服务器。该Saas系统没有按租户分库且数据库数据量太大需要将单租户的数据抽取出来。Saas系统使用Mysql5.7数据库主要使用INFORMATION_SCHEMA.COLUMNS表进行数据库的表数据抽取批量语句编写。
2、源数据库处理过程
2.1 数据库表类型
库类型表业务数据类型备注数据量小和租户关系不大系统级数据。不用单独抽取直接全库导。然后再处理。数据量大没有租户id系统级数据抽取表所有数据没有租户id但可关联过滤抽取表按租户过滤数据有租户id通过两类租户id管理过滤抽取表按租户过滤数据
2.2 抽取过程
1源库olddb直接导出数据量小的几个库通过工具和命令行均可以详细方法略
2源库新建一个库newdb将单租户数据抽取到该表
CREATE DATABASE newdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;3导出源库数据量大的库表结构此处不包含视图、存储过程、函数等。并导入新库newdb
注因为同一个库生成视图、存储过程、函数都会关联之前的源库olddb而不是新库newdb此类单独处理。通过工具和命令行均可以详细方法略。
4源库根据表类型生成抽取数据SQL
# 没有租户id表如TENANT_ID_、tenant_id
SELECT insert into jghg. , table_name, select * from , table_name, ;
FROM information_schema.TABLES WHERE table_schemaolddb
AND table_typeBASE TABLE AND table_name NOT IN (
SELECT table_name FROM INFORMATION_SCHEMA.COLUMNS WHERE
table_schema olddb AND (column_name TENANT_ID_ OR column_name tenant_id )
AND table_name ! bill_view # 过滤视图
) ;
# 注此处也有坑单独修改SQL处理
# a、有些表没有租户id但数据也要过滤。b、有些表没有租户id但可以没用# 有租户id表 TENANT_ID_
SELECT insert into newdb. , table_name, select * from , table_name,
where TENANT_ID_ 116675 or TENANT_ID_ 824853 or TENANT_ID_ 000000
or TENANT_ID_ IS NULL; FROM INFORMATION_SCHEMA.COLUMNS WHERE
table_schema olddb AND column_name TENANT_ID_ AND table_name ! bill_view;
# 注包括该租户和运营的数据# 有租户id表 tenant_id
SELECT insert into newdb. , table_name, select * from , table_name,
where tenant_id 116675 or tenant_id824853 or tenant_id 000000 or
tenant_id IS NULL ; FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema olddb
AND column_name tenant_id AND table_name ! bill_view
AND table_name ! xx_user; # xx_user表特殊有多余租户id字段需要过滤
# 注包括该租户和运营的数据5上面第4点生成的SQL用uedit处理
a、将库名和表名中间的tab空格批量处理Altc即可切换并进行批量处理
b、修改没有租户id表的特殊情况的SQL
c、由于是按表业务数据类型抽取数据存在外检约束问题。需要在每类批量SQL数据执行前加SET SESSION FOREIGN_KEY_CHECKS 0;
样例如下 6上面第5点生成的批量SQL到源库执行将数据抽取到新库newdb
注图方便没有写源库库名需选中源库olddb执行。
7通过命令行导出包含数据的新库jghg
mysqldump --max-allowed-packet512M --skip-opt -v -u root -p -P3306 newdb newdb2025010801.dump8通过命令行压缩导出的新库备份文件并传到客户数据库服务器
注同时上传一份到另外一台电脑同时做导入测试原因是客户服务器导入太慢两台同步进行。最后用mydql的data目录物理迁移速度最快。
zip newdb2025010801.dump.zip newdb2025010801.dump3、客户数据库导入过程
客户自建服务器数据库版本需要和源库数据库版本完全一致方便进行后面写mysql的data目录物理迁移。
3.1 新建数据库服务此处过程略
注新建数据库时my.ini或my.cnf参数尽量少方便排查问题调参等数据迁移成功之后再进行。
3.2 新建数据库
CREATE DATABASE newdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 还包括其他数据量小的系统级库此处略3.3 通过命令行导入新库jghg
mysql RESET MASTER; # 清除所有的二进制日志文件并重置二进制日志索引。
mysql -u root -P3306 -p --default-character-setutf8mb4 newdb /data/newdb2025010801.dump3.4 通过mysql客户端导入其他库和新库的视图、函数、存储过程等。此处详细步骤略
3.5 源库生成批量增加字段自增语句
注当前使用的mysq5.7.28版本可能有bug引起字段自增没有导入
SELECT ALTER TABLE , TABLE_NAME, MODIFY , COLUMN_NAME , COLUMN_TYPE,
AUTO_INCREMENT ; FROM information_schema.COLUMNS WHERE EXTRA LIKE %auto_increment%
AND TABLE_SCHEMA olddb;3.6 客户服务器新库newdb执行批量增加字段自增语句 4、数据库data目录物理迁移方案
mysql5.7支持配置文件中指定的datadir目录整体迁移。可以跨操作系统不过数据库版本必须完全一致。
主要在因为客户服务器导入mysql可能时间太长不如另外一台上恢复时间快时需要
1、客户数据库服务器停止mysql服务
2、将另外一台包含租户完整数据的mysql 的data目录压缩并上传客户数据库服务器
3、解压刚上传data目录并替换客户数据库服务器mysql的data目录
4、重启mysql
