一流网站建设公司/宁波seo教程app推广

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_schema='olddb' 
AND table_type='BASE 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_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' 
AND table_name != "xx_user"; # xx_user表特殊，有多余租户id字段需要过滤
# 注：包括该租户和运营的数据

5）上面第4点生成的SQL用uedit处理

a、将库名和表名中间的tab空格批量处理，Alt+c即可切换并进行批量处理

b、修改没有租户id表的特殊情况的SQL

c、由于是按表业务数据类型抽取数据，存在外检约束问题。需要在每类批量SQL数据执行前加SET SESSION FOREIGN_KEY_CHECKS = 0;

样例如下：

6）上面第5点生成的批量SQL到源库执行，将数据抽取到新库（newdb）

注：图方便没有写源库库名，需选中源库（olddb）执行。

7）通过命令行导出包含数据的新库（jghg）

mysqldump --max-allowed-packet=512M --skip-opt -v -u root -p  -P3306 newdb> newdb2025010801.dump

8）通过命令行压缩导出的新库备份文件，并传到客户数据库服务器

注：同时上传一份到另外一台电脑同时做导入测试，原因是客户服务器导入太慢，两台同步进行。最后用mydql的data目录物理迁移速度最快。

zip newdb2025010801.dump.zip newdb2025010801.dump

3、客户数据库导入过程

客户自建服务器数据库版本需要和源库数据库版本完全一致，方便进行后面写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-set=utf8mb4  newdb < /data/newdb2025010801.dump

3.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