replication 101
Post on 17-May-2015
1.880 Views
Preview:
DESCRIPTION
TRANSCRIPT
©Continuent 2012.
MySQL Replication 101Giuseppe MaxiaContinuent, Inc
1Tuesday, April 10, 12
about me - Giuseppe Maxia• a.k.a. The Data Charmer• QA Director at Continuent, Inc• Long time hacking with MySQL features• Formerly, community manager,db consultant, designer,
coder.• A passion for QA and open source• Blogger
•http://datacharmer.blogspot.com
2Tuesday, April 10, 12
3
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
3Tuesday, April 10, 12
4
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
4Tuesday, April 10, 12
The world today is dominated by the web economy
www$$$
5
5Tuesday, April 10, 12
Databases are the backbone
of the web economy
www$$$
6
6Tuesday, April 10, 12
What database for the web?
7
7Tuesday, April 10, 12
What database for the web?
1 Copyright © 2011, Oracle and/or its affiliates. All rights
reserved.
Insert Information Protection Policy Classification from Slide 8
The most powerful database
7
7Tuesday, April 10, 12
What database for the web?
1 Copyright © 2011, Oracle and/or its affiliates. All rights
reserved.
Insert Information Protection Policy Classification from Slide 8
The most powerful database
The most advanced open source database
7
7Tuesday, April 10, 12
What database for the web?
1 Copyright © 2011, Oracle and/or its affiliates. All rights
reserved.
Insert Information Protection Policy Classification from Slide 8
The most powerful database
The most advanced open source database
The most deployed open source database
7
7Tuesday, April 10, 12
What database for the web?
1 Copyright © 2011, Oracle and/or its affiliates. All rights
reserved.
Insert Information Protection Policy Classification from Slide 8
The most powerful database
The most advanced open source database
The most deployed open source database
The most popular open source database
7
7Tuesday, April 10, 12
Actually, MySQL databases are the
backbone of the web economy
www$$$
8
8Tuesday, April 10, 12
What database for the web?
1 Copyright © 2011, Oracle and/or its affiliates. All rights
reserved.
Insert Information Protection Policy Classification from Slide 8
No built-in replication
No built-in replication
No built-in replication
Built-in replication
9
✔
✗✗✗
9Tuesday, April 10, 12
More precisely, MySQL REPLICATION is the backbone of the web
economy
www$$$
10
10Tuesday, April 10, 12
11
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
11Tuesday, April 10, 12
a simple web application
scheme
12
database server
web server
clients
r/w requests
12Tuesday, April 10, 12
database server
web servers
load balancer
clients
r/w requests
scaling web requests
1313Tuesday, April 10, 12
writeread
database load on a simple
web application
14
85% 15%
14Tuesday, April 10, 12
readwrite
database load on a successful web
application15
20% 80%
15Tuesday, April 10, 12
16
database server
web servers
load balancer
clients
r/w requests✘
scaling up means buying
a bigger database
server
16Tuesday, April 10, 12
17
readwrite
the bigger database server will eventually
have the same problem
80%20%
17Tuesday, April 10, 12
read/writemaster
read/onlyslaves
web servers
R/W
R/O
load balancer
load balancer
clients
a web application
scheme with replication
1818Tuesday, April 10, 12
read/writemaster
read/onlyslaves
readwriteread
database load with
replication19
85% 15% 100%
19Tuesday, April 10, 12
read/writemaster
read/onlyslaves
readwriteread
scaling database load
with replication
20
85% 15% 100%
20Tuesday, April 10, 12
Replication assessment
21
without replication with replication
database handling
performance
Point in Time recovery
failover
write scaling
backup
read scaling
easy harder
high lower (binary logs)
none easy
none possible
none minimal
with downtime without downtime
none easy
21Tuesday, April 10, 12
22
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
22Tuesday, April 10, 12
master
slaveIO thread
SQL thread
reads
reads
client
transaction
binary log
relay log replication concepts
2323Tuesday, April 10, 12
24
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
24Tuesday, April 10, 12
25
1 SHUT DOWN THE DATABASE SERVER
Master
25Tuesday, April 10, 12
26
2 MAKE A BACKUP COPY
Master
26Tuesday, April 10, 12
27
3 ENABLE THE MASTER
Configuration file[mysqld]log-bin=mysql-binserver-id=1
Master
27Tuesday, April 10, 12
28
4 RESTART THE MASTER
Master
28Tuesday, April 10, 12
29
5 CREATE REPLICATION USER
SQL commandGRANT REPLICATION SLAVE ON *.* to 'slave_user@'10.10.100.%' IDENTIFIED BY 'slave_pass';
Master
29Tuesday, April 10, 12
30
6 INSTALL MySQL on the slave
Slave 1
Make sure that:• You're using the same version of MySQL • You have the same directory structure• The server is not started yet
30Tuesday, April 10, 12
31
7 COPY THE MASTER DATA to the slave
Slave 1
31Tuesday, April 10, 12
32
8 ENABLE THE SLAVE
Configuration file[mysqld]server-id=2relay-log=mysql-relayread-only# optional:log-bin=mysql-bin
Slave 1
32Tuesday, April 10, 12
33
9 START THE SLAVE SERVER
Slave 1
33Tuesday, April 10, 12
34
10 INITIALIZE THE SLAVE
SQL commandSET MASTER TOMASTER_HOST=master_IP,MASTER_PORT=3306,MASTER_USER=slave_user,MASTER_PASSWORD='slave_pwd';
Slave 1
34Tuesday, April 10, 12
35
11 START THE SLAVE SERVICE
SQL commandSTART SLAVE;
Slave 1
35Tuesday, April 10, 12
36
12 CHECK THE SLAVE
SQL commandSHOW SLAVE STATUS \G... Slave_IO_Running: YesSlave_SQL_Running: Yes...
Slave 1
36Tuesday, April 10, 12
Troubleshooting
• SHOW SLAVE STATUS says SLAVE_IO_RUNNING=No
• Make sure that the slave host can connect to the master
• Make sure that master and slave have different Server-id
• Check the error log of both master and slave
37
37Tuesday, April 10, 12
Testing the slave
• Create a table in the master.
• Make sure that the slave has replicated the table.
• Insert data in the master
• read that data in the slave
38
38Tuesday, April 10, 12
What if the master was already logging?
• You have two options:
• Physical copy
• Logical copy
39
39Tuesday, April 10, 12
Physical copy
• stop master
• make copy
• remove binary log files and index
• start master
• (alternative: use xtrabackup)
40
40Tuesday, April 10, 12
Logical copy
• mysqldump --all-databases --master-data
41
41Tuesday, April 10, 12
Common replication commands • CHANGE MASTER TO
• SHOW MASTER STATUS
• SHOW SLAVE STATUS
• START SLAVE
• STOP SLAVE
• RESET MASTER (caution!)
• RESET SLAVE (caution!)
42
42Tuesday, April 10, 12
CHANGE MASTER TO basic syntaxSTOP SLAVE;
CHANGE MASTER TOMASTER_HOST='hostname', # or IPMASTER_PORT=3306,MASTER_USER='slaveuser',MASTER_PASSWORD='slavepassword',MASTER_LOG_FILE='filename', # default: first binlogMASTER_LOG_POS=123456; # default: from the beginning
START SLAVE;
43Tuesday, April 10, 12
44
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
44Tuesday, April 10, 12
45
1 NO NEED TO STOP THE MASTER!
Master
45Tuesday, April 10, 12
46
2 STOP THE SLAVE
SQL commandSTOP SLAVE IO_THREAD;# wait until the SQL_THREAD# has done everythingSTOP SLAVE SQL_THREAD;# STOP THE SERVER
Slave 1
46Tuesday, April 10, 12
47
3 MAKE A COPY OF THE DATA DIRECTORY
Slave 1
47Tuesday, April 10, 12
48
4 RESTART THE SLAVE
Slave 1
48Tuesday, April 10, 12
49
5 INSTALL MySQL on the new slave
Slave 2
Make sure that:• You're using the same version of MySQL • You have the same directory structure• The server is not started yet
49Tuesday, April 10, 12
50
6 COPY THE old slave DATA on the slave
Slave 2
50Tuesday, April 10, 12
51
7 ENABLE THE NEW SLAVE
Configuration file[mysqld]server-id=3relay-log=mysql-relayread-only # optional:log-bin=mysql-bin
Slave 2
must be unique!
51Tuesday, April 10, 12
52
8 START THE NEW SLAVE
Slave 2
52Tuesday, April 10, 12
53
9 CHECK THE SLAVE
SQL commandSHOW SLAVE STATUS \G... Slave_IO_Running: YesSlave_SQL_Running: Yes...
Slave 2
53Tuesday, April 10, 12
Why it works
• No need to issue a CHANGE MASTER TO command.
• Because we cloned the old slave
• The new slave gets its parameters from the .info files in the data directory
54
54Tuesday, April 10, 12
Starting and stopping replication
55Tuesday, April 10, 12
Starting and stopping replicationSTART SLAVE;
55Tuesday, April 10, 12
Starting and stopping replicationSTART SLAVE;START SLAVE SQL_THREAD;
55Tuesday, April 10, 12
Starting and stopping replicationSTART SLAVE;START SLAVE SQL_THREAD;START SLAVE IO_THREAD;
55Tuesday, April 10, 12
Starting and stopping replicationSTART SLAVE;START SLAVE SQL_THREAD;START SLAVE IO_THREAD;START SLAVE UNTIL MASTER_LOG_FILE='filename', MASTER_LOG_POS=xxxx;
55Tuesday, April 10, 12
Starting and stopping replicationSTART SLAVE;START SLAVE SQL_THREAD;START SLAVE IO_THREAD;START SLAVE UNTIL MASTER_LOG_FILE='filename', MASTER_LOG_POS=xxxx;STOP SLAVE;
55Tuesday, April 10, 12
Starting and stopping replicationSTART SLAVE;START SLAVE SQL_THREAD;START SLAVE IO_THREAD;START SLAVE UNTIL MASTER_LOG_FILE='filename', MASTER_LOG_POS=xxxx;STOP SLAVE;STOP SLAVE SQL_THREAD;
55Tuesday, April 10, 12
Starting and stopping replicationSTART SLAVE;START SLAVE SQL_THREAD;START SLAVE IO_THREAD;START SLAVE UNTIL MASTER_LOG_FILE='filename', MASTER_LOG_POS=xxxx;STOP SLAVE;STOP SLAVE SQL_THREAD;STOP SLAVE IO_THREAD;
55Tuesday, April 10, 12
Hijacking the agenda
• MySQL Sandbox
56
AGENDA
56Tuesday, April 10, 12
MySQL Sandbox
• Free software (Perl under GPL)
• One (unix) host
• Many database servers
• Single or multiple sandboxes
• Customized scripts to use the servers
• Standard or circular replication
• Installs IN SECONDS
http://mysqlsandbox.net
57Tuesday, April 10, 12
overview
MySQLserver
MySQLserver
Data DB1
DB2 DB3
Data DB1
DB2 DB3DATA DIRECTORY
PORT
SOCKET
58Tuesday, April 10, 12
overview
MySQLserver
MySQLserver
Data DB1
DB2 DB3
Data DB1
DB2 DB3
SAME DATA
DIRECTORY?
DATA CORRUPTION
/var/lib/mysql /var/lib/mysql
59Tuesday, April 10, 12
overview
MySQLserver
MySQLserver
SAME PORT or SOCKET?
DOES NOT START
/tmp/mysql.sock /tmp/mysql.sock
33063306
60Tuesday, April 10, 12
The hard way
61Tuesday, April 10, 12
The hard way
Read the manual
61Tuesday, April 10, 12
The hard way
Read the manual try to figure out what to change
61Tuesday, April 10, 12
The hard way
Read the manual try to figure out what to change Install
61Tuesday, April 10, 12
The easy way
$ make_sandbox \ /path/to/mysql-5.1.54_linux.tar.gz
# it should work always
MySQL Sandbox
62Tuesday, April 10, 12
The easier way
$ make_sandbox 5.1.54
Prepare once Install many times
# some # preliminary # work
63Tuesday, April 10, 12
The easiest way
$ sb 5.1.54
Prepare once Install many times
# some # preliminary # work
64Tuesday, April 10, 12
MySQL Sandbox
MySQLserver
VERSION
$SANDBOX_HOME/msb_VERSION/dataData DB1
DB2 DB3
VERSION
/tmp/mysql_VERSION.sock
65Tuesday, April 10, 12
MySQL Sandbox
MySQLserver
5.1.54
$SANDBOX_HOME/msb_5_1_54/dataData DB1
DB2 DB3
5154
/tmp/mysql_5154.sock
66Tuesday, April 10, 12
MySQL Sandbox
MySQLserver
5.5.9
$SANDBOX_HOME/msb_5_5_09/dataData DB1
DB2 DB3
5509
/tmp/mysql_5509.sock
67Tuesday, April 10, 12
Single SandboxMySQLserver
customized scripts
startstop
restartstatusclear
send_killuse
68Tuesday, April 10, 12
Multiple SandboxMySQLserver
customized scripts
start_allstop_all
restart_allstatus_allclear_all
send_kill_all
use_all
ms1s2
n1n2n3
69Tuesday, April 10, 12
Where do you get it
•from CPANsudo su -
cpan MySQL::Sandbox
•from launchpadhttp://launchpad.net/mysql-sandbox
70Tuesday, April 10, 12
The easy replication way
# some# preparation
MySQL Sandbox
$ make_replication_sandbox \ /path/to/mysql-5.1.54_linux.tar.gz
Prepare once Install many times
$ make_replication_sandbox 5.1.54
71Tuesday, April 10, 12
default architecture
$HOME
/sandboxes opt
mysql
$SANDBOX_HOME
$SANDBOX_BINARY
expandedtarballs
installed sandboxes
72Tuesday, April 10, 12
default architecture
$HOME
/sandboxes opt
mysql
5.0.91
5.1.45
5.1.48
5.5.4
msb_5_0_91
msb_5_1_48
rsandbox_5_1_48
master
node1
node273Tuesday, April 10, 12
74Tuesday, April 10, 12
creating a single sanbox
make_sandbox \ /path/to/mysql-X.X.XX-OS.tar.gz
75Tuesday, April 10, 12
using a single sanbox
# after # make_sandbox \# /path/to/mysql-X.X.XX-OS.tar.gz
$ cd $SANDBOX_HOME/msb_X_X_XX$ ./use
76Tuesday, April 10, 12
creating a single sanboxwith a specific options file
make_sandbox \ /path/to/mysql-X.X.XX-OS.tar.gz \ --my_file=/path/to/my.cnf
77Tuesday, April 10, 12
easily create a sandbox after the first one
$ cd $HOME/opt/mysql$ gunzip -c \ /path/to/mysql-5.1.34-osx10.5-x86.tar.gz \ | tar -xf -$ mv mysql-5.1.34-osx10.5-x86 5.1.34$ make sandbox 5.1.34
The long way
# $SANDBOX_BINARY
78Tuesday, April 10, 12
easily create a sandbox after the first one
$ make_sandbox \ path/to/mysql-5.1.34-osx10.5-x86.tar.gz \ --export_binaries
The short way
79Tuesday, April 10, 12
starting a single sanbox
$ cd $SANDBOX_HOME/msb_X_X_XX$ ./start
80Tuesday, April 10, 12
starting a single sanboxwith temporary options
$ cd $SANDBOX_HOME/msb_X_X_XX$ ./start --option=value
$ ./restart --option=value
$ ./start --key-buffer=20000000
81Tuesday, April 10, 12
creating a sandbox with custom port and directory
$ make_sandbox 5.1.34 \ --sandbox_port=7800 \ --sandbox_directory=mickeymouse
82Tuesday, April 10, 12
creating a sandbox with automatic port checking
$ make_sandbox 5.1.34 --check_port
# if 5.1.34 is free# port=5134# directory=msb_5_1_34# else# port=5135 (or the first free)# directory=msb_5_1_34_a
83Tuesday, April 10, 12
create a replication sandbox
$ make_replication_sandbox \ path/to/mysql-5.1.34-osx10.5-x86.tar.gz
84Tuesday, April 10, 12
create a circular replication sandbox
$ make_replication_sandbox \ --circular=4 \ path/to/mysql-5.1.34-osx10.5-x86.tar.gz
85Tuesday, April 10, 12
changing port to an existing sandbox
$ sbtool -o port \ -s /path/to/source/sandbox \ --new_port=XXXX
86Tuesday, April 10, 12
installing the innodb plugin
$ sbtool -o plugin \ --plugin=innodb \ -s /path/to/source/sandbox
87Tuesday, April 10, 12
creating a replication sandbox with new base port
$ make_replication_sandbox \ --replication_directory=newwdir \ --check_base_port 5.0.79
# Creates a replication directory under# $SANDBOX_HOME/newdir# The previous one is preserved. # No conflicts happen
88Tuesday, April 10, 12
How to set up replication
DEMO
89
AGENDA
89Tuesday, April 10, 12
90
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
90Tuesday, April 10, 12
Binary log formats
• Statement based replication
• default
• available since 3.23
• Row based replication
• introduced in 5.1
• ROW or MIXED
91
91Tuesday, April 10, 12
Viewing the binary logs# statement based replication
$ mysqlbinlog binary-log-name
# or, as a SQL commandSHOW BINLOG EVENTS IN 'binary-log-name';
# row based replication
$ mysqlbinlog --verbose \ --base64-output=decode-rows binary-log-name
92Tuesday, April 10, 12
binary log examples# statement based replication
mysqlbinlog binary-log-name
# row based replication
mysqlbinlog --verbose \ --base64-output=decode-rows binary-log-name
93Tuesday, April 10, 12
94
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
94Tuesday, April 10, 12
what gets replicated
95
What Statement-based row-basedinsert/update/delete statement affected recordsschema/table/view
creation/drop statement statement
stored routine/trigger creation/drop statement statement
stored procedure call statements executed inside SP affected records
stored function call function call affected records
trigger execution none: the slave runs the trigger
affected records moved. No trigger runs
event creation slaveside disabled event
slaveside disabled event
event execution statement affected records
95Tuesday, April 10, 12
what gets replicated
95
What Statement-based row-basedinsert/update/delete statement affected recordsschema/table/view
creation/drop statement statement
stored routine/trigger creation/drop statement statement
stored procedure call statements executed inside SP affected records
stored function call function call affected records
trigger execution none: the slave runs the trigger
affected records moved. No trigger runs
event creation slaveside disabled event
slaveside disabled event
event execution statement affected records
95Tuesday, April 10, 12
what gets replicated
95
What Statement-based row-basedinsert/update/delete statement affected recordsschema/table/view
creation/drop statement statement
stored routine/trigger creation/drop statement statement
stored procedure call statements executed inside SP affected records
stored function call function call affected records
trigger execution none: the slave runs the trigger
affected records moved. No trigger runs
event creation slaveside disabled event
slaveside disabled event
event execution statement affected records
95Tuesday, April 10, 12
what gets replicated
95
What Statement-based row-basedinsert/update/delete statement affected recordsschema/table/view
creation/drop statement statement
stored routine/trigger creation/drop statement statement
stored procedure call statements executed inside SP affected records
stored function call function call affected records
trigger execution none: the slave runs the trigger
affected records moved. No trigger runs
event creation slaveside disabled event
slaveside disabled event
event execution statement affected records
95Tuesday, April 10, 12
what gets replicated
95
What Statement-based row-basedinsert/update/delete statement affected recordsschema/table/view
creation/drop statement statement
stored routine/trigger creation/drop statement statement
stored procedure call statements executed inside SP affected records
stored function call function call affected records
trigger execution none: the slave runs the trigger
affected records moved. No trigger runs
event creation slaveside disabled event
slaveside disabled event
event execution statement affected records
95Tuesday, April 10, 12
what gets replicated
95
What Statement-based row-basedinsert/update/delete statement affected recordsschema/table/view
creation/drop statement statement
stored routine/trigger creation/drop statement statement
stored procedure call statements executed inside SP affected records
stored function call function call affected records
trigger execution none: the slave runs the trigger
affected records moved. No trigger runs
event creation slaveside disabled event
slaveside disabled event
event execution statement affected records
95Tuesday, April 10, 12
what gets replicated
95
What Statement-based row-basedinsert/update/delete statement affected recordsschema/table/view
creation/drop statement statement
stored routine/trigger creation/drop statement statement
stored procedure call statements executed inside SP affected records
stored function call function call affected records
trigger execution none: the slave runs the trigger
affected records moved. No trigger runs
event creation slaveside disabled event
slaveside disabled event
event execution statement affected records
95Tuesday, April 10, 12
what gets replicated
95
What Statement-based row-basedinsert/update/delete statement affected recordsschema/table/view
creation/drop statement statement
stored routine/trigger creation/drop statement statement
stored procedure call statements executed inside SP affected records
stored function call function call affected records
trigger execution none: the slave runs the trigger
affected records moved. No trigger runs
event creation slaveside disabled event
slaveside disabled event
event execution statement affected records
95Tuesday, April 10, 12
what gets replicated
95
What Statement-based row-basedinsert/update/delete statement affected recordsschema/table/view
creation/drop statement statement
stored routine/trigger creation/drop statement statement
stored procedure call statements executed inside SP affected records
stored function call function call affected records
trigger execution none: the slave runs the trigger
affected records moved. No trigger runs
event creation slaveside disabled event
slaveside disabled event
event execution statement affected records
95Tuesday, April 10, 12
Binary logs in practice
DEMO
96
AGENDA
96Tuesday, April 10, 12
97
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
97Tuesday, April 10, 12
From single server application
98
r/w requests
98Tuesday, April 10, 12
To replication-aware application
99
read/writemaster
read/onlyslaves
R/WR/O
load balancer
99Tuesday, April 10, 12
Single server application$link = mysql_connect(
$server_IP, 'mysql_user', 'mysql_password');$result = mysql_query('INSERT INTO table_name (x) VALUES (1)',$link);$result = mysql_query('SELECT * FROM table_name WHERE x=1',$link);
100100Tuesday, April 10, 12
Making an application aware of replication
101
connectreadwrite
IPuserpassword
<<R/W database handling>>
db
connect
IPuserpassword
<<database handling>>db
read
IPuserpassword
<<read-only database handling>>
db
connectreadwrite
IPuserpassword
<<R/W database handling>>
db
101Tuesday, April 10, 12
Using replication: the WRONG way
102
R/W split by
statement
Write statement?
Connect to the master
Yes
Write to the master
Stop
No
Connect to the next available
slave
Read from slave
102Tuesday, April 10, 12
Why statement split is wrong
• Breaks transactions
• High risk of inconsistency
• Loses or corrupts data
103
103Tuesday, April 10, 12
Using replication:the RIGHT way
104
R/W split by function
Write function?
Connect to the master
Yes
Read and write from master
Stop
No
Connect to the next available
slave
Read from slave
more queries?
No
Yesmore
queries?
No
Yes
104Tuesday, April 10, 12
Replication is single threaded
• Let’s assume you have two queries on the master
• one query takes 3 minutes to complete
• the other takes 5 minutes
• Both start at 12:00 noon
105
105Tuesday, April 10, 12
SLAVE
MASTER
Single thread replication
106
12:00 12:01 12:02 12:03 12:04 12:05 12:06 12:07 12:08 12:09 12:10 12:11
106Tuesday, April 10, 12
SLAVE
MASTER
Single thread replication
106
(does nothing)
QUERY 1
12:00 12:01 12:02 12:03 12:04 12:05 12:06 12:07 12:08 12:09 12:10 12:11
QUERY 2
106Tuesday, April 10, 12
SLAVE
MASTER
Single thread replication
106
(does nothing)
QUERY 1
12:00 12:01 12:02 12:03 12:04 12:05 12:06 12:07 12:08 12:09 12:10 12:11
QUERY 1
QUERY 2
106Tuesday, April 10, 12
SLAVE
MASTER
Single thread replication
106
(does nothing)
QUERY 1
12:00 12:01 12:02 12:03 12:04 12:05 12:06 12:07 12:08 12:09 12:10 12:11
QUERY 1 QUERY 2
QUERY 2
106Tuesday, April 10, 12
SLAVE
MASTER
Single thread replication
107
does nothing
QUERY 1
QUERY 2
QUERY 3
QUERY 4
QUERY 5
12:00 12:01 12:02 12:03 12:04 12:05 12:06 12:07 12:08 12:09 12:10 12:11
QUERY 1 QUERY 2
QUERY 6
107Tuesday, April 10, 12
108
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
108Tuesday, April 10, 12
109
Sample monitoring
master
slave
Get master binlog and position
get slave status
Running?
Yes
No
alert
Same or later binlog/position?
check table contents
NoYes
109Tuesday, April 10, 12
commands for checking replicationmaster> SHOW MASTER STATUS
master> SHOW PROCESSLIST
slave> SHOW SLAVE STATUS
slave> SHOW PROCESSLIST
110Tuesday, April 10, 12
replication user seen in the mastermaster > show processlist\G*************************** 1. row *************** Id: 2 User: rsandbox Host: localhost:57011 db: NULLCommand: Binlog Dump Time: 81625 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL[...]
111Tuesday, April 10, 12
IO-thread in the slaveslave > show processlist\G*************************** 1. row *************** Id: 2 User: system user Host: db: NULLCommand: Connect Time: 124801 State: Waiting for master to send event Info: NULL[...]
112Tuesday, April 10, 12
SQL thread in the slaveslave > show processlist\G[...]*************************** 2. row *************** Id: 3 User: system user Host: db: NULLCommand: Connect Time: 124712 State: Slave has read all relay log; waiting for the slave I/O thread to update it Info: NULL[...]
113Tuesday, April 10, 12
monitoring replicationmaster> SHOW MASTER STATUS
slave> SHOW SLAVE STATUS
FULL SCRIPTS:
http://datacharmer.blogspot.com/2011/04/refactored-again-poor-mans-mysql.html
http://forge.mysql.com/tools/tool.php?id=6
114Tuesday, April 10, 12
show master status File: mysql-bin.000002
Position: 78045744
Binlog_Do_DB:
Binlog_Ignore_DB:
115Tuesday, April 10, 12
show slave status Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rsandbox
Master_Port: 27371
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 78045744
Relay_Log_File: mysql_sandbox27372-relay-bin.000055
Relay_Log_Pos: 78045889
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
116Tuesday, April 10, 12
show slave status ...
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
...
117Tuesday, April 10, 12
show slave status...
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 78045744
Relay_Log_Space: 78046100
...
Seconds_Behind_Master: 0
...
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
118Tuesday, April 10, 12
Breaking (and fixing) replication
DEMO
119
AGENDA
119Tuesday, April 10, 12
120
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
120Tuesday, April 10, 12
Logs rotation# server variables
max-binlog-sizeexpire-log-days
# logs commands
SHOW MASTER LOGS;PURGE MASTER LOGS TO 'filename';FLUSH [BINARY] LOGS;
121Tuesday, April 10, 12
122
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
122Tuesday, April 10, 12
123
Replacing a slave
Slave crashes
are there more slaves?
STOP the master STOP one
slave
YesNo
add the first slave
add another slave
Stop
123Tuesday, April 10, 12
124
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
124Tuesday, April 10, 12
125
Replacing the master
Master crashes
Let all slaves catch up with
execution
FIND the most up to date slave
STOP replication in
all slaves
make it the master
Stop
FIND which transactions are
missing from other slaves
connect all slaves to the new master
run missing transactions
to other slaves
125Tuesday, April 10, 12
Planned master switch
• Stop accepting writes
• Wait until all slaves have caught up
• Stop replication in all slaves
• Promote a slave to master
• Point all slaves to the new master
126Tuesday, April 10, 12
Changing a failed master
• Pre-requisite:
• log_bin and log_slave_updates must be enabled in all the slaves
• If not, there is more manual labor
127Tuesday, April 10, 12
Changing a failed master (1)
• Wait until all slaves have caught up
• Identify the most advanced slave
• Make that slave the new master
• ... so far, so good
128Tuesday, April 10, 12
Changing a failed master (2)• For each remaining slave:
• Find the missing statements
• Find the LAST statement replicated by the slave
• Find the same statement in the new master binlog (*)
• get the position of the NEXT statement
(*) if log_slave_updates was not enabled, you need to convert the relay log statements to SQL and do the next step manually
129Tuesday, April 10, 12
Changing a failed master (3)
• For each remaining slave:
• Apply the missing statements
• CHANGE MASTER TO master_host=”new_master_hostname”, master_port=new_master_port, master_log_file=”mysql-bin.xxxxxx”, master_log_pos=YYYY
130Tuesday, April 10, 12
Reasons for complexity
• No global transaction ID
131Tuesday, April 10, 12
What is a global transaction ID
• A unique identifier of a transaction
• Unique for the whole cluster, not for each node
• Generated by the ultimate source (the master)
• Does not change when the transaction goes through an intermediate master
132Tuesday, April 10, 12
Why should you care
• Failure recovery
• MySQL DOES NOT have it
• What can you do? Either wait for MySQL 5.6 or use Tungsten Replicator.
133Tuesday, April 10, 12
134
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
134Tuesday, April 10, 12
Slave = master replacement
• if:
• there are no filters;
• you are monitoring replication
• you make sure data is consistent
135
135Tuesday, April 10, 12
Slave != backup• There is no replacement for a good backup.
• Data loss due to a mistake in the master will propagate to the slaves.
• Disasters are always smarter than replication.
• Do your backups!
• Session on Wednesday at 12pm: "Be a Data Management Hero with Good Backups!"
136
136Tuesday, April 10, 12
137
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
137Tuesday, April 10, 12
Replication =
let the slave do the dirty work
138Tuesday, April 10, 12
master
slaves
STOP SLAVE
remove slave from load balancer
START SLAVE
perform backup
attach slave to load
balancer
Let slave catch up
backup
139Tuesday, April 10, 12
140
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
140Tuesday, April 10, 12
master
slaves
STOP SLAVE
remove slave from load balancer
START SLAVE
calculate summary
tables
attach slave to load
balancer
Let slave catch up
makesummary
tables
141Tuesday, April 10, 12
master
slave
innodbnon partitioned
slave
innodbnon partitionedinnodb
partitioned by range
slave
MyISAMpartitioned by range
Partitionsfor heavy statistics
142Tuesday, April 10, 12
master
slave
innodbnon partitioned
slave
innodbnon partitioned
ARCHIVEpartitioned by range
(date)
slave
ARCHIVEpartitioned by range
(product)
slave
ARCHIVEpartitioned by range
(location)
Simulating multiple
dimensions
143Tuesday, April 10, 12
144
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
144Tuesday, April 10, 12
READ-ONLY slave
• Good practice: make a slave read-only
• Caveat 1: if promoting a slave, you need to remove the read-only option;
• Caveat 1: a user with SUPER privilege can write to a read-only slave
145
145Tuesday, April 10, 12
Default engine
• Caveat: default storage engine is not replicated.
• Example: master with default engine=Innodb and slave with default engine=MyISAM
• a CREATE TABLE without the ENGINE clause will use different engines on master and slave
146
146Tuesday, April 10, 12
master-to-master and circular replication
DEMO
147
AGENDA
147Tuesday, April 10, 12
Common filters
• on the master
• binlog-do-db
• binlog ignore-db
• on the slave
• replicate-do-db
• replicate-ignore-db
148
148Tuesday, April 10, 12
Common filters
• on the master
• binlog-do-db
• binlog ignore-db
• on the slave
• replicate-do-db
• replicate-ignore-db
148
DO NOT USE!
148Tuesday, April 10, 12
do Filters
149
all data comes to the master
The master logs only some of the transactions
The slave only gets some of the above
replicate-do-*
binlog-do-*
transactions from client
What happens when everything goes right
149Tuesday, April 10, 12
ignore Filters
150
all data comes to the master
The master logs all the transactions, except the ones that should be ignored
The slave further filters some of the above
replicate-ignore-*
binlog-ignore-*
transactions from client
150Tuesday, April 10, 12
What happens when something goes wrongfilters
151
Master my.cnf:binlog-do-db=foo
foo
bar
use foo;insert into bar.t1 values (1)
it is replicated (breaks replication)
use bar;insert into foo.t1 values (1)
is NOT replicated
151Tuesday, April 10, 12
What happens when something goes wrongfilters
152
slave my.cnf:replicate-do-db=foo
foo
bar
use foo;insert into bar.t1 values (1)
it is replicated (breaks replication)
use bar;insert into foo.t1 values (1)
is NOT replicated
152Tuesday, April 10, 12
"Safer" filters
153
• on the slave:
• replicate-wild-do-table=db_name.%
• replicate-wild-do-table=foo%.bar%
• replicate-wild-ignore-table=db_name.%
• replicate-wild-ignore-table=foo%.bar%
153Tuesday, April 10, 12
"Safer" filters
153
• on the slave:
• replicate-wild-do-table=db_name.%
• replicate-wild-do-table=foo%.bar%
• replicate-wild-ignore-table=db_name.%
• replicate-wild-ignore-table=foo%.bar%
DON'T MIX "do" and "ignore" filters
153Tuesday, April 10, 12
General rules of replication filters
• DON'T USE filters on the master.
• If you apply slave filters, the slave is not suitable for replacing a master or taking backups;
• Filters can break replication;
• Slave filters don't save bandwidth.
154
154Tuesday, April 10, 12
155
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
155Tuesday, April 10, 12
Read more
• The MySQL online manual
http://dev.mysql.com/doc
156
156Tuesday, April 10, 12
High Performance MySQL
157Tuesday, April 10, 12
MySQL High Availability
158Tuesday, April 10, 12
Web Operations
159Tuesday, April 10, 12
Cloud Application Architectures
160Tuesday, April 10, 12
161
Why replication • The web economy• Scaling out
How to set replication • From single server to replication• Adding a slave
Using Replication• Binary log formats• What gets replicated and how• Replication awareness
Managing replication
• Monitoring• Log management• Replacing a slave• Replacing a master
What Replication is for• master replacement• backup• better reads
Gotchas, tips, and tricks
More info • What to read • More replication sessions
AGENDA
161Tuesday, April 10, 12
More replication sessions - Wednesday• 11am Building a multi-master, multi-region database
infrastructure in Amazon EC2
• 11am Performance practices for minimizing replication delay
• 1pm Diagnosing & Fixing MySQL Replication
• Replaying database load with Percona Playback
• 2pm Build simple and complex replication clusters with Tungsten Replicator
• 3:30pm What's new in MySQL 5.5 and 5.6 Replication
162
162Tuesday, April 10, 12
More replication sessions - Thursday
• 11am MySQL Replication: Pros and Cons
• 2pm Boost Your Replication Throughput with Parallel Apply, Prefetch, and Batching
• 3pm Verifying MySQL Replication Safely With pt-table-checksum 2.0
163
163Tuesday, April 10, 12
©Continuent 2012.
WE ARE HIRING!•Implementation/support engineer•QA engineer•Documentation writer
Database replication and clustering
http://www.continuent.com
164Tuesday, April 10, 12
Bonus slides
• Semi-synchronous replication
165
165Tuesday, April 10, 12
semi-synchronous replication
• Available in 5.5 and higher
• Makes sure that at least one slave has copied the data.
• Increases reliability
166Tuesday, April 10, 12
167
transaction with regular replication
clientmaster
slave
commit
binary log
execute
returns to client
replication
1
2
34
5167Tuesday, April 10, 12
168
transaction with semi-
synchronous replication
clientmaster
slave
commit
binary log
execute
returns to client
sends transaction
to slave
gets acknowledgement
relay log
1
2
3
4 5
6
7
168Tuesday, April 10, 12
semi-synchronous replication in practice
• installation:
• it’s a plugin.
• Actually, two plugins
169Tuesday, April 10, 12
semi-synch replication install# in the masterplugin-load=rpl_semi_sync_master=semisync_master.sorpl_semi_sync_master_enabled=1
# in each slaveplugin-load=rpl_semi_sync_slave=semisync_slave.sorpl_semi_sync_slave_enabled=1
# restart all servers
170Tuesday, April 10, 12
semi-synch replication check# in the mastershow variables like 'rpl_semi%';+------------------------------------+-------+| Variable_name | Value |+------------------------------------+-------+| rpl_semi_sync_master_enabled | ON || rpl_semi_sync_master_timeout | 10000 || rpl_semi_sync_master_trace_level | 32 || rpl_semi_sync_master_wait_no_slave | ON |+------------------------------------+-------+
171Tuesday, April 10, 12
semi-synch replication checkshow status like "rpl_semi_%tx";+-----------------------------+-------+| variable_name | value |+-----------------------------+-------+| RPL_SEMI_SYNC_MASTER_NO_TX | 0 || RPL_SEMI_SYNC_MASTER_YES_TX | 0 |+-----------------------------+-------+
172Tuesday, April 10, 12
semi-synch replication testmaster> create table t1 ( i int);Query OK, 0 rows affected (0.01 sec)
master> show status like "rpl_semi_%tx";+-----------------------------+-------+| Variable_name | Value |+-----------------------------+-------+| Rpl_semi_sync_master_no_tx | 0 || Rpl_semi_sync_master_yes_tx | 1 |+-----------------------------+-------+
173Tuesday, April 10, 12
disabling semi-synch# for each slave
set global rpl_semi_sync_slave_enabled=0;stop slave io_thread; start slave io_thread;
174Tuesday, April 10, 12
disabled semi-synch replication testmaster> insert into t1 values (1);Query OK, 1 row affected (10.00 sec)
master> show status like "rpl_semi_%tx";+-----------------------------+-------+| Variable_name | Value |+-----------------------------+-------+| Rpl_semi_sync_master_no_tx | 1 || Rpl_semi_sync_master_yes_tx | 1 |+-----------------------------+-------+2 rows in set (0.00 sec)
175Tuesday, April 10, 12
disabled semi-synch replication testmaster> insert into t1 values (2);Query OK, 1 row affected (0.01 sec)
master> show status like "rpl_semi_%tx";+-----------------------------+-------+| Variable_name | Value |+-----------------------------+-------+| Rpl_semi_sync_master_no_tx | 2 || Rpl_semi_sync_master_yes_tx | 1 |+-----------------------------+-------+2 rows in set (0.00 sec)
176Tuesday, April 10, 12
re-enabling semi-synch# in one slave
set global rpl_semi_sync_slave_enabled=1;stop slave io_thread; start slave io_thread;
177Tuesday, April 10, 12
reenabled semi-synch replication testmaster> insert into t1 values (3);Query OK, 1 row affected (0.01 sec)
master> show status like "rpl_semi_%tx";+-----------------------------+-------+| Variable_name | Value |+-----------------------------+-------+| Rpl_semi_sync_master_no_tx | 2 || Rpl_semi_sync_master_yes_tx | 2 |+-----------------------------+-------+2 rows in set (0.00 sec)
178Tuesday, April 10, 12
top related