×
Copy
Open
Link
Embed
Share
Beginning
This slide
Copy link URL
Copy link URL
Copy iframe embed code
Copy iframe embed code
Copy javascript embed code
Copy javascript embed code
Share
Tweet
Share
Tweet
Slide 1
Slide 1 text
MySQL Trickery
Slide 2
Slide 2 text
No content
Slide 3
Slide 3 text
No content
Slide 4
Slide 4 text
Database Administration MySQL edition
Slide 5
Slide 5 text
No content
Slide 6
Slide 6 text
No content
Slide 7
Slide 7 text
Migrations
Slide 8
Slide 8 text
to bigint(20) varchar(255)
Slide 9
Slide 9 text
Master Slave 2nd Slave App read/writes Backup Queries
Slide 10
Slide 10 text
16 Cores 64GB RAM SSD
Slide 11
Slide 11 text
Migration Planning
Slide 12
Slide 12 text
Master Slave Fake read/writes Virtual Training Setup
Slide 13
Slide 13 text
1. ALTER TABLE
Slide 14
Slide 14 text
2. oak-online-alter-table
Slide 15
Slide 15 text
table ghost table INSERT UPDATE TRIGGERS BATCHED INSERTS table
Slide 16
Slide 16 text
3. Rewriting dump.sql
Slide 17
Slide 17 text
Final plan
Slide 18
Slide 18 text
1. Rewriting the dump
Slide 19
Slide 19 text
2. Restore tables in parallel
Slide 20
Slide 20 text
3. Let replication catch up
Slide 21
Slide 21 text
4. Switching application to new master
Slide 22
Slide 22 text
4. Restoring replication chain with hot backups
Slide 23
Slide 23 text
~15 hours
Slide 24
Slide 24 text
What could possibly go wrong?
Slide 25
Slide 25 text
SELECT * FROM table WHERE fbuserid = 123 varchar(255)
Slide 26
Slide 26 text
SELECT * FROM table WHERE fbuserid = '123'
Slide 27
Slide 27 text
No content
Slide 28
Slide 28 text
Tools of trade
Slide 29
Slide 29 text
innodbbackupx
Slide 30
Slide 30 text
innobackupex \ --user=root \ --password=ChangeMe \ /var/backups/database
Slide 31
Slide 31 text
innobackupex \ --user=root \ --password=ChangeMe \ --apply-log \ /var/backups/database/2014-12-16_14-11-08/
Slide 32
Slide 32 text
pv
Slide 33
Slide 33 text
12KiB 0:00:02 [ 169KiB/s] [ <=> ]
Slide 34
Slide 34 text
sed
Slide 35
Slide 35 text
s/\`fbuserid\` bigint(20)[^,]*,/\`fbuserid\` varchar(255) NOT NULL,/ s/\([, ]\)(\([0-9]*\),\([0-9]*\)/\1(\2,'\3'/g
Slide 36
Slide 36 text
csplit
Slide 37
Slide 37 text
pv dump.sql \ | csplit \ --silent \ --prefix=table - \ '/— Table structure for table/' {*}
Slide 38
Slide 38 text
my.cnf
Slide 39
Slide 39 text
max_connections = 810 max_user_connections = 800
Slide 40
Slide 40 text
https://openarkkit.googlecode.com/svn/trunk/ openarkkit/doc/html/introduction.html http://www.percona.com/software/percona- toolkit http://www.ivarch.com/programs/pv.shtml http://www.gnu.org/software/coreutils/csplit
Slide 41
Slide 41 text
That’s all folks
Slide 42
Slide 42 text
Lukas @Overbryd