MySQL Replication

For any DBA with a long history of Sybase, Oracle or SQL Server, MySQL really isn't anything new or complicated, but with ver. 5 and above, there's a lot of interesting tuning involved and places where you can fall on your face. One area is replication. It's quite different and not as mature as Sybase, Oracle or MS SQL Server, but pretty cool and easy in a lot of ways. Once again, lots of gotchas though. 

First and foremost - prepare. Chances are you are being dragged in for setting up a replicant of an existing master DB. Be sure to get the user, password, full hostname (or IP), port, relay log name and location and position (via `show master status;` command). This data will end up in your master.info file in the <mysql> home directory that is running (assuming you have mysql installed). The "position" data is only useful if there is no activity and transactions have been flushed. Enter this data either by using the `change master to....` command with all parameters listed or edit a file in the home of your mysql database called master.info.

Now, it's time to bring your slave server up to speed. The easiest way is to dump the master using a `mysqldump` for the DB's you want to replicate and load them into the slave using `mysql < dumpfile.sql`. Once you have that and you recorded your position using the `change master to` command or source file, you should check the params of your main configuration file "/etc/my.cnf" and make sure you allot for huge packet size by setting the max_packetsize_allowed to an insane number like 128 MB. If your network is shaky, set your retry and connection intervals high as it will go down when errors of this sort kick in, and they will!

Now with everything in order, run a `start slave;` and see what happens, then run a `show slave status;` to see if the event process is even looking at the master and if the connection is there. Pay close attention to network settings because that is where you will get burned. Second place is poorly formed indexes and keys as well as dreaded data corruption.  

Contact: daquijne@josephdaqui.com (copyright 2010)