MySQL如何使用oak-online-alter-table工具

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

这篇文章将为大家详细讲解有关MySQL如何使用oak-online-alter-table工具，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

oak-online-alter-table小工具是用来实现MySQL Online DDL的
下载地址:https://code.google.com/archive/p/openarkkit/downloads
从下图中找到openark-kit-196-1.noarch.rpm
MySQL如何使用oak-online-alter-table工具

安装:
[root@idb4 ~]# rpm -ivh openark-kit-196-1.noarch.rpm
Preparing... ########################################### [100%]
1:openark-kit ########################################### [100%]

该工具提供了以下三种基本功能：

1、一个非阻塞ALTER TABLE操作，以下几种情况都是支持的

1）添加列 (新列必须有一个默认值)

2）删除列 (旧表必须有一个单列的唯一索引)

3）修改列 (改变字段类型,包括唯一键的列)

4）添加索引 (普通索引,唯一索引,全文索引)

5）删除索引(旧表必须有一个单列的唯一索引)

6) 修改表引擎:当处理非事务性引擎应该格外注意

7）添加外键约束

2、(可能会在未来版本不再支持):创建一个镜像表,与原始表同步,只要不发生如下操作:

1）对原始表ALTER TABLE操作

2）对原始表TRUNCATE操作

3）使用LOAD DATA INFILE向原始表导入数据

4）对原始表OPTIMIZE TABLE操作

3、一个空的ALTER,重建一个表:释放磁盘空间和重新组织表，相当于优化表。

检查表是否符合oak-online-alter-table的条件:
单列唯一索引（联合索引和联合主键是不可以的，因为会触发mysql的一个bug）
没有foreign key （没有外键,oak-online-alter-table对有外键的表是没有办法的）
没有定义触发器（有的话也要先删除）

线上环境的oak 命令用法:
oak-online-alter-table

具体帮助信息：
[root@idb4 data]# oak-online-alter-table --help
Usage: oak-online-alter-table [options]

Options:
-h, --help show this help message and exit
-u USER, --user=USER MySQL user
-H HOST, --host=HOST MySQL host (default: localhost)
-p PASSWORD, --password=PASSWORD
MySQL password
--ask-pass Prompt for password
-P PORT, --port=PORT TCP/IP port (default: 3306)
-S SOCKET, --socket=SOCKET
MySQL socket file. Only applies when host is localhost
--defaults-file=DEFAULTS_FILE
Read from MySQL configuration file. Overrides all
other options
-d DATABASE, --database=DATABASE
Database name (required unless table is fully
qualified)
-t TABLE, --table=TABLE
Table to alter (optionally fully qualified)
-g GHOST, --ghost=GHOST
Table name to serve as ghost. This table will be
created and synchronized with the original table
-a ALTER_STATEMENT, --alter=ALTER_STATEMENT
Comma delimited ALTER statement details, excluding the
'ALTER TABLE t' itself
-c CHUNK_SIZE, --chunk-size=CHUNK_SIZE
Number of rows to act on in chunks. Default: 1000
-l, --lock-chunks Use LOCK TABLES for each chunk
-N, --skip-binlog Disable binary logging
-r MAX_LOCK_RETRIES, --max-lock-retries=MAX_LOCK_RETRIES
Maximum times to retry on deadlock or
lock_wait_timeout. (default: 10; 0 is unlimited)
--skip-delete-pass Do not execute the DELETE data pass
--sleep=SLEEP_MILLIS Number of milliseconds to sleep between chunks.
Default: 0
--sleep-ratio=SLEEP_RATIO
Ratio of sleep time to execution time. Default: 0
--cleanup Remove custom triggers, ghost table from possible
previous runs
-v, --verbose Print user friendly messages
-q, --quiet Quiet mode, do not verbose

测试如下：

1、添加一个字段duansf

[root@idb4 ixinnuo_sfsj]# oak-online-alter-table -uroot -p123456 -S /tmp/mysql.sock --table=dsf_data --alter="ADD COLUMN duansf VARCHAR(64) DEFAULT ''"
-- ERROR: Errors found. Initiating cleanup
-- ERROR: No database specified. Specify with fully qualified table name or with -d or --database
报错，提示需要指定--database

加--database后成功执行:
[root@idb4 ixinnuo_sfsj]# oak-online-alter-table -uroot -p123456 -S /tmp/mysql.sock --database=ixinnuo_sfsj --table=dsf_data --alter="ADD COLUMN duansf VARCHAR(64) DEFAULT ''"
-- Connecting to MySQL
-- Table ixinnuo_sfsj.dsf_data is of engine innodb
-- Checking for UNIQUE columns on ixinnuo_sfsj.dsf_data, by which to chunk
-- Possible UNIQUE KEY column names in ixinnuo_sfsj.dsf_data:
-- - id,sh
-- Table ixinnuo_sfsj.__oak_dsf_data has been created
-- Table ixinnuo_sfsj.__oak_dsf_data has been altered
-- Checking for UNIQUE columns on ixinnuo_sfsj.__oak_dsf_data, by which to chunk
-- Possible UNIQUE KEY column names in ixinnuo_sfsj.__oak_dsf_data:
-- - id,sh
-- Checking for UNIQUE columns on ixinnuo_sfsj.dsf_data, by which to chunk
-- - Found following possible unique keys:
-- - id,sh (bigint)
-- Chosen unique key is 'id,sh'
-- Shared columns: status, update_time, kpjh, sh, month, create_time, fp_data, zfjh, id
-- Created AD trigger
-- Created AU trigger
-- Created AI trigger
-- Attempting to lock tables

-- Tables locked WRITE
-- id,sh (min, max) values: ([971717L, '33021155799011X'], [1174348L, '64010407380179X'])
-- Tables unlocked
-- - Reminder: altering ixinnuo_sfsj.dsf_data: ADD COLUMN duansf VARCHAR(64) ...
-- Copying range (971717,33021155799011X), (972716,440300683797687), progress: 0%
/usr/local/bin/oak-online-alter-table:84: Warning: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT IGNORE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave.
num_affected_rows = cursor.execute(query)
/usr/local/bin/oak-online-alter-table:84: Warning: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
num_affected_rows = cursor.execute(query)
-- Copying range (972716,440300683797687), (973716,340104688102768), progress: 0%
-- Copying range (973716,340104688102768), (974716,130302065712316), progress: 0%
-- Copying range (974716,130302065712316), (975716,91330201567021582Y), progress: 1%
-- Copying range (975716,91330201567021582Y), (976716,91330201567021582Y), progress: 1%
-- Copying range (976716,91330201567021582Y), (977716,91330201567021582Y), progress: 2%
-- Copying range (977716,91330201567021582Y), (978716,330226L66718333), progress: 2%
-- Copying range (978716,330226L66718333), (979716,34242119620528171701), progress: 3%
-- Copying range (979716,34242119620528171701), (980716,34242119620528171701), progress: 3%
-- Copying range (980716,34242119620528171701), (981716,330203074908847), progress: 4%
-- Copying range (981716,330203074908847), (982716,330226L17725262), progress: 4%
-- Copying range (982716,330226L17725262), (983716,91420100731061034W), progress: 5%
-- Copying range (983716,91420100731061034W), (984716,340104688102768), progress: 5%
-- Copying range (984716,340104688102768), (985716,440300683797687), progress: 6%
-- Copying range (985716,440300683797687), (986716,340181090787790), progress: 6%
-- Copying range (986716,340181090787790), (987716,91330201674719468Q), progress: 7%
-- Copying range (987716,91330201674719468Q), (988716,500108696565383), progress: 7%
-- Copying range (988716,500108696565383), (989716,440300590749985), progress: 8%
-- Copying range (989716,440300590749985), (990716,330281717286130), progress: 8%
-- Copying range (990716,330281717286130), (991716,130302065712316), progress: 9%
-- - Reminder: altering ixinnuo_sfsj.dsf_data: ADD COLUMN duansf VARCHAR(64) ...
-- Copying range (991716,130302065712316), (992716,131102063134364), progress: 9%
-- Copying range (992716,131102063134364), (993716,330204756267282), progress: 10%
-- Copying range (993716,330204756267282), (994716,91110105318223036H), progress: 10%
-- Copying range (994716,91110105318223036H), (995716,91420117572023195P), progress: 11%
-- Copying range (995716,91420117572023195P), (996716,91330204MA2824NX66), progress: 11%
-- Copying range (996716,91330204MA2824NX66), (997716,913205065617808877), progress: 12%
-- Copying range (997716,913205065617808877), (998716,500903765946981), progress: 12%
-- Copying range (998716,500903765946981), (999716,440301724700897), progress: 13%
-- Copying range (999716,440301724700897), (1000716,330282587473615), progress: 13%
-- Copying range (1000716,330282587473615), (1001716,913302011447225710), progress: 14%
-- Copying range (1001716,913302011447225710), (1002716,91330203573663733B), progress: 14%
-- Copying range (1002716,91330203573663733B), (1003716,91330204053841348X), progress: 15%
-- Copying range (1003716,91330204053841348X), (1004716,91330204053841348X), progress: 15%
-- Copying range (1004716,91330204053841348X), (1005716,310228630999533), progress: 16%
-- Copying range (1005716,310228630999533), (1006716,310228630999533), progress: 16%
-- Copying range (1006716,310228630999533), (1007716,91310112679338381W), progress: 17%
-- Copying range (1007716,91310112679338381W), (1008716,330227563850615), progress: 17%
-- Copying range (1008716,330227563850615), (1009716,330227563850615), progress: 18%
-- Copying range (1009716,330227563850615), (1010716,330227563850615), progress: 18%
-- Copying range (1010716,330227563850615), (1011716,330203698235871), progress: 19%
-- - Reminder: altering ixinnuo_sfsj.dsf_data: ADD COLUMN duansf VARCHAR(64) ...
-- Copying range (1011716,330203698235871), (1012716,9133028214483969XM), progress: 19%
-- Copying range (1012716,9133028214483969XM), (1013716,440301772706775), progress: 20%
-- Copying range (1013716,440301772706775), (1014716,9133028214483969XM), progress: 20%
-- Copying range (1014716,9133028214483969XM), (1015716,91330226747385420R), progress: 21%
-- Copying range (1015716,91330226747385420R), (1016716,330203665593904), progress: 21%
-- Copying range (1016716,330203665593904), (1017716,131102063134364), progress: 22%
-- Copying range (1017716,131102063134364), (1018716,330226758887514), progress: 22%
-- Copying range (1018716,330226758887514), (1019716,330226758887514), progress: 23%
-- Copying range (1019716,330226758887514), (1020716,91330226062904195F), progress: 23%
..........................................................................................
..........................................................................................
..........................................................................................
-- Deleting range (1158716,330281L41374386), (1159716,330281L41374386), progress: 92%
-- Deleting range (1159716,330281L41374386), (1160716,510107743634485), progress: 92%
-- Deleting range (1160716,510107743634485), (1161716,110108569534285), progress: 93%
-- Deleting range (1161716,110108569534285), (1162716,91330212681091461J), progress: 93%
-- Deleting range (1162716,91330212681091461J), (1163716,91330212681091461J), progress: 94%
-- Deleting range (1163716,91330212681091461J), (1164716,91330206695072544C), progress: 94%
-- Deleting range (1164716,91330206695072544C), (1165716,91330206695072544C), progress: 95%
-- Deleting range (1165716,91330206695072544C), (1166716,330282780442490), progress: 95%
-- Deleting range (1166716,330282780442490), (1167716,330204736982430), progress: 96%
-- Deleting range (1167716,330204736982430), (1168716,64010407380179X), progress: 96%
-- Deleting range (1168716,64010407380179X), (1169716,91330281684260355F), progress: 97%
-- Deleting range (1169716,91330281684260355F), (1170716,915002367116623424), progress: 97%
-- Deleting range (1170716,915002367116623424), (1171716,91310112679338381W), progress: 98%
-- - Reminder: altering ixinnuo_sfsj.dsf_data: ADD COLUMN duansf VARCHAR(64) ...
-- Deleting range (1171716,91310112679338381W), (1172716,91330204726429965L), progress: 98%
-- Deleting range (1172716,91330204726429965L), (1173716,91510113732356280Q), progress: 99%
-- Deleting range (1173716,91510113732356280Q), (1174348,64010407380179X), progress: 99%
-- Deleting range 100% complete. Number of rows: 0
-- Table ixinnuo_sfsj.dsf_data has been renamed to ixinnuo_sfsj.__arc_dsf_data,
-- and table ixinnuo_sfsj.__oak_dsf_data has been renamed to ixinnuo_sfsj.dsf_data
-- Table ixinnuo_sfsj.__arc_dsf_data was found and dropped
-- ALTER TABLE completed

查看新增加的列:

mysql> show columns from dsf_data;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| SH | varchar(32) | NO | PRI | | |
| KPJH | varchar(32) | YES | | NULL | |
| ZFJH | varchar(32) | YES | | NULL | |
| MONTH | varchar(10) | YES | | NULL | |
| STATUS | varchar(255) | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| FP_DATA | mediumtext | YES | | NULL | |
| duansf | varchar(64) | YES | | | | duansf为新增加的列
+-------------+--------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

在新增加的列字段duansf上添加普通索引：

[root@idb4 ixinnuo_sfsj]# oak-online-alter-table -uroot -p123456 -S /tmp/mysql.sock --database=ixinnuo_sfsj --table=dsf_data --alter="ADD KEY(duansf)"
-- Connecting to MySQL
-- Table ixinnuo_sfsj.dsf_data is of engine innodb
-- Checking for UNIQUE columns on ixinnuo_sfsj.dsf_data, by which to chunk
-- Possible UNIQUE KEY column names in ixinnuo_sfsj.dsf_data:
-- - id,sh
-- Table ixinnuo_sfsj.__oak_dsf_data has been created
-- Table ixinnuo_sfsj.__oak_dsf_data has been altered
-- Checking for UNIQUE columns on ixinnuo_sfsj.__oak_dsf_data, by which to chunk
-- Possible UNIQUE KEY column names in ixinnuo_sfsj.__oak_dsf_data:
-- - id,sh
-- Checking for UNIQUE columns on ixinnuo_sfsj.dsf_data, by which to chunk
-- - Found following possible unique keys:
-- - id,sh (bigint)
-- Chosen unique key is 'id,sh'
-- Shared columns: status, update_time, kpjh, duansf, month, sh, create_time, fp_data, zfjh, id
-- Created AD trigger
-- Created AU trigger
-- Created AI trigger
-- Attempting to lock tables

-- Tables locked WRITE
-- id,sh (min, max) values: ([971717L, '33021155799011X'], [1174348L, '64010407380179X'])
-- Tables unlocked
-- - Reminder: altering ixinnuo_sfsj.dsf_data: ADD KEY(duansf)...
-- Copying range (971717,33021155799011X), (972716,440300683797687), progress: 0%
/usr/local/bin/oak-online-alter-table:84: Warning: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT IGNORE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave.
num_affected_rows = cursor.execute(query)
/usr/local/bin/oak-online-alter-table:84: Warning: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
num_affected_rows = cursor.execute(query)
-- Copying range (972716,440300683797687), (973716,340104688102768), progress: 0%
-- Copying range (973716,340104688102768), (974716,130302065712316), progress: 0%
-- Copying range (974716,130302065712316), (975716,91330201567021582Y), progress: 1%
-- Copying range (975716,91330201567021582Y), (976716,91330201567021582Y), progress: 1%
-- Copying range (976716,91330201567021582Y), (977716,91330201567021582Y), progress: 2%
-- Copying range (977716,91330201567021582Y), (978716,330226L66718333), progress: 2%
-- Copying range (978716,330226L66718333), (979716,34242119620528171701), progress: 3%
-- Copying range (979716,34242119620528171701), (980716,34242119620528171701), progress: 3%
-- Copying range (980716,34242119620528171701), (981716,330203074908847), progress: 4%
-- Copying range (981716,330203074908847), (982716,330226L17725262), progress: 4%
-- Copying range (982716,330226L17725262), (983716,91420100731061034W), progress: 5%
-- Copying range (983716,91420100731061034W), (984716,340104688102768), progress: 5%
-- Copying range (984716,340104688102768), (985716,440300683797687), progress: 6%
-- Copying range (985716,440300683797687), (986716,340181090787790), progress: 6%
-- Copying range (986716,340181090787790), (987716,91330201674719468Q), progress: 7%
-- Copying range (987716,91330201674719468Q), (988716,500108696565383), progress: 7%
-- Copying range (988716,500108696565383), (989716,440300590749985), progress: 8%
-- Copying range (989716,440300590749985), (990716,330281717286130), progress: 8%
-- Copying range (990716,330281717286130), (991716,130302065712316), progress: 9%
-- - Reminder: altering ixinnuo_sfsj.dsf_data: ADD KEY(duansf)...
-- Copying range (991716,130302065712316), (992716,131102063134364), progress: 9%
-- Copying range (992716,131102063134364), (993716,330204756267282), progress: 10%
-- Copying range (993716,330204756267282), (994716,91110105318223036H), progress: 10%
........................................................................................
........................................................................................
........................................................................................

-- Deleting range (1148716,330206563854464), (1149716,330281704899333), progress: 87%
-- Deleting range (1149716,330281704899333), (1150716,91330281725139747P), progress: 87%
-- Deleting range (1150716,91330281725139747P), (1151716,91330281725139747P), progress: 88%
-- - Reminder: altering ixinnuo_sfsj.dsf_data: ADD KEY(duansf)...
-- Deleting range (1151716,91330281725139747P), (1152716,64010407380179X), progress: 88%
-- Deleting range (1152716,64010407380179X), (1153716,330211730184147), progress: 89%
-- Deleting range (1153716,330211730184147), (1154716,91640300MA75WH5L9P), progress: 89%
-- Deleting range (1154716,91640300MA75WH5L9P), (1155716,330282662070630), progress: 90%
-- Deleting range (1155716,330282662070630), (1156716,913302037204794358), progress: 90%
-- Deleting range (1156716,913302037204794358), (1157716,64010407380179X), progress: 91%
-- Deleting range (1157716,64010407380179X), (1158716,330281L41374386), progress: 91%
-- Deleting range (1158716,330281L41374386), (1159716,330281L41374386), progress: 92%
-- Deleting range (1159716,330281L41374386), (1160716,510107743634485), progress: 92%
-- Deleting range (1160716,510107743634485), (1161716,110108569534285), progress: 93%
-- Deleting range (1161716,110108569534285), (1162716,91330212681091461J), progress: 93%
-- Deleting range (1162716,91330212681091461J), (1163716,91330212681091461J), progress: 94%
-- Deleting range (1163716,91330212681091461J), (1164716,91330206695072544C), progress: 94%
-- Deleting range (1164716,91330206695072544C), (1165716,91330206695072544C), progress: 95%
-- Deleting range (1165716,91330206695072544C), (1166716,330282780442490), progress: 95%
-- Deleting range (1166716,330282780442490), (1167716,330204736982430), progress: 96%
-- Deleting range (1167716,330204736982430), (1168716,64010407380179X), progress: 96%
-- Deleting range (1168716,64010407380179X), (1169716,91330281684260355F), progress: 97%
-- Deleting range (1169716,91330281684260355F), (1170716,915002367116623424), progress: 97%
-- Deleting range (1170716,915002367116623424), (1171716,91310112679338381W), progress: 98%
-- - Reminder: altering ixinnuo_sfsj.dsf_data: ADD KEY(duansf)...
-- Deleting range (1171716,91310112679338381W), (1172716,91330204726429965L), progress: 98%
-- Deleting range (1172716,91330204726429965L), (1173716,91510113732356280Q), progress: 99%
-- Deleting range (1173716,91510113732356280Q), (1174348,64010407380179X), progress: 99%
-- Deleting range 100% complete. Number of rows: 0
-- Table ixinnuo_sfsj.dsf_data has been renamed to ixinnuo_sfsj.__arc_dsf_data,
-- and table ixinnuo_sfsj.__oak_dsf_data has been renamed to ixinnuo_sfsj.dsf_data
-- Table ixinnuo_sfsj.__arc_dsf_data was found and dropped
-- ALTER TABLE completed

查看添加的索引(key):
mysql> show index from dsf_data;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dsf_data | 0 | PRIMARY | 1 | id | A | 169898 | NULL | NULL | | BTREE | | |
| dsf_data | 0 | PRIMARY | 2 | SH | A | 169898 | NULL | NULL | | BTREE | | |
| dsf_data | 1 | index_sh | 1 | SH | A | 1296 | NULL | NULL | | BTREE | | |
| dsf_data | 1 | duansf | 1 | duansf | A | 6 | NULL | NULL | YES | BTREE | | | duansf为新增加的key
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

备注：
使用此工具在线添加索引和字段都不会锁表，效率也很高，推荐在生产环境中使用。