Changing the structure of tables in a MySQL sync cluster with replicas

To change the table structure in a MySQL Sync cluster with replicas, we recommend using the pt-online-schema-change utility from the Percona Toolkit package. This utility modifies the table structure without locking read and write operations.

We do not recommend using the standard ALTER TABLE command in MySQL Sync clusters with replicas. It may cause performance limitations in the cluster:

the table in the master node will be unavailable while the structure is being changed;
replicas may lag behind the master node, and replication may stop.

How pt-online-schema-change works

Requirements, limitations, and known issues when using the utility can be found in the pt-online-schema-change documentation by Percona Toolkit.

If foreign keys reference the modified table, the utility operation will be complicated. Learn more in the Operating features of the utility when using foreign keys subsection.

The pt-online-schema-change utility modifies the structure of a table copy rather than the original table. The copy becomes the new version of the table. Because of this, the original table is not locked, and read and write operations remain available on it.

When launched, the utility creates an empty copy of the original table, modifies its structure, and then copies data from the original table to the new one row by row. For data safety, the utility does not apply changes to the table by default unless you specify the --execute parameter. Learn more about the parameter in the Main parameters for modifying a table in a MySQL Sync cluster subsection.

To synchronize data between tables, the utility creates triggers — this allows any data changes in the original table to be automatically propagated to the new one. If any triggers were already defined in the original table before running the utility, the utility will not work. Learn more about triggers in the Using Triggers section of the MySQL 8.4 documentation.

Once all data has been copied, the utility simultaneously renames the original and new tables. It then replaces the original table with the new one and deletes the original table.

Features of the utility when using foreign keys

Foreign keys in MySQL allow referencing data in other tables, maintaining relationships between them. Learn more about foreign keys in the Using Foreign Keys section of the MySQL 8.4 documentation.

If foreign keys reference the table being changed:

the utility will not be able to modify the table without the --alter-foreign-keys-method parameter. It is required so that foreign keys correctly point to the new table. Learn more about the parameter in the Main parameters for modifying a table in a MySQL Sync cluster subsection;
foreign key names in the new table will be modified — for example, constraint_name will become _constraint_name. MySQL does not allow using identical key names in one database. The utility adds the _ character to avoid name conflicts.

Change table structure

Install the Percona Toolkit package.
Ensure that there is a backup of the cluster and that it is operational. The backup will allow you to restore the cluster in case of data loss due to a potential utility failure.
If the table already has any other triggers, delete them. You can save trigger definitions before deletion and restore the triggers after table modification. Learn more about triggers in the Using Triggers section of the MySQL 8.4 documentation.
Open the CLI.
Change the table structure:
```
pt-online-schema-change \
  h=<host>,P=<port>,u=<database_user_name>,p=<password>,D=<database_name>,t=<table> \
  --alter "<command>" \
  --recursion-method=none \
  --execute \
  <options>
```
Specify:
- DSN parameters for connecting to the cluster:
  - <host> — the DNS or IP address of the node;
  - <port> — the port for the connection;
  - <database_user_name> — the database user name;
  - <password> — the database user password;
  - <database_name> — the database name;
  - <table> — the table name;
- parameters for changing the table:
  - <command> — the command to change the table;
  - --recursion-method=none — the parameter for disabling replica discovery;
  - --execute — the parameter for applying changes to the table;
  - optional: <options> — additional parameters for changing the table.

Main DSN parameters for connecting to a MySQL sync cluster

DSN (Data Source Name) parameters are parameters for connecting to the cluster.

Parameter format:

each parameter is specified as parameter=value — for example, p=password;
parameters are case-sensitive. For example, P and p are different parameters;
there should be no spaces before and after =. If the value contains spaces, enclose it in quotes;
parameters are separated by commas.

We have specified the main DSN parameters for connecting to a MySQL Sync cluster. The full list of parameters is provided in the DSN OPTIONS section of the pt-online-schema-change section of the Percona Toolkit documentation.

`D` (`database`)	Database name
`h` (`host`)	DNS or IP address of the node
`p` (`password`)	Database user password. If the password contains commas, escape them with a backslash or enclose the entire value in quotes
`P` (`port`)	Port for connecting to the cluster
`t` (`table`)	Table name
`u` (`user`)	Database user name

Main parameters for changing a table in a MySQL sync cluster

We have specified the main pt-online-schema-change parameters for modifying a table in a MySQL Sync cluster. The full list of pt-online-schema-change parameters and their descriptions are provided in the OPTIONS section of the pt-online-schema-change section of the Percona Toolkit documentation.

--alter	Modifies the table structure using `ALTER TABLE` syntax, but without the `ALTER TABLE` keywords. To make multiple changes to a table, specify them separated by commas. Learn more about the `ALTER TABLE` syntax in the ALTER TABLE Statement section of the MySQL 8.0 documentation. There are restrictions on the parameter. Failure to comply may cause the utility to malfunction: the table must have a `PRIMARY KEY` or `UNIQUE INDEX` so that the `DELETE` trigger can correctly synchronize the new table with the original; in `DROP FOREIGN KEY constraint_name` specify `_constraint_name` instead of the actual `constraint_name`. This is necessary because the utility adds the `_` character to foreign key names to avoid name conflicts when copying tables; do not use `RENAME` to rename tables; do not rename columns, otherwise data will not be copied; do not add columns with `NOT NULL` without an explicit default value
--alter-foreign-keys-method	Determines how the utility handles foreign keys
--dry-run	Checks the possibility of modifying the table but does not make actual changes to the structure or data. We recommend using this parameter to safely test the table modification process before running the utility with the `--execute` parameter. you cannot use the `--dry-run` and `--execute` parameters together
--execute	Applies changes to the table. Without this parameter, the utility only checks for the possibility of safely modifying the table and terminates. you cannot use the `--dry-run` and `--execute` parameters together
--recursion-method	Determines the method for finding replicas in the cluster. This parameter is mandatory for the utility to work in MySQL Sync clusters. Specify only the `--recursion-method=none` value. Using other values or omitting the parameter may cause the utility to fail

Utility usage examples

Add a new column to a table

pt-online-schema-change \
    h=host,P=6033,u=database_user_name,p=password,D=database_name,t=table \
    --alter "ADD COLUMN new_column INT" \
    --execute \
    --recursion-method=none

Where:

h=host — the node DNS or IP address;
P=6033 — the connection port;
u=database_user_name — the database user name;
p=password — the database user password;
D=database_name — the database name;
t=table — the table name;
--alter "ADD COLUMN new_column INT" — the command to add a column to a table;
--execute — the parameter for applying table changes;
--recursion-method=none — the parameter for disabling replica discovery.

Change the table storage engine to InnoDB

pt-online-schema-change \
    h=host,P=6033,u=database_user_name,p=password,D=database_name,t=table \
    --alter "ENGINE=InnoDB" \
    --execute \
    --recursion-method=none

Where:

h=host — the node DNS or IP address;
P=6033 — the connection port;
u=database_user_name — the database user name;
p=password — the database user password;
D=database_name — the database name;
t=table — the table name;
--alter "ENGINE=InnoDB" — the command to change the table storage engine;
--execute — the parameter for applying table changes;
--recursion-method=none — the parameter for disabling replica discovery.