How to use the oak-online-alter-table tool for MySQL 01/27 Update SLTechnology News&Howtos

How to use the oak-online-alter-table tool for MySQL

2025-01-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article will explain in detail how MySQL uses oak-online-alter-table tools. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

The oak-online-alter-table gadget is used to implement MySQL Online DDL

Download address: https://code.google.com/archive/p/openarkkit/downloads

Find openark-kit-196-1.noarch.rpm from the following figure

Installation:

[root@idb4] # rpm-ivh openark-kit-196-1.noarch.rpm

Preparing... # [100%]

1:openark-kit # # [100%]

The tool provides the following three basic functions:

1. A non-blocking ALTER TABLE operation is supported in the following situations

1) add columns (new columns must have a default value)

2) Delete columns (the old table must have a unique index with a single column)

3) modify columns (change field types, including columns with unique keys)

4) add index (general index, unique index, full-text index)

5) delete the index (the old table must have a unique index with a single column)

6) modify the table engine: pay special attention when dealing with non-transactional engines

7) add foreign key constraint

2. (it may no longer be supported in future versions): create a mirror table and synchronize with the original table, as long as the following actions do not occur:

1) ALTER TABLE operation on the original table

2) TRUNCATE operation on the original table

3) use LOAD DATA INFILE to import data into the original table

4) OPTIMIZE TABLE operation on the original table

3. An empty ALTER, rebuild a table: free disk space and reorganize the table, which is equivalent to optimizing the table.

Check that the table meets the criteria of oak-online-alter-table:

Single column unique index (federated index and federated primary key are not allowed because a bug of mysql is triggered)

There is no foreign key (without foreign keys, oak-online-alter-table has no way for tables with foreign keys)

No triggers are defined (delete if any)

Oak command usage for online environments:

Oak-online-alter-table

Specific help information:

[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

-an 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

The tests are as follows:

1. Add a field 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

If an error is reported, you need to specify-- database.

Successfully executed after adding-- 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 (971717pr 33021155799011X), (972716pr 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 (972716pr 440300683797687), (973716pr 34010104688102768), progress: 0

-- Copying range (973716 rewards 340104688102768), (974716pr 130302065712316), progress: 0%

-- Copying range (130302065712316), (9757161.91330201567021582Y), progress: 1%

-- Copying range (91330201567021582Y), (9767161.91330201567021582Y), progress: 1%

-- Copying range (91330201567021582Y), (9777161.91330201567021582Y), progress: 2%

-- Copying range (91330201567021582Y), (978716Power330226L66718333), progress: 2%

-- Copying range (978716pr 330226L66718333), (979716pr 34242119620528171701), progress: 3%

-- Copying range (34242119620528171701), (980716pr 34242119620528171701), progress: 3%

-- Copying range (34242119620528171701), (981716Power3302074908847), progress: 4%

-- Copying range (981716pr 330203074908847), (982716pr 330226L17725262), progress: 4%

-- Copying range (982716pr. 330226L17725262), (983716pr. 91420100731061034W), progress: 5%

-- Copying range (91420100731061034W), (984716pr 34010104688102768), progress: 5%

-- Copying range (984716pr 340104688102768), (985716pr 440300683797687), progress: 6%

-- Copying range (985716pr 440300683797687), (986716pr 340181090787790), progress: 6%

-- Copying range (986716pr 340181090787790), (987716pr 91330201674719468Q), progress: 7%

-- Copying range (987716pr 91330201674719468Q), (988716pr 500108696565383), progress: 7%

-- Copying range (988716) 500108696565383, (989716) 440300590749985), progress: 8%

-- Copying range (989716pr 440300590749985), (990716pr 330281717286130), progress: 8

-- Copying range (990716pr 330281717286130), (991716pr 130302065712316), progress: 9%

-Reminder: altering ixinnuo_sfsj.dsf_data: ADD COLUMN duansf VARCHAR (64).

-- Copying range (130302065712316), (992716pr 131102063134364), progress: 9%

-- Copying range (992716pr 131102063134364), (993716pr 330204756267282), progress: 10%

-- Copying range (993716pr 330204756267282), (994716pr 91110105318223036H), progress: 10%

-- Copying range (994716pr 91110105318223036H), (995716pr 91420117572023195P), progress: 11%

-- Copying range (91420117572023195P), (996716pr. 91330204MA2824NX66), progress: 11%

-- Copying range (91330204MA2824NX66), (997716pr. 913205065617808877), progress: 12%

-- Copying range (913205065617808877), (998716heroin 500903765946981), progress: 12%

-- Copying range (500903765946981), (999716instruction 440301724700897), progress: 13%

-- Copying range (999716pr. 440301724700897), (1000716pr 330282587473615), progress: 13%

-- Copying range (1000716pr 330282587473615), (1001716pr 913302011447225710), progress: 14%

-- Copying range (1001716pr 913302011447225710), (1002716pr 91330203573663733B), progress: 14%

-Copying range (1002716pr 91330203573663733B), (1003716pr 91330204053841348X), progress: 15%

-- Copying range (1003716pr 91330204053841348X), (1004716pr. 91330204053841348X), progress: 15%

-- Copying range (1004716pr 91330204053841348X), (1005716pr 310228630999533), progress: 16%

-- Copying range (1005716pr 310228630999533), (1006716pr 310228630999533), progress: 16%

-- Copying range (1006716pr 310228630999533), (1007716pr 91310112679338381W), progress: 17%

-- Copying range (1007716pr 91310112679338381W), (1008716pr 330227563850615), progress: 17%

-- Copying range (1008716pr 330227563850615), (1009716pr 330227563850615), progress: 18%

-- Copying range (1009716pr 330227563850615), (1010716pr 330227563850615), progress: 18%

-- Copying range (1010716pr 330227563850615), (1011716pr 330203698235871), progress: 19%

-Reminder: altering ixinnuo_sfsj.dsf_data: ADD COLUMN duansf VARCHAR (64).

-- Copying range (1011716pr 330203698235871), (1012716pr 9133028214483969XM), progress: 19%

-- Copying range (1012716rec 9133028214483969XM), (1013716pr 440301772706775), progress: 20%

-- Copying range (1013716rec 440301772706775), (1014716pr 9133028214483969XM), progress: 20%

-- Copying range (1014716rec 9133028214483969XM), (1015716pr 91330226747385420R), progress: 21%

-- Copying range (1015716rec 91330226747385420R), (1016716pr 330203665593904), progress: 21%

-- Copying range (1016716rem 330203665593904), (1017716pr 131102063134364), progress: 22%

-- Copying range (1017716pr 131102063134364), (1018716pr 330226758887514), progress: 22%

-- Copying range (1018716pr 330226758887514), (1019716pr 330226758887514), progress: 23%

-- Copying range (1019716pr. 330226758887514), (1020716pr. 91330226062904195F), progress: 23%

-- Deleting range (1158716pr 330281L41374386), (1159716pr 330281L41374386), progress: 92%

-- Deleting range (1159716pr. 330281L41374386), (1160716pr 510107743634485), progress: 92%

-- Deleting range (1160716 progress: 510107743634485), (11617160.9934285)

-- Deleting range (110108569534285), (1162716recovery91330212681091461J), progress: 93%

-- Deleting range (11330212681091461J), (1163716101330212681091461J), progress: 94%

-- Deleting range (11330212681091461J), (11647160.91330206695072544C), progress: 94%

-- Deleting range (11330206695072544C), (11657160.995072544C), progress: 95%

-- Deleting range (11330206695072544C), (11667161.330282780442490), progress: 95%

-- Deleting range (1166716rec 330282780442490), (1167716pr 330204736982430), progress: 96%

-- Deleting range (1167716pr 330204736982430), (1168716pr 64010407380179X), progress: 96%

-- Deleting range (1168716rec 64010407380179X), (1169716rem 91330281684260355F), progress: 97%

-- Deleting range (91330281684260355F), (1170716) 915002367116623424), progress: 97%

-- Deleting range (915002367116623424), (11717161.1310112679338381W), progress: 98%

-Reminder: altering ixinnuo_sfsj.dsf_data: ADD COLUMN duansf VARCHAR (64).

-- Deleting range (91310112679338381W), (91330204726429965L), progress: 98%

-- Deleting range (91330204726429965L), (1173716meme 91510113732356280Q), progress: 99%

-- Deleting range (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

View the newly added columns:

Mysql > show columns from dsf_data

+-+ +

+-+ +

| | id | bigint (20) | NO | PRI | NULL | auto_increment |

| | SH | varchar (32) | NO | PRI | |

+-+ +

10 rows in set (0.00 sec)

Add a normal index to the newly added column field 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).