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

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

When started, the utility creates an empty copy of the original table, changes its schema, 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. Read more about the parameter 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 started, the utility will not work. Read more about triggers in 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 table.

Features of the utility when using foreign keys

Foreign keys in MySQL allow referencing data in other tables, maintaining relationships between them. Read 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 change the table without the --alter-foreign-keys-method parameter. It is necessary for foreign keys to correctly point to the new table. Read more about the parameter in 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 a single database. The utility adds a _ character to avoid name conflicts.

Change table structure

Install the Percona Toolkit package.
Make sure there is a backup of the cluster and that it works. 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, remove them. You can save the trigger definitions before removal and restore the triggers after changing the table. Read 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 listed the main DSN parameters for connecting to a MySQL sync cluster. The full list of parameters is in the DSN OPTIONS section of the pt-online-schema-change documentation of 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 listed 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 can be found in the OPTIONS section of the pt-online-schema-change documentation of Percona Toolkit.

--alter	Changes the table schema using `ALTER TABLE` syntax, but without the `ALTER TABLE` keywords. To make several changes to a table, separate them with commas. More details on `ALTER TABLE` syntax are 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 a `_` character 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 the possibility of changing the table, but does not make actual changes to the schema and data. We recommend using this parameter to safely test the process of changing the table before starting 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 changing the table is safe and then terminates. you cannot use the `--dry-run` and `--execute` parameters together
--recursion-method	Determines the method of finding replicas in the cluster. This parameter is mandatory for the utility to work in MySQL Sync clusters. Only specify the `--recursion-method=none` value. Using other values or omitting the parameter may lead to a 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.