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.
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.
The following procedure assumes that you are using a generated SQL file to perform the data imports and run MySQL commands.
You need the ID for each of your node VMs to run RSU.
To retrieve node VM IDs, do the following:
To retrieve your service instance GUID, run the following command:
cf service MY-INSTANCE --guid
YOUR-INSTANCEis the name of your MySQL for PCF service instance. Record the guid returned in the output.
To identify the node VMs in your cluster, run the following command:
bosh -d service-instance_GUID vms
GUIDis the guid you recorded in the previous step.
To run RSU, for each node VM in your deployment, do the following:
To BOSH SSH into your node VM, do the procedure in BOSH SSH in the Pivotal Cloud Foundry documentation.
To set enable
global read_onlyas an admin user, run the following command:
mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf -e "set global read_only=on;"
To verify that the node is unhealthy, do the procedure in Monitoring Node Health.
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.
set wsrep_osu_method=rsu; set global wsrep_desync=on; ... set global wsrep_desync=off;
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
DATABASE-NAMEis the name of the database to use.
SQL-FILE-PATHis the path to your import file.
global read_only, run the following command:
$ mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf -e "set global read_only=off;""
To verify that the node is healthy, do the procedure in Monitoring Node Health.
Repeat the above procedure for your next node.
At this point your cluster should be stable and running with the new schema.