Continuent Ltd

Database Replication and Clustering

Continuent Documentation

Search:
Manual Home
Continuent Tungsten 4.0 Manual
Page in Other Manuals
Tungsten Replicator 4.0 Manual
Tungsten Clustering for MySQL 5.0 Manual
Breadcrumbs
» Continuent Tungsten 4.0 Manual

       » 3. Deployment: MySQL Topologies

       » 3.2. Deploying Multisite/Multimaster Clusters
Related Pages
5.14.4. Making Online Schema Changes
Chapters
Preface
1. Introduction
2. Deployment
3. Deployment: MySQL Topologies
4. Deployment: Advanced
5. Operations Guide
6. Tungsten Connector
7. Tungsten Manager
8. Command-line Tools
9. The tpm Deployment Command
10. Replication Filters
11. Performance and Tuning
A. Release Notes
B. Prerequisites
C. Terminology Reference
D. Files, Directories, and Environment
E. Internals
F. Troubleshooting
G. Frequently Asked Questions (FAQ)
H. Ecosystem Support
I. Configuration Property Reference
Navigate Up
3.2. Deploying Multisite/Multimaster Clusters
Parent Sections
3.2.4. Configuring Startup on Boot
3.2.5. Performing Schema Changes
3.2.6. Resetting a single dataservice
3.2.7. Resetting all dataservices
3.2.8. Adding a new Cluster/Dataservice
3.2.9. Enabling SSL for Replicators Only
3.2.10. Provisioning during live operations
3.2.11. Dataserver maintenance
3.2.5. Performing Schema Changes
Prev 3.2. Deploying Multisite/Multimaster Clusters Next

3.2.5. Performing Schema Changes

3.2.5.1. Steps for special DDL Changes

Warning

The procedures in this section are designed for the pre-v6.x Multisite/Multimaster topology ONLY. Do NOT use these procedures with version 6.x Multisite Clusters.

For version 6.x Multisite Clustering, please refer to Deploying Composite Multimaster Clustering.

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.

  1. Apply Changes to the Slaves

    Repeat the following process for each slave node in turn

    1. Within cctrl: 

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

    2. Within mysql, apply the required DDL changes: 

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

    3. Within cctrl: 

      cctrl> datasource slavenode recover;

  2. 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

  3. Switch and Apply to the Master

    1. Within cctrl: 

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

    2. Within mysql, apply the required DDL changes: 

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

    3. Within cctrl: 

      cctrl> datasource previous master recover;

  4. 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

Warning

The procedures in this section are designed for the pre-v6.x Multisite/Multimaster topology ONLY. Do NOT use these procedures with version 6.x Multisite Clusters.

For version 6.x Multisite Clustering, please refer to Deploying Composite Multimaster Clustering.

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:

  1. Enable the ColumnNames filter in the replicators by adding the --svc-extractor-filters=colnames to tpm config for both Cluster and cross-site replicators

  2. Restart the Replicators (Both cluster and cross-site replicators)

  3. Follow above steps for rolling maintenance

  4. Remove the ColumnNames filter from the configuration

  5. 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