3.2.5. Performing Schema Changes

This section will guide you through applying schema changes to large tables in a MSMM topology. Small DDL changes can be applied to the master and allowed to replicate through, however large schema change can cause issues such as the replicator backing up and running out of memory and high latency.

The basic process for performing a schema change within an MSMM deployment is similar to the rolling-maintenance procedure in Section 5.14.4, “Making Online Schema Changes” with a few modifications and additions to account for the differences in the MSMM environment.

This process also assumes that for Table Column changes, additional columns are APPENDED to the table, and not applied using the AFTER <colname> option. If this is in use, or you are removing columns from a table, see the additional steps at the end of this section.

Prior to starting, ensure both clusters are in MAINTENANCE mode.

Warning

The best practice is to specify SET SESSION SQL_LOG_BIN=0; during your mysql session to ensure that none of your changes are written to the MySQL binary logs. This is to prevent any of the replicators from picking up on the DDL changes and applying them to another node before you are ready. Even if the Replicators are offline when you do the change, if you do not set SQL_LOG_BIN=0, then the replicators will still see the DDL queries when they are brought back online.

Apply Changes to the Slaves

Repeat the following process for each slave node in turn

Within cctrl:

cctrl> datasource slavenode shun;
cctrl> replicator slavenode offline;

Within mysql, apply the required DDL changes:

mysql> SET SESSION SQL_LOG_BIN=0;
mysql> Your DDL commands here...

Within cctrl:
```
cctrl> datasource slavenode recover;
```

Stop Cross-site Replicators (MSMM Only)
Before we apply the changes to the final two masters, we need to temporarily offline the cross-site replicators, if we don't do this, after the switch step, there is a possibility that changes written into the new tables may force the replicator to ERROR as it tries to apply the changes to the remaining nodes that are yet to be updated. This would only happen in a situation where the table being updated is receiving writes/updates in a live environment. If the applications services have been stopped, then this would not be an issue.
At the command prompt:
```
shell> mmtrepctl offline
```

Switch and Apply to the Master

Within cctrl:

cctrl> switch
cctrl> datasource previous master shun;
cctrl> replicator previous master offline;

Within mysql, apply the required DDL changes:

mysql> SET SESSION SQL_LOG_BIN=0;
mysql> Your DDL commands here...

Within cctrl:

cctrl> datasource previous master recover;

Restart Replicators (MSMM Only)
Now that all nodes have the DDL changes applied, you may restart all the cross-site replicators.
At the command prompt:
```
shell> mmtrpectl online
```

3.2.5.1. Steps for special DDL Changes

Occasionally, you may need to add columns o the middle of a table structure using the syntax ALTER TABLE table ADD column AFTER column or you may wish to remove a column. In these circumstances the following additional steps must be taken:

Enable the ColumnNames filter in the replicators by adding the --svc-extractor-filters=colnames to tpm config for both Cluster and cross-site replicators
Restart the Replicators (Both cluster and cross-site replicators)
Follow above steps for rolling maintenance
Remove the ColumnNames filter from the configuration
Restart the Replicators

Also note, that this process may not work and/or may produce additional errors in versions earlier than Continuent Tungsten 4.0.5.

Prev	Up	Next
3.2.4. Configuring Startup on Boot	Home	3.2.6. Resetting a single dataservice