Replicating MSSQL to MySQL

ok well its not really “Replication”, its keeping a MySQL table in sync with MSSQL using a SQL Job.

Whilst great for small, frequent, data updates, this method (using a linked-server) is not suitable for bulk data movement, where SSIS, BCP, or SQLCDM would be much faster.

Job Step 1 Remove old data from MySQL

USE SomeDatabase;
GO

DELETE FROM OPENQUERY
            (SomeMySQLInstance,
             'SELECT ID, Code, Comments FROM SomeMySqlTable'
            )
WHERE ID NOT IN
      (
          SELECT ID
          FROM [SomeServer].[dbo].[SomeView]
      );

PRINT 'Rows Deleted = ' + CONVERT(varchar(10), @@rowcount)

Job Step 2 Copy new data to MySQL

USE SomeDatabase;
GO

INSERT OPENQUERY
       (SomeMySqlInstance,
        'SELECT ID, Code, Comments FROM SomeMySqlTable'
       )
SELECT *
FROM [SomeDatabase].[dbo].[SomeView]
WHERE ID NOT IN
      (
          SELECT ID FROM SomeMySqlInstance...SomeMySqlTable
      );

PRINT 'Rows Inserted = ' + CONVERT(varchar(10), @@rowcount)

Just to decode that a bit …

  • Step2 Line12:”SomeMySqlInstance…SomeMySqlTable” refers to a Linked Server connection (called “SomeMySqlInstance”), where the default database is the one we want (containing the table “SomeMySqlTable”).
  • Lines 11 & 9: “[SomeView]” is the source of the current data that the MySQL table needs to be synced with. It is just a shortcut way to not need to save a stored-proc output to a table for consumption by OPENQUERY. Note that the view needs a unique ID column.
  • The PRINT statements are just for the jobs history.

Configuring MySQL replication

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).

server-id=2
master-host=169.254.0.17
master-user=repuser
master-password=password

I then confirmed all other conflicting settings were commented out (EG: server-id=1) and sdaved the file.

MySQL monitoring tools

“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.

Testing Apache, PHP, and MySQL.

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

<?php
@mysql_connect(“localhost”,”root”,”[password]”);
$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&#8217;.