Replicating data from SQL Server to MySQL

Ok not really “Replication”. More like keeping a MySQL table in sync with a SQL Server table using both varieties of SQL.

I avoided using a linked-server as I wanted this to be able to cope with bulk loading. Sadly the correct tool, SSIS, was not available during this project.

I created a SQL Job with two steps 1) Export data to CSV and 2) Import into MySQL. The SQL code is highly parameterised so it can be reused.

Job Step 1: Export data to a CSV file

/* ExportDataToCsvFile.sql */

	DECLARE @datasource VARCHAR(100) = 'SomeTableName';
	SELECT @cmd = 'BCP SomeDatabase.dbo.' + @datasource + ' out D:\Export\' + @datasource + '.csv -t, -c -T';
	EXEC master..xp_cmdshell @cmd;

Line 1: Is just to let me know I have my own copy of this code block.
Line 3: Should be updated to the data source specific to each project (NOTE: For simplicity the data-source and CSV file both share this name.)

Job Step 2: Import CSV into MySQL table

/* ImportCsvIntoMYSqlTable.sql */

	DECLARE @table VARCHAR(100) = 'SomeTable'; /* << change this */
	DECLARE @database VARCHAR(100) = 'SomeInstance_SomeDatabase'; /* << change this */
	DECLARE @sql VARCHAR(2000) = '';
	DECLARE @cmd VARCHAR(8000);

/* 1 Build MySQL script to empty then refill table */

	SET @sql = @sql + 'START TRANSACTION;';
	SET @sql = @sql + 'DELETE FROM ' + @table + ';';
	SET @sql = @sql + 'LOAD DATA LOCAL INFILE ''G:\\Export\\' + @table + '.csv'' INTO TABLE ' + @table
	SET @sql = @sql + ' FIELDS TERMINATED BY '','' ENCLOSED BY ''\"'' LINES TERMINATED BY ''\r\n'';';
	SET @sql = @sql + 'COMMIT;';

/* 2 Execute it */

	SET @cmd = 'G:\Export\MySql\bin\mysql.exe --defaults-extra-file=G:\Export\MySql\'' + @database + '.cnf -e "' + @sql + '";';
	EXEC @IsError = master..xp_cmdshell @cmd;
	IF @IsError <> 0 RAISERROR('INFILE Error', 16, 1);

/* 3 Defragment table and Update stats */

	SET @sql = 'OPTIMIZE TABLE ' + @table + ';';
	SET @cmd = 'G:\Export\MySql\bin\mysql.exe --defaults-extra-file=G:\Export\MySql\' + @database + '.cnf -e "' + @sql + '";';
	EXEC @IsError = master..xp_cmdshell @cmd;
	IF @IsError <> 0 RAISERROR('OPTIMIZE Error', 16, 1);

Lines 3: Will need to be changed for each project. And names both the CSV file and target MySQL table.

Line 4: Is a previously created file used by MySQL to connect to a target instance & database.

Lines 12 to 16: Builds up a string of MySQL commands to empty then refill the table from the CSV file.

Lines 12 & 16: These two lines create a single transaction. This serves two purposes. 1) The ‘old data’ will not be deleted if the ‘new data’ load fails. 2) The switch-over from ‘old data’ to ‘new data’ will be instant.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s