openark对MySQL进行Online_DDL
来源:互联网 发布:matlab矩阵怎么运算 编辑:程序博客网 时间:2024/06/05 00:37
1.用oak对表sbtest1做添加字段和增加索引的Online DDL
openark kit 提供一组小程序,用来帮助日常的 MySQL 维护任务,可代替繁杂的手工操作。包括:oak-apply-ri: apply referential integrity on two columns with parent-child relationship.oak-block-account: block or release MySQL users accounts, disabling them or enabling them to login.oak-chunk-update: Perform long, non-blocking UPDATE/DELETE operation in auto managed small chunks.oak-kill-slow-queries: terminate long running queries.oak-modify-charset: change the character set (and collation) of a textual column.oak-online-alter-table: Perform a non-blocking ALTER TABLE operation.oak-purge-master-logs: purge master logs, depending on the state of replicating slaves.oak-security-audit: audit accounts, passwords, privileges and other security settings.oak-show-limits: show AUTO_INCREMENT “free space”.oak-show-replication-status: show how far behind are replicating slaves on a given master.
**原文链接: http://www.oschina.net/p/openark-kit**
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 安装 oak
cd /u01/toolstar -xzvf openark-kit-196.tar.gz cd openark-kit-196#安装时报错 ImportError: No module named MySQLdbyum install MySQL-python
1.3 检查ONLINE_DDL表是否有外键触发器 有则删除
** 通过 information_schema.key_column_usage**
SELECT TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_OBJECT_SCHEMA,EVENT_OBJECT_TABLEFROM information_schema.TRIGGERSWHERE event_object_schema = 'replTestDB';Select * from information_schema.key_column_usage whereReferenced_table_schema='replTestDB' andReferenced_table_name='sbtest1';
1.4 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.5 ONLINE_DDL后数据校验
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)mysql> select count(1) from new_sbtest1 -> ;+----------+| count(1) |+----------+| 991001 |+----------+1 row in set (0.36 sec)mysql> select count(1) from sbtest1 -> ;+----------+| count(1) |+----------+| 991001 |+----------+1 row in set (0.36 sec)
1.6表切换
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;
阅读全文
0 0
- openark对MySQL进行Online_DDL
- openark对MySQL进行Online_DDL
- Mysql 5.6 ONLINE_DDL
- 对MYSQL进行压力测试
- mybatis对mysql进行分页
- MYSQL对表进行操作
- mysql 对时间进行操作
- 如何对MySQL 服务器进行调优
- 如何对MySQL 服务器进行调优。
- 用批处理对MySQL进行数据操作
- mysqlslap对MySQL进行压力测试
- 用mysqlslap对MySQL进行压力测试
- 用mysqlslap对MySQL进行压力测试
- 用mysqlslap对MySQL进行压力测试
- 根据STATUS信息对MySQL进行优化
- 根据STATUS信息对MySQL进行优化
- 如何对MySQL 服务器进行调优
- 对mysql数据库进行备份和恢复
- 面试怎么谈薪资——让自己利益最大化
- 求救大神
- eclipse工程引入新版本的包
- Java读写文件(Excel)
- 系统管理员应该知道的20条Linux命令
- openark对MySQL进行Online_DDL
- 【es】es避坑小餐
- hdu 2067 卡特兰数打表(求解多边形,圆形等分割问题,括号匹配)(一直在更新。。。)
- 树莓派3在无屏幕无路由器无串口情况下的使用
- Layer:1、alert点击确定按钮后回调
- FreeImage库HDR转JPEG
- bugku 输入密码查看flag
- 关于Dalvik,我们该知道些什么?
- iOS屏幕旋转,强制横竖屏