Published on November 14, 2008
Agnostic Database Replication... ...with Binary Logs 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 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 - Logging PostgreSQL Example ‣ postgresql.conf # allows archiving to be done archive_mode = on # command to use to archive a logﬁle segment archive_command = '/path/to/your/archiving/script %p' # force a logﬁle segment switch after this time archive_timeout = 60 ‣ Log naming 000000010000000000000000 000000010000000000000001 ... 00000001000000000000000f www.mor.ph
Phase I - Logging MySQL Example ‣ my.cnf # Where to store your binary logs log-bin=/var/log/mysql/bin/mysql-bin-log #Max size for each log segment max_binlog_size = 50MB ‣ Log naming mysql-bin-log.0001 mysql-bin-log.0002 ... www.mor.ph
Phase II - Archiving www.mor.ph
Phase II - Archiving • Ship logs to a reliable location • Ship logs regularly & often • Ensure ﬁle integrity • Do regular full dumps www.mor.ph
Phase III - Recovery 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 - Recovery PostgreSQL Example ‣ recovery.conf restore_command = '/var/pgsql/bin/warm-standby-s3 /var/pgsql/tripﬁle /var/pgsql/backups/WAL/%f quot;%pquot;' www.mor.ph
Phase III - Recovery MySQL 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 ﬁnal 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 www.mor.ph
The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified ... If you are using replication, ...
Replication and Binary Log Server System ... Be careful of changing the binary log format when a replication environment is already ... replicate_do_db.
Bei der Statement-Based Replikation werden im Binary Log direkt die SQL Kommandos aufgezeichnet, ... Position | Binlog_Do_DB | Binlog_Ignore_DB ...
Binary Replication Tutorial. From ... Log shipping is required for most forms of binary replication. This log consists of 16MB segments ...
MySQL Replication Tutorial ... Binary log 1. Replication ... binlog-do-db binlog-ignore-db Replication Slave replicate-do-db, ...
binlog-do-db... says "send only these databases". replicate-do-db ... • Re: Master does not write binary logs! (was: Re: Replication does not start)
Replication and Binary Log Status Variables. Home; Open Questions; MariaDB; ... The following status variables are useful in binary logging and replication.
View 590 Db Replication posts, presentations, experts, and more. ... DB Developer at Insight Global. Greater Los Angeles Area. Aviation & Aerospace. Current
The Binary log can provide valuable information about the frequency of per table DML statements. This simple one line Linux command can provide valuable ...