Openark-kit online ddl of mysql

MySQL toolkit openark-kit (official website http://code.openark.org/forge/openark-kit), which contains many gadgets used to implement online ddl operations prior to 5.6,

This article uses CentOS as the operating system, and the Python environment has been installed in the default operating system.

Install the openark-kit toolkit

Installs the MySQL-python of the Python module package for using Python connections to operate MySQL use.

Yum install-y MySQL-python

RPM installation mode

Get RPM package https://code.google.com/p/openarkkit/downloads/detail?name=openark-kit-196-1.noarch.rpm

Execute the command rpm-ivh openark-kit-196-1.noarch.rpm

TAR package installation method

Get tar package https://code.google.com/p/openarkkit/downloads/detail?name=openark-kit-196.tar.gz

Extract tar package tar-zxvf openark-kit-196.tar.gz-C / usr/local/openark-kit/

Install openark-kit tools python setup.py install

1.1 sysbench load data

/ u01/sysbench-0.5/sysbench/sysbench-test=/u01/sysbench-0.5/sysbench/tests/db/insert.lua-oltp-table-size=1000000-mysql-table-engine=innodb-mysql-user=root-mysql-password=root123-mysql-port=3306-mysql-host=127.0.0.1-mysql-db=replTestDB-max-requests=0-max-time=60-oltp-tables-count=2-report-interval=10-num_threads=2 prepare

/ u01/sysbench-0.5/sysbench/sysbench-test=/u01/sysbench-0.5/sysbench/tests/db/insert.lua-oltp-table-size=1000000-mysql-table-engine=innodb-mysql-user=root-mysql-password=root123-mysql-port=3306-mysql-host=127.0.0.1-mysql-db=replTestDB-max-requests=0-max-time=60-oltp-tables-count=2-report-interval=10-num_threads=2 run

1.2 check whether the ONLINE_ DDL table has a foreign key trigger and delete it.

* * through information_schema.key_column_usage**

SELECT TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_OBJECT_SCHEMA

EVENT_OBJECT_TABLE

FROM information_schema.TRIGGERS

WHERE event_object_schema = 'replTestDB'

Select * from information_schema.key_column_usage where

Referenced_table_schema='replTestDB' and

Referenced_table_name='sbtest1'

1.3 ONLINE_DDL

Cd / u01/tools/openark-kit-196/scripts/

Python oak-online-alter-table-u root-- ask-pass-S / u01/mysql/my3306/run/mysql.sock-d replTestDB-t sbtest1-g new_sbtest1-a "add last_update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,add key last_update_time (last_update_time)"-- sleep=300-- skip-delete-pass

1.4 data check after ONLINE_DDL

Select count (*) from sbtest1

Union all

Select count (*) from new_sbtest1

Mysql > desc new_sbtest1

->

+-- +

| | Field | Type | Null | Key | Default | Extra | |

+-- +

| | id | int (10) unsigned | NO | PRI | NULL | auto_increment |

| | k | int (10) unsigned | NO | MUL | 0 | |

| | c | char (120) | NO | |

| | pad | char (60) | NO | |

| | last_update_time | timestamp | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | |

+-- +

5 rows in set (0.02 sec)

1.5 Table switching

Use replTestDB

Set names utf8

Rename table sbtest1 to old_sbtest1,new_sbtest1 to sbtest1

Mysql > SELECT TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_OBJECT_SCHEMA

-> EVENT_OBJECT_TABLE

-> FROM information_schema.TRIGGERS

-> WHERE event_object_schema = 'replTestDB'

+-+

| | TRIGGER_SCHEMA | TRIGGER_NAME | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE | |

+-+

| | replTestDB | sbtest1_AI_oak | replTestDB | sbtest1 | |

| | replTestDB | sbtest1_AU_oak | replTestDB | sbtest1 | |

| | replTestDB | sbtest1_AD_oak | replTestDB | sbtest1 | |

+-+

3 rows in set (0.01sec)

Drop trigger sbtest1_AI_oak

Drop trigger sbtest1_AU_oak

Drop trigger sbtest1_AD_oak

Drop table old_sbtest1