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

這篇文章將為大家詳細(xì)講解有關(guān)MySQL如何使用oak-online-alter-table工具，小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

oak-online-alter-table小工具是用來(lái)實(shí)現(xiàn)MySQL Online DDL的
下載地址:https://code.google.com/archive/p/openarkkit/downloads
從下圖中找到openark-kit-196-1.noarch.rpm


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


該工具提供了以下三種基本功能：

1、一個(gè)非阻塞ALTER TABLE操作，以下幾種情況都是支持的

   1）添加列 (新列必須有一個(gè)默認(rèn)值)

   2）刪除列 (舊表必須有一個(gè)單列的唯一索引)

   3）修改列 (改變字段類型,包括唯一鍵的列)

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

   5）刪除索引(舊表必須有一個(gè)單列的唯一索引)

   6) 修改表引擎:當(dāng)處理非事務(wù)性引擎應(yīng)該格外注意

   7）添加外鍵約束

2、(可能會(huì)在未來(lái)版本不再支持):創(chuàng)建一個(gè)鏡像表,與原始表同步,只要不發(fā)生如下操作:

   1）對(duì)原始表ALTER TABLE操作

   2）對(duì)原始表TRUNCATE操作

   3）使用LOAD DATA INFILE向原始表導(dǎo)入數(shù)據(jù)

   4）對(duì)原始表OPTIMIZE TABLE操作

3、一個(gè)空的ALTER,重建一個(gè)表:釋放磁盤空間和重新組織表，相當(dāng)于優(yōu)化表。


檢查表是否符合oak-online-alter-table的條件:
單列唯一索引（聯(lián)合索引和聯(lián)合主鍵是不可以的，因?yàn)闀?huì)觸發(fā)mysql的一個(gè)bug）
沒(méi)有foreign key （沒(méi)有外鍵,oak-online-alter-table對(duì)有外鍵的表是沒(méi)有辦法的）
沒(méi)有定義觸發(fā)器（有的話也要先刪除）


線上環(huán)境的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

測(cè)試如下：

1、添加一個(gè)字段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
報(bào)錯(cuò)，提示需要指定--database


加--database后成功執(zhí)行:
[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)


備注：
使用此工具在線添加索引和字段都不會(huì)鎖表，效率也很高，推薦在生產(chǎn)環(huán)境中使用。

關(guān)于“MySQL如何使用oak-online-alter-table工具”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。