5.14.4. 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 Section 3.2.5, “Performing Schema Changes”.

The basic process is to temporarily shun each slave, perform the schema update, and then recover the slave back to the dataservice.

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.

Important

While a slave is in the SHUNNED state, Continuent Tungsten will have switched the server to read-only mode. You must use a user with SUPER privileges to execute the schema change statements to bypass this read-only restriction.

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 Shun slave host2 datasource host2 shun Master Shunned Slave
3 Connect to dataserver on host2 and update schema   Master Shunned Slave
4 Recover slave back datasource host2 recover Master Slave Slave
5 Ensure the slave ( host2 ) has caught up ls Master Slave Slave
6 Shun slave host3 datasource host3 shun Master Slave Shunned
7 Connect to dataserver on host3 and update schema   Master Slave Shunned
8 Recover slave back datasource host3 recover Master Slave Slave
9 Ensure the slave ( host3 ) has caught up ls Master Slave Slave
10 Switch master to host2 switch to host2 Slave Master Slave
11 Shun host1 datasource host1 shun Shunned Master Slave
12 Connect to dataserver on host1 and update schema   Shunned Master Slave
13 Recover host1 back datasource host1 recover Slave Master Slave
14 Ensure the slave ( host1 ) has caught up ls Slave Master Slave
15 Switch master back to host1 switch to host1 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.