Simple Master-Slave Replication in MySQL

There's a lot of documentation to set up MySQL replication. The official MySQL documentation goes in to a lot of detail. It took me a while to get it running though. Here's a simple step by step guide for a very simple master-slave setup based on my experience. This was done Ubuntu 12. You might have to tweak the paths and shell commands for other Linux distros.

1. On both master and slave, allow remote access in /etc/mysql/my.cnf. Set

   bind-address = 0.0.0.0

2. Configure the slave in my.cnf

   server-id = 30 #unique id
   log-bin = /var/log/mysql/mysql-bin.log
   log-slave-updates
   replicate-same-server-id = 0
   auto_increment_increment = 10
   auto_increment_offset = 2 # unique offset
   report-host = let # slave host name
   

3. Restart mysql (shell)

   restart mysql

   or (depending on how mysql has been set up)

   service mysql.server restart

4. Create a slave user on the master (in mysql):

   CREATE USER 'rep_slave'@'SLAVE IP ADDRESS' IDENTIFIED BY 'replication_password';

5. Allow replication for slave user (in mysql):

   grant replication slave on *.* to 'rep_slave'@'SLAVE IP ADDRESS';

6. If data needs to be imported from the master, import it using mysqldump
   
   
7. On the Master MySQL get the Log File Name & Log Position (in mysql)

     FLUSH TABLES WITH READ LOCK;
     SHOW MASTER STATUS;
   

8. On the slave (in mysql)

    CHANGE MASTER TO
      MASTER_HOST='master_host_name/IP address',
      MASTER_USER='rep_slave',
      MASTER_PASSWORD='replication_password',
      MASTER_LOG_FILE='recorded_log_file_name',
      MASTER_LOG_POS=recorded_log_position;
   
   SHOW SLAVE STATUS \G
   
   START SLAVE
   

9. On the Master (in mysql)

   UNLOCK TABLES