One of the most asked questions is how to setup MySQL replication. The MySQL Reference Manual provides a good Replication How To as a starting guide on MySQL Replication 101.

MySQL replication has many uses including read scalability, backups, failover, online maintenance, upgrade testing and verification, software upgrades, alternative data or structure for performance queries and even benchmarking to name the popular uses.

When reviewing an installation of MySQL replication I use the following as part of my checklist of steps used for confirming your replication environment is operational.

Master Variables

• server-id – Replication will not work without this correctly set and unique
• log-bin – Pre-requisite for working replication
• log-bin-index
• max_binlog_size
• binlog_cache_size
• expire_logs_days – a value from 5 to 10 is good, not set can result in a full disk.
• binlog-do-db/binlog-ignore-db – Use with caution
• sync_binlog
• innodb_support_xa

Slave Variables

• server-id – Replication will not work without this correctly set and unique
• read_only = TRUE
• log-bin – may or may not be present
• relay-log
• relay-log-index
• max_binlog_size
• binlog_cache_size
• expire_logs_days – a value from 5 to 10 is good, not set can result in a full disk.
• replicate-do-???? – Warning, use these with caution. Your slave will not be the same as your master.
• slave-skip-errors – Warning, this can lead to your slave being inconsistent with your slave.

On the Master I audit the following information.

• SHOW MASTER STATUS
  • If any Binlog_Do_DB, then a SHOW SCHEMAS for verification
• SHOW MASTER LOGS
  • Confirm physical files as well as available diskspace on log-bin disk partition
• SHOW SLAVE STATUS (in a true master/slave environment this should be empty)
• SHOW GLOBAL VARIABLES LIKE ‘binlog_cache_size';
• SHOW GLOBAL STATUS LIKE ‘Binlog%’
• SELECT host,user,password FROM mysql.user WHERE Repl_slave_priv=’Y’ AND Super_priv=’N';

On the Slave I audit the following information.

• SHOW SLAVE STATUS
• SHOW MASTER STATUS – This will determine if you have log-bin enabled on the slave

The key information for MySQL slaves is in the SHOW SLAVE STATUS command. An example output is:

mysql> show slave statusG
*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: 10.10.1.1
                Master_User: slave
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: bin-log.001817
        Read_Master_Log_Pos: 369684547
             Relay_Log_File: relay-log.000449
              Relay_Log_Pos: 42347742
      Relay_Master_Log_File: bin-log.001817
           Slave_IO_Running: No
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 369684547
            Relay_Log_Space: 42347742
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

It is important that you learn and understand these values. In this above case, replication is NOT running as indicated by Slave_IO_Running and Slave_SQL_Running.

This information is just an introduction as to what to look at. In my next lesson, I’ll spend more detail of the output of the various commands, as well as describe in greater detail the relationship of underlying files that are important for a working MySQL Replication environment.

Other References

Verifying MySQL Replication in Action
MySQL Replication Architecture