Setting up replication

Twospot can be used locally in multiple locations sharing the same data, to do this you set up MySQL circular replication and enter different key sequences for each server. The data will travel round the replication chain and stop when it returns to the server where it was first entered.

Servers are numbered both in the MySQL setup and in the TwoSpot setup, they don't have to match, but it's convenient to have the same number for both to avoid confusion. Each server must have it's own sequence interval defined in the TwoSpot layer, server 1 starts at 1 and ends at 999999, server 2 starts at 1000000 and ends at 1999999 for example. This is set in the local/conndb.inc.php that also defines how to connect to the database. The three variables server_id, server_min_no and server_max_no will make sure that any auto increment value will be within those intervals. If a server runs out of digits within the sequence the update that fails will be stored in an update log and the user is notified that the update failed. If this happens, the right thing to do is to assign a new previously unused interval for the server.

To get the clone started it's handy to use the backup script above, note the --record_log_pos directive in the mysqlhotcopy statement. The row inserted by that can be used start a new slave or repair a broken slave.

When using the replication scheme described above it's possible to have every server in operation at the same time to spread the amount of work being done amongst the servers and scale the system to many more concurrent users. It's also possible to have a simple one way replication set up and only use the spare machine as a hot backup in case the master goes down due to hardware failure or similar. In either case, it's convenient to run the backups on the least busy server.