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

To change the table schema 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 schema 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 restrictions in cluster operation:

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 modified, this will complicate the utility's operation. More details are available in the Utility features when using foreign keys subsection.

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

When launched, the utility creates an empty copy of the original table, changes its schema, and then copies data row by row from the original table to the new one. For data safety, the utility does not apply changes to the table by default unless you specify the --execute parameter. More details about the parameter are in 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. More details about triggers are in the Using Triggers instruction 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 table.

Features of the utility when using foreign keys

Foreign keys in MySQL allow referencing data in other tables, maintaining relationships between them. More details about foreign keys are in the Using Foreign Keys instruction 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 necessary so that foreign keys correctly point to the new table. More details about the parameter are in the Main parameters for changing a table in a MySQL sync cluster subsection;
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 one database. The utility adds the _ character to avoid naming conflicts.

Change table structure

Install the Percona Toolkit package.
Make sure that a backup of the cluster exists and is functional. A 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 the trigger definitions before deletion and restore the triggers after modifying the table. More details about triggers are in the Using Triggers instruction 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 or 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 instruction in 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 changing 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 instruction in the Percona Toolkit documentation.

--alter	Modifies the table schema using the `ALTER TABLE` syntax, but without the `ALTER TABLE` keywords. To make multiple changes to a table, specify them separated by commas. More details about the `ALTER TABLE` syntax are in the ALTER TABLE Statement instruction 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 naming 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 the possibility of modifying the table, but does not make actual changes to the schema and data. We recommend using this parameter to safely test the table modification process before launching 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	Defines the method for finding replicas in a cluster. The parameter is required 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.