Transfer data from SQL Server to MySQL


By:   |   Updated: 2020-08-14   |   Comments (2)   |   Related: More > Import and Export


Problem

In companies where there are multiple database environments (Oracle, MySQL, SQL Server, etc.), there are often requirements to exchange data between different environments. We recently had such a requirement to move data from SQL Server to MySQL, is there a good way to accomplish this data transfer?

Solution

To move data between SQL Server and other databases, one common solution is to use SQL Server Integration Services (SSIS), which is a free product to licensed SQL Server users, but the SSIS option has some short comings, such as it is usually complex to setup, configure and deploy, and more difficult to debug and troubleshoot.

A simpler way is to use PowerShell by taking advantage of MySQLs .NET library. The current version is "Connector/NET 8.0.21".

Current downlaod from mysql download website

Download and install this latest version. On my computer I have it installed on my D:\ folder.

location

The critical DLL file in this folder is MySql.Data.dll, and we will use it to perform bulk loading. The class used for bulk loading is MySqlBulkLoader class and we will use MySqlCommand as well.

We will first create a test table and some data. We also assume the SQL Server BCP utility is installed.

In SQL Server (my version is SQL Server 2016 as shown below), we first create a test table with a few records.

SQLServer version
use mssqltips;
go

drop table if exists dbo.Test;
create table dbo.Test(id int, c varchar(30), d date);
go

insert into dbo.Test(id, c, d)
values(1, 'hello', '2020-01-01'),(2, 'world', '2020-01-02')
,(3, 'good', '2020-01-03'),(4, 'morning', '2020-01-04');

We then create the same table on MySQL (my version is 5.6.10).

MySQL version
use mssqltips;
create table `Test` (id int, c varchar(30), d date);

PowerShell Script to Move Data from SQL Server to MySQL

We will use two methods to do the data loading. First, is the MySql.Data.MySqlClient.MySqlBulkLoader class. Second, is to use MySQL "load data local infile" statement run by the MySql.Data.MySqlClient.MySqlCommand class.

For both methods, the algorithm is the same:

  1. Export data from the SQL Server table to a csv file via the BCP utility.
  2. Import data from csv file into the MySQL table using one of the two methods mentioned above.

So, let’s prepare the PowerShell script to do the work.

If we open a PowerShell ISE (my ISE is installed with PowerShell V5.1) window then copy and paste the following code into the window:

# Load Data from SQL Server to MySQL via MySQLBulkLoader class
add-type -Path "D:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.21\Assemblies\v4.5.2\MySql.Data.dll";
#uncomment the following line to calculate the time used to do the loading
#$start = get-date; 
 
$sql_instance = 'InsightR17DBDEV'; #assume we use windows authentication
$sql_db = 'MSSqlTips'; # note: your db system may be case sensitive
$csv_file = "c:\temp\mssql_test.csv"
$mysql_instance = '<mysql_server_name>';
$mysql_user = '<mysql_user, such as root>';
$mysql_pwd = '<password to connect to mysql>';
bcp dbo.test out $csv_file -S $sql_instance -d dba -t"," -T -c; #export sql table to a csv file
 
$conn_str="server=$mysql_instance;user=$mysql_user;password=$mysql_pwd;database=$sql_db;port=3306;AllowLoadLocalInfile=true";
$mysql_conn = new-object MySql.Data.MySqlClient.mysqlconnection($conn_str);
$mysql_conn.Open();
$bulk = New-Object Mysql.data.MySqlClient.MySqlBulkLoader($mysql_conn);
 
$bulk.Local=$true; # this is important setting
$bulk.FileName = $csv_file;
$bulk.TableName = 'Test'; #destination table on MySQL side
$bulk.FieldTerminator=',';
$bulk.NumberOfLinesToSkip=0;
$bulk.LineTerminator="`n";
#$bulk.FieldQuotationCharacter = '"';
#$bulk.FieldQuotationOptional = $false;
$bulk.Load() | out-null # without out-null, it will show # of rows inserted
<# uncomment to calculate the time taken
#calculate the time used 
write-host "Total time for method with [mysqlbulkloader] is" -ForegroundColor green;
(get-date)-$start
#>
$mysql_conn.Close(); 

After running the script, I can see the following result:

bulk load method 1

The following script uses the MySQL sqlcommand class together with "load data local infile" statement.

# Load Data from SQL Server to MySQL via MySQLCommand class
add-type -Path "D:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.21\Assemblies\v4.5.2\MySql.Data.dll";
#uncomment the following line to calculate the time used to do the loading
#$start = get-date; 
 
$sql_instance = 'InsightR17DBDEV'; #assume we use windows authentication
$sql_db = 'MSSqlTips'; # note: your db system may be case sensitive
$csv_file = "c:\temp\mssql_test.csv";
$csv_file2 = "c:\\temp\\mssql_test.csv"
$mysql_instance = '<mysql_server_name>';
$mysql_user = '<mysql_user, such as root>';
$mysql_pwd = '<password to connect to mysql>';
bcp dbo.test out $csv_file -S $sql_instance -d dba -t"," -T -c; #export sql table to a csv file
 
$conn_str="server=$mysql_instance;user=$mysql_user;password=$mysql_pwd;database=$sql_db;port=3306;AllowLoadLocalInfile=true";
$mysql_conn = new-object MySql.Data.MySqlClient.mysqlconnection($conn_str);
$cmd = New-Object Mysql.data.MySqlClient.Mysqlcommand;
$cmd.CommandText = "load data local infile '$csv_file2' into table Test fields terminated by ',' enclosed by '' escaped by '\\' lines terminated by '\r\n';"
 
$cmd.Connection = $mysql_conn;
$mysql_conn.Open();
$cmd.ExecuteNonQuery();
<# uncomment to calculate the time taken
#calculate the time used 
write-host "Total time for method with [mysqlcommand] is" -ForegroundColor green;
(get-date)-$start
#>
$mysql_conn.Close(); 

Running this script, I get the following result:

load copy method 2

Now, we successfully bulk load data from a SQL Server table to a MySQL table.

From MySQL, I can see the four records inserted into the test table.

results

Performance Test

A natural question is which method is better in performance? So, I prepared 1 million records for the test.

Here is the script to run on SQL Server to generate 1 million rows.

use  mssqltips;
go
; with t1 as (
select 1 as c union all select 1 as c)
, t2 as (
select t1.c from t1 inner join t1 t on t1.c = t.c)
, t3 as (
select t2.c from t2 inner join t2 t on t2.c = t.c)
, t4 as (
select t3.c from t3 inner join t3 t on t3.c = t.c)
, t5 as (
select t4.c from t4 inner join t4 t on t4.c = t.c)
, t6 as (
select t5.c from t5 inner join t3 t on t5.c = t.c)
, t7 as (select rnk = row_number() over (order by c) from t6) 
 
insert into dbo.test (id, c, d)
select rnk , 'abc'+cast(rand(rnk)*100000 as varchar(12)), dateadd(day,  ceiling(rand(rnk)*1000), getdate())
from t7;
-- 1,048,576 inserted + the previous 4 records=total 1,048,580 records in sql server table

I then run the two PowerShell scripts multiple times (of course truncating the table on MySQL before each run), and the time needed is almost the same, with the method of using MySql.Data.MySqlClient.MySqlBulkLoader class a tiny bit better.

Here are the results:

lading speed using MySQLBulkLoader
loading speed using mysqlcommand

Using MySQLCommand class is about 184 milliseconds slower than using MySQLBulkLoader class, and such difference is negligible.

Summary

In this tip, we discussed how to bulk load data from SQL Server to MySQL using PowerShell and the official MySQL Connector/Net data provider. We used two different methods; one is the MySQLBulkLoader class and the other is the MySQLCommand class. The two methods are almost identical in performance.

By using PowerShell, we can avoid the typical SSIS solution to do the same work. An SSIS package is what you see is what you design, it is good for explanation and presentation, while the PowerShell script is what you see is what you run, and is good for debugging and troubleshooting, especially once deployed.

Next Steps

The scripts provided in the tip can be further improved to be an advanced function (like a cmdlet) and thus included in your own customized PowerShell module.

There are quite a few tips on MSSQLTips.com that talk about MySQL and SQL Server, please review the following tips for more information:



Last Updated: 2020-08-14


get scripts

next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips





Comments For This Article




Friday, September 04, 2020 - 4:42:12 AM - Frigate Back To Top (86413)
Odbc connection and linked server?

Thursday, September 03, 2020 - 11:15:56 PM - Venkataraman R Back To Top (86412)
I think there is a typo in the million rows generation.

, t6 as (
select t5.c from t5 inner join t3 t on t5.c = t.c)

It should be
, t6 as (
select t5.c from t5 inner join t5 t on t5.c = t.c)


download





Recommended Reading

Simple way to export SQL Server data to Text Files

Using OPENROWSET to read large files into SQL Server

Simple Image Import and Export Using T-SQL for SQL Server

How to Copy a Table in SQL Server to Another Database

Bulk Insert Data into SQL Server








get free sql tips
agree to terms