SQL Server 到 MySQL 数据传输 [英] SQL Server to MySQL data transfer

问题描述

我正在尝试将基于常量和连续的批量数据从 SQL Server 数据库传输到 MYSQL 数据库.我想使用 SQL Server 的 SSMS 复制，但这显然仅适用于 SQL Server 到 Oracle 或 IBM DB2 的连接.目前，我们正在使用 SSIS 转换数据并将其推送到 MYSQL 数据库中的临时位置，并在那里进行复制.我想要最快的数据传输方式，并且使几种方法复杂化.

I am trying to transfer bulk data on a constant and continuous based from a SQL Server database to a MYSQL database. I wanted to use SQL Server's SSMS's replication but this apparently is only for SQL Server to Oracle or IBM DB2 connection. Currently we are using SSIS to transform data and push it to a temporary location at the MYSQL database where it is copied over. I would like the fastest way to transfer data and am complication several methods.

我有一个新的方法来转换数据，我确信这将解决大多数时间问题，但我想确保我们将来不会遇到时间问题.我已经建立了一个链接服务器，它使用 MYSQL ODBC 驱动程序在 SQL Server 和 MYSQL 之间进行通信.这似乎很慢.我有一些代码也使用 Microsoft 的 ODBC 驱动程序，但使用的太少以至于我无法衡量性能.有谁知道这两个数据库之间的快速通信方式?我一直在研究似乎与 OleDB 层通信的 MYSQL 数据提供程序.我不太确定该相信什么以及该朝哪个方向发展，有什么想法吗?

I have a new way I plan on transforming the data which I am sure will solve most time issues but I want to make sure we do not run into time problems in the future. I have set up a linked server that uses a MYSQL ODBC driver to talk between SQL Server and MYSQL. This seems VERY slow. I have some code that also uses Microsoft's ODBC driver but is used so little that I cannot gauge the performance. Does anyone know of lightening fast ways to communicate between these two databases? I have been researching MYSQL's data providers that seem to communicate with a OleDB layer. Im not too sure what to believe and which way to steer towards, any ideas?

推荐答案

过去我使用 Java 中的 jdbc-odbc 桥来完成此操作，但通过 ODBC 的性能不是很好.我建议查看类似 http://jtds.sourceforge.net/ 之类的东西，这是一个纯 Java 驱动程序您可以放入一个简单的 Groovy 脚本，如下所示:

I used the jdbc-odbc bridge in Java to do just this in the past, but performance through ODBC is not great. I would suggest looking at something like http://jtds.sourceforge.net/ which is a pure Java driver that you can drop into a simple Groovy script like the following:

import groovy.sql.Sql
sql = Sql.newInstance( 'jdbc:jtds:sqlserver://serverName/dbName-CLASS;domain=domainName',     
'username', 'password', 'net.sourceforge.jtds.jdbc.Driver' )
sql.eachRow( 'select * from tableName' ) { 
  println "$it.id -- ${it.firstName} --" 
  // probably write to mysql connection here or write to file, compress, transfer, load
}

以下性能数据可让您大致了解它的性能:http://jtds.sourceforge.net/benchTest.html

The following performance numbers give you a feel for how it might perform: http://jtds.sourceforge.net/benchTest.html

您可能会发现将数据转储为 mysql 转储文件格式并使用 mysql loaddata 而不是逐行写入的一些性能优势.如果您加载 infile 并执行诸如原子表交换之类的操作，MySQL 对于大型数据集有一些显着的性能改进.

You may find some performance advantages to dumping data to a mysql dumpfile format and using mysql loaddata instead of writing row by row. MySQL has some significant performance improvements for large data sets if you load infile's and doing things like atomic table swaps.

我们使用这样的东西快速将大型数据文件从一个系统加载到 mysql 到另一个系统，例如这是将数据加载到 mysql 中最快的机制.但是实时逐行可能是一个简单的循环，可以在 groovy + 一些表中执行，以跟踪已移动的行.

We use something like this to quickly load large datafiles into mysql from one system to another e.g. This is the fastest mechanism to load data into mysql. But real time row by row might be a simple loop to do in groovy + some table to keep track of what row had been moved.

mysql> select * from table into outfile 'tablename.dat';  

shell> myisamchk --keys-used=0 -rq '/data/mysql/schema_name/tablename'

mysql> load data infile 'tablename.dat' into table tablename;

shell> myisamchk -rq /data/mysql/schema_name/tablename

mysql> flush tables;
mysql> exit;

shell> rm tablename.dat

这篇关于SQL Server 到 MySQL 数据传输的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！