Any DDL statement that runs for the database, such as CREATE TABLE or GRANT, upgrades the schema. These DDL statements change the database itself and are non-transactional.
Galera Cluster processes schema upgrades in two different methods:
You can set the method for online schema upgrades by using the wsrep_OSU_method parameter in the configuration file, (my.ini or my.cnf, depending on your build) or through the MySQL client. Galera Cluster defaults to the Total Order Isolation method.
See Also: See also the pt-online-schema-change in the Percona Toolkit for an alternative approach to schema changes.
When you want your online schema upgrades to replicate through the cluster and don’t mind that other transactions will be blocked while the cluster processes the DDL statements, use the Total Order Isolation method.
SET GLOBAL wsrep_OSU_method='TOI';
In Total Order Isolation, queries that update the schema replicate as statements to all nodes in the cluster. The nodes wait for all preceding transactions to commit then, simultaneously, they execute the schema upgrade in isolation. For the duration of the DDL processing, no other transactions can commit.
The main advantage of Total Order Isolation is its simplicity and predictability, which guarantees data consistency.
In addition, when using Total Order Isolation, you should take the following particularities into consideration:
When you want to maintain high-availability during schema upgrades and can avoid conflicts between new and old schema definitions, use the Rolling Schema Upgrade method.
SET GLOBAL wsrep_OSU_method='RSU';
In Rolling Schema Upgrade, queries that update the schema are only processed on the local node. While the node processes the schema upgrade, it desynchronizes with the cluster. When it finishes processing the schema upgrade it applies delayed replication events and synchronizes itself with the cluster.
To upgrade the schema cluster-wide, you must manually execute the query on each node in turn. Bear in mind that during a rolling schema upgrade the cluster continues to operate, with some nodes using the old schema structure while others use the new schema structure.
The main advantage of the Rolling Schema Upgrade is that it only blocks one node at a time.
The main disadvantage of the Rolling Schema Upgrade is that it is potentially unsafe, and may fail if the new and old schema definitions are incompatible at the replication event level.
Warning: To avoid conflicts between new and old schema definitions, execute operations such as CREATE TABLE and DROP TABLE using the Total Order Isolation method.