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 external keys refer to the table to be modified, it will complicate the utility's work.For more details, see the subsection Peculiarities of the utility's work when using external 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, the utility does not apply changes to the table by default unless you specify the --execute parameter.For more information about the parameter, see Basic Parameters for Modifying a Table in a MySQL sync Cluster.

To synchronize data between tables, the utility creates triggers — this allows you to automatically pull data from the original table into the new table if any changes are made to it.If any triggers were already defined in the original table before the utility was launched, the utility will not work.For more information about triggers, see Using Tr iggers in MySQL documentation.

When all the data is copied, the utility renames the original and new tables simultaneously.Then it 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 reference data in other tables, maintaining relationships between them.Learn more about foreign keys in the Using Foreign Keys instruction of the 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 the Basic parameters for changing a table in MySQL sync cluster;
The foreign key names in the new table will be changed — for example, constraint_name will become _constraint_name.MySQL does not allow the use of identical key names in the same database.The utility adds the _ character to avoid name conflicts.

Change table structure

Install the Percona Toolkit package.
Make sure there is a backup of the cluster and it is working.The backup will allow you to restore the cluster in case of data loss due to a possible utility failure.
If the table already has any other triggers, delete them.You can save the trigger definitions before deleting and restore the triggers after the table is modified.Learn more about triggers in the Using Triggers instruction of 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 complete list of parameters is specified in the DSN OPTIONS section of the pt-online-schema-change instruction pt-online-schema-change section 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.For a complete list of pt-online-schema-change parameters and their descriptions, see the OPTIONS section of the pt-online-schema-change instructions of 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 if the table can be modified, but does not make any actual changes to the structure or data.We recommend using this parameter to safely test the table modification process before running the utility with the `--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 for finding replicas in the cluster.The parameter is required for the utility to work in MySQL Sync clusters.Specify only the value `--recursion-method=none`.Using other values or missing the parameter may cause the utility to fail.

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.