8.15. Making Online Schema Changes

Similar to the maintenance procedure, schema changes to an underlying dataserver may need to be performed on dataservers that are not part of an active dataservice. Although many inline schema changes, such as the addition, removal or modification of an existing table definition will be correctly replicated to slaves, other operations, such as creating new indexes, or migrating table data between table definitions, is best performed individually on each dataserver while it has been temporarily taken out of the dataservice.

Note

If you are attempting an Online schema change and running in a MSMM environment, then you should follow the steps in Performing Schema Changes.

The basic process is to temporarily put each slave offline, perform the schema update, and then put the slave online and monitor it and catch up.

Operations supported by these online schema changes must be backwards compatible. Changes to the schema on slaves that would otherwise break the replication cannot be performed using the online method.

The following method assumes a schema update on the entire dataservice by modifying the schema on the slaves first. The schema shows three datasources being updated in sequence, slaves first, then the master.

Step Description Command host1 host2 host3
1 Initial state   Master Slave Slave
2 Set the slave host2 offline trepctl -host host2 offline Master Offline Slave
3 Connect to dataserver for host2 and update schema   Master Offline Slave
4 Set the slave online trepctl -host host2 online Master Slave Slave
5 Ensure the slave (host2) has caught up trepctl -host host2 status Master Slave Slave
6 Set the slave host3 offline trepctl -host host3 offline Master Slave Offline
7 Connect to dataserver for host3 and update schema   Master Slave Offline
8 Set the slave (host3) online trepctl -host host3 online Master Slave Slave
9 Ensure the slave (host3) has caught up trepctl -host host3 status Master Slave Slave
10 Switch master to host2 See Section 8.12, “Switching Master Hosts” Slave Master Slave
11 Set the slave host1 offline trepctl -host host1 offline Offline Master Slave
12 Connect to dataserver for host1 and update schema   Offline Master Slave
13 Set the slave host1 online trepctl -host host1 online Slave Master Slave
14 Ensure the slave (host1) has caught up trepctl -host host1 status Master Slave Slave
15 Switch master back to host1 See Section 8.12, “Switching Master Hosts” Master Slave Slave

Note

With any schema change to a database, the database performance should be monitored to ensure that the change is not affecting the overall dataservice performance.