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';
DECLARE @cmd VARCHAR(400);
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);
DECLARE @IsError INT;
/* 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.
Database Consultant 