Online DDL Usage Guide

• The table must have a unique key.
• Binlog must be enabled, the binlog format must be row, and binlog_row_image must be set to FULL. To check whether binlog is enabled, see Querying and Downloading Binlogs.
• Temporary tables and triggers are not supported.
• Use this tool during off-peak hours. Do not perform any other DDL operations while it is in use.
• If gh-ost enters the throttled state, you can increase the value of the -max-lag-millis parameter (default value: 1500, unit: ms), for example, set -max-lag-millis to 10000.
• gh-ost does not support foreign keys. You are advised to use MySQL official DDL. If you must use gh-ost, delete foreign keys first or configure the --skip-foreign-key-checks parameter for gh-ost. In this case, you can use gh-ost to execute DDL. However, after the execution is complete, the foreign keys on the table will be lost and you need to manually add them.
• gh-ost works by creating a ghost table with the same structure as the original table. It synchronizes all data in the original table to the ghost table by parsing binlogs. After the synchronization is complete, it exchanges the names of the original table and ghost table using the RENAME TABLE statement.

  Due to a known issue of gh-ost, RENAME TABLE can sometimes hang and fail with the error message "ERROR Error 1205: Lock wait timeout exceeded; try restarting transaction".

  To resolve or avoid this, consider the following:

  • Increase the value of the gh-ost parameter --cut-over-lock-timeout-seconds (which specifies the RENAME TABLE timeout, in seconds), for example, set --cut-over-lock-timeout-seconds to 10. The value ranges from 1 to 10. Values outside this default to 3.
  • Set the gh-ost parameter --cut-over to two-step to switch the table in a non-atomic manner. That is, rename the original table to a temporary table first, and then rename the ghost table to the original table. However, if the switchover fails, the original table may be lost. Exercise caution when using this method. You are advised to use the first method.

Change the storage engine of the sbtest.sbtest1 table to InnoDB.

gh-ost  -user="temp"  -password="test" -host=**.*.*.*  \            
           -database="sbtest" -table="sbtest1" \             
           -alter="engine=innodb" \            
           -chunk-size=2000 \            
           -allow-on-master \            
           -cut-over=default \             
           -default-retries=120 \            
           -panic-flag-file=/tmp/ghost.panic.flag \             
           -execute \            
           -debug  \           
           -max-load=Threads_running=20 \             
           -critical-load=Threads_running=100

For more parameters, see the official website.

The DDL execution duration depends on the execution algorithm, table data volume, instance specifications, and workloads. You are advised to execute DDL statements in off-peak hours.

You are advised to enable DDL Fast Timeout and Non-blocking DDL before executing DDL statements (ensure that the kernel version of the instance meets the requirements).

• Method 1: Execute SQL statements.
  1. Set algorithm to instant. If the algorithm is not supported, an error will be reported.
  2. Set algorithm to inplace. If the algorithm is not supported, an error will be reported.
     Example:

     -- The instant algorithm is not supported.
     alter table t drop primary key,algorithm=instant; 
      ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY/INPLACE.

     -- The inplace algorithm is not supported.
     alter table t drop primary key,algorithm=inplace;   
     ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY.

     -- Only the copy algorithm is supported.
     alter table t drop primary key,algorithm=copy;  
     Query OK, 0 rows affected (0.13 sec) Records: 0  Duplicates: 0  Warnings: 0

• Method 2: Visit the MySQL official website.

You can only check the progress of secondary index creation (Progress Queries for Creating Secondary Indexes).

To check the progress of other DDL operations, use the following SQL query (performance_schema must be set to ON):

SELECT EVENT_NAME AS 'current_execution_phase',WORK_COMPLETED AS 'completed_workload',WORK_ESTIMATED AS 'estimated_total_workload', ROUND(IF(WORK_ESTIMATED = 0, 0, (WORK_COMPLETED / WORK_ESTIMATED) * 100), 2) AS 'progress (%)' FROM performance_schema.events_stages_current WHERE EVENT_NAME LIKE 'stage/sql/copy to tmp table'OR EVENT_NAME LIKE 'stage/innodb/alter table%';