如何将在远程计算机上运行的MS SQL数据库迁移到在Linux上运行的本地MySQL数据库中，并保留编码 [英] How to migrate MS SQL database running on remote machine to my local MySQL database running on linux, preserving encoding

问题描述

我有一台运行SQL Server(v12，Express版)的远程Windows 10计算机，我需要将一个数据库传输到运行MySQL数据库(MariaDB)的计算机.

I have a remote Windows 10 machine running SQL Server (v12, Express edition) and I need to transfer one database to my machine, which is running MySQL database (MariaDB).

MS SQL数据库包含特殊字符(捷克语变音符号)，如果我尝试在远程计算机上使用MySQL Workbench将数据库迁移到在同一远程计算机上运行的MySQL，则会被弄乱.

MS SQL database contains special characters (Czech language diacritics) that get messed up if I try to use MySQL Workbench on the remote machine to migrate database to MySQL running on the same remote machine.

如何正确传输数据库?

推荐答案

我使用MySQL Workbench尝试了这些组合，但没有一个起作用:

Using MySQL Workbench, I have tried these combinations, none of them worked:

远程MS SQL>远程MySQL本机ODBC驱动程序

remote MS SQL > remote MySQL native ODBC driver

远程MS SQL>远程MySQL ODBC数据源驱动程序

remote MS SQL > remote MySQL ODBC DataSource driver

远程MS SQL>远程MySQL freeTDS ODBC驱动程序(带或不带UTF-8)

remote MS SQL > remote MySQL freeTDS ODBC driver (with or without UTF-8)

虚拟MS SQL>虚拟MySQL本机ODBC驱动程序

virtual MS SQL > virtual MySQL native ODBC driver

虚拟MS SQL>虚拟MySQL ODBC数据源驱动程序

virtual MS SQL > virtual MySQL ODBC DataSource driver

虚拟MS SQL>虚拟MySQL freeTDS ODBC驱动程序(带有或不带有UTF-8)

virtual MS SQL > virtual MySQL freeTDS ODBC driver (with or without UTF-8)

在任何一种情况下，迁移过程都被卡住(工作台无响应)或字符传输不正确.

In either of those cases, either migration process got stuck (Non responding Workbench) or characters were not transferred correctly.

反复试验的总时间约为12小时.

Total time of trial and error was around 12 hours.

在这里，我与您分享一种如何将整个MS SQL数据库成功迁移到运行在我的开发机上的本地MySQL服务器的方法.

Here I am sharing with you one way how I was able to successfully migrate whole MS SQL database to my local MySQL server running on my dev machine.

当我通过VirtualBox计算机通过远程桌面连接(RDC)连接到远程W10计算机时，起初我想删除此多余的步骤以简化操作.

As I was connecting to the remote W10 machine via Remote Desktop Connection (RDC) from my VirtualBox machine, at first I wanted to remove this extra step to make things easier.

1. 如果尚未安装，请下载并安装 Microsoft提供的免费Virtual Box机器并启动

从此虚拟机启动RDC，然后在连接之前，在虚拟机上添加本地磁盘，以供远程计算机

from this virtual machine start RDC and before connecting, add local disk on the virtual machine to be available on remote machine

在远程计算机上启动SQL Server Management Studio并创建数据库备份(右键单击所需的数据库，依次单击任务">备份..."，将备份文件保存到磁盘)

on remote machine start SQL Server management studio and create a backup of the database (Right click on the desired database, Tasks > Backup..., save backup file to the disk)

通过导航到网络"并将该备份文件上传到虚拟机上的C:驱动器，将此备份文件复制到虚拟机上的驱动器中

copy this backup file to your drive on virtual machine by navigating to Networks and uploading the file to C: drive on your virtual machine

在虚拟机上免费安装MS SQL Server Express版以及SQL Server Management Studio(我已经下载了2014版)

install on your virtual machine MS SQL server, Express edition which is free, along with SQL Server Management Studio (I have downloaded 2014 version)

创建新数据库，然后选择任务...">还原">数据库..."，选择上载到虚拟机的文件，然后选择覆盖现有数据库"..

create new database and select Tasks... > Restore > Database..., select the file you uploaded to your virtual machine, select Overwrite the existing database..

为您的MS SQL服务器创建新的登录名(右键单击登录名")，选择所需的登录名和密码，分配sysadmin角色，只是为了使事情变得简单，这是一次性迁移过程

create new login for your MS SQL server (right click on Logins), select desired login and password, assign a sysadmin role, just to make things easier, as this is a one-time migration process

在您的本地Host Linux计算机上安装了最新的MySQL Workbench，我假设MySQL数据库已经在本地运行，如果没有，请安装它

on your local Host Linux machine install latest MySQL Workbench, I assume that MySQL database is running locally already, if not, install it

因为您使用的是Linux，因此如果需要

because you're on Linux, you'll need extra step to install FreeTDS ODBC driver to your system, if this link, how to install FreeTDS driver on Linux goes dead, search for Database Migration Wizard / Microsoft SQL Server migration in MySQL Workbench manual

可从您的Host linux系统访问设置虚拟机，方法是选择附加到:桥接适配器"，然后在虚拟机的设置中选择wlan0.

setup virtual machine to be accessible from your Host linux system, I've done that by selecting Attached to: Bridged adapter and picking wlan0 in the settings of the virtual machine

启动虚拟机并从与适配器使用相同的网络中选择IP范围

start virtual machine and select IP range from the same network as you adapter uses

在虚拟机上为MS SQL Server添加防火墙规则或暂时关闭防火墙

on your virtual machine add a firewal rule for MS SQL server or temporarily turn firewall off

从现在开始，一切都在运行虚拟机的本地Linux机器上完成

1. 启动MySQL Workbench的数据库迁移，选择MS SQL作为源计算机，输入之前在虚拟机中设置的正确IP地址.

1. start MySQL Workbench' Database Migration, select MS SQL as a source machine, input correct IP address that you setup in your virtual machine earlier

作为目标数据库，使用本地MySQL数据库设置和凭据

as a target database, use your local MySQL database settings and credentials

在目标创建选项"步骤中，仅选择创建SQL脚本文件"，然后选择保留方案"(如果它们已经退出).

in the Target Creation Options step select only Create a SQL script file and select Keep schemas if they already exit.

继续执行下一步，直到到达数据传输设置".在那里选择表格的在线副本...但不要按Next

go through next steps until you reach Data Transfer Setup. There select Online copy of table... but do not press Next

编辑在步骤15中保存的脚本.

edit the script saved in the step 15.:

删除:

CREATE SCHEMA IF NOT EXISTS `Test` ;

添加:

CREATE DATABASE `Test` DEFAULT CHARACTER SET utf16 COLLATE utf16_czech_ci;

通过在表定义中添加所需的字符集和排序规则来更改所有表定义:

alter all create table definitions by adding desired character set and collation to the table definition:

CREATE TABLE IF NOT EXISTS `TestTable` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf16_czech_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf16 COLLATE=utf16_czech_ci;

1. 在您的MySQL数据库中运行此更新的脚本

1. run this updated script in your MySQL database

转到MySQL Workbench中的下一步，数据应正确导入

go to the next step in MySQL Workbench and data should import correctly

有些步骤可能没有明确说明，如果需要澄清，请在评论中让我知道

There may be some steps not explicitly stated, please let me know in comments if you need some clarifications

这篇关于如何将在远程计算机上运行的MS SQL数据库迁移到在Linux上运行的本地MySQL数据库中，并保留编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！