Gh-ost改分区表测试

MySQL
Database MySQL
发布日期:   2023-06-06
文章字数:   7k
阅读时长:   42 分

测试环境

  • Gh-ost版本：v1.1.5

  • MySQL版本：5.7

测试过程及结果

Range分区

创建测试表

CREATE TABLE `t` (
  `ftime` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# 插入测试数据
insert into t values('2017-4-1',1),('2018-4-1',1),('2019-4-1',1),('2020-4-1',1),('2021-4-1',1);

Gh-ost脚本如下，其中—alter参数指定了按照RANGE分区，将ftime字段按照年份大小分成p_2017, p_2018, p_2019, p_other四个分区。

./gh-ost \
--max-load=Threads_running=32 \
--critical-load=Threads_running=32 \
--chunk-size=1000 \
--throttle-control-replicas="$throttle_control_replicas" \
--max-lag-millis=1000 \
--initially-drop-old-table \
--initially-drop-ghost-table \
--ok-to-drop-table \
--user="${USER}" \
--password='${PASSWORD}' \
--host="${HOST}" \
--port="${PORT}" \
--database="test" \
--table="t" \
--alter="PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);" \
--switch-to-rbr \
--allow-master-master \
--cut-over=default \
--default-retries=3 \
--critical-load-interval-millis=3000 \
--panic-flag-file=/tmp/ghost.panic.flag \
--verbose \
--allow-on-master \
--execute

执行Gh-ost脚本后的日志分析：

2023-06-02 16:11:46 INFO starting gh-ost 1.1.5
2023-06-02 16:11:46 INFO Migrating `test`.`t`
2023-06-02 16:11:46 INFO inspector connection validated on localhost:5002
2023-06-02 16:11:46 INFO User has SUPER, REPLICATION SLAVE privileges, and has ALL privileges on `test`.*
2023-06-02 16:11:46 INFO binary logs validated on localhost:5002
2023-06-02 16:11:46 INFO Restarting replication on localhost:5002 to make sure binlog settings apply to replication thread
2023-06-02 16:11:46 INFO Inspector initiated on set-yp-dba-dba-hancheng-test01.mt:5002, version 5.7.26-301031002-log
2023-06-02 16:11:46 INFO Table found. Engine=InnoDB
2023-06-02 16:11:46 INFO Estimated number of rows via EXPLAIN: 2
2023-06-02 16:11:46 INFO Recursively searching for replication master
2023-06-02 16:11:46 INFO Master found to be set-yp-dba-dba-hancheng-test01.mt:5002
2023-06-02 16:11:46 INFO log_slave_updates validated on localhost:5002
2023-06-02 16:11:46 INFO streamer connection validated on localhost:5002
2023-06-02 16:11:46 INFO Connecting binlog streamer at mysql-bin.000003:5711965
[2023/06/02 16:11:46] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql localhost 5002 siqi    false false <nil> false UTC true 0 0s 0s 0 false}
[2023/06/02 16:11:46] [info] binlogsyncer.go:354 begin to sync binlog from position (mysql-bin.000003, 5711965)
[2023/06/02 16:11:46] [info] binlogsyncer.go:203 register slave for master server localhost:5002
[2023/06/02 16:11:46] [info] binlogsyncer.go:723 rotate to (mysql-bin.000003, 5711965)
2023-06-02 16:11:46 INFO rotate to next log from mysql-bin.000003:0 to mysql-bin.000003
2023-06-02 16:11:46 INFO applier connection validated on localhost:5002
2023-06-02 16:11:46 INFO applier connection validated on localhost:5002
2023-06-02 16:11:46 INFO will use time_zone='+08:00' on applier
2023-06-02 16:11:46 INFO Examining table structure on applier
2023-06-02 16:11:46 INFO Applier initiated on set-yp-dba-dba-hancheng-test01.mt:5002, version 5.7.26-301031002-log
2023-06-02 16:11:46 INFO Dropping table `test`.`_t_gho`
2023-06-02 16:11:46 INFO Table dropped
2023-06-02 16:11:46 INFO Dropping table `test`.`_t_del`
2023-06-02 16:11:46 INFO Table dropped
2023-06-02 16:11:46 INFO Dropping table `test`.`_t_ghc`
2023-06-02 16:11:46 INFO Table dropped
2023-06-02 16:11:46 INFO Creating changelog table `test`.`_t_ghc`
2023-06-02 16:11:46 INFO Changelog table created
2023-06-02 16:11:46 INFO Creating ghost table `test`.`_t_gho`
2023-06-02 16:11:46 INFO Ghost table created
2023-06-02 16:11:46 INFO Altering ghost table `test`.`_t_gho`
2023-06-02 16:11:46 INFO Ghost table altered
2023-06-02 16:11:46 INFO Intercepted changelog state GhostTableMigrated
2023-06-02 16:11:46 INFO Waiting for ghost table to be migrated. Current lag is 0s
2023-06-02 16:11:46 INFO Handled changelog state GhostTableMigrated
2023-06-02 16:11:46 INFO Chosen shared unique key is PRIMARY
2023-06-02 16:11:46 INFO Shared columns are ftime,c
2023-06-02 16:11:46 INFO Listening on unix socket file: /tmp/gh-ost.test.t.sock
2023-06-02 16:11:46 INFO Intercepted changelog state ReadMigrationRangeValues
2023-06-02 16:11:46 INFO Handled changelog state ReadMigrationRangeValues
2023-06-02 16:11:46 INFO Migration min values: [2017-04-01 00:00:00]
2023-06-02 16:11:46 INFO Migration max values: [2018-04-01 00:00:00]
2023-06-02 16:11:46 INFO Waiting for first throttle metrics to be collected
2023-06-02 16:11:46 INFO First throttle metrics collected
# Migrating `test`.`t`; Ghost table is `test`.`_t_gho`
# Migrating set-yp-dba-dba-hancheng-test01.mt:5002; inspecting set-yp-dba-dba-hancheng-test01.mt:5002; executing on set-yp-dba-dba-hancheng-test01.mt
# Migration started at Fri Jun 02 16:11:46 +0800 2023
# chunk-size: 1000; max-lag-millis: 1000ms; dml-batch-size: 10; max-load: Threads_running=32; critical-load: Threads_running=32; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.test.t.sock
Copy: 0/2 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000003:5716531; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: N/A
Copy: 0/2 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000003:5723706; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: N/A
2023-06-02 16:11:47 INFO Row copy complete
Copy: 2/2 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000003:5725244; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: due
2023-06-02 16:11:47 INFO Grabbing voluntary lock: gh-ost.34762682.lock
2023-06-02 16:11:47 INFO Setting LOCK timeout as 6 seconds
2023-06-02 16:11:47 INFO Looking for magic cut-over table
2023-06-02 16:11:47 INFO Creating magic cut-over table `test`.`_t_del`
2023-06-02 16:11:47 INFO Magic cut-over table created
2023-06-02 16:11:47 INFO Locking `test`.`t`, `test`.`_t_del`
2023-06-02 16:11:47 INFO Tables locked
2023-06-02 16:11:47 INFO Session locking original & magic tables is 34762682
2023-06-02 16:11:47 INFO Writing changelog state: AllEventsUpToLockProcessed:1685693507241901946
2023-06-02 16:11:47 INFO Intercepted changelog state AllEventsUpToLockProcessed
2023-06-02 16:11:47 INFO Handled changelog state AllEventsUpToLockProcessed
2023-06-02 16:11:47 INFO Waiting for events up to lock
Copy: 2/2 100.0%; Applied: 0; Backlog: 1/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000003:5733950; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: due
2023-06-02 16:11:48 INFO Waiting for events up to lock: got AllEventsUpToLockProcessed:1685693507241901946
2023-06-02 16:11:48 INFO Done waiting for events up to lock; duration=997.008696ms
# Migrating `test`.`t`; Ghost table is `test`.`_t_gho`
# Migrating set-yp-dba-dba-hancheng-test01.mt:5002; inspecting set-yp-dba-dba-hancheng-test01.mt:5002; executing on set-yp-dba-dba-hancheng-test01.mt
# Migration started at Fri Jun 02 16:11:46 +0800 2023
# chunk-size: 1000; max-lag-millis: 1000ms; dml-batch-size: 10; max-load: Threads_running=32; critical-load: Threads_running=32; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.test.t.sock
Copy: 2/2 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000003:5734829; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: due
2023-06-02 16:11:48 INFO Setting RENAME timeout as 3 seconds
2023-06-02 16:11:48 INFO Session renaming tables is 34762683
2023-06-02 16:11:48 INFO Issuing and expecting this to block: rename /* gh-ost */ table `test`.`t` to `test`.`_t_del`, `test`.`_t_gho` to `test`.`t`
2023-06-02 16:11:48 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2023-06-02 16:11:48 INFO Checking session lock: gh-ost.34762682.lock
2023-06-02 16:11:48 INFO Connection holding lock on original table still exists
2023-06-02 16:11:48 INFO Will now proceed to drop magic table and unlock tables
2023-06-02 16:11:48 INFO Dropping magic cut-over table
2023-06-02 16:11:48 INFO Releasing lock from `test`.`t`, `test`.`_t_del`
2023-06-02 16:11:48 INFO Tables unlocked
2023-06-02 16:11:48 INFO Tables renamed
2023-06-02 16:11:48 INFO Lock & rename duration: 1.002203157s. During this time, queries on `t` were blocked
[2023/06/02 16:11:48] [info] binlogsyncer.go:164 syncer is closing...
[2023/06/02 16:11:48] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
2023-06-02 16:11:48 INFO Closed streamer connection. err=<nil>
2023-06-02 16:11:48 INFO Dropping table `test`.`_t_ghc`
[2023/06/02 16:11:48] [info] binlogsyncer.go:179 syncer is closed
2023-06-02 16:11:48 INFO Table dropped
2023-06-02 16:11:48 INFO Dropping table `test`.`_t_del`
2023-06-02 16:11:48 INFO Table dropped
2023-06-02 16:11:48 INFO Done migrating `test`.`t`
2023-06-02 16:11:48 INFO Removing socket file: /tmp/gh-ost.test.t.sock
2023-06-02 16:11:48 INFO Tearing down inspector
2023-06-02 16:11:48 INFO Tearing down applier
2023-06-02 16:11:48 INFO Tearing down streamer
2023-06-02 16:11:48 INFO Tearing down throttler
# Done

Mysql查看表t的分区结果：

  • 可以看到Create_options字段的值为partitioned代表分区成功。
> show table status;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| t    | InnoDB |      10 | Dynamic    |    0 |              0 |       65536 |               0 |            0 |         0 |           NULL | 2023-06-02 16:11:48 | 2023-06-02 16:11:47 | NULL       | latin1_swedish_ci |     NULL | partitioned    |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)
  • 可以看到partitions字段的值，代表已经按照DDL成功分成四个分区：
> explain select * from t;
+----+-------------+-------+-------------------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions                    | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+-------------------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | p_2017,p_2018,p_2019,p_others | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+-------+-------------------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
  • 查看分区结果：
>SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
 FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='t';
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | SUBPARTITION_NAME | SUBPARTITION_METHOD | SUBPARTITION_EXPRESSION |
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
| p_2017         | RANGE            | YEAR(ftime)          | 2017                  |          0 | NULL              | NULL                | NULL                    |
| p_2018         | RANGE            | YEAR(ftime)          | 2018                  |          1 | NULL              | NULL                | NULL                    |
| p_2019         | RANGE            | YEAR(ftime)          | 2019                  |          1 | NULL              | NULL                | NULL                    |
| p_others       | RANGE            | YEAR(ftime)          | MAXVALUE              |          3 | NULL              | NULL                | NULL                    |
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
4 rows in set (0.00 sec)

Hash分区

创建测试表：

CREATE TABLE tbhash (
    id INT NOT NULL,
    name varchar(255),
    PRIMARY KEY (`id`)
);
INSERT INTO tbhash() VALUES(100,'a'),(101,'b'),(102,'c'),(103,'d'),(104,'e');

Gh-ost执行脚本如下，其中—alter参数指定按照id字段的HASH方式分区，共分为四个分区，将id模4以决定插入在哪个分区。

./gh-ost \
--max-load=Threads_running=32 \
--critical-load=Threads_running=32 \
--chunk-size=1000 \
--throttle-control-replicas="$throttle_control_replicas" \
--max-lag-millis=1000 \
--initially-drop-old-table \
--initially-drop-ghost-table \
--ok-to-drop-table \
--user="${USER}" \
--password='${PASSWORD}' \
--host="${HOST}" \
--port="${PORT}" \
--database="test" \
--table="tbhash" \
--alter="PARTITION BY HASH(id)
PARTITIONS 4;" \
--switch-to-rbr \
--allow-master-master \
--cut-over=default \
--default-retries=3 \
--critical-load-interval-millis=3000 \
--panic-flag-file=/tmp/ghost.panic.flag \
--verbose \
--allow-on-master \
--execute

执行gh-ost命令后的日志分析：

2023-06-02 16:39:23 INFO starting gh-ost 1.1.5
2023-06-02 16:39:23 INFO Migrating `test`.`tbhash`
2023-06-02 16:39:23 INFO inspector connection validated on localhost:5002
2023-06-02 16:39:23 INFO User has SUPER, REPLICATION SLAVE privileges, and has ALL privileges on `test`.*
2023-06-02 16:39:23 INFO binary logs validated on localhost:5002
2023-06-02 16:39:23 INFO Restarting replication on localhost:5002 to make sure binlog settings apply to replication thread
2023-06-02 16:39:23 INFO Inspector initiated on set-yp-dba-dba-hancheng-test01.mt:5002, version 5.7.26-301031002-log
2023-06-02 16:39:23 INFO Table found. Engine=InnoDB
2023-06-02 16:39:23 INFO Estimated number of rows via EXPLAIN: 1
2023-06-02 16:39:23 INFO Recursively searching for replication master
2023-06-02 16:39:23 INFO Master found to be set-yp-dba-dba-hancheng-test01.mt:5002
2023-06-02 16:39:23 INFO log_slave_updates validated on localhost:5002
2023-06-02 16:39:23 INFO streamer connection validated on localhost:5002
2023-06-02 16:39:23 INFO Connecting binlog streamer at mysql-bin.000003:5739754
[2023/06/02 16:39:23] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql localhost 5002 siqi    false false <nil> false UTC true 0 0s 0s 0 false}
[2023/06/02 16:39:23] [info] binlogsyncer.go:354 begin to sync binlog from position (mysql-bin.000003, 5739754)
[2023/06/02 16:39:23] [info] binlogsyncer.go:203 register slave for master server localhost:5002
[2023/06/02 16:39:23] [info] binlogsyncer.go:723 rotate to (mysql-bin.000003, 5739754)
2023-06-02 16:39:23 INFO rotate to next log from mysql-bin.000003:0 to mysql-bin.000003
2023-06-02 16:39:23 INFO applier connection validated on localhost:5002
2023-06-02 16:39:23 INFO applier connection validated on localhost:5002
2023-06-02 16:39:23 INFO will use time_zone='+08:00' on applier
2023-06-02 16:39:23 INFO Examining table structure on applier
2023-06-02 16:39:23 INFO Applier initiated on set-yp-dba-dba-hancheng-test01.mt:5002, version 5.7.26-301031002-log
2023-06-02 16:39:23 INFO Dropping table `test`.`_tbhash_gho`
2023-06-02 16:39:23 INFO Table dropped
2023-06-02 16:39:23 INFO Dropping table `test`.`_tbhash_del`
2023-06-02 16:39:23 INFO Table dropped
2023-06-02 16:39:23 INFO Dropping table `test`.`_tbhash_ghc`
2023-06-02 16:39:23 INFO Table dropped
2023-06-02 16:39:23 INFO Creating changelog table `test`.`_tbhash_ghc`
2023-06-02 16:39:23 INFO Changelog table created
2023-06-02 16:39:23 INFO Creating ghost table `test`.`_tbhash_gho`
2023-06-02 16:39:23 INFO Ghost table created
2023-06-02 16:39:23 INFO Altering ghost table `test`.`_tbhash_gho`
2023-06-02 16:39:23 INFO Ghost table altered
2023-06-02 16:39:23 INFO Intercepted changelog state GhostTableMigrated
2023-06-02 16:39:23 INFO Waiting for ghost table to be migrated. Current lag is 0s
2023-06-02 16:39:23 INFO Handled changelog state GhostTableMigrated
2023-06-02 16:39:23 INFO Chosen shared unique key is PRIMARY
2023-06-02 16:39:23 INFO Shared columns are id,name
2023-06-02 16:39:23 INFO Listening on unix socket file: /tmp/gh-ost.test.tbhash.sock
2023-06-02 16:39:23 INFO Intercepted changelog state ReadMigrationRangeValues
2023-06-02 16:39:23 INFO Handled changelog state ReadMigrationRangeValues
2023-06-02 16:39:23 INFO Migration min values: [<nil>]
2023-06-02 16:39:23 INFO Migration max values: [<nil>]
2023-06-02 16:39:23 INFO Waiting for first throttle metrics to be collected
2023-06-02 16:39:23 INFO First throttle metrics collected
# Migrating `test`.`tbhash`; Ghost table is `test`.`_tbhash_gho`
2023-06-02 16:39:23 INFO Row copy complete
# Migrating `test`.`tbhash`; Ghost table is `test`.`_tbhash_gho`
# Migrating set-yp-dba-dba-hancheng-test01.mt:5002; inspecting set-yp-dba-dba-hancheng-test01.mt:5002; executing on set-yp-dba-dba-hancheng-test01.mt
# Migration started at Fri Jun 02 16:39:23 +0800 2023
# chunk-size: 1000; max-lag-millis: 1000ms; dml-batch-size: 10; max-load: Threads_running=32; critical-load: Threads_running=32; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.test.tbhash.sock
# Migrating set-yp-dba-dba-hancheng-test01.mt:5002; inspecting set-yp-dba-dba-hancheng-test01.mt:5002; executing on set-yp-dba-dba-hancheng-test01.mt
# Migration started at Fri Jun 02 16:39:23 +0800 2023
# chunk-size: 1000; max-lag-millis: 1000ms; dml-batch-size: 10; max-load: Threads_running=32; critical-load: Threads_running=32; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.test.tbhash.sock
Copy: 0/1 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000003:5744168; Lag: 0.00s, HeartbeatLag: 0.01s, State: migrating; ETA: N/A
Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000003:5744168; Lag: 0.00s, HeartbeatLag: 0.01s, State: migrating; ETA: due
2023-06-02 16:39:23 INFO Grabbing voluntary lock: gh-ost.34764585.lock
2023-06-02 16:39:23 INFO Setting LOCK timeout as 6 seconds
2023-06-02 16:39:23 INFO Looking for magic cut-over table
2023-06-02 16:39:23 INFO Creating magic cut-over table `test`.`_tbhash_del`
2023-06-02 16:39:23 INFO Magic cut-over table created
2023-06-02 16:39:23 INFO Locking `test`.`tbhash`, `test`.`_tbhash_del`
2023-06-02 16:39:23 INFO Tables locked
2023-06-02 16:39:23 INFO Session locking original & magic tables is 34764585
2023-06-02 16:39:23 INFO Writing changelog state: AllEventsUpToLockProcessed:1685695163039195366
2023-06-02 16:39:23 INFO Intercepted changelog state AllEventsUpToLockProcessed
2023-06-02 16:39:23 INFO Handled changelog state AllEventsUpToLockProcessed
2023-06-02 16:39:23 INFO Waiting for events up to lock
2023-06-02 16:39:24 INFO Waiting for events up to lock: got AllEventsUpToLockProcessed:1685695163039195366
2023-06-02 16:39:24 INFO Done waiting for events up to lock; duration=997.176799ms
# Migrating `test`.`tbhash`; Ghost table is `test`.`_tbhash_gho`
# Migrating set-yp-dba-dba-hancheng-test01.mt:5002; inspecting set-yp-dba-dba-hancheng-test01.mt:5002; executing on set-yp-dba-dba-hancheng-test01.mt
# Migration started at Fri Jun 02 16:39:23 +0800 2023
# chunk-size: 1000; max-lag-millis: 1000ms; dml-batch-size: 10; max-load: Threads_running=32; critical-load: Threads_running=32; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.test.tbhash.sock
Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 0s(copy); streamer: mysql-bin.000003:5754105; Lag: 0.00s, HeartbeatLag: 0.01s, State: migrating; ETA: due
Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 0s(copy); streamer: mysql-bin.000003:5754105; Lag: 0.00s, HeartbeatLag: 0.01s, State: migrating; ETA: due
2023-06-02 16:39:24 INFO Setting RENAME timeout as 3 seconds
2023-06-02 16:39:24 INFO Session renaming tables is 34764589
2023-06-02 16:39:24 INFO Issuing and expecting this to block: rename /* gh-ost */ table `test`.`tbhash` to `test`.`_tbhash_del`, `test`.`_tbhash_gho` to `test`.`tbhash`
2023-06-02 16:39:24 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2023-06-02 16:39:24 INFO Checking session lock: gh-ost.34764585.lock
2023-06-02 16:39:24 INFO Connection holding lock on original table still exists
2023-06-02 16:39:24 INFO Will now proceed to drop magic table and unlock tables
2023-06-02 16:39:24 INFO Dropping magic cut-over table
2023-06-02 16:39:24 INFO Releasing lock from `test`.`tbhash`, `test`.`_tbhash_del`
2023-06-02 16:39:24 INFO Tables unlocked
2023-06-02 16:39:24 INFO Tables renamed
2023-06-02 16:39:24 INFO Lock & rename duration: 1.003182825s. During this time, queries on `tbhash` were blocked
[2023/06/02 16:39:24] [info] binlogsyncer.go:164 syncer is closing...
[2023/06/02 16:39:24] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
2023-06-02 16:39:24 INFO Closed streamer connection. err=<nil>
[2023/06/02 16:39:24] [info] binlogsyncer.go:179 syncer is closed
2023-06-02 16:39:24 INFO Dropping table `test`.`_tbhash_ghc`
2023-06-02 16:39:24 INFO Table dropped
2023-06-02 16:39:24 INFO Dropping table `test`.`_tbhash_del`
2023-06-02 16:39:24 INFO Table dropped
2023-06-02 16:39:24 INFO Done migrating `test`.`tbhash`
2023-06-02 16:39:24 INFO Removing socket file: /tmp/gh-ost.test.tbhash.sock
2023-06-02 16:39:24 INFO Tearing down inspector
2023-06-02 16:39:24 INFO Tearing down applier
2023-06-02 16:39:24 INFO Tearing down streamer
2023-06-02 16:39:24 INFO Tearing down throttler
# Done

Mysql查看表tbhash的分区结果：

  • 可以看到表tbhash的Create_options字段为partitioned代表已经分区成功：
>show table status;
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| t      | InnoDB |      10 | Dynamic    |    0 |              0 |       65536 |               0 |            0 |         0 |           NULL | 2023-06-02 16:11:48 | 2023-06-02 16:11:47 | NULL       | latin1_swedish_ci  |     NULL | partitioned    |         |
| tbhash | InnoDB |      10 | Dynamic    |    5 |          13107 |       65536 |               0 |            0 |         0 |           NULL | 2023-06-02 16:39:24 | 2023-06-02 16:43:12 | NULL       | utf8mb4_unicode_ci |     NULL | partitioned    |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
  • 可以在partitions字段看到一共分成4个分区：
>explain select * from tbhash;
+----+-------------+--------+-------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+-------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tbhash | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+--------+-------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
  • 查看分区结果：
>SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
 FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tbhash';
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | SUBPARTITION_NAME | SUBPARTITION_METHOD | SUBPARTITION_EXPRESSION |
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
| p0             | HASH             | id                   | NULL                  |          2 | NULL              | NULL                | NULL                    |
| p1             | HASH             | id                   | NULL                  |          1 | NULL              | NULL                | NULL                    |
| p2             | HASH             | id                   | NULL                  |          1 | NULL              | NULL                | NULL                    |
| p3             | HASH             | id                   | NULL                  |          1 | NULL              | NULL                | NULL                    |
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
4 rows in set (0.00 sec)

Linear hash分区

创建测试表：

CREATE TABLE tblinhash (
    id INT NOT NULL,
    name varchar(255),
    PRIMARY KEY (`id`)
);
INSERT INTO tblinhash() VALUES(100,'a'),(101,'b'),(102,'c'),(103,'d'),(104,'e');

gh-ost测试脚本，在—alter参数指定按照LINEAR HASH分区，将id模4来决定插入到哪个分区：

./gh-ost \
--max-load=Threads_running=32 \
--critical-load=Threads_running=32 \
--chunk-size=1000 \
--throttle-control-replicas="$throttle_control_replicas" \
--max-lag-millis=1000 \
--initially-drop-old-table \
--initially-drop-ghost-table \
--ok-to-drop-table \
--user="${USER}" \
--password='${PASSWORD}' \
--host="${HOST}" \
--port="${PORT}" \
--database="test" \
--table="tblinhash" \
--alter="PARTITION BY LINEAR HASH(id)
PARTITIONS 4;" \
--switch-to-rbr \
--allow-master-master \
--cut-over=default \
--default-retries=3 \
--critical-load-interval-millis=3000 \
--panic-flag-file=/tmp/ghost.panic.flag \
--verbose \
--allow-on-master \
--execute

执行gh-ost后日志分析：

2023-06-02 17:28:14 INFO starting gh-ost 1.1.5
2023-06-02 17:28:14 INFO Migrating `test`.`tblinhash`
2023-06-02 17:28:14 INFO inspector connection validated on localhost:5002
2023-06-02 17:28:14 INFO User has SUPER, REPLICATION SLAVE privileges, and has ALL privileges on `test`.*
2023-06-02 17:28:14 INFO binary logs validated on localhost:5002
2023-06-02 17:28:14 INFO Restarting replication on localhost:5002 to make sure binlog settings apply to replication thread
2023-06-02 17:28:14 INFO Inspector initiated on set-yp-dba-dba-hancheng-test01.mt:5002, version 5.7.26-301031002-log
2023-06-02 17:28:14 INFO Table found. Engine=InnoDB
2023-06-02 17:28:14 INFO Estimated number of rows via EXPLAIN: 5
2023-06-02 17:28:14 INFO Recursively searching for replication master
2023-06-02 17:28:14 INFO Master found to be set-yp-dba-dba-hancheng-test01.mt:5002
2023-06-02 17:28:14 INFO log_slave_updates validated on localhost:5002
2023-06-02 17:28:14 INFO streamer connection validated on localhost:5002
2023-06-02 17:28:14 INFO Connecting binlog streamer at mysql-bin.000003:5811972
[2023/06/02 17:28:14] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql localhost 5002 siqi    false false <nil> false UTC true 0 0s 0s 0 false}
[2023/06/02 17:28:14] [info] binlogsyncer.go:354 begin to sync binlog from position (mysql-bin.000003, 5811972)
[2023/06/02 17:28:14] [info] binlogsyncer.go:203 register slave for master server localhost:5002
[2023/06/02 17:28:14] [info] binlogsyncer.go:723 rotate to (mysql-bin.000003, 5811972)
2023-06-02 17:28:14 INFO rotate to next log from mysql-bin.000003:0 to mysql-bin.000003
2023-06-02 17:28:14 INFO applier connection validated on localhost:5002
2023-06-02 17:28:14 INFO applier connection validated on localhost:5002
2023-06-02 17:28:14 INFO will use time_zone='+08:00' on applier
2023-06-02 17:28:14 INFO Examining table structure on applier
2023-06-02 17:28:14 INFO Applier initiated on set-yp-dba-dba-hancheng-test01.mt:5002, version 5.7.26-301031002-log
2023-06-02 17:28:14 INFO Dropping table `test`.`_tblinhash_gho`
2023-06-02 17:28:14 INFO Table dropped
2023-06-02 17:28:14 INFO Dropping table `test`.`_tblinhash_del`
2023-06-02 17:28:14 INFO Table dropped
2023-06-02 17:28:14 INFO Dropping table `test`.`_tblinhash_ghc`
2023-06-02 17:28:14 INFO Table dropped
2023-06-02 17:28:14 INFO Creating changelog table `test`.`_tblinhash_ghc`
2023-06-02 17:28:14 INFO Changelog table created
2023-06-02 17:28:14 INFO Creating ghost table `test`.`_tblinhash_gho`
2023-06-02 17:28:14 INFO Ghost table created
2023-06-02 17:28:14 INFO Altering ghost table `test`.`_tblinhash_gho`
2023-06-02 17:28:14 INFO Ghost table altered
2023-06-02 17:28:14 INFO Intercepted changelog state GhostTableMigrated
2023-06-02 17:28:14 INFO Waiting for ghost table to be migrated. Current lag is 0s
2023-06-02 17:28:14 INFO Handled changelog state GhostTableMigrated
2023-06-02 17:28:14 INFO Chosen shared unique key is PRIMARY
2023-06-02 17:28:14 INFO Shared columns are id,name
2023-06-02 17:28:14 INFO Listening on unix socket file: /tmp/gh-ost.test.tblinhash.sock
2023-06-02 17:28:14 INFO Intercepted changelog state ReadMigrationRangeValues
2023-06-02 17:28:14 INFO Handled changelog state ReadMigrationRangeValues
2023-06-02 17:28:14 INFO Migration min values: [100]
2023-06-02 17:28:14 INFO Migration max values: [104]
2023-06-02 17:28:14 INFO Waiting for first throttle metrics to be collected
2023-06-02 17:28:14 INFO First throttle metrics collected
# Migrating `test`.`tblinhash`; Ghost table is `test`.`_tblinhash_gho`
# Migrating set-yp-dba-dba-hancheng-test01.mt:5002; inspecting set-yp-dba-dba-hancheng-test01.mt:5002; executing on set-yp-dba-dba-hancheng-test01.mt
# Migration started at Fri Jun 02 17:28:14 +0800 2023
# chunk-size: 1000; max-lag-millis: 1000ms; dml-batch-size: 10; max-load: Threads_running=32; critical-load: Threads_running=32; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.test.tblinhash.sock
Copy: 0/5 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000003:5816444; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: N/A
Copy: 0/5 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000003:5823786; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: N/A
2023-06-02 17:28:15 INFO Row copy complete
Copy: 5/5 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000003:5825317; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: due
2023-06-02 17:28:15 INFO Grabbing voluntary lock: gh-ost.34767952.lock
2023-06-02 17:28:15 INFO Setting LOCK timeout as 6 seconds
2023-06-02 17:28:15 INFO Looking for magic cut-over table
2023-06-02 17:28:15 INFO Creating magic cut-over table `test`.`_tblinhash_del`
2023-06-02 17:28:15 INFO Magic cut-over table created
2023-06-02 17:28:15 INFO Locking `test`.`tblinhash`, `test`.`_tblinhash_del`
2023-06-02 17:28:15 INFO Tables locked
2023-06-02 17:28:15 INFO Session locking original & magic tables is 34767952
2023-06-02 17:28:15 INFO Writing changelog state: AllEventsUpToLockProcessed:1685698095052230622
2023-06-02 17:28:15 INFO Intercepted changelog state AllEventsUpToLockProcessed
2023-06-02 17:28:15 INFO Handled changelog state AllEventsUpToLockProcessed
2023-06-02 17:28:15 INFO Waiting for events up to lock
Copy: 5/5 100.0%; Applied: 0; Backlog: 1/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000003:5834236; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: due
2023-06-02 17:28:16 INFO Waiting for events up to lock: got AllEventsUpToLockProcessed:1685698095052230622
2023-06-02 17:28:16 INFO Done waiting for events up to lock; duration=996.6416ms
# Migrating `test`.`tblinhash`; Ghost table is `test`.`_tblinhash_gho`
# Migrating set-yp-dba-dba-hancheng-test01.mt:5002; inspecting set-yp-dba-dba-hancheng-test01.mt:5002; executing on set-yp-dba-dba-hancheng-test01.mt
# Migration started at Fri Jun 02 17:28:14 +0800 2023
# chunk-size: 1000; max-lag-millis: 1000ms; dml-batch-size: 10; max-load: Threads_running=32; critical-load: Threads_running=32; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.test.tblinhash.sock
Copy: 5/5 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000003:5835131; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: due
2023-06-02 17:28:16 INFO Setting RENAME timeout as 3 seconds
2023-06-02 17:28:16 INFO Session renaming tables is 34767945
2023-06-02 17:28:16 INFO Issuing and expecting this to block: rename /* gh-ost */ table `test`.`tblinhash` to `test`.`_tblinhash_del`, `test`.`_tblinhash_gho` to `test`.`tblinhash`
2023-06-02 17:28:16 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2023-06-02 17:28:16 INFO Checking session lock: gh-ost.34767952.lock
2023-06-02 17:28:16 INFO Connection holding lock on original table still exists
2023-06-02 17:28:16 INFO Will now proceed to drop magic table and unlock tables
2023-06-02 17:28:16 INFO Dropping magic cut-over table
2023-06-02 17:28:16 INFO Releasing lock from `test`.`tblinhash`, `test`.`_tblinhash_del`
2023-06-02 17:28:16 INFO Tables unlocked
2023-06-02 17:28:16 INFO Tables renamed
2023-06-02 17:28:16 INFO Lock & rename duration: 1.002036516s. During this time, queries on `tblinhash` were blocked
[2023/06/02 17:28:16] [info] binlogsyncer.go:164 syncer is closing...
[2023/06/02 17:28:16] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2023/06/02 17:28:16] [info] binlogsyncer.go:179 syncer is closed
2023-06-02 17:28:16 INFO Closed streamer connection. err=<nil>
2023-06-02 17:28:16 INFO Dropping table `test`.`_tblinhash_ghc`
2023-06-02 17:28:16 INFO Table dropped
2023-06-02 17:28:16 INFO Dropping table `test`.`_tblinhash_del`
2023-06-02 17:28:16 INFO Table dropped
2023-06-02 17:28:16 INFO Done migrating `test`.`tblinhash`
2023-06-02 17:28:16 INFO Removing socket file: /tmp/gh-ost.test.tblinhash.sock
2023-06-02 17:28:16 INFO Tearing down inspector
2023-06-02 17:28:16 INFO Tearing down applier
2023-06-02 17:28:16 INFO Tearing down streamer
2023-06-02 17:28:16 INFO Tearing down throttler
# Done

Mysql查看表tblinhash的分区结果：

  • 可以看到表tblinhash的Create_options字段为partitioned代表已经分区成功：
>show table status;
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| t         | InnoDB |      10 | Dynamic    |    5 |          13107 |       65536 |               0 |            0 |         0 |           NULL | 2023-06-02 17:00:50 | 2023-06-02 17:00:49 | NULL       | latin1_swedish_ci  |     NULL | partitioned    |         |
| tbhash    | InnoDB |      10 | Dynamic    |    5 |          13107 |       65536 |               0 |            0 |         0 |           NULL | 2023-06-02 16:39:24 | 2023-06-02 16:43:12 | NULL       | utf8mb4_unicode_ci |     NULL | partitioned    |         |
| tblinhash | InnoDB |      10 | Dynamic    |    5 |          13107 |       65536 |               0 |            0 |         0 |           NULL | 2023-06-02 17:28:16 | 2023-06-02 17:28:15 | NULL       | utf8mb4_unicode_ci |     NULL | partitioned    |         |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
3 rows in set (0.00 sec)
  • 可以在partitions字段看到一共分成4个分区：
>explain select * from tblinhash;
+----+-------------+-----------+-------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table     | partitions  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+-------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tblinhash | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+-----------+-------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
  • 查看分区结果：
>SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
 FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblinhash';
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | SUBPARTITION_NAME | SUBPARTITION_METHOD | SUBPARTITION_EXPRESSION |
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
| p0             | LINEAR HASH      | id                   | NULL                  |          2 | NULL              | NULL                | NULL                    |
| p1             | LINEAR HASH      | id                   | NULL                  |          1 | NULL              | NULL                | NULL                    |
| p2             | LINEAR HASH      | id                   | NULL                  |          1 | NULL              | NULL                | NULL                    |
| p3             | LINEAR HASH      | id                   | NULL                  |          1 | NULL              | NULL                | NULL                    |
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
4 rows in set (0.00 sec)

List分区

创建测试表：

CREATE TABLE tblist (
    id INT NOT NULL,
    name varchar(255),
    PRIMARY KEY (`id`)
);
insert into tblist() values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');

gh-ost测试脚本，—alter参数指定按照LIST方式对id字段分区，一共分成两个分区：

./gh-ost \
--max-load=Threads_running=32 \
--critical-load=Threads_running=32 \
--chunk-size=1000 \
--throttle-control-replicas="$throttle_control_replicas" \
--max-lag-millis=1000 \
--initially-drop-old-table \
--initially-drop-ghost-table \
--ok-to-drop-table \
--user="${USER}" \
--password='${PASSWORD}' \
--host="${HOST}" \
--port="${PORT}" \
--database="test" \
--table="tblist" \
--alter="PARTITION BY LIST(id) (
    PARTITION a VALUES IN (1,3,5),
    PARTITION b VALUES IN (2,4,6));" \
--switch-to-rbr \
--allow-master-master \
--cut-over=default \
--default-retries=3 \
--critical-load-interval-millis=3000 \
--panic-flag-file=/tmp/ghost.panic.flag \
--verbose \
--allow-on-master \
--execute

MySQL查看分区结果：

  • 可以看到表tblist的Create_options字段为partitioned代表已经分区成功：
>show table status;
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| t         | InnoDB |      10 | Dynamic    |    5 |          13107 |       65536 |               0 |            0 |         0 |           NULL | 2023-06-02 17:00:50 | 2023-06-02 17:00:49 | NULL       | latin1_swedish_ci  |     NULL | partitioned    |         |
| tbhash    | InnoDB |      10 | Dynamic    |    5 |          13107 |       65536 |               0 |            0 |         0 |           NULL | 2023-06-02 16:39:24 | 2023-06-02 16:43:12 | NULL       | utf8mb4_unicode_ci |     NULL | partitioned    |         |
| tblinhash | InnoDB |      10 | Dynamic    |    5 |          13107 |       65536 |               0 |            0 |         0 |           NULL | 2023-06-02 17:28:16 | 2023-06-02 17:28:15 | NULL       | utf8mb4_unicode_ci |     NULL | partitioned    |         |
| tblist    | InnoDB |      10 | Dynamic    |    5 |           6553 |       32768 |               0 |            0 |         0 |           NULL | 2023-06-02 17:35:15 | 2023-06-02 17:35:14 | NULL       | utf8mb4_unicode_ci |     NULL | partitioned    |         |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
4 rows in set (0.00 sec)
  • 可以在partitions字段看到一共分成4个分区：
>explain select * from tblist;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tblist | a,b        | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
  • 查看分区结果：
>SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
 FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblist';
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | SUBPARTITION_NAME | SUBPARTITION_METHOD | SUBPARTITION_EXPRESSION |
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
| a              | LIST             | id                   | 1,3,5                 |          3 | NULL              | NULL                | NULL                    |
| b              | LIST             | id                   | 2,4,6                 |          2 | NULL              | NULL                | NULL                    |
+----------------+------------------+----------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
2 rows in set (0.00 sec)

测试结论

通过对Gh-ost在MySQL分区表的四种方式（RANGE, HASH, LINEAR HASH和LIST）上的改分区表测试，测试结果表明Gh-ost可以把原表改为分区表。

文章作者: foursevenlove
文章链接: https://foursevenlove.github.io/2023/06/06/gh-ost-gai-fen-qu-biao-ce-shi/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 foursevenlove !
MySQL
赏
  目录