So you edited the slave.......

When syncronising 2 databases via replication one thing you do not want to do is touch the slave in any way. The system is clever you see, it knows where its up to and really doesn't expect a helping hand. Unfortunatly this afternoon I went to edit my little utility meter reading database, a very simple and efficient little system, I just needed to add a column but I chose the slave by mistake. In the immortal words of homer simpson - Doh.

At this point everything falls apart, the slave process has a fit because it is trying to add a column which is already there and stops taking orders from the master. So at this point if you are also a bit of a dunce with 2 similarly named databases you need to follow these instructions to get your little donkeys back on the straight and narrow.

1. Stop your slave with 'slave stop'
2. Back up your master
3. Restore it onto your slave
4. On the master run the 'show master staus command' to get your log file and position
5. On the slave run the following rather longer command.

mysql> CHANGE MASTER TO
   ->     MASTER_HOST='master_host_name',
   ->     MASTER_USER='replication_user_name',
   ->     MASTER_PASSWORD='replication_password',
   ->     MASTER_LOG_FILE='recorded_log_file_name',
   ->     MASTER_LOG_POS=recorded_log_position;

6. On the slave restart the slave process with 'slave start';
7. Check its all OK again with 'show slave status\G'
8. Make a nice cup of tea and congratulate yourself on getting your codling's out of the fire :o)

More from the rack next week as I am getting educated tomorrow and Friday, brushing up on my SQL skills and seeing if Microsofts SQL server 2005 has anything to offer. Without being cynical however given the fact that Microsoft's licensing model is getting more and more complicated and expensive they better have something really good up their sleeves to regain my attention given how positive my recent MYSQL experiences have been.