MySQL oak-online-alter-table工具使用初探
oak-online-alter-table小工具是用來實現MySQL Online DDL的
下載地址:
從下圖中找到openark-kit-196-1.noarch.rpm
安裝:
[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)
備註：
使用此工具線上新增索引和欄位都不會鎖表，效率也很高，推薦在生產環境中使用。
下載地址:
從下圖中找到openark-kit-196-1.noarch.rpm
安裝:
[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)
備註：
使用此工具線上新增索引和欄位都不會鎖表，效率也很高，推薦在生產環境中使用。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28686045/viewspace-2138059/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql表結構同步工具SchemaSync使用初探MySql
- MySQL pt-duplicate-key-checker工具使用初探MySql
- 【MySQL】MySQL 5.7 初探MySql
- MySQL 索引初探MySql索引
- MySQL 8.4 初探MySql
- 線上改表工具oak-online-alter-table和pt-online-schema-change的使用限制總結
- MySQL UDF 提權初探MySql
- MongoDB使用初探MongoDB
- MySQL Innodb Cluster搭建與初探MySql
- 【實驗】【MySQL】MySQL 5.0 windows版本之初探MySqlWindows
- 初探 Infrastructure as Code 工具 Terraform vs PulumiASTStructORM
- Python 工具包 werkzeug 初探Python
- 【必知必會的MySQL知識】①初探MySQLMySql
- mysql 5.7 sys資料庫初探MySql資料庫
- 【MySQL】Tokudb安裝測試初探MySql
- MySQL Innotop for Linux安裝初探MySqlLinux
- MySQL Inception For Linux安裝初探MySqlLinux
- mysql5.5 performance_schema 初探MySqlORM
- 使用mysql_markdown_win工具實現生成mysql文件MySql
- Python 分發工具初探之 setuptoolsPython
- 初探S他的使用
- 初探ADDM的使用
- 乾貨好文 | 初探MySQL遷移到ClickHouseMySql
- MySQL 5.7.17 for WIN8安裝初探MySql
- 使用Mysql工具進行資料清洗MySql
- Mysql 物理備份工具xtrabackup的使用MySql
- MySQL-09.效能分析工具的使用MySql
- SSDB安裝和使用初探
- ActivityLifecycleCallbacks使用方法初探
- MySQL 高效能儲存引擎：TokuDB初探MySql儲存引擎
- mysql慢查詢日誌分析工具使用MySql
- mysql語句分析工具explain使用說明MySqlAI
- sql server匯入mysql，使用工具SQLyogServerMySql
- redis資料備份和遷移工具redis-dump安裝和使用初探Redis
- Python 分發工具初探之 setuptools 進階Python
- 初探MySQL資料備份及備份原理MySql
- MySQL壓測工具mysqlslap的介紹與使用MySql
- mysql使用mysqld_multi工具啟動多例項MySql