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

Using ALTER TABLE in MySQL sync

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

Utility pt-online-schema-change changes the table structure as ALTER TABLEBut it does not block read and write operations.

Read more about how to use it pt-online-schema-change in official documentation.

For your information

Use the utility only if you have backups.

Description of pt-online-schema-change utility⁠

Utility pt-online-schema-change works not on the table itself, but on its copy. The original table is not locked, read and write operations remain 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 operation RENAME TABLE 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 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 link tables, more details in 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 fails. Utility pt-online-schema-change must update the foreign keys to reference the new table after changes are made to the schema. The problem is solved by 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 otherwise specified in the 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 utility key --execute.

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 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 the 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 it ENGINE с sakila.actor on InnoDB (to execute a non-blocking request OPTIMIZE TABLE):

    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 utility pt-online-schema-change:

--alter⁠

Changing the table structure without ALTER TABLE keywords. Multiple changes are separated by a comma. Read more in official MySQL documentation.

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 trigger DELETE to keep the new table up-to-date during the change process;
  • cannot be used RENAME to rename tables;
  • columns cannot be renamed, otherwise the data will not be copied from the original table to the new one;
  • create columns without specifying a value DEFAULT and in doing so, indicating NOT NULL is not possible, it is necessary to explicitly set the value DEFAULT;
  • for operations of the type DROP FOREIGN KEY need to specify _constraint_name instead of constraint_name. Due to the limitations of MySQL 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.

Utilization --dry-run and --execute together is impossible.

--execute⁠

Without specifying this option, only preliminary checks will be performed, no changes will be made to the table structure.

Utilization --dry-run and --execute together is impossible.

--recursion-method⁠

A method for detecting replicas. More details about the parameter in 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) 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 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 whose structure will be modified;
  • u (user) — the user under which the connection is made.