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

Using ALTER TABLE in MySQL sync

Using the ALTER TABLE command on MySQL sync cloud databases can cause the cluster to stop. Instead of ALTER TABLE, we recommend using the pt-online-schema-change utility from Percona.

The pt-online-schema-change utility changes the table structure as ALTER TABLE, but it does not block read and write operations.

See official documentation for more information on using pt-online-schema-change.

For your information

Use the utility only if backup is available.

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 the copying of data to the new table is complete, pt-online-schema-change uses the RENAME TABLE operation and renames the original and new table at the same time. After defaulting, the original table is deleted.

The utility creates triggers in the original table to update the data in the new table — any changes in the original table when copied will be reflected in the new table. If any triggers are already defined in the table, the utility will not work.

Risks of using⁠

Foreign keys (constraints) are used to link tables, see official MySQL documentation for details.

The use of external keys complicates the utility's operation and poses 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 should update the foreign keys to reference the new table after changes are made to the schema. The problem is solved with parameter --alter-foreign-keys-method.

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), and 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 used with the key --execute utility.

Install pt-online-schema-change⁠

  1. Connect to database.

  2. Install the pt-online-schema-change utility in one of the following ways:

    • download the Percona Toolkit from 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

Using the utility⁠

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> is the database username;
    • <password> — database user password.

  2. Change ENGINE from sakila.actor to InnoDB (so that the 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. Multiple changes are separated by a comma. See official MySQL documentation for more information.

Limitations that may cause the utility to malfunction:

  • in most cases, the table should 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 impossible to create columns without specifying DEFAULT and at the same time with specifying NOT NULL, you must explicitly specify the value of 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⁠

A method for replica detection. More information about the parameter in official Percona Toolkit documentation.

DSN Parameters⁠

Each parameter is written as follows:

parameter=value

Example:

p=<password>

All parameters are case sensitive, spaces cannot be used. If the value contains spaces, it must be enclosed in quotes. Options are separated by a comma.

Parameter list:

  • A (charset) is the 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 entire value must be enclosed in quotes;
  • P (port) — the port used for connection;
  • S (mysql_socket) is the socket file used for the connection;
  • t (table) — the table whose structure will be modified;
  • u (user) — the user under which the connection is made.