Siusic Dot Com

I was very surprised when I found MySQL had replication feature. I was assigned to a task that needed to set up MySQL replication and I thought its replication would be similar to the replication of MS SQL server at the beginning. I was quite confused at the time. Later I found even though both MySQL and

SQL server call it replication they are in fact totally different animal. MySQL replication is more like log shipping of SQL server. We should use replication for MySQL the same way as we use log shipping for MS SQL server. The primary use of log shipping is to have a stand by server (secondary server) just in case the primary server crashes. It is the same for MySQL server but MySQL calls it master and slave instead of primary and secondary.

Their mechanisms are very similar too. Log shipping of MS SQL server, like what its name implies, is a process of shipping the log for the primary server to the secondary server and applying the log to the secondary server so to bring it update to date. The process can be further divided into backup, copy and restore. Backup is a log backup operation on the primary server. The secondary server will copy the log backup over and restore it. MySQL replication works alike but MySQL doesn’t have log backup process like SQL server. In MySQL the slave will use a thread to connect to the master server read its binary log and store the transaction it reads to a local file called relay log. Another thread will read the relay-log and execute the SQL statement it finds on the slave.

The transactional replication feature of MS SQL is similar to MySQL replication in that it also relies on transaction log (called binary log in MySQL). But usually it is impractical to define transactional replication on all tables of a database. Generally MS SQL replication is defined on a small set of tables and it allows you to have a lot more control on what you want to replicate.

Related Posts:

Error Reading Packet From Server Error Reading Log Entry Server_Errno=1236

MySQL Bug Errno 12 When Connecting MySQL Server Has Been Confirmed Fixed

MySQL Update Statement

MySQL Was Acquired By Sun For 1 Billion

Link MySQL Server Into MSSQL Server