LATEST VERSION: 2.5 - RELEASE NOTES

Using Rolling Schema Upgrade (HA Cluster)

Rolling Schema Upgrade (RSU) is a method for processing schema upgrades. During RSU, nodes are desynchronized from your cluster so that schema upgrades do not block the rest of the nodes. Operators can then manually apply data definition language (DDL) statements to the desynchronized nodes individually.

For more information about RSU, see wsrep_OSU_method in the Percona XtraDB Cluster documentation.

Using RSU for DDL

If you are modifying a large number of rows, you must use RSU. Large transactions fail under Total Order Isolation (TOI). For more information about TOI, see wsrep_OSU_method in the Percona XtraDB Cluster documentation.

Pivotal recommends developers run migrations on a test environment to gauge how long the production DDL will take. If this is longer than the cluster can be unavailable, contact your operator to do the following procedure.

Note: DDL statements are applied to nodes one at a time. Because of this, the statements must be backwards compatible with the previous schema.

Prerequisite

The following procedure assumes that you are using a generated SQL file to perform the data imports and run MySQL commands.

Retrieve Node VM ID

You need the ID for each of your node VMs to run RSU.

To retrieve node VM IDs, do the following:

  1. To retrieve your service instance GUID, run the following command:

    cf service MY-INSTANCE --guid
    

    Where YOUR-INSTANCE is the name of your MySQL for PCF service instance. Record the guid returned in the output.

  2. To identify the node VMs in your cluster, run the following command:

    bosh -d service-instance_GUID vms
    

    Where GUID is the guid you recorded in the previous step.

Run RSU

To run RSU, for each node VM in your deployment, do the following:

  1. To BOSH SSH into your node VM, do the procedure in BOSH SSH in the Pivotal Cloud Foundry documentation.

  2. To set enable global read_only as an admin user, run the following command:

    mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf -e "set global read_only=on;"
    
  3. To verify that the node is unhealthy, do the procedure in Monitoring Node Health.

  4. Add the following to the SQL file you generated:

    • set wsrep_osu_method=rsu; to the top of the import file.
    • set global wsrep_desync=on; to the top of the import file.
    • set global wsrep_desync=off; to the bottom of the import file.

    For example:

    set wsrep_osu_method=rsu;
    set global wsrep_desync=on;
    ...
    set global wsrep_desync=off;
    
  5. To execute the SQL file, run the following command:

    mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf -e -D DATABASE-NAME < SQL-FILE-PATH
    

    Where:

    • DATABASE-NAME is the name of the database to use.
    • SQL-FILE-PATH is the path to your import file.
  6. To disable global read_only, run the following command:

    $ mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf -e "set global read_only=off;""
    
  7. To verify that the node is healthy, do the procedure in Monitoring Node Health.

  8. Repeat the above procedure for your next node.

At this point your cluster should be stable and running with the new schema.

Create a pull request or raise an issue on the source for this page in GitHub