MySQL Replication 101 Giuseppe Maxia Continuent, Inc
©Continuent 2012. Wednesday, April 18, 12
1
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
Wednesday, April 18, 12
2
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 3
Wednesday, April 18, 12
3
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 4
Wednesday, April 18, 12
4
www $$$ The world today is dominated by the web economy 5 Wednesday, April 18, 12
5
www $$$ Databases are the backbone of the web economy 6 Wednesday, April 18, 12
6
What database for the web?
7 Wednesday, April 18, 12
7
What database for the web? The most powerful database 1
Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 8
7 Wednesday, April 18, 12
7
What database for the web? The most powerful database 1
Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 8
The most advanced open source database
7 Wednesday, April 18, 12
7
What database for the web? The most powerful database 1
Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 8
The most advanced open source database The most deployed open source database
7 Wednesday, April 18, 12
7
What database for the web? The most powerful database 1
Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 8
The most advanced open source database The most deployed open source database The most popular open source database 7 Wednesday, April 18, 12
7
www $$$ Actually, MySQL databases are the backbone of the web economy 8 Wednesday, April 18, 12
8
What database for the web? No built-in replication 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
Built-in replication
✗ ✗ ✗ ✔ 9
Wednesday, April 18, 12
9
www $$$ More precisely, MySQL REPLICATION is the backbone of the web economy 10 Wednesday, April 18, 12
10
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 11
Wednesday, April 18, 12
11
database server
a simple web application scheme r/w requests
web server
clients 12 Wednesday, April 18, 12
12
database server
scaling web requests
r/w requests
web servers load balancer
clients 13 Wednesday, April 18, 12
13
database load on a simple web application
write 85%
r e a d 15%
14 Wednesday, April 18, 12
14
write 20%
Wednesday, April 18, 12
read 80%
database load on a successful web application
15 15
database server
✘
scaling up means buying a bigger database server
r/w requests
web servers load balancer
clients 16 Wednesday, April 18, 12
16
write 20%
read 80%
the bigger database server will eventually have the same problem Wednesday, April 18, 12
17 17
read/write master read/only slaves
a web application scheme with replication
load balancer R/W R/O
web servers load balancer
clients 18 Wednesday, April 18, 12
18
r e a d
write 85%
15%
read 100%
read/write master read/only slaves
database load with replication 19 Wednesday, April 18, 12
19
r e a d
write 85%
15%
read 100%
read/write master read/only slaves
scaling database load with replication 20 Wednesday, April 18, 12
20
Replication assessment without replication
with replication
database handling
easy
harder
performance
high
lower (binary logs)
Point in Time recovery
none
easy
failover
none
possible
write scaling
none
minimal
backup
with downtime
without downtime
read scaling
none
easy
21 Wednesday, April 18, 12
21
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 22
Wednesday, April 18, 12
22
client
master transaction
binary log
reads
slave IO thread
relay log
SQL thread
replication concepts
reads 23
Wednesday, April 18, 12
23
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 24
Wednesday, April 18, 12
24
1
SHUT DOWN THE DATABASE SERVER
Master
25 Wednesday, April 18, 12
25
2
MAKE A BACKUP COPY
Master
26 Wednesday, April 18, 12
26
3
ENABLE THE MASTER
Master
Configuration file [mysqld] log-bin=mysql-bin server-id=1 27 Wednesday, April 18, 12
27
4
RESTART THE MASTER
Master
28 Wednesday, April 18, 12
28
CREATE REPLICATION USER
5
Master
SQL command GRANT REPLICATION SLAVE ON *.* to 'slave_user@'10.10.100.%' IDENTIFIED BY 'slave_pass'; 29 Wednesday, April 18, 12
29
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
30 Wednesday, April 18, 12
30
7
COPY THE MASTER DATA to the slave
Slave 1
31 Wednesday, April 18, 12
31
8
ENABLE THE SLAVE
Slave 1
Configuration file [mysqld] server-id=2 relay-log=mysql-relay read-only # optional: log-bin=mysql-bin Wednesday, April 18, 12
32 32
9
START THE SLAVE SERVER
Slave 1
33 Wednesday, April 18, 12
33
10
INITIALIZE THE SLAVE
Slave 1
SQL command SET MASTER TO MASTER_HOST=master_IP, MASTER_PORT=3306, MASTER_USER=slave_user, MASTER_PASSWORD='slave_pwd'; 34 Wednesday, April 18, 12
34
11
START THE SLAVE SERVICE
Slave 1
SQL command START SLAVE;
35 Wednesday, April 18, 12
35
12
CHECK THE SLAVE
Slave 1
SQL command SHOW SLAVE STATUS \G ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... 36 Wednesday, April 18, 12
36
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 Wednesday, April 18, 12
37
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 Wednesday, April 18, 12
38
What if the master was already logging?
• You have two options: • Physical copy • Logical copy
39 Wednesday, April 18, 12
39
Physical copy • stop master • make copy • remove binary log files and index • start master
• (alternative: use xtrabackup)
40 Wednesday, April 18, 12
40
Logical copy
• mysqldump --all-databases --master-data
41 Wednesday, April 18, 12
41
Common replication commands • CHANGE MASTER TO • SHOW MASTER STATUS • SHOW SLAVE STATUS • START SLAVE • STOP SLAVE • RESET MASTER (caution!) • RESET SLAVE (caution!) 42 Wednesday, April 18, 12
42
CHANGE MASTER TO basic syntax STOP SLAVE; CHANGE MASTER TO MASTER_HOST='hostname', # or IP MASTER_PORT=3306, MASTER_USER='slaveuser', MASTER_PASSWORD='slavepassword', MASTER_LOG_FILE='filename', # default: first binlog MASTER_LOG_POS=123456; # default: from the beginning START SLAVE;
Wednesday, April 18, 12
43
DA N E AG
Why replication How to set replication Using Replication
Managing replication
What Replication is for
• The web economy • Scaling out • From single server to replication • Adding a slave • • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 44
Wednesday, April 18, 12
44
1
NO NEED TO STOP THE MASTER!
Master
45 Wednesday, April 18, 12
45
2
STOP THE SLAVE
Slave 1
SQL command STOP SLAVE IO_THREAD; # wait until the SQL_THREAD # has done everything STOP SLAVE SQL_THREAD; # STOP THE SERVER 46 Wednesday, April 18, 12
46
3
MAKE A COPY OF THE DATA DIRECTORY
Slave 1
47 Wednesday, April 18, 12
47
4
RESTART THE SLAVE
Slave 1
48 Wednesday, April 18, 12
48
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
49 Wednesday, April 18, 12
49
6
COPY THE old slave DATA on the slave
Slave 2
50 Wednesday, April 18, 12
50
7
ENABLE THE NEW SLAVE
Slave 2
Configuration file ! e u q i n u e [mysqld] b t us m server-id=3 relay-log=mysql-relay read-only # optional: log-bin=mysql-bin Wednesday, April 18, 12
51 51
8
START THE NEW SLAVE
Slave 2
52 Wednesday, April 18, 12
52
9
CHECK THE SLAVE
Slave 2
SQL command SHOW SLAVE STATUS \G ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... 53 Wednesday, April 18, 12
53
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 Wednesday, April 18, 12
54
Starting and stopping replication
Wednesday, April 18, 12
55
Starting and stopping replication START SLAVE;
Wednesday, April 18, 12
55
Starting and stopping replication START SLAVE; START SLAVE SQL_THREAD;
Wednesday, April 18, 12
55
Starting and stopping replication START SLAVE; START SLAVE SQL_THREAD; START SLAVE IO_THREAD;
Wednesday, April 18, 12
55
Starting and stopping replication START SLAVE; START SLAVE SQL_THREAD; START SLAVE IO_THREAD; START SLAVE UNTIL MASTER_LOG_FILE='filename', MASTER_LOG_POS=xxxx;
Wednesday, April 18, 12
55
Starting and stopping replication START SLAVE; START SLAVE SQL_THREAD; START SLAVE IO_THREAD; START SLAVE UNTIL MASTER_LOG_FILE='filename', MASTER_LOG_POS=xxxx; STOP SLAVE;
Wednesday, April 18, 12
55
Starting and stopping replication START 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;
Wednesday, April 18, 12
55
Starting and stopping replication START 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;
Wednesday, April 18, 12
55
DA N E AG
Hijacking the agenda
• MySQL Sandbox
56 Wednesday, April 18, 12
56
MySQL Sandbox http://mysqlsandbox.net
• 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 Wednesday, April 18, 12
57
overview MySQL server Data
DB2
MySQL server DB1
DB3
DATA DIRECTORY
Data
DB1
DB2
DB3
PORT
SOCKET Wednesday, April 18, 12
58
overview MySQL server
MySQL server
Data
DB1
DB2
DB3
SAME DATA DIRECTORY?
/var/lib/mysql
Data
DB1
DB2
DB3
/var/lib/mysql
DATA CORRUPTION Wednesday, April 18, 12
59
overview MySQL server
MySQL server SAME PORT or SOCKET?
3306
/tmp/mysql.sock
3306 /tmp/mysql.sock
DOES NOT START Wednesday, April 18, 12
60
The hard way
Wednesday, April 18, 12
61
The hard way Read the manual
Wednesday, April 18, 12
61
The hard way Read the manual
Wednesday, April 18, 12
try to figure out what to change
61
The hard way Read the manual
Wednesday, April 18, 12
try to figure out what to change
Install
61
The easy way MySQL Sandbox $ make_sandbox \ /path/to/mysql-5.1.54_linux.tar.gz # it should work always
Wednesday, April 18, 12
62
The easier way Prepare once # some # preliminary # work
Wednesday, April 18, 12
Install many times $ make_sandbox 5.1.54
63
The easiest way Prepare once # some # preliminary # work
Wednesday, April 18, 12
Install many times $ sb 5.1.54
64
MySQL Sandbox VERSION MySQL server Data
DB2
DB1
DB3
$SANDBOX_HOME/msb_VERSION/data
VERSION
/tmp/mysql_VERSION.sock Wednesday, April 18, 12
65
MySQL Sandbox 5.1.54 MySQL server Data
DB2
DB1
DB3
$SANDBOX_HOME/msb_5_1_54/data
5154
/tmp/mysql_5154.sock Wednesday, April 18, 12
66
MySQL Sandbox 5.5.9 MySQL server Data
DB2
DB1
DB3
$SANDBOX_HOME/msb_5_5_09/data
5509
/tmp/mysql_5509.sock Wednesday, April 18, 12
67
Single Sandbox MySQL server
customized scripts
start stop restart status clear send_kill use Wednesday, April 18, 12
68
Multiple Sandbox MySQL server
customized scripts
start_all stop_all restart_all m n1 status_all s1 n2 clear_all s2 n3 send_kill_a ll use_all Wednesday, April 18, 12
69
Where do you get it
•from CPAN sudo su cpan MySQL::Sandbox
•from launchpad http://launchpad.net/mysql-sandbox
Wednesday, April 18, 12
70
The easy replication way MySQL Sandbox $ make_replication_sandbox \ /path/to/mysql-5.1.54_linux.tar.gz
Prepare once # some # preparation
Wednesday, April 18, 12
Install many times $ make_replication_sandbox 5.1.54
71
default architecture $HOME
/sandboxes
opt
expanded tarballs
$SANDBOX_HOME mysql
installed sandboxes Wednesday, April 18, 12
$SANDBOX_BINARY
72
default architecture $HOME
/sandboxes
opt
msb_5_0_91 mysql
msb_5_1_48 rsandbox_5_1_48 master node1 node2 Wednesday, April 18, 12
5.0.91 5.1.45 5.1.48 5.5.4 73
Wednesday, April 18, 12
74
creating a single sanbox make_sandbox \ /path/to/mysql-X.X.XX-OS.tar.gz
Wednesday, April 18, 12
75
using a single sanbox # after # make_sandbox \ # /path/to/mysql-X.X.XX-OS.tar.gz $ cd $SANDBOX_HOME/msb_X_X_XX $ ./use
Wednesday, April 18, 12
76
creating a single sanbox with a specific options file make_sandbox \ /path/to/mysql-X.X.XX-OS.tar.gz \ --my_file=/path/to/my.cnf
Wednesday, April 18, 12
77
easily create a sandbox after the first one The long way $ cd $HOME/opt/mysql # $SANDBOX_BINARY $ 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
Wednesday, April 18, 12
78
easily create a sandbox after the first one The short way $ make_sandbox \ path/to/mysql-5.1.34-osx10.5-x86.tar.gz \ --export_binaries
Wednesday, April 18, 12
79
starting a single sanbox $ cd $SANDBOX_HOME/msb_X_X_XX $ ./start
Wednesday, April 18, 12
80
starting a single sanbox with temporary options $ cd $SANDBOX_HOME/msb_X_X_XX $ ./start --option=value $ ./restart --option=value $ ./start --key-buffer=20000000
Wednesday, April 18, 12
81
creating a sandbox with custom port and directory $ make_sandbox 5.1.34 \ --sandbox_port=7800 \ --sandbox_directory=mickeymouse
Wednesday, April 18, 12
82
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
Wednesday, April 18, 12
83
create a replication sandbox
$ make_replication_sandbox \ path/to/mysql-5.1.34-osx10.5-x86.tar.gz
Wednesday, April 18, 12
84
create a circular replication sandbox
$ make_replication_sandbox \ --circular=4 \ path/to/mysql-5.1.34-osx10.5-x86.tar.gz
Wednesday, April 18, 12
85
changing port to an existing sandbox
$ sbtool -o port \ -s /path/to/source/sandbox \ --new_port=XXXX
Wednesday, April 18, 12
86
installing the innodb plugin
$ sbtool -o plugin \ --plugin=innodb \ -s /path/to/source/sandbox
Wednesday, April 18, 12
87
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
Wednesday, April 18, 12
88
DA N E AG
How to set up replication
DEMO
89 Wednesday, April 18, 12
89
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 90
Wednesday, April 18, 12
90
Binary log formats • Statement based replication • default • available since 3.23 • Row based replication • introduced in 5.1 • ROW or MIXED
91 Wednesday, April 18, 12
91
Viewing the binary logs # statement based replication $ mysqlbinlog binary-log-name # or, as a SQL command SHOW BINLOG EVENTS IN 'binary-log-name'; # row based replication $ mysqlbinlog --verbose \ --base64-output=decode-rows binary-log-name
Wednesday, April 18, 12
92
binary log examples # statement based replication mysqlbinlog binary-log-name
# row based replication mysqlbinlog --verbose \ --base64-output=decode-rows binary-log-name
Wednesday, April 18, 12
93
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 94
Wednesday, April 18, 12
94
what gets replicated What
Statement-based
row-based
insert/update/delete
statement
affected records
schema/table/view creation/drop
statement
statement
stored routine/trigger creation/drop
statement
statement
stored procedure call stored function call trigger execution
statements executed inside SP function call
affected records affected records
none: the slave runs the affected records trigger moved. No trigger runs
event creation
slaveside disabled event
slaveside disabled event
event execution
statement
affected records 95
Wednesday, April 18, 12
95
what gets replicated What
Statement-based
row-based
insert/update/delete
statement
affected records
schema/table/view creation/drop
statement
statement
stored routine/trigger creation/drop
statement
statement
stored procedure call stored function call trigger execution
statements executed inside SP function call
affected records affected records
none: the slave runs the affected records trigger moved. No trigger runs
event creation
slaveside disabled event
slaveside disabled event
event execution
statement
affected records 95
Wednesday, April 18, 12
95
what gets replicated What
Statement-based
row-based
insert/update/delete
statement
affected records
schema/table/view creation/drop
statement
statement
stored routine/trigger creation/drop
statement
statement
stored procedure call stored function call trigger execution
statements executed inside SP function call
affected records affected records
none: the slave runs the affected records trigger moved. No trigger runs
event creation
slaveside disabled event
slaveside disabled event
event execution
statement
affected records 95
Wednesday, April 18, 12
95
what gets replicated What
Statement-based
row-based
insert/update/delete
statement
affected records
schema/table/view creation/drop
statement
statement
stored routine/trigger creation/drop
statement
statement
stored procedure call stored function call trigger execution
statements executed inside SP function call
affected records affected records
none: the slave runs the affected records trigger moved. No trigger runs
event creation
slaveside disabled event
slaveside disabled event
event execution
statement
affected records 95
Wednesday, April 18, 12
95
what gets replicated What
Statement-based
row-based
insert/update/delete
statement
affected records
schema/table/view creation/drop
statement
statement
stored routine/trigger creation/drop
statement
statement
stored procedure call stored function call trigger execution
statements executed inside SP function call
affected records affected records
none: the slave runs the affected records trigger moved. No trigger runs
event creation
slaveside disabled event
slaveside disabled event
event execution
statement
affected records 95
Wednesday, April 18, 12
95
what gets replicated What
Statement-based
row-based
insert/update/delete
statement
affected records
schema/table/view creation/drop
statement
statement
stored routine/trigger creation/drop
statement
statement
stored procedure call stored function call trigger execution
statements executed inside SP function call
affected records affected records
none: the slave runs the affected records trigger moved. No trigger runs
event creation
slaveside disabled event
slaveside disabled event
event execution
statement
affected records 95
Wednesday, April 18, 12
95
what gets replicated What
Statement-based
row-based
insert/update/delete
statement
affected records
schema/table/view creation/drop
statement
statement
stored routine/trigger creation/drop
statement
statement
stored procedure call stored function call trigger execution
statements executed inside SP function call
affected records affected records
none: the slave runs the affected records trigger moved. No trigger runs
event creation
slaveside disabled event
slaveside disabled event
event execution
statement
affected records 95
Wednesday, April 18, 12
95
what gets replicated What
Statement-based
row-based
insert/update/delete
statement
affected records
schema/table/view creation/drop
statement
statement
stored routine/trigger creation/drop
statement
statement
stored procedure call stored function call trigger execution
statements executed inside SP function call
affected records affected records
none: the slave runs the affected records trigger moved. No trigger runs
event creation
slaveside disabled event
slaveside disabled event
event execution
statement
affected records 95
Wednesday, April 18, 12
95
what gets replicated What
Statement-based
row-based
insert/update/delete
statement
affected records
schema/table/view creation/drop
statement
statement
stored routine/trigger creation/drop
statement
statement
stored procedure call stored function call trigger execution
statements executed inside SP function call
affected records affected records
none: the slave runs the affected records trigger moved. No trigger runs
event creation
slaveside disabled event
slaveside disabled event
event execution
statement
affected records 95
Wednesday, April 18, 12
95
DA N E AG
Binary logs in practice
DEMO
96 Wednesday, April 18, 12
96
DA N E AG
Why replication How to set replication Using Replication
Managing replication
What Replication is for
• The web economy • Scaling out • From single server to replication • Adding a slave • Binary log formats • What gets replicated and how • Replication awareness • Monitoring • Log management • Replacing a slave • Replacing a master • master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 97
Wednesday, April 18, 12
97
From single server application
r/w requests
98 Wednesday, April 18, 12
98
To replication-aware application read/write master read/only slaves
load balancer
R/W R/O
99 Wednesday, April 18, 12
99
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 ); 100 Wednesday, April 18, 12
100
Making an application aware of replication db
db
IP user password connect
IP user password connect read write
db
db
IP user password
IP user password
read
connect read write
101 Wednesday, April 18, 12
101
Using replication: the WRONG way No
R/W split by statement Wednesday, April 18, 12
Write statement?
Yes
Connect to the next available slave
Connect to the master
Read from slave
Write to the master
Stop 102 102
Why statement split is wrong
• Breaks transactions • High risk of inconsistency • Loses or corrupts data
103 Wednesday, April 18, 12
103
Using replication: the RIGHT way No
Wednesday, April 18, 12
Yes
Connect to the next available slave
Connect to the master
Read from slave
Read and write from master
more queries?
R/W split by function
Write function?
more queries?
Yes
No
Yes
No
Stop 104 104
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 Wednesday, April 18, 12
105
Single thread replication MASTER
12:00 12:01 12:02 12:03 12:04 12:05 12:06 12:07 12:08 12:09 12:10 12:11
SLAVE
106 Wednesday, April 18, 12
106
Single thread replication MASTER QUERY 1 QUERY 2
12:00 12:01 12:02 12:03 12:04 12:05 12:06 12:07 12:08 12:09 12:10 12:11
(does nothing)
SLAVE
106 Wednesday, April 18, 12
106
Single thread replication MASTER QUERY 1 QUERY 2
12:00 12:01 12:02 12:03 12:04 12:05 12:06 12:07 12:08 12:09 12:10 12:11
(does nothing)
QUERY 1
SLAVE
106 Wednesday, April 18, 12
106
Single thread replication MASTER QUERY 1 QUERY 2
12:00 12:01 12:02 12:03 12:04 12:05 12:06 12:07 12:08 12:09 12:10 12:11
(does nothing)
QUERY 1
QUERY 2
SLAVE
106 Wednesday, April 18, 12
106
Single thread replication MASTER QUERY 1
QUERY 3
QUERY 2
QUERY 5 QUERY 4
QUERY 6
12:00 12:01 12:02 12:03 12:04 12:05 12:06 12:07 12:08 12:09 12:10 12:11
does nothing
QUERY 1
QUERY 2
SLAVE
107 Wednesday, April 18, 12
107
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 108
Wednesday, April 18, 12
108
Sample monitoring
get slave status
slave Running?
No
Yes
master
Get master binlog and position
Yes
check table contents Wednesday, April 18, 12
Same or later binlog/position?
No
alert
109 109
commands for checking replication master> SHOW MASTER STATUS master> SHOW PROCESSLIST slave> SHOW SLAVE STATUS slave> SHOW PROCESSLIST
Wednesday, April 18, 12
110
replication user seen in the master master > show processlist\G *************************** 1. row *************** Id: 2 User: rsandbox Host: localhost:57011 db: NULL Command: Binlog Dump Time: 81625 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL [...]
Wednesday, April 18, 12
111
IO-thread in the slave slave > show processlist\G *************************** 1. row *************** Id: 2 User: system user Host: db: NULL Command: Connect Time: 124801 State: Waiting for master to send event Info: NULL [...]
Wednesday, April 18, 12
112
SQL thread in the slave slave > show processlist\G [...] *************************** 2. row *************** Id: 3 User: system user Host: db: NULL Command: Connect Time: 124712 State: Slave has read all relay log; waiting for the slave I/O thread to update it Info: NULL [...]
Wednesday, April 18, 12
113
monitoring replication master> SHOW MASTER STATUS slave> SHOW SLAVE STATUS
FULL SCRIPTS: http://datacharmer.blogspot.com/2011/04/refactored-again-poor-mansmysql.html http://forge.mysql.com/tools/tool.php?id=6
Wednesday, April 18, 12
114
show master status File: mysql-bin.000002 Position: 78045744 Binlog_Do_DB: Binlog_Ignore_DB:
Wednesday, April 18, 12
115
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
Wednesday, April 18, 12
116
show slave status ... Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: ...
Wednesday, April 18, 12
117
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:
Wednesday, April 18, 12
118
DA N E AG
Breaking (and fixing) replication
DEMO
119 Wednesday, April 18, 12
119
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 120
Wednesday, April 18, 12
120
Logs rotation # server variables max-binlog-size expire-log-days # logs commands SHOW MASTER LOGS; PURGE MASTER LOGS TO 'filename'; FLUSH [BINARY] LOGS;
Wednesday, April 18, 12
121
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 122
Wednesday, April 18, 12
122
Replacing a slave
Slave crashe s
No
are there more slaves?
STOP the master
Yes
STOP one slave
add the first slave
add another slave Stop 123
Wednesday, April 18, 12
123
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 124
Wednesday, April 18, 12
124
Replacing the master Let all slaves catch up with execution
Master crashe s
STOP replication in all slaves
FIND the most up to date slave
FIND which transactions are missing from other slaves
make it the master
run missing transactions to other slaves
connect all slaves to the new master
Stop 125 Wednesday, April 18, 12
125
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 Wednesday, April 18, 12
126
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
Wednesday, April 18, 12
127
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
Wednesday, April 18, 12
128
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 Wednesday, April 18, 12
129
Changing a failed master (3)
• For each remaining slave: • Apply the missing statements •
Wednesday, April 18, 12
CHANGE MASTER TO master_host=”new_master_hostname”, master_port=new_master_port, master_log_file=”mysql-bin.xxxxxx”, master_log_pos=YYYY
130
Reasons for complexity
• No global transaction ID
Wednesday, April 18, 12
131
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
Wednesday, April 18, 12
132
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.
Wednesday, April 18, 12
133
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 134
Wednesday, April 18, 12
134
Slave = master replacement
• if: • there are no filters; • you are monitoring replication • you make sure data is consistent
135 Wednesday, April 18, 12
135
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 Wednesday, April 18, 12
136
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 137
Wednesday, April 18, 12
137
Replication = let the slave do the dirty work
Wednesday, April 18, 12
138
backup
master
slaves
STOP SLAVE remove slave from load balancer
START SLAVE Wednesday, April 18, 12
perform backup
Let slave catch up
attach slave to load balancer 139
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master master replacement backup better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 140
Wednesday, April 18, 12
140
make summary tables
master
slaves
STOP SLAVE calculate summary tables
START SLAVE Wednesday, April 18, 12
remove slave from load balancer
Let slave catch up
attach slave to load balancer 141
Partitions for heavy statistics
master innodb non partitioned
slave
slave
innodb non partitioned
innodb partitioned by range
slave MyISAM partitioned by range Wednesday, April 18, 12
142
Simulating multiple dimensions
master innodb non partitioned
slave slave innodb non partitioned ARCHIVE partitioned by range (date)
slave slave ARCHIVE partitioned by range (product) Wednesday, April 18, 12
ARCHIVE partitioned by range (location)
143
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 144
Wednesday, April 18, 12
144
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 Wednesday, April 18, 12
145
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 Wednesday, April 18, 12
146
DA N master-to-master E AG
and circular replication
DEMO
147 Wednesday, April 18, 12
147
Common filters • on the master • binlog-do-db • binlog ignore-db • on the slave • replicate-do-db • replicate-ignore-db
148 Wednesday, April 18, 12
148
Common filters • on the master • binlog-do-db • binlog ignore-db • on the slave
DO NOT USE!
• replicate-do-db • replicate-ignore-db
148 Wednesday, April 18, 12
148
do Filters
What happens when everything goes right
transactions from client all data comes to the master
binlog-do-* The master logs only some of the transactions
replicatedo-* The slave only gets some of the above
149 Wednesday, April 18, 12
149
ignore Filters
transactions from client all data comes to the master
binlog-ignore-*
The master logs all the transactions, except the ones that should be ignored
replicateignore-* The slave further filters some of the above
150 Wednesday, April 18, 12
150
filters
What happens when something goes wrong
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
151 Wednesday, April 18, 12
151
filters
What happens when something goes wrong
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
152 Wednesday, April 18, 12
152
"Safer" filters • 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%
153 Wednesday, April 18, 12
153
"Safer" filters • 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 153 Wednesday, April 18, 12
153
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 Wednesday, April 18, 12
154
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 155
Wednesday, April 18, 12
155
Read more
• The MySQL online manual
http://dev.mysql.com/doc
156 Wednesday, April 18, 12
156
High Performance MySQL
Wednesday, April 18, 12
157
MySQL High Availability
Wednesday, April 18, 12
158
Web Operations
Wednesday, April 18, 12
159
Cloud Application Architectures
Wednesday, April 18, 12
160
DA N E AG
Why replication
• The web economy • Scaling out
How to set replication
• From single server to replication • Adding a slave
Using Replication
Managing replication
What Replication is for
• • • • • • •
Binary log formats What gets replicated and how Replication awareness Monitoring Log management Replacing a slave Replacing a master
• master replacement • backup • better reads
Gotchas, tips, and tricks More info
• What to read • More replication sessions 161
Wednesday, April 18, 12
161
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 Wednesday, April 18, 12
162
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-tablechecksum 2.0
163 Wednesday, April 18, 12
163
http://www.continuent.com
Database replication and clustering
WE ARE HIRING! •Implementation/support engineer •QA engineer •Documentation writer ©Continuent 2012. Wednesday, April 18, 12
164
`
• http://www.slideshare.net/datacharmer
Wednesday, April 18, 12
165
Bonus slides
• Semi-synchronous replication
166 Wednesday, April 18, 12
166
semi-synchronous replication
• Available in 5.5 and higher • Makes sure that at least one slave has copied the data.
• Increases reliability
Wednesday, April 18, 12
167
client
master
1 commit
execute
4
returns to client
binary log
5
transaction with regular replication
2 3
slave
replication 168
Wednesday, April 18, 12
168
client
1
master
commit
execute
7
returns to client
binary log
4 6
sends transaction to slave
transaction with semisynchronous replication
2 3
slave
relay log
5
gets acknowledgement 169
Wednesday, April 18, 12
169
semi-synchronous replication in practice
• installation: • •
Wednesday, April 18, 12
it’s a plugin. Actually, two plugins
170
semi-synch replication install # in the master plugin-load=rpl_semi_sync_master=semisync_master.so rpl_semi_sync_master_enabled=1
# in each slave plugin-load=rpl_semi_sync_slave=semisync_slave.so rpl_semi_sync_slave_enabled=1
# restart all servers
Wednesday, April 18, 12
171
semi-synch replication check # in the master show 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 | +------------------------------------+-------+
Wednesday, April 18, 12
172
semi-synch replication check show status like "rpl_semi_%tx"; +-----------------------------+-------+ | variable_name | value | +-----------------------------+-------+ | RPL_SEMI_SYNC_MASTER_NO_TX | 0 | | RPL_SEMI_SYNC_MASTER_YES_TX | 0 | +-----------------------------+-------+
Wednesday, April 18, 12
173
semi-synch replication test master> 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 | +-----------------------------+-------+
Wednesday, April 18, 12
174
disabling semi-synch # for each slave set global rpl_semi_sync_slave_enabled=0; stop slave io_thread; start slave io_thread;
Wednesday, April 18, 12
175
disabled semi-synch replication test master> 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)
Wednesday, April 18, 12
176
disabled semi-synch replication test master> 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)
Wednesday, April 18, 12
177
re-enabling semi-synch # in one slave set global rpl_semi_sync_slave_enabled=1; stop slave io_thread; start slave io_thread;
Wednesday, April 18, 12
178
reenabled semi-synch replication test master> 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)
Wednesday, April 18, 12
179
