Wednesday, October 30, 2013

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