Mysql online security change tool gh-ost

gh-ost(GitHub Online Schema Translater) is a tool for securely changing the table structure of MySQL databases, designed specifically for online database migrations. Its main advantage is that it can modify the table structure without interrupting business without affecting the availability and performance of the database.

Main features

• Online migration：gh-ostAllows changes to table structure in high-load production environments without locking tables, ensuring database availability.

• Progressive copy: By gradually copying the line,gh-ostIt can avoid load peaks in a short period of time when targeting large amounts of data.

• Security: It has automatic rollback function. If there is a problem during the migration process, it can safely roll back to the original state.

• Easy to program and operate: The tool is simple and intuitive to use, compatible with the MySQL client, and supports the use of SQL statements to make changes to table structures.

• Multiple configuration options: Supports multiple options for configuring operations, such as parallel migration, maximum movement speed, etc.

Use scenarios

• Publish new fields: Add new columns without affecting the application's access.
• Modify existing field types: Safely change the column's data type, such as fromVARCHARarriveTEXT。
• Rename column: without affecting the running system.

Basic usage

• Install: Can be passedgithubDownload the executable file, or useHomebrew(on macOS) to install.

  brew install gh-ost
  

• Basic Commands：
  The following is a usegh-ostBasic examples of changing table structure:

  gh-ost \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=1000 \
  --host=127.0.0.1 \
  --user=username \
  --password=your_password \
  --database=your_database \
  --table=your_table \
  --alter="ADD COLUMN new_col INT DEFAULT 0" \
  --execute
  

  This command willyour_tableAdd a name to the tablenew_colThe new column of  , the field type isINT, the default value is 0.

Configuration Options

• --max-load: Specify the maximum allowable load.
• --critical-load: Specifies the load to trigger the rollback.
• --host，--user，--password: Database connection information.
• --database，--table: The database and table name to be modified.
• --alter: Specify the ALTER statement.
• --execute: means to perform migration; if this option is not added, only dry run (dry run) will be performed and not actually executed.

gh-ost data migration process

1. Preparation and initialization

• Installation and configuration: Make sure your MySQL database has binary logging enabled (binlog) and installgh-osttool.
• Parameter settings: Start with appropriate parametersgh-ost, including connection information, tables to be migrated, change statements, etc.

2. Create a temporary table

• The structure of temporary tables：gh-ostFirst, a new temporary table will be created and the structure of the original table will be copied into the temporary table according to the definition of your ALTER statement.
• Start copying data: Once the temporary table is created,gh-ostYou can start copying existing data from the original table row by row to the temporary table.

3. Data copying and change monitoring

• Copy line by line：gh-ostCopy the original table's data to the temporary table row by row in incremental fashion. This replication process is gradual, ensuring the lowest load on the database.

• Use transaction isolation: During the data replication process,gh-ostuseREPEATABLE READTransaction isolation level. This means that during data replication,The read of the original table is a snapshot, will not be affected by other transactions.

• Monitor binary logs (binlog)：

  • at the same time,gh-ostStart a thread, monitor MySQL's binlog in real time, and capture all changes to the original table (including INSERT, UPDATE, DELETE).
  • This allowsgh-ostCapture any updates to the original table during data replication.

4. Real-time synchronous changes

• Handle changes：

  • Any changes to the original table (for example: INSERT or UPDATE) during data replication will begh-ostMonitored and recorded in binlog.
  • gh-ostThese changes will be madereal timeApply to the temporary table (the changed data will not be processed until the copy is completed) to ensure that the data in the temporary table is kept in sync with the original table.

• Final update operation：

  • For the update operation of the original table, if the data of the row has been modified when copied to a certain row,gh-ostThe temporary table will be updated on the binary log to prevent data inconsistencies.

5. Table switching

• Complete copy and update：

  • Once all data has been copied and all binary log changes have been synchronized to the temporary table,gh-ostThe final check will be conducted.

• Atomic renaming：

  • gh-ostPerform atomic operations and rename the original table to the backup name (for example:your_table_old) and rename the temporary table to the original table's name (for example:your_table）。
  • This process ensures that at any time, the data in the database is consistent and that the entire migration operation is transparent to the outside world.

6. Cleaning and rolling back

• Delete old table: If the configuration allows,gh-ostThe old table can be safely deleted to free up resources.
• Rollback mechanism：
  • If an error occurs during the migration process,gh-ostA rollback function is provided, which can safely restore the database to its original state, ensuring that errors do not affect the operation of the database and the integrity of the data.

Summarize

gh-ostThrough the above steps, online, secure and gradual MySQL data migration has been achieved. Its design ensures that data consistency and accuracy throughout the process and has minimal impact on the production system.Leverage transaction isolation and binary log capture，gh-ostEnsure that all changes are effectively processed during the migration process, and ultimately achieve smooth table structure updates.

This is the article about the use of Mysql online security change tool gh-ost. For more related content in Mysql gh-ost, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!