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

To change the structure of a table in a MySQL sync cluster with replicas, we recommend using the pt-online-schema-change utility from the Percona Toolkit package. This utility changes the table structure without blocking 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 table being changed, this will complicate the utility's operation. For more details, see the Features of the utility when using foreign keys subsection.

The pt-online-schema-change utility changes the structure in a copy of the table, not the original. 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.

When launched, the utility creates an empty copy of the original table, changes 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. For more details about this parameter, see the Main parameters for changing 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 pulled into the new one. If any triggers were already defined in the original table before the utility was launched, the utility will not work. For more details about triggers, see the Using Triggers section of the MySQL 8.4 documentation.

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

Features of the utility when using foreign keys

Foreign keys in MySQL allow for referencing data in other tables, maintaining relationships between them. For more details on foreign keys, see 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 change the table without the --alter-foreign-keys-method parameter. It is necessary for foreign keys to correctly point to the new table. For more details about this parameter, see the Main parameters for changing a table in a MySQL sync cluster;
foreign key names in the new table will be changed — for example, constraint_name will become _constraint_name. MySQL does not allow using identical key names in the same database. The utility adds the _ symbol to avoid name conflicts.

Change table structure

Install the Percona Toolkit package.
Ensure that there is a cluster backup and that it is working. 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 other triggers, delete them. You can save trigger definitions before deletion and restore them after the table change. For more details about triggers, see 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. A full list of parameters is provided in the DSN OPTIONS section of the pt-online-schema-change documentation by Percona Toolkit.

`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 changing a table in a MySQL sync cluster. A full list of pt-online-schema-change parameters and their descriptions can be found in the OPTIONS section of the pt-online-schema-change documentation by Percona Toolkit.

--alter	Changes the table structure using `ALTER TABLE` syntax, but without the `ALTER TABLE` keywords. To make several changes to a table, specify them separated by commas. For more details on `ALTER TABLE` syntax, see 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 real name `constraint_name`. This is necessary because the utility adds the `_` symbol to foreign key names to avoid name conflicts when copying the table; 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 if changing the table is possible, but does not actually make any changes to the structure or data. We recommend using this parameter to safely test the table change 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 if a safe table change is possible and terminates. you cannot use the `--dry-run` and `--execute` parameters together
--recursion-method	Determines the method for replica discovery in the cluster. This parameter is required for the utility to work in MySQL Sync clusters. Specify only the `--recursion-method=none` value. Using other values or omitting this parameter may result in utility failure

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.