MySQL Replication

MySQL Replication Workflow

  1. When a slave resume replication, it connects to the master DB
  2. Slave informs the master on the last replication coordinates (the last binary file name and the position within the file)
  3. The master send all events (updates) after the last replication coordinates to the slave
  4. The I/O thread in the slave read the events and write it to a relay log (relay-bin.nnnnnn)
  5. The SQL thread in the slave read an event in the relay log and execute it
  6. The master continues sending all new events to the slave
  7. If no more events, the slave will pause and wait for new events

* Master and slave can have different storage engine

Replication Requirement

  • Both master and slave should run the same version of MySQL

MySQL Replication Configuration

  1. On the master DB instance, add replication information under [mysqld] group in the Option file c:\mysql\mysql-5.1\my.ini
    # Replace a_unique_integer with a number range from 1 to 2^32-1
    # a_unique_integer must be unique among all servers include master and slave(s)
    # Enable binary logging (log-bin)
    [mysqld]
    server-id=a_unique_integer
    log-bin=log-bin
    
  2. For each slave DB instance(s), add replication information under [mysqld] group in the Option file c:\mysql\mysql-5.1\my.ini
    # Replace a_unique_integer with a number range from 1 to 2^32-1
    # a_unique_integer must be unique among all servers include master and slave(s)
    # Do not replicate mysql DB
    [mysqld]
    server-id=a_unique_integer
    replicate-ignore-db=mysql
    
  3. Restart both master and slave
  4. Create a user account on the master for replication
    mysql> CREATE USER 'a_slave_user'@'slave_host' IDENTIFIED BY 'somepassword';
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'a_slave_user'@'slave_host';
    
  5. In the master, stop any DB changes. (Use the tables locking option in MySQL Operation)
  6. In the master, login with an administrator account and flush the tables
    mysql> FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
  7. Do not exit the SQL session so the READ LOCK prevent other sessions to write
  8. Mark down the File name and Position value (current binary log coordinates) in SHOW MASTER STATUS
  9. Copy the DB from the master to slave. (Instructions in MySQL Maintenance, Backup & Recovery)

    IMPORTANT: Do NOT copy the mysql DB since mysql DB contains user accounts. Master and slave should maintain different user accounts.
  10. Unlock the tables in the same session locking the table (on the master)
    mysql> UNLOCK TABLES
  11. In the slave DB instance, configure the replication with the user account, File & position value done in previous step. master_host is the hostname of the master. Use 127.0.0.1 if it is on the same machine.
    mysql> CHANGE MASTER TO
      MASTER_HOST = 'master_host',
      MASTER_USER = 'a_slave_user',
      MASTER_PASSWORD = 'somepassword',
      MASTER_LOG_FILE = 'file_name_in_previous_step',
      MASTER_LOG_POS = file_position_in_previous_step;
    

    Add MASTER_PORT if the master server does not listen at the default port

  12. In the slave DB instance
    mysql> START SLAVE
    
  13. Check status
    mysql> SHOW SLAVE STATUS
    

Stop/Re-start MySQL Replication

mysql> STOP SLAVE;
mysql> START SLAVE;

Stop reading replication events from master. But SQL thread will continue finish events in the relay log.

mysql> STOP SLAVE IO_THREAD;

Continue reading replication events from master and save it to relay logs. But the SQL thread will not process events in the relay logs.

mysql> STOP SLAVE SQL_THREAD;

Using MySQL Slave for DB Backup

  1. Stop data replication
    mysql> STOP SLAVE SQL_THREAD
    
  2. Perform any logical backup in MySQL Maintenance, Backup & Recovery, or
  3. Perform a DB Files backup MySQL Maintenance, Backup & Recovery
  4. Restart data replication
    mysql> START SLAVE
    

Adding More Slave

  1. Shutdown an existing slave
  2. Copy the data directory to the new slave
  3. Copy master.info and relay-log.info to the new slave
  4. Re-start the existing slave
  5. On the new slave, give the new slave a new unique server-id
  6. Start the new slave

Other commands

Show what replication format used; Mixed, statement based or row based

SHOW VARIABLES LIKE 'binlog_format';