Skip to main content
Using ALTER TABLE in MySQL sync
Last update:

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.

For your information

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⁠

  1. Connect to the database.

  2. 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:

Example of work⁠

  1. 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=none

    Specify:

    • <host> — DNS or IP address of the node;
    • <user_name> — database user name;
    • <password> — database user password.

  2. Change the ENGINE from sakila.actor to InnoDB (so that a non-blocking OPTIMIZE 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 or UNIQUE INDEX. This is necessary because the utility creates a DELETE 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 with NOT NULL, you must explicitly specify DEFAULT;
  • For DROP FOREIGN KEY operations, _constraint_name must be specified instead of constraint_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.