Reliable MySQL Using

Replication
Issac Goldstand
Mirimar Networks
www.mirimar.net
[email protected]
What is replication?
• Allows 2 or more databases to maintain state
between them
• MySQL 3.23 and up supports asynchronous
replication
• One server acts as a master for one or more
slaves
• Slaves pull data to be replicated from server’s
binary logs and execute the relevant statements
locally
Replication basics…
Slave
Master Slave
Slave
Slaves

• One master supports multiple slaves

• Each node on the network is assigned a unique
identifying number
• Each slave attempts to connect to the master and
fetches data to be replicated
Replication basics…
Client Master Slave

DATA DATA
INSERT
INTO …

• Clients perform data modification on master

server
• INSERT, SELECT, DELETE, LOAD DATA
• EXECUTE in MySQL 5.0 and above
Replication basics…
Client Master Slave

DATA
DATA

Binary INSERT
Log INTO …

• Immediately following execution of command on master,

the command is written to the local binary log
• Additionally, the master records its unique ID (to prevent
endless loops in circular replication scenarios) and the
timestamp for use with statements which use NOW(),
etc.
Replication basics…
Client Master Slave

DATA
DATA

INSERT Replication INSERT Relay

INTO … Thread INTO … Log

• If the slave is online, the command is transmitted to the slave in

parallel (well, immediately following) to being written in the local
binary log
• Otherwise, when the slave next connects it will receive a list of all
pending statements from the master server’s binary log
• The slave’s replication IO thread stores the command in the local
relay log
Replication basics…
Client Master Slave

DATA DATA
DATA

INSERT Replication INSERT Relay

INTO … Thread INTO … Log

• Once the data is received in the slave’s relay log, the

slave SQL thread executes the command locally,
bringing the slave up-to-date with the master
• In MySQL 3.23, the IO and SQL threads were just one
thread. In later versions this was changed to boost
performance
Replication Strategies
• Load balancing – single write, distributed read

Client (INSERT) Master

Slave Slave Slave

Client (SELECT) Client (SELECT) Client (SELECT) Client (SELECT) Client (SELECT) Client (SELECT)
Replication Strategies
• Load balancing – single write, distributed read
• Load balancing – circular read/write

Client MySQL MySQL

Client
Server Server
INSERT INSERT
INTO … INTO…

MySQL
Server
Replication Strategies
• Load balancing – single write, distributed read
• Load balancing – circular read/write
• High availability (hot failover)

Client Master Slave

DATA DATA

DATA DATA DATA

Replication Strategies
• Load balancing – single write, distributed read
• Load balancing – circular read/write
• High availability (hot failover)
• Snapshot backups
Master

Client Client
INSERT / SELECT INSERT / SELECT

Slave

Backup with LOCK TABLES (or single transaction)

Simple Replication Setup
• Modify my.cnf to include a unique server-id for
each node
• On master server, ensure that log-bin (binary
logging) is enabled in my.cnf
• On slave, configure login credentials on master,
either via my.cnf or CHANGE MASTER TO
statement
• Copy initial data snapshot from master to slave
• Configure initial binary log position on slave
• Start replication with SLAVE START command
 Configure master
my.cnf
-------------
[mysqld]
server-id = 1
log-bin
 Configure slave
my.cnf
-------------
[mysqld]
server-id = 2
master-user = someuser
master-password = secret
master-host = ip.of.master
Initial dataset
• Binary log provides a record of all modifications to
master database starting from a fixed point: when binary
logging was activated
• If all binary logs exist on master from initial install of
MySQL, the slave(s) can use these to bring themselves
up-to-date
• Otherwise, a snapshot of the master must be taken,
using mysqldump –master-data, to provide an initial
dataset for the slave(s)
• If only MyISAM tables are used, the LOAD DATA FROM
MASTER statement may be used on the slave(s)
Configure log position
MASTER mysql> SHOW MASTER STATUS;
+---------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------------+----------+--------------+------------------+
| vmware-mirimar-bin.000002 | 79 | | |
+---------------------------+----------+--------------+------------------+

SLAVE mysql> CHANGE MASTER TO MASTER_LOG_FILE=‘vmware-

mirimar-bin.000002’, MASTER_LOG_POS=79;
SLAVE mysql> START SLAVE;
Using CHANGE MASTER TO
• MASTER_HOST
• MASTER_USER
• MASTER_PASSWORD
• MASTER_LOG_FILE
• MASTER_LOG_POS
Advanced CHANGE MASTER TO
• MASTER_PORT
• RELAY_LOG_FILE
• RELAY_LOG_POS
• MASTER_SSL
• SSL: CA/CAPATH/CERT/KEY/CIPHER
Confirming it works
SLAVE mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: vmware-mirimar
Master_User: someuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: vmware-mirimar-bin.000002
Read_Master_Log_Pos: 79
Relay_Log_File: vmware1-mirimar-relay-bin.000002
Relay_Log_Pos: 250
Relay_Master_Log_File: vmware-mirimar-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 79
Relay_Log_Space: 250
Seconds_Behind_Master: 0
Permissions
• Slaves need REPLICATION SLAVE
permission on master for basic usage
• If LOAD TABLE FROM MASTER or LOAD
DATA FROM MASTER statements are
used, slave will also need SUPER and
RELOAD privileges
Internal Threads
• Since MySQL 4.0, replication slaves run
two threads
• IO thread continuously receives updates
from master and writes to local relay log
• SQL thread continuously executes
statements in relay log
IO thread isolation
• Isolating IO thread means that slave won’t
have to wait for long-executing statements
to finish executing before retrieving data
from master
• Also, slave will continue reading data from
master if a statement creates a data
conflict
SQL thread isolation
• SQL thread isolation allows for replication in an
environment without a continuous link between
slave and masters
• If master fails (or slave simply has no access),
the IO thread will try to reconnect endlessly
(waiting 60 seconds between attempts)
• SQL thread will continue processing relay logs
even while IO thread is unable to connect to
master
Master Thread
• Additionally, the master server runs the
Binlog Dump thread
• This thread is simply dedicated to
scanning the binary logs on the master
and sending updates to the connected
slave
• If this thread isn’t running, it means that
replication isn’t running – more accurately,
that no slaves are currently connected
Status files
• 2 status files for replication’s use
• Their use is to record the state of
replication between server shutdown and
startup
• master.info records information about the
slave’s master server
• relay-log.info records information about
the local relay logs
Information in master.info
• Master log file
• Read master log pos
• Master Host
• Master User
• Password (will not be shown in SHOW SLAVE
STATUS)
• Master Port
• Connect Retry
• In MySQL 4.1+, SSL options are stored if SSL is
used
Information in relay-log.info
• Relay log file
• Relay log pos
• Relay master-log pos
• Exec master-log pos
Backup master
• Master backups can be accomplished
with mysqldump
• Care must be taken to ensure the
following 2 special considerations:
1. Consistent snapshot of master date (via lock
tables for MyISAM or single transaction for
InnoDB)
2. Recording of binary log information, for use
on slaves (master-data)
Backup master files
• If a file-system level backup is required, care should be
taken to manually record binary log name and position
via SHOW MASTER STATUS statement.
• To ensure consistency between backup and binary log
position, the tables should be locked via FLUSH
TABLES WITH READ LOCK immediately before backup
(and SHOW MASTER STATUS)
• LEAVE THE CLIENT CONNECTED!!!
• After backup finishes, execute UNLOCK TABLES to
release the read lock
Backup slave
• Same idea as master file system backup
• Instead of recording position, it’s enough
to backup the master.info and relay-
log.info files
• Instead of acquiring global read lock, it’s
enough to STOP SLAVE before backup
and START SLAVE once backup finishes
Live demo
• Time permitting, we’ll show a short
demonstration of a simple unidirectional
replication setup
For more information
• MySQL documentation
• 5.0 documentation
http://mirror.mirimar.net/mysql/doc/refman/5.0/
• 4.1 documentation
http://mirror.mirimar.net/mysql/doc/refman/4.1/
Thank You!

For more information:

Issac Goldstand
[email protected]
http://www.beamartyr.net/
http://www.mirimar.net/