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.
There are many ways to copy (or move) a MySQL database (aka schema) from one server (aka instance) to another (including the data).
On this occasion I used the Export and Import utilities within “MySQL Workbench” (like doing a backup and restore). The fact that the source and target instances were both hosted on GCP was irrelevant (no brackets required!).
1. Connect to Source and start the Data Export utility …
2.Within the utility, I left the defaults as they were, apart from …
- TIC the schema I wanted to export (see screenshot)
- Select “Export to Self-Contained File” as I wanted all the tables
- and create a meaningful dump-file name
3. Because the Export and Import utilities would be using different logins, I clicked “Advanced Options” within the Export utility, and typed “OFF” over the top of the default “AUTO” setting for set-gtid-purged …
…before clicking the “Return” then “Start Export” back on the main page.
4. To keep it simple, I closed and reopened MySQL Workbench before connecting to the Target instance. Then from the Server menu I chose Data Import …
5. I left all the defaults as they were except …
- I chose “Import from Self-Contained File”
- and navigated to the dump-file
- I clicked “New” and typed the schema name that would receive the import.
6. Finally. On the “Import Progress” page I clicked “Start Import”. Then waited about five minutes before anything seemed to happen
Avoid 3rd Party management software. Backup software doubly so.
This is a record of the steps I performed to enable replication between MySQL5.0.77 on RHEL and 5.5.25 on WindowsXP.
1. ensure two-way connectivity with pings.
2. make mysql on windows easier to work with by copying mysql.exe and mysqldump.exe to c:windowssystem32.
3. On the Replication-Master (RHEL) I made a copy of /etc/my.cnf then amended the original using VI adding the lines
“log-bin=mysql-bin” and “server-id=1” within the [mysqld] section.
I rebooted and tested that binary-logging was ‘on’ with the command “show variables like ‘log_bin'”.
4. On the WindowsXP / 5.5 / Slave machine I copied then amended the file “c:program filesmysqlmysql server 5.5my-large.ini” (note: I chose ~large.ini as my machine had 512mb memory).
I then confirmed all other conflicting settings were commented out (EG: server-id=1) and sdaved the file.
“AjaxMyTop” is a really useful MySQL monitoring tool.
If you’re on a web-server, and therefore have apache and mysql readily available you can easily implement a live web-page version of the Unix ‘top’ command.
– Download and unpack the ‘ajaxMyTop’ folder into htdocs.
– Configure the config.php file with user (root?), and password
– Then browse to http://localhost/ajaxMyTop/ and wait.
After a few seconds you should at-the-least see a line for ‘show processlist’ generated by the tool.
Here’s the simplest test-page I can think of that will test …
– the supplied credentials
– PHP can connect to MySQL
– Apache can display the results
$result = mysql_query(“show databases”);
echo “<h1>Databases this user can connect to …</h1>”;
while ($row=mysql_fetch_array($result)) echo($row[“Database”].”<br />”);
Paste it into a file called ‘test.php’ (with the correct password) in Apache’s ‘htdocs’ folder, then browse ‘http://localhost/test.php’.