MySQL Trickery

A talk that outlines the options for live MySQL database migrations. It also features a bunch of great unix tools like sed, csplit or pv.

Held at the Berlin Ruby User Group 5 February 2015.

Transcript

1. MySQL Trickery
4. Database Administration MySQL edition
7. Migrations
8. to bigint(20) varchar(255)
9. Master Slave 2nd Slave App read/writes Backup Queries
10. 16 Cores 
 64GB RAM SSD
11. Migration Planning
12. Master Slave Fake read/writes Virtual Training Setup
13. 1. ALTER TABLE
14. 2. oak-online-alter-table
15. table ghost table INSERT UPDATE TRIGGERS BATCHED INSERTS table
16. 3. Rewriting dump.sql
17. Final plan
18. 1. Rewriting the dump
19. 2. Restore tables in parallel
20. 3. Let replication catch up
21. 4. Switching application
 to new master
22. 4. Restoring replication chain
 with hot backups
23. ~15 hours
24. What could possibly go wrong?
25. SELECT * FROM table WHERE fbuserid = 123 varchar(255)
26. SELECT * FROM table WHERE fbuserid = '123'
28. Tools of trade
29. innodbbackupx
30. innobackupex \ --user=root \ --password=ChangeMe \ /var/backups/database
31. innobackupex \ --user=root \ --password=ChangeMe \ --apply-log \ /var/backups/database/2014-12-16_14-11-08/
32. pv
33. 12KiB 0:00:02 [ 169KiB/s] [ <=> ]
34. sed
35. s/\`fbuserid\` bigint(20)[^,]*,/\`fbuserid\` varchar(255) NOT NULL,/ s/\([, ]\)(\([0-9]*\),\([0-9]*\)/\1(\2,'\3'/g
36. csplit
37. pv dump.sql \ | csplit \ --silent \ --prefix=table - \ '/— Table structure for table/' {*}
38. my.cnf
39. max_connections = 810 max_user_connections = 800
40. 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
41. That’s all folks
42. Lukas @Overbryd