Download - Advanced mysql replication for the masses
![Page 1: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/1.jpg)
Advanced MySQL Replication for the
masses
Giuseppe Maxia, QA Director, Continuent, Inc
Facts. And Demos. Possibly fun
1
![Page 2: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/2.jpg)
Once Upon A Time, In The Life Of A
Database Consultant ...
2
![Page 3: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/3.jpg)
The story of a steel foundry
• Used MySQL databases to store production monitoring data
• Inserted a zillion records per second.
• Slaves often lagged behind
3
![Page 4: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/4.jpg)
BINARY LOG
REPLICATION
MySQLDBMS
trans
actio
n
trans
actio
n
trans
actio
n
trans
actio
n
trans
actio
ntra
nsac
tion
trans
actio
ntra
nsac
tion
trans
actio
ntra
nsac
tion
trans
actio
n
MySQLDBMS
transactiontransaction
transactiontransaction
transactiontransaction
transactiontransaction
transactiontransaction
transactiontransaction
4
![Page 5: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/5.jpg)
The story of a shoe maker
• Had a successful business, spread to a dozen stores.
• Needed to aggregate the data from the stores in his headquarters database.
5
![Page 6: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/6.jpg)
MySQLDBMS MySQL
DBMS
MySQLDBMS
MySQLDBMS
headquarters
store storestore
6
![Page 7: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/7.jpg)
The story of a widgets seller
• Had a successful business, designed for one server.
• Products were created in several sites.
• Needed to allow insertions from more than one master at the time
7
![Page 8: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/8.jpg)
MySQLDBMS
MySQLDBMS
MySQLDBMS
MySQLDBMS
mastermaster
master master
8
![Page 9: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/9.jpg)
All these stories tell us:
Nice dream, but MySQL
can’t do it9
![Page 10: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/10.jpg)
Enter Tungsten Replicator
10
![Page 11: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/11.jpg)
Tungsten Replicator 2.0
• What is it?
11
![Page 12: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/12.jpg)
http://code.google.com/p/tungsten-replicator
100% GPL v2Open Source
12
![Page 13: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/13.jpg)
What can it do?
• Easy failover
• Multiple masters
• Multiple sources to a single slave
• Parallel replication
• Replicate to Oracle and PostgreSQL database
13
![Page 14: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/14.jpg)
© Continuent 2010
MySQL to foreign services
BinLogs
Master DB
(Binlogs enabled) Slave DB
MySQL setup to run as
MySQL master
Data is applied to PostgreSQL or
Oracle
14
![Page 15: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/15.jpg)
From the beginning ...
15
![Page 16: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/16.jpg)
Fail-over (1)
16
![Page 17: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/17.jpg)
Fail-over (2)
17
![Page 18: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/18.jpg)
Fail-over (3)
18
![Page 19: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/19.jpg)
Fail-over (4)
19
![Page 20: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/20.jpg)
Fail-over (5)
20
![Page 21: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/21.jpg)
Fail-over (6)
21
![Page 22: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/22.jpg)
Fail-over (7)
22
![Page 23: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/23.jpg)
Fail-over (8)
23
![Page 24: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/24.jpg)
Failover
• DEMO
24
![Page 25: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/25.jpg)
master/slave with an attitude
25
![Page 26: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/26.jpg)
The steel foundry dreamor parallel replication
From here ...
26
![Page 27: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/27.jpg)
The steel foundry dreamor parallel replication
To here.
27
![Page 28: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/28.jpg)
Parallel replication facts
• Sharded by database
• Good choice for slave lag problems
• Bad choice for single database projects
28
![Page 29: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/29.jpg)
Slave Lagging: why?
• Complex queries on the master
• Long lasting DDL
• Slave restart
29
![Page 30: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/30.jpg)
Testing parallel replication
30
![Page 31: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/31.jpg)
sysbench
sysbench
sysbench
sysbench
sysbench
sysbench
sysbench
sysbench
sysbench
sysbench
db1
db2
db3
db4
db5
db6
db7
db8
db9
preparation (1)db0
31
![Page 32: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/32.jpg)
preparation (2)db0
db1
db2
db3
db4
db5
db6
db7
db8
db9
32
![Page 33: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/33.jpg)
before the test (1)db0
db1
db2
db3
db4
db5
db6
db7
db8
db9
33
![Page 34: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/34.jpg)
before the test (2)
binary logs
MySQL slave
Tungsten slave
RELAY logs
RELAY logs
IO thread
SQL thread
replicator alpha
direct: alpha(slave)
34
![Page 35: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/35.jpg)
starting the test
binary logs
MySQL slave
Tungsten slave
RELAY logs
RELAY logs
IO thread
SQL thread
replicator alpha
direct: alpha(slave)
35
![Page 36: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/36.jpg)
MySQL native replication
slave catch up in 00:59:30
36
![Page 37: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/37.jpg)
Tungsten parallel replication
slave catch up in 00:25:40
37
![Page 38: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/38.jpg)
The widget seller dream, or multi masters
• Tungsten Replicator recipe: use more services
38
![Page 39: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/39.jpg)
Bi-directional replication
39
![Page 40: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/40.jpg)
Bi-directional replication with slaves
40
![Page 41: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/41.jpg)
True multiple master
We’ll see that in a moment.But first
41
![Page 42: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/42.jpg)
The shoe maker dream, or multiple sources
• Tungsten Replicator recipe is still valid: use more services
42
![Page 43: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/43.jpg)
Multiple source replication
43
![Page 44: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/44.jpg)
Multiple masters replication: 3 nodes
44
![Page 45: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/45.jpg)
Multiple masters replication: 4 nodes
45
![Page 46: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/46.jpg)
Updating 4 masters : 1 flow
46
![Page 47: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/47.jpg)
Updating 4 masters : 2 flows
47
![Page 48: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/48.jpg)
Updating 4 masters : 3 flows
48
![Page 49: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/49.jpg)
Updating 4 masters : 4 flows
49
![Page 50: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/50.jpg)
Tungsten in practice
• installation
50
![Page 51: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/51.jpg)
Installation
• Get the binaries
• Expand the tarball
• Run ./tools/tungsten-installer
51
![Page 52: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/52.jpg)
Tools
• replicator
• trepctl
• thl
52
![Page 53: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/53.jpg)
replicator
• It’s the service provider
• You launch it once when you start
• You may restart it when you change config
53
![Page 54: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/54.jpg)
trepctl
• Tungsten Replicator ConTroLler
• It’s the driving seat for your replication
• You can start, update, and stop services
• You can get specific info
54
![Page 55: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/55.jpg)
thl
• Transaction History List
• Gives you access to the Tungsten relay logs
55
![Page 56: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/56.jpg)
ADVERTISING
56
![Page 57: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/57.jpg)
http://www.continuent.com/about/careers
WE ARE HIRING!
QA and supportengineers
57
![Page 58: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/58.jpg)
© Continuent 2011
Conclusion and Questions
58
![Page 59: Advanced mysql replication for the masses](https://reader034.vdocuments.us/reader034/viewer/2022051208/545cb00eb1af9f500a8b4848/html5/thumbnails/59.jpg)
© Continuent 2011
Worldwide560 S. Winchester Blvd., Suite 500 San Jose, CA 95128 Tel (866) 998-3642 Fax (408) 668-1009e-mail: [email protected]
Contact Information
Continuent Web Site:http://www.continuent.com
Tungsten Projecthttp://tungsten-replicator.org
59