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.15.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
The best practice is to specify
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
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:
Switch and Apply to the Master
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:
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: