Agnostic DB Replication With Binary Logs

54 %
46 %
Information about Agnostic DB Replication With Binary Logs

Published on November 14, 2008

Author: morphlabs



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.

Agnostic Database Replication... ...with Binary Logs

Methodology • Constant Binary Log Replication • Phase I - Logging • Phase II - Archiving • Phase III - Recovery • Failover - What to do when disaster strikes!

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

Phase I - Logging

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

Phase I - Logging PostgreSQL Example ‣ postgresql.conf # allows archiving to be done archive_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 time archive_timeout = 60 ‣ Log naming 000000010000000000000000 000000010000000000000001 ... 00000001000000000000000f

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

Phase II - Archiving

Phase II - Archiving • Ship logs to a reliable location • Ship logs regularly & often • Ensure file integrity • Do regular full dumps

Phase III - Recovery

Phase III - Recovery • Ship logs from your backup server • Ensure log ordering • Constant incremental recovery • Incremental Recovery is Replication

Phase III - Recovery PostgreSQL Example ‣ recovery.conf restore_command = '/var/pgsql/bin/warm-standby-s3 /var/pgsql/tripfile /var/pgsql/backups/WAL/%f quot;%pquot;'

Phase III - Recovery MySQL Example ‣ No built-in mechanism sudo -u mysql ./ | mysql -h localhost

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

Failover - When disaster strikes! • Ensure final log segment recovery • Make your standby DB ‘active’ • Update client apps • Start the replication chain again!

Warm Standby vs. Clustering • When to Cluster • Draw backs to clustering • The search for the holy 100% transparent clustering...

Additional Resources • interactive/wal.html • backup-and-recovery.html • point-in-time-recovery.html • backup.html

#max presentations

Add a comment

Related presentations

Related pages


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, ...
Read more

MariaDB- Replication and Binary Log Server System Variables

Replication and Binary Log Server System ... Be careful of changing the binary log format when a replication environment is already ... replicate_do_db.
Read more

MySQL Replikation – Thomas-Krenn-Wiki

Bei der Statement-Based Replikation werden im Binary Log direkt die SQL Kommandos aufgezeichnet, ... Position | Binlog_Do_DB | Binlog_Ignore_DB ...
Read more

Binary Replication Tutorial - PostgreSQL wiki

Binary Replication Tutorial. From ... Log shipping is required for most forms of binary replication. This log consists of 16MB segments ...
Read more

MySQL Replication Tutorial Presentation 2 - O'Reilly Media

MySQL Replication Tutorial ... Binary log 1. Replication ... binlog-do-db binlog-ignore-db Replication Slave replicate-do-db, ...
Read more

MySQL Lists: replication: RE: Master does not write binary ...

binlog-do-db... says "send only these databases". replicate-do-db ... • Re: Master does not write binary logs! (was: Re: Replication does not start)
Read more

Replication and Binary Log Status Variables - MariaDB ...

Replication and Binary Log Status Variables. Home; Open Questions; MariaDB; ... The following status variables are useful in binary logging and replication.
Read more

Db Replication | LinkedIn

View 590 Db Replication posts, presentations, experts, and more. ... DB Developer at Insight Global. Greater Los Angeles Area. Aviation & Aerospace. Current
Read more

MySQL :: MySQL 5.7 Reference Manual :: 6.4.4 The Binary Log

The Binary log can provide valuable information about the frequency of per table DML statements. This simple one line Linux command can provide valuable ...
Read more