agnostic db replication with binary logs
DESCRIPTION
Database replication can be quite demanding. It can take up a great deal of time and money to implement, even with a simple application. What's more, if you are a startup, you might not have a lot of resources to start with.When you overlook replication and backup, unplanned events such as a downtime can cripple your Web application. Or worse yet, have significant negative impact on your overall business.In this presentation, we'll discuss how to overcome the challenges of delivering reliable Web applications. For info about the recorded webinar, please contact us.TRANSCRIPT
www.mor.ph
Methodology
• Constant Binary Log Replication
• Phase I - Logging
• Phase II - Archiving
• Phase III - Recovery
• Failover - What to do when disaster strikes!
www.mor.ph
Methodology• Agnosticism?
• The methodology described herein is not bound to one vendor’s DB
• With proper scripting your code should be reusable for many vendors DB solutions
• Non-Invasive
• Drastically reduced downtime compared to traditional restore from backup methods
www.mor.ph
Phase I - Logging
• Set your DB server to create binary logs
• Make logs a manageable size (<50MB)
• To timeout or not to timeout?
• Ensure time based sort-ability
www.mor.ph
Phase I - LoggingPostgreSQL Example
‣ postgresql.conf
‣ Log naming
# allows archiving to be donearchive_mode = on # command to use to archive a logfile segment archive_command = '/path/to/your/archiving/script %p'# force a logfile segment switch after this timearchive_timeout = 60
000000010000000000000000000000010000000000000001...00000001000000000000000f
www.mor.ph
Phase I - LoggingMySQL Example
‣ my.cnf
‣ Log naming
# Where to store your binary logslog-bin=/var/log/mysql/bin/mysql-bin-log
#Max size for each log segmentmax_binlog_size = 50MB
mysql-bin-log.0001mysql-bin-log.0002...
www.mor.ph
Phase II - Archiving
• Ship logs to a reliable location
• Ship logs regularly & often
• Ensure file integrity
• Do regular full dumps
www.mor.ph
Phase III - Recovery
• Ship logs from your backup server
• Ensure log ordering
• Constant incremental recovery
• Incremental Recovery is Replication
www.mor.ph
Phase III - RecoveryPostgreSQL Example
‣ recovery.conf
restore_command = '/var/pgsql/bin/warm-standby-s3 \/var/pgsql/tripfile /var/pgsql/backups/WAL/%f "%p"'
www.mor.ph
Phase III - RecoveryMySQL Example
‣ No built-in mechanism
sudo -u mysql ./latest-dump-s3.pl | mysql -h localhost
www.mor.ph
Failover - When disaster strikes!
• “When” NOT “If”
• Always assume your DB will go down
• Have a failover plan A
• Have a failover plan B
• Best laid plan will fall to waste if you do not test/validate regularly
www.mor.ph
Failover - When disaster strikes!
• Ensure final log segment recovery
• Make your standby DB ‘active’
• Update client apps
• Start the replication chain again!
www.mor.ph
Warm Standby vs. Clustering
• When to Cluster
• Draw backs to clustering
• The search for the holy 100% transparent clustering...
www.mor.ph
Additional Resources
• http://www.postgresql.org/docs/8.3/interactive/wal.html
• http://dev.mysql.com/doc/refman/6.0/en/backup-and-recovery.html
• http://dev.mysql.com/doc/refman/6.0/en/point-in-time-recovery.html
• http://dev.mysql.com/doc/refman/6.0/en/backup.html