Changing table structure in 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. 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 can cause limitations in cluster operation:

table in the master node will be unavailable for the duration of the structure change;
replicas may lag behind the master node, replication may stop.

Operating principle of pt-online-schema-change

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

If the table to be modified is referenced by foreign keys, it will complicate the utility operation. For more details, see the subsection Peculiarities of the utility operation when using foreign keys.

The pt-online-schema-change utility changes the structure 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 in it remain available.

At startup, the utility creates an empty copy of the original table, modifies its structure, and then copies the data from the original table to the new table line by line. For data security, by default the utility does not apply changes to the table unless you specify the --execute parameter. For more information about the parameter, see the Basic Parameters for Modifying a Table in a MySQL sync Cluster subsection.

To synchronize data between tables, the utility creates triggers — this allows any changes to the data in the original table to be automatically pulled into the new table. If any triggers have already been defined in the original table before launching the utility, the utility will not work. For more information about triggers, see Using Triggers in MySQL documentation.

When all data is copied, the utility renames the original and new tables simultaneously. Then replaces the original table with the new table and deletes the original table.

Peculiarities of the utility operation when using external keys

Foreign keys in MySQL allow you to refer to data in other tables, maintaining relationships between them. For more information about foreign keys, see Using Foreign Keys in MySQL documentation.

If the table being modified is referenced by foreign keys:

the utility will not be able to change 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 Basic parameters for changing a table in 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 the same key names in the same database. The utility adds the _ character to avoid name conflict.

Change table structure

Install the Percona Toolkit package.
Make sure that there is a backup of the cluster and that it is working. The backup will allow you to restore the cluster in case of data loss due to a possible utility failure.
If there are already any other triggers in the table, delete them. You can save the trigger definitions before deleting them and restore the triggers after modifying the table. For more information about triggers, see Using Triggers in the MySQL documentation.
Open the CLI.
Change the structure of the table:
```
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 to connect to the cluster:
  - <host> — DNS or IP address of the node;
  - <port> — port for connection;
  - <database_user_name> — database user name;
  - <password> — database user password;
  - <database_name> — database name;
  - <table> — table name;
- parameters to modify the table:
  - <command> — command to modify the table;
  - --recursion-method=none — parameter to disable replica search;
  - --execute — parameter to apply changes to the table;
  - optional: <options> — additional parameters to modify the table.

Basic DSN parameters for connecting to MySQL sync cluster

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

Parameter record 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 the value contains spaces, enclose it in quotes;
parameters are separated by commas.

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

`D` (`database`)	Database Name
`h` (`host`)	DNS or IP address of the node
`p` (`password`)	The password for the database user. 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

Basic parameters for changing a table in a MySQL sync cluster

We have specified the basic 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 specified in the OPTIONS section of the pt-online-schema-change instructions in 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. For more information about `ALTER TABLE` syntax, see the ALTER TABLE Statement instructions in the MySQL documentation. Limitations are set for the parameter. If they are not observed, the utility may malfunction: table must have `PRIMARY KEY` or `UNIQUE INDEX` for the `DELETE` trigger to correctly synchronize the new table with the original table; in `DROP FOREIGN KEY constraint_name` specify `_constraint_name` instead of the real `constraint_name`. This is necessary because the utility adds the `_` character to foreign key names to avoid name conflicts when copying the table; do not use `RENAME` to rename tables; do not rename columns or the 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 whether the table can be modified, but does not make actual changes to the structure and data. It is recommended to use this parameter to safely test the table modification process before running the utility with `--execute` parameter. Using the `--dry-run` and `--execute` parameters together is not possible
--execute	Applies the changes to the table. Without this parameter, the utility only checks if the table can be safely modified and terminates. Using the `--dry-run` and `--execute` parameters together is not possible
--recursion-method	Specifies the method of searching for replicas in the cluster. The parameter is mandatory for the utility to work in MySQL Sync clusters. Specify only the value `--recursion-method=none`. Use of other values or the absence of the parameter may cause the utility to malfunction

Examples of using the utility

Add a new column to the 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

Here:

h=host — DNS or IP address of the node;
P=6033 — port to connect to;
u=database_user_name — database user name;
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 search.

Change the mechanism for storing data in a table 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

Here:

h=host — DNS or IP address of the node;
P=6033 — port to connect to;
u=database_user_name — database user name;
p=password — database user password;
D=database_name — database name;
t=table — table name;
--alter "ENGINE=InnoDB" — command to change the mechanism of data storage in the table;
--execute — parameter to apply changes to the table;
--recursion-method=none — parameter to disable replica search.