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 blocking read and write operations.

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

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

For requirements, limitations, and known errors when using the utility, see the pt-online-schema-change instructions in the Percona Toolkit documentation.

If the table being modified is referenced by foreign keys, this will complicate the utility's operation. For more information, see the Features of the utility 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 in it remain available.

When launched, the utility creates an empty copy of the original table, alters its schema, and then copies data row by row from the original table to the new one. For data safety, the utility by default does not apply changes to the table unless you specify the --execute parameter. For more information about the parameter, see the Basic 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 on the original table before running the utility, the utility will not work. For more information about triggers, see the Using Triggers instructions in the MySQL 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 you to reference data in other tables, maintaining relationships between them. For more information about foreign keys, see the Using Foreign Keys instructions in the MySQL documentation.

If foreign keys reference the table being changed:

the utility will not be able to alter the table without the --alter-foreign-keys-method parameter. It is required for foreign keys to correctly point to the new table. For more information about the parameter, see the Basic 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 duplicate key names in the same database. The utility adds the _ character to avoid naming conflicts.

Change table structure

Install the Percona Toolkit package.
Make sure you have a working backup of the cluster. 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 deleting and restore the triggers after altering the table. For more information about triggers, see the Using Triggers instructions in the MySQL 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> — DNS or IP address of the node;
  - <port> — connection port;
  - <database_user_name> — database username;
  - <password> — database user password;
  - <database_name> — database name;
  - <table> — table name;
- parameters for changing the table:
  - <command> — command to alter the table;
  - --recursion-method=none — parameter to disable replica discovery;
  - --execute — parameter to apply 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 must be no spaces before and after =. If a value contains spaces, enclose it in quotation marks;
parameters are separated by commas.

We listed 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 instructions 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 listed the main pt-online-schema-change parameters for altering 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 instructions in the Percona Toolkit documentation.

--alter	Alters 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. For more information about the `ALTER TABLE` syntax, see the ALTER TABLE Statement instructions in the MySQL 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 correctly synchronizes the new table with the original one; in `DROP FOREIGN KEY constraint_name`, specify `_constraint_name` instead of the real name `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 `NOT NULL` columns without an explicit default value
--alter-foreign-keys-method	Determines how the utility handles foreign keys
--dry-run	Checks whether the table can be altered, but makes no actual changes to the schema or data. We recommend using this parameter to safely test the table alteration process before running the utility with the `--execute` parameter. Using the `--dry-run` and `--execute` parameters together is not possible
--execute	Applies changes to the table. Without this parameter, the utility only checks for the possibility of safely modifying the table and terminates. Using the `--dry-run` and `--execute` parameters together is not possible
--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 lead to 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 — DNS or IP address of the node;
P=6033 — connection port;
u=database_user_name — database username;
p=password — database user password;
D=database_name — database name;
t=table — table name;
--alter "ADD COLUMN new_column INT" — command to add a column to the table;
--execute — parameter to apply changes to the table;
--recursion-method=none — parameter to disable 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 — DNS or IP address of the node;
P=6033 — connection port;
u=database_user_name — database username;
p=password — database user password;
D=database_name — database name;
t=table — table name;
--alter "ENGINE=InnoDB" — command to change the storage engine of the table;
--execute — parameter to apply changes to the table;
--recursion-method=none — parameter to disable replica discovery.