MySQL数据迁移相关总结

前言：

在平时工作中，经常会遇到数据迁移的需求，比如要迁移某个表、某个库或某个实例。根据不同的需求可能要采取不同的迁移方案，数据迁移过程中也可能会遇到各种大小问题。本篇文章，我们一起来看下 MySQL 数据迁移那些事儿，希望能帮助到各位。

1.关于数据迁移

首先引用下维基百科中对数据迁移的解释：

数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外，验证迁移数据的完整性和退役原来旧的数据存储，也被认为是整个数据迁移过程的一部分。

数据迁移是任何系统实现、升级或集成的关键考虑因素，通常以尽可能自动化的方式执行，从而将人力资源从繁琐的任务中解放出来。

数据迁移有多种原因，包括服务器或存储设备更换、维护或升级、应用程序迁移、网站集成、灾难恢复和数据中心迁移。

对于 MySQL 数据库，也会经常碰到数据迁移的需求，比如说从测试库迁到开发库，或者从一台机器迁移到另一台机器，可能只是迁移一张表，也可能需要迁移整个数据库实例。对于不同的需求可能要采取不同的迁移方案，但总体来讲，MySQL 数据迁移方案大致可以分为物理迁移和逻辑迁移两类。

2.迁移方案及注意点

物理迁移适用于大数据量下的整体迁移。物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种。不同服务器之间可以采用物理迁移，我们可以在新的服务器上安装好同版本的数据库软件，创建好相同目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝来数据文件及日志文件，配置好文件组权限，之后在新服务器这边使用 mysqld 命令启动数据库。

使用物理迁移方案的优点是比较快速，但需要停机迁移并且要求 MySQL 版本及配置必须和原服务器相同，也可能引起未知问题。

相比来说，逻辑迁移适用范围更广，无论是部分迁移还是全量迁移，都可以使用逻辑迁移。逻辑迁移中使用最多的就是通过 mysqldump 等备份工具导出再导入了。

mysqldump 同样适用于不同版本不同配置之间的迁移，不过全量迁移时，笔者不建议使用 -A 参数备份全部数据库，特别是对于不同版本之间迁移，可能某些系统库稍有不同，迁移后容易出现未知问题。若使用 mysqldump 进行全量迁移，可以按照下面步骤操作：

# 原库执行 得到的创建数据库语句在新库执行 (排除系统库)
SELECT
  CONCAT( 'CREATE DATABASE IF NOT EXISTS ', '`', SCHEMA_NAME, '`', ' DEFAULT CHARACTER SET ', DEFAULT_CHARACTER_SET_NAME, ';' ) AS CreateDatabaseQuery 
FROM
  information_schema.SCHEMATA 
WHERE
  SCHEMA_NAME NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' );
  
 # 原库执行 得到的创建用户语句在新库执行 (排除系统用户)
 SELECT
  CONCAT( 'create user \'', USER, '\'@\'', HOST, '\'' ' IDENTIFIED BY PASSWORD \'', authentication_string, '\';' ) AS CreateUserQuery 
FROM
  mysql.`user` 
WHERE
  `User` NOT IN ( 'root', 'mysql.session', 'mysql.sys' );
  
# 原库执行 show grants 得到用户权限 然后逐一在新库执行 （也可以复制那几张权限表到新库）
show grants for 'testuser'@'%';

# 至此 新环境已经存在要迁移的库和用户 只是没有数据
# 原环境使用mysqldump备份除系统库外的所有库 
mysqldump  -uroot -pxxxx -R -E --single-transaction --databases db1 db2 > db.sql

# 然后导入新环境
mysql -uroot -pxxxx < db.sql

对于迁移部分库和部分表，也可以采用上方类似方案，只是备份时要按需备份，下面列举出几种常见的 mysqldump 备份场景：

# 备份单个库
mysqldump -uroot -pxxxxx -R -E --single-transactio --databases db1 > db1.sql

# 备份部分表
mysqldump -uroot -pxxxxx --single-transaction db1 tb1 tb2 > tb12.sql

# 排查某些表
mysqldump -uroot -pxxxxx db1 --ignore-table=db1.tb1 --ignore-table=db1.tb2 > db1.sql

# 只备份结构或数据
mysqldump -uroot -pxxxxx testdb --no-data > testdb_jiegou.sql
mysqldump -uroot -pxxxxx testdb --no-create-info > testdb_data.sql

总得来说，使用 mysqldump 方案更加灵活，为了快速迁移，我们应该尽量减少备份内容，比如说排除无用的日志表。对于一些大表，我们也可以采用单独迁移的方案，比方说，db1 中的 tb1 表特别大，我们可以在备份时先排除 tb1，对于大表 tb1 ，可以使用 LOAD DATA 方式或舍弃再导入表空间的方式来迁移。

数据迁移过程中，可能会遇到各种错误，一步步去解决即可。建议在新库创建好用户并授予好权限后再迁移，这样可以避免出现视图及函数导入错误，因为视图及函数有个定义者的概念。新环境执行导入时，最好使用 root 等具有 SUPER 权限的管理员用户，也可以避免一些因权限产生的问题。

迁移完成后，对于新环境，我们还应该再次进行检查，比如表的个数是否相同，随机抽查几张表，数据是否相同、是否有乱码等。只有确定无误才大功告成。

总结：

本篇文章介绍了 MySQL 数据库数据迁移相关方案及注意点，总结如下思维导图：

以上就是MySQL数据迁移相关总结的详细内容，更多关于MySQL数据迁移的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • python制作mysql数据迁移脚本

    用python写了个数据迁移脚本,主要是利用从库将大的静态表导出表空间,载导入到目标实例中. #!/usr/bin/env python3 #-*- coding:utf8 -*- #author:zhanbin.liu #!!!!!DB必须同版本 #python3环境 pip3 install pymysql paramiko import os #from pathlib import Path import sys import pymysql import paramiko #每次只能迁

  • mysql8.0.11数据目录迁移的实现

    mysql的存储目录默认是在/var/lib/mysql/,下面将修改默认存储目录. 本文使用mysql8.0.11版本,与mysql8.0.20版本不同, 8.0.20版本的数据迁移请看另一篇博客 : mysql8.0.20数据迁移 1.先停止mysql服务: service mysql stop 2.创建需要存放的目录位置并赋予权限: mkdir -p /bak/mysql_data chmod 766 /bak/mysql_data 3.转移原有数据,这里使用 cp -a 进行复制(先进入

  • Mysql的数据库迁移到另一个机器上的方法详解

    Mysql的数据库迁移到另一个机器上的方法详解

    1.先找到迁移服务器上的Data文件,我安装的是mysql5.7,默认安装的路径.那么就是在C:\ProgramData\MySQL文件里面,先把隐藏文件打开. 这个Data文件就是你的数据文件:将你要迁移的数据库文件放到这里如下: 然后打开my.ini:找到datadir,然后将你的Data文件路径复制到下面,你要启动哪一个Data文件就用那个文件的地址,只能存在一个.如下: 然后在重启MySQL服务如下: 然后打开数据库连接工具,我这边用的Navicat,你连接迁移数据库,是你迁移之前的密码

  • MySQL数据库迁移快速导出导入大量数据

    数据库迁移是我们经常可遇到的问题,对于少量的数据,迁移基本上不会有什么问题.生产环境中,有以下情况需要做迁移工作: 磁盘空间不够.比如一些老项目,选用的机型并不一定适用于数据库.随着时间的推移,硬盘很有可能出现短缺: 业务出现瓶颈.比如项目中采用单机承担所有的读写业务,业务压力增大,不堪重负.如果 IO 压力在可接受的范围,会采用读写分离方案: 机器出现瓶颈.机器出现瓶颈主要在磁盘 IO 能力.内存.CPU,此时除了针对瓶颈做一些优化以外,选择迁移是不错的方案: 项目改造.某些项目的数据库存在跨

  • Docker版的MySQL5.7升级到MySQL8.0.13，数据迁移

    1.备份旧的MySQL5.7的数据 记得首先要备份旧的数据,防止升级失败导致数据丢失.备份的方式有两种,一种是在宿主机直接执行导出命令,另外一种是先进入Docker环境下进行操作.主要的导出命令如下: #方式一,直接在宿主机器进行数据备份 # 0df568 是docker的id ;-uroot -p123456 是用户名和密码;dbA dbB是要备份的数据,--databases 后面可以接多个数据库名,导出的sql到/root/all-databases3306.sql docker exec

  • 如何把本地mysql迁移到服务器数据库

    我们可以使用linux的scp命令(scp无法在windows使用),加上mysql自带的mysqldump,能很快的完成数据库的迁移 将本地的数据库(music_db)导出为sql文件(music_db.sql) mysqldump -uroot -p music_db > music_db.sql 用scp命令将sql文件发送到服务器 scp music_db.sql 远程登录账号@服务器ip:服务器目录/music_db.sql ssh登录服务器 ssh 远程登录账号@远程ip 在服务器上

  • mysql8.0.20数据目录迁移的方法

    mysql的存储目录默认是在/var/lib/mysql/,下面将修改默认存储目录. 本文使用mysql8.0.20版本,与mysql8.0.11版本不同, 8.0.11版本的数据迁移请看另一篇博客:mysql8.0.11数据迁移 1.先停止mysql服务: service mysqld stop 2.创建需要存放的目录位置并赋予权限: mkdir -p /bak/mysql_data chmod 766 /bak/mysql_data 3.转移原有数据,这里使用 cp -a 进行复制(先进入到

  • mysql事件之修改事件（ALTER EVENT）、禁用事件（DISABLE）、启用事件（ENABLE）、事件重命名及数据库事件迁移操作详解

    本文实例讲述了mysql事件之修改事件(ALTER EVENT).禁用事件(DISABLE).启用事件(ENABLE).事件重命名及数据库事件迁移操作.分享给大家供大家参考,具体如下: 我们要知道,MySQL允许我们更改现有事件的各种属性.如果我们要更改现有事件,可以使用ALTER EVENT语句,如下所示: ALTER EVENT event_name ON SCHEDULE schedule ON COMPLETION [NOT] PRESERVE RENAME TO new_event_n

  • 从云数据迁移服务看MySQL大表抽取模式的原理解析

    从云数据迁移服务看MySQL大表抽取模式的原理解析

    摘要:MySQL JDBC抽取到底应该采用什么样的方式,且听小编给你娓娓道来. 小编最近在云上的一个迁移项目中被MySQL抽取模式折磨的很惨.一开始爆内存被客户怼,再后来迁移效率低下再被怼.MySQL JDBC抽取到底应该采用什么样的方式,且听小编给你娓娓道来. 1.1 Java-JDBC通信原理 JDBC与数据库之间的通信是通过socket完,大致流程如下图所示.Mysql Server ->内核Socket Buffer -> 客户端Socket Buffer ->JDBC所在的JV

  • MySQL数据迁移相关总结

    MySQL数据迁移相关总结

    前言: 在平时工作中,经常会遇到数据迁移的需求,比如要迁移某个表.某个库或某个实例.根据不同的需求可能要采取不同的迁移方案,数据迁移过程中也可能会遇到各种大小问题.本篇文章,我们一起来看下 MySQL 数据迁移那些事儿,希望能帮助到各位. 1.关于数据迁移 首先引用下维基百科中对数据迁移的解释: 数据迁移(data migration)是指选择.准备.提取和转换数据,并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程.此外,验证迁移数据的完整性和退役原来旧的数据存储,也被认为是整

  • MySQL数据迁移使用MySQLdump命令

    该方案优点是简单,容易手上:缺点是停机时间较长. 因此它适用于数据量不大,或者允许停机的时间较长,并且在这个时间范围内能够完成. 以下是MySQLdump命令的一些用法: 1.将整个数据库的数据及建表文件导出到sql文件中 mysqldump -uroot -proot database1 > database1.sql 2.将sql文件的数据导入,不再是mysqldump,而是mysql mysql -uroot -proot database2 < database1.sql 3.将数据库

  • Python中MySQL数据迁移到MongoDB脚本的方法

    MongoDB简介 MongoDB 是一个基于分布式文件存储的数据库.由 C++ 语言编写.旨在为 WEB 应用提供可扩展的高性能数据存储解决方案. MongoDB 是一个介于关系数据库和非关系数据库之间的产品,是非关系数据库当中功能最丰富,最像关系数据库的. MongoDB是一个文档数据库,在存储小文件方面存在天然优势.随着业务求的变化,需要将线上MySQL数据库中的行记录,导入到MongoDB中文档记录. 一.场景:线上MySQL数据库某表迁移到MongoDB,字段无变化. 二.Python

  • 关于MySQL数据迁移--data目录直接替换注意事项的详解

    近日更换服务器,要做数据库迁移,将数据库内的数据从服务器A迁移到服务器B.由于数据量较大,直接做dump耗时太长,故而采用如下方式处理:首先,在服务器B上安装了与服务器A同版本的MySQL,停止MySQL服务,将安装后的data目录删除:然后,服务器A锁住全部表,从服务器A将整个data目录和数据文件直到拷贝到服务器B上,修改服务器B上MySQL的my.cnf文件中的datadir指向新的data目录.最后,启动服务器B上的MySQL服务.结果启动失败,报出 "无法启动MySQL服务"

  • mysql数据迁移到Oracle的正确方法

    mysql数据迁移到Oracle的正确方法

    在mysql数据库里有一个表student,它的结构如下: 在Oracle数据库里有一个表from_mysql,它的结构如下: 现在要把数据从mysql的student转移到Oracle的from_mysql中去,这里笔者借助kettle的spoon工具,可以快速的实现这个功能. 首先,打开spoon,新建一个转换,命名为表输入到表输出.然后在核心对象中的输入那里找到表输入,拖到编辑界面中,再从输出那里找到输出拖到编辑界面中,连接表输入和表输出,如图所示: 右击表输入,选择编辑步骤,弹出如下窗口

  • 简述MySQL分片中快速数据迁移

    推荐阅读:MySQL 数据库跨操作系统的最快迁移方法 mysql 备份与迁移 数据同步方法 操作实践背景: travelrecord表定义为10个分片,尝试将10个分片中的2个分片转移到第二台MySQL上,并完成记录, 要求最快的数据迁移做法,中断业务时间最短 思路一利用mysqldump: 在这里我们只针对mysql innodb engine,而且配置bin-log的数据库进行分析.因为是将10个分片中的两个分片进行迁移,其实就是数据库的迁移过程,就是将10个数据库中的两个数据迁移到另外一台

  • 解决mysql数据库数据迁移达梦数据乱码问题

    解决mysql数据库数据迁移达梦数据乱码问题

    受到领导的嘱托,接手了一个java项目,要进行重构,同时了项目的整体建设要满足信创的要求. 那么首先就要满足两点: 1,使用国产数据库达梦8替换mysql数据库 2,使用金蝶中间件替换tomcat进行容器部署 在不懈的努力下,我已在本地的搭建和安装完成达梦8(dm8)数据库,也完成了代码框架更改数据库源,替换达梦数据库的demo验证工作. driverClassName: dm.jdbc.driver.DmDriver url: jdbc:dm://10.0.3.132:5236/XC-SERV

  • MySQL数据备份、还原、数据库迁移以及表的导出和导入

    目录 前言 一.数据备份 1.使用mysqldump命令备份 2.直接复制整个数据库目录 3.使用mysqlhotcopy工具快速备份 二.数据还原 1.使用mysql命令还原 2.直接复制到数据库目录 三.数据库迁移 1.相同版本的MySQL数据库之间的迁移 2.不同版本的MySQL数据库之间的迁移 四.表的导出和导入 1.用select…into outfile导出文本文件 2.用mysqldump命令导出文本文件 3.用mysql命令导出文本文件 4.用load data infile方式

随机推荐