数据迁移之MySQL

最新推荐文章于 2024-03-23 07:29:29 发布
最新推荐文章于 2024-03-23 07:29:29 发布
阅读量591 收藏 2
点赞数 2
分类专栏： MySQL 文章标签： 数据库迁移 环境调研 方案制定 迁移演练 数据验证
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_37692493/article/details/115110014
版权

文章目录


当我们面对一个未知的数据库业务系统，并需要在一定时间内完成整套业务（应用+数据库）的迁移，前期的环境调研、迁移方案制定、迁移测试演练、正式迁移割接、迁移回滚方案，每一步都是重中之重必不可少，本文档主要梳理整个迁移过程中一些通用关注点，可帮助大家快速上手数据库迁移。

一、环境调研

无论我们迁移什么数据库，我们要做的第一步都是先要了解清楚我们要迁移的数据库的基本环境信息，以及它的使用情况，在整个业务拓扑的流向。这样我们才能尽量在第一次迁移测试时少走一些弯路。

1.1 服务器信息

所属业务系统服务器名称服务器IP服务器配置存储配置数据库IP:port数据库版本
xxx_生产xxx_prod172.16.xx.xx16C128GSSD 2T172.16.xx.xx:3306MySQL 5.7.33
xxx_预发xxx_stg

1.2 数据库信息

1.2.1 数据库基本信息

若MySQL迁移为自建环境，那么我们最好使用源环境的my.cnf文件对数据库进行初始化并应用，但是如果我们需要将自建数据库迁移至一些云数据库环境中，某些影响数据库使用的关键参数会影响到我们的迁移方案制定以及目标环境的正常业务使用，我们还是需要重点关注以下这些参数：

数据库版本事物隔离级别自动提交Server_id是否开启binlogbinlog模式binlog镜像模式大小写敏感索引前缀上限sql_mode
5.7.20-logREAD-COMMITTED1330611ROWFULL01STRICT_TRANS_TABLES,NO_ZERO_IN_DATE

相关SQL支持：

mysql> SELECT version(), @@transaction_isolation, @@autocommit, @@server_id, @@log_bin, @@binlog_format, @@binlog_row_image, @@lower_case_table_names, @@innodb_large_prefix, @@sql_mode;

1.2.2 数据库用户信息

数据库账号权限所属业务
monitor@%GRANT PROCESS, REPLICATION CLIENT ON . TO ‘monitor’@’%’xxx_a
root@localhostGRANT ALL PRIVILEGES ON . TO ‘root’@‘localhost’ WITH GRANT OPTIONxxx_b
sansi_rw@%GRANT ALL PRIVILEGES ON . TO ‘sansi_rw’@’%’xxx_c

相关SQL支持：

-- 查看当前数据库用户列表
mysql> select user,host from mysql.user;

-- 查看制定数据库用户权限
mysql> show grants for 'root'@'localhost';

1.3 库表信息调研

1.3.1 数据库对象统计概览

数据库对象类型对象数量
db1EVENTS1
db1PROCEDURE2
db1TABLE4
db1TRIGGER1
db1VIEW2

1.3.2 数据库信息统计概览

数据库字符集校对规则预估记录数预估数据容量(MB)预估索引容量(MB)预估空闲空间(MB)预估总大小(MB)
mysqlutf8mb4utf8mb4_general_ci19822.110.154.006.31
BIGDATA_ORDERutf8mb4utf8mb4_general_ci26110.210.000.000.21
information_schemautf8utf8_general_ciNULL0.100.001230.001230.10
db1utf8utf8_general_ci90.030.000.000.03
AAA_bbbutf8utf8_general_ci100.020.000.000.02
sysutf8utf8_general_ci60.010.000.000.01
db2utf8utf8_general_ci40.010.000.000.01
performance_schemautf8utf8_general_ci13222960.000.000.000.00
db3utf8utf8_general_ciNULLNULLNULLNULLNULL

1.3.3 表信息统计概览

数据库名表名引擎行模式是否有主键预估数据容量(MB)预估索引容量(MB)预估空闲空间(MB)预估总大小(MB)预估行数
db1t2InnoDBDynamic无0.010.000.000.018
db1AAA_bbbInnoDBDynamic无0.010.000.000.012
db1t1InnoDBDynamic有0.180.000.000.182605
db1childInnoDBDynamic有0.010.000.000.010
db1parentInnoDBDynamic有0.010.000.000.010

1.3.4 忽略大小写敏感后是否存在重名表

数据库表名统计数
xxxx2

相关SQL支持：

-- 数据库对象统计概览
select db AS '数据库',type AS '对象类型',cnt AS '对象数量' from
(select 'TABLE' type,table_schema db, COUNT(*) cnt  from information_schema.`TABLES` a where table_type='BASE TABLE' group by table_schema
union all
select 'EVENTS' type,event_schema db,count(*) cnt from information_schema.`EVENTS` b group by event_schema
union all
select 'TRIGGER' type,trigger_schema db,count(*) cnt from information_schema.`TRIGGERS` c group by trigger_schema
union all
select 'PROCEDURE' type,db ,count(*) cnt from mysql.proc d where`type` = 'PROCEDURE' group by db
union all
select 'FUNCTION' type,db,count(*) cnt  from mysql.proc e where`type` = 'FUNCTION' group by db
union all
select 'VIEW' type,TABLE_SCHEMA,count(*) cnt  from information_schema.VIEWS f group by table_schema  ) t
order by db,type;

-- 数据库信息统计概览
select a.SCHEMA_NAME AS '数据库名', a.DEFAULT_CHARACTER_SET_NAME AS '字符集', a.DEFAULT_COLLATION_NAME AS '校对规则', sum(table_rows) AS '记录数总计', sum(truncate(data_length/1024/1024, 2)) AS '预估数据容量(MB)', sum(truncate(index_length/1024/1024, 2)) AS '预估索引容量(MB)', sum(truncate(data_free/1024/1024, 2)) AS '预估空闲空间(MB)', sum(truncate((data_length+index_length+data_free)/1024/1024, 2)) AS '预估总大小(MB)'  from INFORMATION_SCHEMA.SCHEMATA a left outer join information_schema.tables b on a.SCHEMA_NAME=b.TABLE_SCHEMA group by a.SCHEMA_NAME,  a.DEFAULT_CHARACTER_SET_NAME,a.DEFAULT_COLLATION_NAME order by sum(data_length) desc, sum(index_length) desc;

-- 表信息统计概览
select a.table_schema AS '数据库名', a.table_name as '表名', a.ENGINE as '引擎', a.ROW_FORMAT as '行模式', case when tmp.table_name is null then '有' when tmp.table_name is not null then '无' end '是否有主键', truncate(data_length/1024/1024, 2) AS '预估数据容量(MB)', truncate(index_length/1024/1024, 2) AS '预估索引容量(MB)', truncate(data_free/1024/1024, 2) AS '预估空闲空间(MB)', truncate((data_length+index_length+data_free)/1024/1024, 2) AS '预估总大小(MB)', a.table_rows as '预估行数' from information_schema.tables  a left join ( select table_schema,table_name from information_schema.tables where (table_schema,table_name) not in(     select distinct table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI' ) and TABLE_TYPE='BASE TABLE' and table_schema not in ('sys','mysql','information_schema','performance_schema') ) tmp on a.table_schema=tmp.table_schema and a.table_name=tmp.table_name where a.TABLE_TYPE='BASE TABLE' and a.table_schema not in ('sys','mysql','information_schema','performance_schema') order by a.table_schema,data_length desc;


-- 忽略大小写敏感后是否存在重名表
select table_schema as '数据库',lower(table_name) as '表名',count(*) as '统计数' from information_schema.tables group by table_schema,lower(table_name) having count(*) >1;

1.4 数据流拓扑

数据流拓扑可帮助我们了解在整个业务模块中，该数据库的整个数据来源以及流向。首先就拿数据源来讲，可能数据库的数据源除应用程序1、应用程序2等等，此外可能还存在一些数据周期性的同步任务，这就需要我们在整个迁移乃至割接过程中需要协调各方人员一起配合完成整个迁移工作。其次是数据流向/使用也可能是多种多样的，我们需要调研清楚这些信息，方便我们规划在整个迁移中的数据/业务验证方式。

二、方案制定

2.1 各种方案特点对比

方案优势劣势描述
依赖原生复制可达到数据的实时同步，割接停机窗口耗时最短；可直接对原master进行复制，也可对slave进行级联复制，MySQL 5.7以上也可实现多源复制；可设置复制对象master、级联复制源的slave需要开启binlog相关参数；若数据库中存在频繁对无主键表的DML，复制延迟将会很大;受限于数据库版本兼容性
xtrabackup物理备份恢复速度相对较快，可通过全量备份+增量备份+binlog的方式缩短割接停机窗口需要预留一定的割接停机窗口，物理恢复不可跨版本迁移；不可单独迁移其中某个数据库/表
mysqldump适用于数据量相对较小，需要跨版本迁移的场景;可通过全量dump+binlog增量恢复的方式缩短割接停机窗口；迁移对象灵活需要预留一定的割接停机窗口，逻辑恢复相对较慢，不适用于数据量较大的场景；
DTS比较成熟的数据同步工具，可实现全量+实时增量同步，割接停机窗口耗时最短；比较适用于自建上云使用；迁移对象灵活需要开启相关binlog才可进行增量同步；对无主键表进行了一定的优化，但还是不能很好支持无主键表的频繁变更操作增量同步；依赖阿里，部分报错处理只能通过阿里进行支持
Dataworks一种离线数据处理工具，适用于数据库异构改造迁移；迁移对象灵活必须源环境停止业务后，才可进行一次性迁移处理，割接停机窗口耗时最大；表级别配置迁移任务
canal阿里开源项目，可通过binlog实现增量实时同步；迁移对象灵活与DTS相同

2.2 数据库账号权限

源端数据库账号权限要求：

  • 物理备份用户权限：RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT
  • 原生复制用户权限：REPLICATION SLAVE
  • DTS/canal：全量操作：待迁移数据库SELECT权限 ； 增量操作：待迁移数据库SELECT权限、REPLICATION SLAVE、REPLICATION CLIENT、SHOW VIEW

目标端数据库账号权限要求：迁移数据库的读写权限

2.3 迁移步骤/进度记录

迁移任务迁移步骤重要过程和问题结果记录预计执行时间实际完成时间执行人配合方是否通过进度描述
迁移任务1目标环境数据库部署-已完成2021-03-012021-03-01xxx-通过已完成
-源/目标网络打通-已完成2021-03-032021-03-03xxxyyy通过已完成
-源/目标数据库账号申请-已完成2021-03-032021-03-03xxxyyy通过已完成
-连通性测试-已完成2021-03-032021-03-03xxx-通过已完成
-前置任务需要业务方配置的一些业务关停等操作，根据实际编写已完成2021-03-052021-03-05yyyxxx通过已完成
-数据迁移迁移对象记录、迁移时间记录、迁移中问题点记录已完成2021-03-052021-03-05xxxyyy通过已完成
-数据校验常规count校验，业务校验已完成2021-03-052021-03-05xxxyyy通过已完成
-业务割接-已完成2021-03-052021-03-05xxxyyy通过已完成
-回滚方案-已完成2021-03-052021-03-05xxxyyy通过已完成
迁移任务2

2.4 数据迁移中的一些关注点

  • 评估跨版本迁移的风险性和兼容性，跨版本下业务需要提前做好测试
  • 迁移源/目标中关键性参数尽量保证一致，特别是1.2.1 中提到的一些关键参数、以及数据库字符集格式等
  • 在使用一些利用binlog进行数据同步迁移方案中，要考虑目标端event、trigger对数据一致性的影响
  • 数据校验中，重要数据还是需要业务层进行业务校验

三、迁移演练

3.1 迁移演练记录

迁移任务迁移步骤重要过程和问题结果记录预计执行时间实际完成时间执行人配合方是否通过进度描述
迁移任务1目标环境数据库部署-已完成2021-03-012021-03-01xxx-通过已完成
-源/目标网络打通-已完成2021-03-032021-03-03xxxyyy通过已完成
-源/目标数据库账号申请-已完成2021-03-032021-03-03xxxyyy通过已完成
-连通性测试-已完成2021-03-032021-03-03xxx-通过已完成
-前置任务需要业务方配置的一些业务关停等操作，根据实际编写已完成2021-03-052021-03-05yyyxxx通过已完成
-数据迁移迁移对象记录、迁移时间记录、迁移中问题点记录已完成2021-03-052021-03-05xxxyyy通过已完成
-数据校验常规count校验，业务校验已完成2021-03-052021-03-05xxxyyy通过已完成
-业务割接-已完成2021-03-052021-03-05xxxyyy通过已完成
-回滚方案-已完成2021-03-052021-03-05xxxyyy通过已完成
迁移任务2

3.2 迁移方案优化

迁移方案的演练，是为了踩一些我们未能预料到的坑，也是为了后续更好的评估整个方案的可行性，让我们对整个迁移方案做出更好的优化，迁移方案的优化主要关注：

  • 迁移中未考虑到的点
  • 迁移步骤可行性
  • 迁移时间窗口限制

四、迁移割接/业务验证/回滚

迁移割接采取怎样的方案很大程序上依赖我们迁移方案的选择。我们需要确认好以下几点：

  • 具体的割接执行步骤
  • 评估割接时间窗口
  • 数据/业务验证方式
  • 回滚方案

4.1 数据验证脚本

一般而言检查mysql数据一致性使用count计数进行校验。对于checksum，checksum默认会对表记录一行一行进行校验，且校验期间会对表加read lock，若数据库存在大表，请谨慎操作！！！！

#cat mysql_check.sh
#!/bin/sh
#****************************************************************#
# ScriptName: mysql_check.sh
# Author: Sansi
# Create Date: 2019-11-23 15:30
# Modify Author: Sansi
# Modify Date: 2019-12-17 11:19
# Function:
# Usage: bash mysql_check.sh db1 [db2] ... [dbN]
#***************************************************************#
#!/bin/bash

#source info
#source_user=transfer
s_user=${db_user}
s_host=172.16.x.xx
s_port=${db_port}
s_password=${db_password}

#target info
t_user=${db_user}
t_host=172.16.x.xx
t_port=${db_port}
t_password=${db_password}

#检查db下每张表的checksum值
Checksum(){
	t_checksum=`mysql -u${t_user} -p${t_password} -h${t_host} -P${t_port} -e "checksum table ${table_name};" | grep -v Checksum | awk '{print $2}'`
	s_checksum=`mysql -u${s_user} -p${s_password} -h${s_host} -P${s_port} -e "checksum table ${table_name};" | grep -v Checksum | awk '{print $2}'`

	if [ ${t_checksum} -eq ${s_checksum} ]
	then
		echo -e "${table_name} \tchecksum is ok"
	else
		echo -e "${table_name} \tchecksum is not ok"
	fi
}

#遍历需要校验的所有db，并检查每个db下每张表的count数
for db in $@
do
	echo -e "Begin check Schema : $db ..."
	#导出db下所有表的目录
	mysql -u${s_user} -p${s_password} -h${s_host} -P${s_port} -e "select concat(table_schema,\".\",table_name) from information_schema.tables where table_schema='$db';" | grep ${db}>${db}.txt

for table_name in `cat ${db}.txt`
do
		t_count=`mysql -u${t_user} -p${t_password} -h${t_host} -P${t_port} -e "select count(*) from ${table_name};" | grep -v count`
		s_count=`mysql -u${s_user} -p${s_password} -h${s_host} -P${s_port} -e "select count(*) from ${table_name};" | grep -v count`

		if [ ${t_count} -eq ${s_count} ]
		then
			echo -e "${table_name} \t's count is ok ..."
			#Checksum
		else
			echo -e "${table_name} \tcount is different\tScource:${s_count}\tTarget:${t_count}."
			
		fi

done
done
三思呐三思
关注
  • 2
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
MySQL表数据迁移
02-26
直接解压到www目录下，在浏览器中输入http://localhost/datamove.html 即可使用（当然如果不是80端口自行加上端口号） PS:数据库目前只支持MySQL数据库
MySQL+人大金仓+数据迁移
09-09
"MySQL+人大金仓+数据迁移" MySQL 是一个流行的开源关系数据库管理系统，而人大金仓是基于 PostgreSQL 的一款商业数据库管理系统。数据迁移是指将数据从一个数据库管理系统迁移到另一个数据库管理系统的过程。本文...
如何简单实现mysql数据库迁移
weixin_41645232的博客
02-21 8244
业务场景：需要将服务器A上的mysql数据库迁移到另外一台服务器B。 1. 用ssh终端连接服务器A 2. mysqldump指令将数据库表/数据保存成xx.sql文件存到本地 #导出数据到/root/目录下 # 导出所有数据库的表结构和数据 mysqldump -uroot -p123456 --all-databases > /root/alldata.sql # 导出指定数据......
mysql数据库数据迁移
qq_33376464的博客
06-29 220
近期项目中遇到较多的数据迁移，我们项目使用的是mysql数据库；开始使用的是Kettle进行数据的迁移。后来针对个别表的数据迁移使用的方法是：insert into tableA select columnA,columnB, columnB from tableB； 其中要注意的是： 1. TableA中的字段要与查询TableB的字段数量一致； 2.TableA 中数据赋值的顺序与TableB查询出的字段顺序一致； ...
MySQL数据迁移
eagle89的专栏
12-21 1835
mysql迁移
mysql和Oracle数据互相迁移
11-18
Oracle和Mysql互相迁移数据，可同时迁移多个不同数据库数据（并发的将数据在oracle和mysql之间随意迁移），效率高（实际测试表明，将100万数据从oracle迁移到mysql耗时2分30秒，其中1分30秒耗在...支持中文数据迁移。
MySQL表数据迁移自动化
02-03
之前我写过关于SQLServer的数据迁移自动化的文章：SQLServer数据库迁移偏方，在上篇文章中设计了一张临时表，这个临时表记录搬迁的配置信息，用一个存储过程读取这张表进行数据的迁移，再由一个Job进行迭代调用这个...
简述MySQL分片中快速数据迁移
09-10
MySQL分片中的快速数据迁移是一项重要的任务，尤其在大型分布式数据库系统中，确保数据的完整性和业务的连续性至关重要。本文将深入探讨两种常见的迁移策略：利用`mysqldump`和MySQL的多源复制（Multi-Source ...
MySQL数据迁移到postgresql必备手册.pdf
09-01
MySQL到PostgreSQL的数据迁移是一项常见的任务，特别是在企业需要利用PostgreSQL的特定功能或者希望转换到一个更开放的数据库系统时。以下是对标题和描述中提到的知识点的详细说明： 1. **数据迁移工具：Navicat ...
Mysql的数据库迁移到另一个机器上的方法详解
01-19
1.先找到迁移服务器上的Data文件，我安装的是mysql5.7，默认安装的路径。那么就是在C:\ProgramData\MySQL文件里面，先把隐藏文件打开。 这个Data文件就是你的数据文件；将你要迁移的数据库文件放到这里如下： 然后打开my.ini：找到datadir,然后将你的Data文件路径复制到下面，你要启动哪一个Data文件就用那个文件的地址，只能存在一个。如下： 然后在重启MySQL服务如下： 然后打开数据库连接工具，我这边用的Navicat，你连接迁移数据库，是你迁移之前的密码。 总结 以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定
数据库迁移（任何一款到任何一款）
11-17
数据库之前任意的迁移工具，从Oracle 到DB2 ，DB2 到 Sybase ，Mysql 等。另外从Oracle 到国产化数据库，如达梦数据库，人大金仓数据库 等
mysql数据库数据迁移操作
栗少的博客
05-27 6306
1.新建一个需要迁移的数据库 2.进行数据迁移 　　迁移步骤： 　　　　　　1.工具-->点击 数据传输 ，选择需要被导的数据库，和导入的数据库 看到了吗以及导入成功了 ...
MySQL数据库迁移注意点
weixin_33851177的博客
03-25 223
——废话先不罗嗦了，就让这篇草草完成的博客文章全且当做个记录吧。 以前我使用的是CentOS上的MySQL，后来因为测试需要，需要将网站以及在数据库存储的数据迁移到新的系统中进行测试，目标系统为RedHat Enterprise Linux Server(RHEL)。 由于以前从来没有迁移过数据库，因此马上就遇到了难题。 还有其他问题没有列出，仅仅就权限的问题在下面的命令以及执行结果中展示...
mysql数据迁移教程_mysql搭建及数据迁移教程
weixin_30916627的博客
02-06 134
ubuntu 下 mysql数据库的搭建 及 数据迁移1.mysql的安装 我是使用apt-get直接安装的 :sudo apt-get install mysql-server sudo apt-get install mysql-client 2.配 ...分布式计算(二)使用Sqoop实现MySQL与HDFS数据迁移近期接触了一个需求,业务背景是需要将关系型数据库的数据传输至HDFS进行计算...
MySQL数据库--几种数据迁移的方法详解都在这了！看完必懂
最新发布
m0_58269070的博客
03-23 8543
大型分布式系统犹如一个生命，系统中各个服务犹如骨骼，其中的数据犹如血液，而Kafka犹如经络，串联整个系统。这份Kafka源码笔记通过大量的设计图展示、代码分析、示例分享，把Kafka的实现脉络展示在读者面前，帮助读者更好地研读Kafka代码。麻烦帮忙转发一下这篇文章+关注我如经络，串联整个系统。这份Kafka源码笔记通过大量的设计图展示、代码分析、示例分享，把Kafka的实现脉络展示在读者面前，帮助读者更好地研读Kafka代码。麻烦帮忙转发一下这篇文章+关注我。
MySQL篇—迁移数据目录到新的本地路径
奈斯DBA的博客
02-02 3184
通过linux的mv命令或者MySQL的clone插件迁移数据目录到新的本地新的路径下。
mysql数据库通过拷贝目录实现迁移
cooldream2009的博客
09-06 2559
在windows环境中，如果mysql已有数据目录，进行数据迁移，可以通过直接拷贝数据文件的方式实现。下面是详细步骤。
数据库迁移全记录
liuzp111的专栏
12-24 2455
前言：1）A服务器（mysql版5.5）上的数据库文件是放在系统盘的（心塞），配置mysql的时候千万要改数据库存放路径，系统盘的空间才20G，目前买了一台新的数据库服务器，只做数据处理，所以要迁移到新的服务器上。2）目标服务器也是阿里云的ECS（mysql版本5.7），所以想直接通过内网复制把数据库的存放目录整体迁移，可是移过去后却是有部分表打不开，不知道为啥，为了没有mysql版本兼容等问题，...
Apollo H2数据迁移至MySQL数据库指南
"Apollo H2文件数据迁移MySQL数据库手册提供了使用DBeaver和Navicat工具将Apollo的H2数据库文件迁移至MySQL的详细步骤。该过程涉及Apollo配置数据库apolloconfigdb.mv.db和门户数据库apolloportaldb.mv.db的数据迁移...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值