MySQL Binary Logging 

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 

Overview 

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 

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 ( ) ) / * ! * / ; 

Practical Applications 

What can it be used for? Scaling Out Denormalisation Disaster Recovery Data investigation 

Scaling Out 

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 

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 

Scaling Out - Master Master replication Very cool on paper Start with a hack ... Now double your problems Enjoy your nightmares 

Denormalisation 

Denormalisation - Introduction Definition Examples Summary tables Userland code MySQL Triggers Cron job MySQL view Basically an on-demand temporary table (for TEMPTABLE views) 

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) 

Disaster Recovery 

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 

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 

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 . . . 

Data Investigation 

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 

Reading List 

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/