MySQL Replication 102

Avatar Category: Database | 177 days ago |
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

Comments
Any coments? Contact us.