Database Replication

bubble I propose GAIA implements its own method of lazy replication as described below as the options described in DatabaseReplicationOptions do not seem appropriate for GAIA. -- SteveMarple - 16 Mar 2007


  • One master database.
  • Many read-only slave databases.
  • Slave databases are connected to the master database with a wide area network (WAN), so assume network is slow and unreliable.


  • Must be able to update at any time.
  • Must scale to many hosts (at least 50).
  • Must tolerate network outages.
  • Update must not interrupt any connections already using the database.
  • Must be able to change the master database (expect only infrequent changes).


Use a form of lazy replication, making use of rsync to transfer database dumpfiles to the slave database servers.

  • Master database periodically creates a dump file. Create new files each time and leave some old files present so that any existing rsync operations are not broken. Remove files dumpfiles older than a certain age.
  • The name of the master host should be defined outside of the database (eg by a DNS hostname) otherwise if the master database goes down we have no automatic way to propagate the details of change of database master.
    bubble If the master goes down, how do we decide who gets to be the master? -- MikkoSyrjaesuo - 16 Mar 2007
    bubble Someone with appropriate authority should change the DNS entry so that points to a suitable host. It shouldn't matter which host is chosen, (as long as it has a reliable network connection) and isn't affected by any of the restrictions on which hosts can be the master.-- SteveMarple - 17 Mar 2007

  • The slave database server uses a web service to obtain the name of the latest dumpfile and initiates rsync to update the last dumpfile it obtained.
  • The slave database server creates a new database (eg gaia_YYYYMMDDhhmmss) and initialises it with the latest dumpfile. Any local database configuration scripts are then run (eg to install stored procedures which may not contained in the dumpfile). The slave database server then updates its record of which database the GAIA PHP code should access.
  • The slave database can then delete at its leisure any old GAIA databases. DROP on databases which are in use will fail, that is not a problem and will not break any existing connections; all old databases should get dropped on either the first or second attempt.
    todo.gif Investigate the effect PHP's persistent connections will have. As long as persistent connections timeout after a given period they should not cause any problems.


  • The first thing the master script(s) should do is to test if the host holds the master database. If it does not then quit.
  • The first thing the slave script(s) should do is to test if the host holds the master database. If it does then quit.
These actions should allow any host to swap between master and slave modes.


  • rsyncd must be available on the master node so that the slave nodes can download the database dump file. This poses a restriction on which hosts should be selected if the master node is taken offline.

Topic revision: r4 - 2007-03-18 - 04:57:07 - SteveMarple
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback