Using ALTER TABLE in MySQL sync
Using the ALTER TABLE
command in MySQL sync cloud databases may cause the cluster to stop. Instead of ALTER TABLE
, we recommend using Percona'
s pt-online-schema-change
utility.
The pt-online-schema-change
utility changes the table structure as ALTER TABLE
, but it does not block read and write operations.
Read more about using pt-online-schema-change
in the official documentation.
Use the utility only if you have a backup.
Description of pt-online-schema-change utility
The pt-online-schema-change
utility does not work on the table itself, but on a copy of it. The original table is not locked, read and write operations are still available.
The utility creates an empty copy of the table being modified, makes the necessary edits to its structure, and then copies the data from the original table.
After completing the copying of data to the new table, pt-online-schema-change
uses the RENAME TABLE
operation and renames the original and new table simultaneously. After defaulting, the original table is deleted.
The utility creates triggers in the original table to update data in the new table — any changes to the original table when copying will be reflected in the new table. If any triggers are already defined in the table, the utility will not work.
Risks of use
External keys (constraints
) are used to bind tables, more details in the official MySQL documentation.
The use of foreign keys complicates the utility and poses an additional risk. When foreign keys reference a table, atomic renaming of the original and new table does not work. The pt-online-schema-change
utility must update the foreign keys to reference the new table after a schema change is made. The problem is solved with the --alter-foreign-keys-method
parameter.
When using foreign keys, the final table will have the same foreign keys and indexes as the original table (unless you specify otherwise in ALTER
), but the object names may change slightly to avoid name collisions in MySQL and InnoDB.
For data security, changes to the table will only be made when the --execute
key is used with the utility.
Install pt-online-schema-change
-
Install the pt-online-schema-change utility in one of the following ways:
-
download the Percona Toolkit from the official website;
-
load the Percona Toolkit through the command line using the commands:
wget percona.com/get/percona-toolkit.tar.gz
wget percona.com/get/percona-toolkit.rpm
wget percona.com/get/percona-toolkit.deb -
install only the utility:
wget percona.com/get/pt-online-schema-change
-
Utility usage
Utility Syntax:
pt-online-schema-change [OPTIONS] DSN
Specify:
[OPTIONS]
— utility parameters;DSN
— DSN parameters (Data Source Name), they specify databases and tables.
Example of work
-
Add a field to
sakila.actor
:pt-online-schema-change \
h=<host>,P=6033,u=<user_name>,p=<password>,D=sakila,t=actor \
--alter "ADD COLUMN c1 INT" \
--execute \
--recursion-method=noneSpecify:
<host>
— DNS or IP address of the node;<user_name>
— database user name;<password>
— database user password.
-
Change the
ENGINE
fromsakila.actor
toInnoDB
(so that a non-blockingOPTIMIZE TABLE
query is executed ):pt-online-schema-change \
h=<host>,P=6033,u=<user>,p=<password>,D=sakila,t=actor \
--alter "ENGINE=InnoDB" \
--execute \
--recursion-method=none
Parameters pt-online-schema-change
Description of the main parameters of the pt-online-schema-change
utility:
--alter
Changing the table structure without ALTER TABLE keywords. Several changes are separated by a comma. More details in the official MySQL documentation.
Limitations that may cause the utility to malfunction:
- in most cases, the table must have
PRIMARY KEY
orUNIQUE INDEX
. This is necessary because the utility creates aDELETE
trigger to keep the new table up to date during the change process; - you cannot use
RENAME
to rename tables; - columns cannot be renamed, otherwise the data will not be copied from the original table to the new one;
- It is not possible to create columns without specifying
DEFAULT
and at the same time withNOT NULL
, you must explicitly specifyDEFAULT
; - For
DROP FOREIGN KEY
operations,_constraint_name
must be specified instead ofconstraint_name
. Due to MySQL limitations,pt-online-schema-change
adds underscores to foreign key names when creating a new table.
--ask-pass
Password request when connecting to MySQL.
--dry-run
Creating and making changes to the structure of a new table without creating triggers, without copying data and without substituting the original table.
It is not possible to use --dry-run
and --execute
together.
--execute
Without specifying this option, only preliminary checks will be performed, no changes will be made to the table structure.
It is not possible to use --dry-run
and --execute
together.
--recursion-method
Method for replica detection. More information about the parameter in the official Percona Toolkit documentation.
DSN parameters
Each parameter is written as follows:
параметр=значение
Example:
p=<password>
All parameters are case sensitive, spaces cannot be used. If a value contains spaces, it must be enclosed in quotes. Options are separated by a comma.
Parameter list:
- A
(charset
) — default character set; - D
(database
) — database name for the original and new table; - F
(mysql_read_default_file
) — use default options from the specified file; - h
(host
) — host address; - p
(password
) — password for connection. If the password contains commas, they must be escaped with a backslash or the whole value must be enclosed in quotes; - P
(port
) — the port used for connection; - S
(mysql_socket
) — the socket file used for the connection; - t
(table
) — the table, the structure of which will be modified; - u
(user
) — the user under which the connection is made.