A practical guide to adding fields to MySQL's billion-level tables

1. Risk assessment and preparation work

1.1 Potential issues with direct execution of ALTER

ALTER TABLE `orders` ADD COLUMN `is_priority` TINYINT NULL DEFAULT 0;

This direct way of execution can result in up to 2-6 hours of locking table time in MySQL 5.6, while a brief blocking write is required even in MySQL 5.7+. This can lead to the following business impacts:

• All read and write requests timed out
• Connection pool exhausted (Too many connections）
• May trigger high availability switches (such as MHA)

1.2 Key indicator inspection

Before the operation, the table size and current long transaction need to be checked:

-- Check table size（GB）
SELECT
    table_name,
    ROUND(data_length/1024/1024/1024,2) AS size_gb
FROM information_schema.tables
WHERE table_schema = 'your_db' AND table_name = 'orders';

-- Check the current long transaction
SELECT * FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

1.3 Data backup

Backing up data is a crucial step before any structural changes are made. You can use the following command to back up the data table:

mysqldump -u [username] -p[password] [database_name] [table_name] > [backup_file_name].sql

Comparison of two and three safety solutions

3. Solution 1: MySQL native Online DDL (5.7+)

3.1 Optimal execution of commands

ALTER TABLE `orders`
ADD COLUMN `is_priority` TINYINT NULL DEFAULT 0,
ALGORITHM=INPLACE,
LOCK=NONE;

3.2 Monitor progress (sessment is held separately)

• View DDL status:

SHOW PROCESSLIST;

• View InnoDB operation progress:

SELECT * FROM information_schema.innodb_alter_table;

3.3 Estimated execution time (empirical formula)

time(minute) = Table size(GB) × 2 + 10

For example, the table size is 50GB and the estimated execution time is 110 minutes.

4. Plan 2: pt-online-schema-change Practical combat

4.1 Installation and execution

# Install Percona Toolkitsudo yum install percona-toolkit

# Perform changespt-online-schema-change \
--alter "ADD COLUMN is_priority TINYINT NULL DEFAULT 0" \
D=your_db,t=orders \
--chunk-size=1000 \
--max-load="Threads_running=50" \
--critical-load="Threads_running=100" \
--execute

4.2 Key parameter description

4.3 Java application compatibility processing

During the trigger's effect, a duplicate primary key exception is handled:

try {
    (newOrder);
} catch (DuplicateKeyException e) {
    (newOrder);
}

5. Solution 3: Advanced usage of gh-ost

5.1 Execute commands (no trigger required)

gh-ost \
--database="your_db" \
--table="orders" \
--alter="ADD COLUMN is_priority TINYINT NULL DEFAULT 0" \
--assume-rbr \
--allow-on-master \
--cut-over=default \
--execute

5.2 Core Advantages

• No trigger design to avoid performance loss
• Dynamic current limit, automatically adapt to server load
• Interactive control, supports pause/recovery

6. Java application layer adaptation strategy

6.1 Double write compatibility mode (recommended)

public void createOrder(Order order) {
    (0); // New field default value    (order);

    // Compatible with old code    if (order.getV2() == null) {
        ((), 0);
    }
}

6.2 Dynamic SQL routing

<insert >
    INSERT INTO orders
    (id, user_id, amount
    <if test="isPriority != null">, is_priority</if>)
    VALUES
    (#{id}, #{userId}, #{amount}
    <if test="isPriority != null">, #{isPriority}</if>)
</insert>

7. Monitoring and rollback schemes

7.1 Real-time monitoring indicators

• Monitor replication latency (master-slave architecture):

pt-heartbeat --monitor --database=your_db

• View gh-ost progress:

tail -f 

7.2 Emergency rollback steps

• pt-osc rollback:

pt-online-schema-change --drop-new-table --alter="..." --execute

• gh-ost rollback:

gh-ost --panic-on-failure --revert

8. Summary and Suggestions

1. Preferred plan：

   • MySQL 8.0 → NativeALGORITHM=INSTANT(Completed in seconds)
   • MySQL 5.7 → gh-ost(No trigger influence)

2. Execution window：

   • Select the lowest time period for business traffic (such as 2-4 am)
   • Notify the business party in advance to prepare a downgrade plan

3. Verification process：

SELECT COUNT(*) FROM orders WHERE is_priority IS NULL;

• Follow-up optimization：

ALTER TABLE orders
MODIFY COLUMN is_priority TINYINT NOT NULL DEFAULT 0;

By rationally selecting tools and application layer adaptation strategies, even tables with billions of data can achieve zero downtime fields.

This is the article about this practical guide on the safe addition of fields for MySQL billion-level large tables. For more related contents for adding fields for MySQL billion-level large tables, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!