MySQL Binary Logging

Once binary logging is configured there are several important areas of application for the binary logs: scaling out, disaster recovery and simple but effective data forensics. By the end of the talk, binary logging should be considered a standard setup for both sysadmins and developers alike.

Transcript

1. MySQL Binary Logging
2. Who? Adrian Hardy I've been doing this for 9.92 years I both love and hate MySQL I just discovered Sass last week Magma Digital Development Team Lead Purveyor of fine software solutions
3. Overview
4. How to set it up? /etc/my.cnf l o g ­ b i n = b i n l o g Let's add some predictability l o g ­ b i n = / b a c k u p / m y s q l / b i n l o g b i n l o g ­ f o r m a t = S T A T E M E N T ; d e f a u l t s t o M I X E D o n s o m e v e r s i o n s e x p i r e _ l o g s _ d a y s = 1 4
5. Test it out Our query m y s q l > u s e m y d b ; m y s q l > I N S E R T I N T O u s e r s ( u s e r n a m e , c r e a t e d ) V A L U E S ( ' a d r i a n ' , N O W ( ) ) ; The binary log # 1 2 1 1 0 4 1 1 : 2 5 : 3 0 s e r v e r i d 1 e n d _ l o g _ p o s 2 1 7 I n t v a r S E T I N S E R T _ I D = 2 / * ! * / ; # a t 2 1 7 # 1 2 1 1 0 4 1 1 : 2 5 : 3 0 s e r v e r i d 1 e n d _ l o g _ p o s 3 5 5 Q u e r y t h r e a d _ i d = 2 e x e c _ t i m e = 0 e r r o r _ c o d e = 0 u s e m y d b / * ! * / ; S E T T I M E S T A M P = 1 3 5 2 0 2 8 3 3 0 / * ! * / ; I N S E R T I N T O u s e r s ( u s e r n a m e , c r e a t e d ) V A L U E S ( ' a d r i a n ' , N O W ( ) ) / * ! * / ;
6. Practical Applications
7. What can it be used for? Scaling Out Denormalisation Disaster Recovery Data investigation
8. Scaling Out
9. Scaling Out - Introduction Read heavy application is killing "DB1" Let's create a split architecture Introduce DB2 A new machine which will take the read load off DB1 Have DB2 become a MySQL Replication Slave DB2 connects to DB1, reads & executes the binary log
10. Scaling Out - Common Pitfalls In a simple setup, you can only write to DB1 Mechanism for directing read vs write queries (e.g. MySQLProxy, userland implementation) Re-architect your application to assume the worst Is the slave up? How far behind is it? Binary log replication is not bullet proof "Percona Toolkit" can help
11. Scaling Out - Master Master replication Very cool on paper Start with a hack ... Now double your problems Enjoy your nightmares
12. Denormalisation
13. Denormalisation - Introduction Definition Examples Summary tables Userland code MySQL Triggers Cron job MySQL view Basically an on-demand temporary table (for TEMPTABLE views)
14. Denormalisation - Using Binary Logs Materialised Views Why they're cool MySQL doesn't support them :( Flexviews (flexviews.googlecode.com) A process tails RBR binary logs to implement "Change Detection" Written in PHP Surprisingly powerful definition language (supports most aggregate functions)
15. Disaster Recovery
16. Disaster Recovery - Introduction So you lost your DB Table irreparably corrupted, DROP database, whatever But you have a backup right? rotating daily snapshots: automysqlbackup, XtraBackup, mysqldump If you don't have a backup, find a new job Or even a new career
17. Disaster Recovery - The setup Assuming REPAIR or "recovery mode" can't save you... (and that's another talk in itself) Re-import your backup p v / b a c k u p / m y s q l / l a t e s t . s q l . g z | m y s q l ­ u u s e r ­ p d b So your system has been restored to say, 12 hours ago Now for the magic
18. Disaster Recovery - The magic If possible, have your backups include binary log positions m y s q l d u m p ­ ­ m a s t e r ­ d a t a = 2 ­ u r o o t ­ p m y d b . . . C H A N G E M A S T E R T O M A S T E R _ L O G _ F I L E = ' b i n l o g . 0 0 0 0 0 2 ' , M A S T E R _ L O G _ P O S = 3 2 3 7 8 6 5 . . . Replay the binary log by piping it into mysql m y s q l b i n l o g b i n l o g . 0 0 0 0 2 ­ ­ s t a r t ­ p o s i t i o n = 3 2 3 7 8 6 5 | m y s q l . . .
19. Data Investigation
20. Well ... that's not supposed to happen Get your local machine to precipice Use binary logs with start and end positions to isolate the incident Get digging through text Caress the binary logs with our friends, less and grep m y s q l b i n l o g b i n l o g . * ­ ­ d a t a b a s e m y d b | g r e p ­ C 5 b a d m a n | l e s s Try to correlate timestamps in web/app logs
21. Reading List
22. Reading list - no surprises here mysqlbinlog http://dev.mysql.com/doc/refman/5.5/en/mysqlbinlog.html How to Set Up Replication http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html Percona Toolkit http://www.percona.com/doc/percona-toolkit/2.1/ Flexviews http://flexviews.googlecode.com/