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.
-
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’;
- 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][2]
[MySQL Replication Architecture][3]