Running MySQL replication on a single machine

This is article 2 of 3 for a MySQL test strategy.

First up, from a redundancy point of view, you're unlikely to want to run a master and a replicated slave instance on the same machine.  If the machine goes down you probably lose both.  My reason for doing this is specifically for testing during development.

See article 1 for a guide to installing muliple MySQL instances on the same machine. We'll start with a 2 instances, a master and a slave.

1. Ensure master and slave instances have a different server-id set (see Setting the Replication Master Configuration and Setting the Replication Slave Configuration)
2. Enable binary logging on the master: log-bin=mysql-bin (see Setting the Replication Master Configuration)
3. As this is only a test instance the slave will use the root account to access the master access.  If this was a production system you'd want to create a replication account with more limited privileges (see Creating a User for Replication).
4. Flush and lock the master database:
   

       mysql> FLUSH TABLES WITH READ LOCK; 

5. Obtain the current log position information:
   

       mysql> SHOW MASTER STATUS;

6.  Take a dump of the master database (from another command window):
   

       > mysqldump -u root dbName > dbdump.sql

7. Unlock the master database:
   

       mysql> UNLOCK TABLES; 

8. Import dump into slave database (assuming your slave instance is running on port 3307):
       > mysql --port 3307 -u root dbName < dbdump.sql
9. Set up the replication link to the master:
   

       mysql> CHANGE MASTER TO MASTER_HOST='localhost',
       MASTER_USER='root',
       MASTER_PASSWORD='<password>',
       MASTER_LOG_FILE='as obtained from step 5',
       MASTER_LOG_POS=as obtained from step 5;

10. Start replication:
    

        mysql> START SLAVE;

11. Check status:
    

        mysql> SHOW SLAVE STATUS\G;