ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql之 openark-kit online ddl

mysql之 openark-kit online ddl

原创 Linux操作系统 作者:张冲andy 时间:2018-10-22 09:47:29 0 删除 编辑

MySQL工具集openark-kit (官方网站 http://code.openark.org/forge/openark-kit),内部包含很多小工具,在5.6之前用于实现online ddl操作,
本文以CentOS为操作系统,且默认操作系统中已经安装Python环境。

 

1.0、 安装openark-kit工具包

安装Python模块包之MySQL-python,用于使用Python连接操作MySQL使用。
yum install -y MySQL-python

RPM安装方式

获得RPM包 https://code.google.com/p/openarkkit/downloads/detail?name=openark-kit-196-1.noarch.rpm
执行命令 rpm -ivh openark-kit-196-1.noarch.rpm


TAR包安装方式

获取tar包 https://code.google.com/p/openarkkit/downloads/detail?name=openark-kit-196.tar.gz
解压tar包 tar -zxvf openark-kit-196.tar.gz -C /usr/local/openark-kit/
安装openark-kit工具 python setup.py install

 

1.1 sysbench加载数据

/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 检查ONLINE_DDL表是否有外键触发器 有则删除

** 通过 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 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表切换

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.01 sec)


drop trigger sbtest1_AI_oak;
drop trigger sbtest1_AU_oak;
drop trigger sbtest1_AD_oak;
drop table old_sbtest1;

 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31383567/viewspace-2217016/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
11g oracle ocp、5.6 mysql ocp , 专注数据库领域,熟悉oracle、mysql、redis、mongodb等相关技术

注册时间:2016-08-11

  • 博文量
    560
  • 访问量
    145725