在线修改表结构mysql5.5版本和pt-online-schema-change
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了在线修改表结构mysql5.5版本和pt-online-schema-change，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8509字，纯文字阅读大概需要13分钟。
内容图文
一、测试环境
系统：Centos 6.2
数据库：mysql Ver 14.14 Distrib 5.5.18, for Linux (x86_64) using readline 5.1
percona工具：percona-toolkit-2.2.12
测试数据库大小：tx_ljxz_71--16G、t_log_item--3G
二、在线修改表结构的过程
mysql在线修改表结构
1 按照原始表（original_table）的表结构和DDL语句，新建一个不可见的临时表（tmp_table）
2 在原表上加write lock，阻塞所有更新操作（insert、delete、update等）
3 执行insert into tmp_table select * from original_table
4 rename original_table和tmp_table，最后drop original_table
5 释放 write lock。
我们可以看见在InnoDB执行DDL的时候，原表是只能读不能写的。
pt-online-schema-change在线修改表结构
1 创建一个和你要执行 alter 操作的表一样的空表结构。
2 执行表结构修改，然后从原表中的数据到copy到 表结构修改后的表，
3 在原表上创建触发器将 copy 数据的过程中，在原表的更新操作 更新到新表.
注意:如果表中已经定义了触发器这个工具就不能工作了。
4 copy 完成以后，用rename table 新表代替原表，默认删除原表。
三、安装配置percona-toolkit
1.安装
官网下载即可
tar zxvf percona-toolkit-2.2.12.tar.gz perl Makefile.PL make make test make install
2.测试安装成功
pt-online-schema-change -uroot --alter="modify buff_id int(20)" --dry-run D=tx_ljxz_71,t=t_world_buff
报错：
Cannot connect to MySQL: install_driver(mysql) failed: Attempt to reload DBD/mysql.pm aborted. Compilation failed in require at (eval 21) line 3. at /usr/local/bin/pt-online-schema-change line 2261
后在虚拟机上的centos 6.5上安装测试，没发现同样的问题，经检测，是定制系统的问题
/usr/local/lib64/perl5/Bundle/DBD/mysql.pm /usr/local/lib64/perl5/DBD/mysql.pm
要求 perl-DBD-MySQL的安装目录在/usr/lib64/perl5/，将原来的目录移走，重新安装即可
mv /usr/local/lib64/perl5/ /data/backup/ yum install perl-DBD-MySQL
重测一次，返回
Operation, tries, wait: copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Starting a dry run. `tx_ljxz_71`.`t_world_buff` will not be altered. Specify --execute instead of --dry-run to alter the table. Creating new table... Created new table tx_ljxz_71._t_world_buff_new OK. Altering new table... Altered `tx_ljxz_71`.`_t_world_buff_new` OK. Not creating triggers because this is a dry run. Not copying rows because this is a dry run. Not swapping tables because this is a dry run. Not dropping old table because this is a dry run. Not dropping triggers because this is a dry run. 2015-01-28T17:00:22 Dropping new table... 2015-01-28T17:00:22 Dropped new table OK. Dry run complete. `tx_ljxz_71`.`t_world_buff` was not altered.
四、测试在线修改表结构
1.在线修改表结构的时间对比：
mysql在线修改表结构
time mysql -uroot tx_ljxz_71 -e "alter table t_log_item modify role_level int(20)"
| real 18m51.198s | real 18m17.492s | real 19m53.119s |
pt-online-schema-change在线修改表结构
time pt-online-schema-change --user=root --critical-load Threads_running=100 --alter="modify role_level int(20)" --execute D=tx_ljxz_71,t=t_log_item
--critical-load值默认的Threads_running为50，不修改会报错： Error copying rows from `tx_ljxz_71`.`t_log_item` to `tx_ljxz_71`.`_t_log_item_new`: Threads_running=51 exceeds its critical threshold 50
| real 18m56.473s | real 19m27.950s | real 18m58.556s |
此时，没有执行任何sql语句对数据库进行操作
2.在线修改表结构时对数据库进行增删查改
mysql在线修改表结构
time mysql -uroot tx_ljxz_71 -e "alter table t_log_item modify role_level int(20)"
执行测试脚本，发现除了select语句，其他语句无法继续执行直至表结构修改完毕再进行增删改的操作
pt-online-schema-change在线修改表结构
time pt-online-schema-change --user=root --critical-load Threads_running=100 --alter="modify role_level int(20)" --execute D=tx_ljxz_71,t=t_log_item
a.percona-toolkit默认的innodb_lock_wait_timeout为1，retries值为3，需要在mysql的配置文件中修改innodb_lock_wait_timeout值,如果innodb_lock_wait_timeout值不够大（隐患），在执行mysql语句的同时，复制数据表，会报错：
2015-02-03T16:22:48 Error copying rows from `tx_ljxz_71`.`t_log_item` to `tx_ljxz_71`.`_t_log_item_new`: 2015-02-03T16:22:48 DBD::mysql::st execute failed: Lock wait timeout exceeded; try restarting transaction [for Statement "INSERT LOW_PRIORITY IGNORE INTO `tx_ljxz_71`.`_t_log_item_new` (`pid`, `agent_id`, `server_id`, `role_id`, `role_level`, `action`, `item_id`, `amount`, `bag_amount`, `zero_dateline`, `year`, `month`, `day`, `equip_id`, `color`, `fineness`, `start_time`, `end_time`, `bind_type`, `super_unique_id`, `from`) SELECT `pid`, `agent_id`, `server_id`, `role_id`, `role_level`, `action`, `item_id`, `amount`, `bag_amount`, `zero_dateline`, `year`, `month`, `day`, `equip_id`, `color`, `fineness`, `start_time`, `end_time`, `bind_type`, `super_unique_id`, `from` FROM `tx_ljxz_71`.`t_log_item` FORCE INDEX(`PRIMARY`) WHERE ((`pid` >= ?)) AND ((`pid` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 20031 copy nibble*/" with ParamValues: 0=‘13794706058943‘, 1=‘13794714479917‘] at /usr/local/bin/pt-online-schema-change line 10385.
b.在线修改表结构时，会优先执行sql语句，暂停表结构的修改，暂停的时间如果大于innodb_lock_wait_timeout值会报上面一个错误，暂停的时间小于执行sql语句的，则执行完sql语句后，继续修改表结构,完成修改表结构的时间与在这期间执行sql语句的时间挂钩
Altering `tx_ljxz_71`.`t_log_item`... Creating new table... Created new table tx_ljxz_71._t_log_item_new OK. Altering new table... Altered `tx_ljxz_71`.`_t_log_item_new` OK. 2015-02-03T16:25:31 Creating triggers... 2015-02-03T16:25:31 Created triggers OK. 2015-02-03T16:25:31 Copying approximately 14221411 rows... Copying `tx_ljxz_71`.`t_log_item`: 0% 01:01:13 remain Copying `tx_ljxz_71`.`t_log_item`: 1% 59:04 remain Pausing because Threads_running=33. Pausing because Threads_running=30. Copying `tx_ljxz_71`.`t_log_item`: 1% 01:57:15 remain Copying `tx_ljxz_71`.`t_log_item`: 5% 44:33 remain ...... 2015-02-03T16:40:48 Copied rows OK. 2015-02-03T16:40:48 Swapping tables... 2015-02-03T16:40:50 Swapped original and new tables OK. 2015-02-03T16:40:50 Dropping old table... 2015-02-03T16:40:59 Dropped old table `tx_ljxz_71`.`_t_log_item_old` OK. 2015-02-03T16:40:59 Dropping triggers... 2015-02-03T16:40:59 Dropped triggers OK. Successfully altered `tx_ljxz_71`.`t_log_item`.
c.在线修改表结构时，内存会飙高
top - 14:52:46 up 110 days, 23:13, 3 users, load average: 2.04, 2.07, 1.51 Tasks: 242 total, 1 running, 241 sleeping, 0 stopped, 0 zombie Cpu(s): 12.6%us, 0.7%sy, 0.0%ni, 84.6%id, 2.2%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 12179948k total, 11409720k used, 770228k free, 174448k buffers Swap: 12287992k total, 17084k used, 12270908k free, 6134552k cached
五、结论
-
在没有执行sql语句时，mysql在线修改表结构的时间与pt-online-schma-change的基本上相等，无大的差别
-
在线修改表结构的同时执行mysql语句，mysql直接修改的方式会先修改完表结构再执行sql语句，pt-online-schema-change方式会优先执行sql语句，再复制数据表，复制完毕后再把执行sql语句的结果更新到新表
-
pt-online-schema-change方式会占用较大的内存
测试脚本：
#!/usr/local/bin/python import threading from os import system from random import randint import time import MySQLdb MYSQL = "/usr/local/bin/mysql -uroot" cxn = MySQLdb.connect(host=‘localhost‘, user=‘root‘, port=3306, db=‘tx_ljxz_71‘) def run1(db): role_level = randint(40, 70) start_time = int(time.time()) system(‘%s %s -e "select count(*) from t_log_item where role_level=%s"‘ % (MYSQL, db, role_level)) print ‘%s select count(*) from t_log_item where role_level=%s use %d sec.‘ % (db, role_level, int(time.time()) - start_time) def run2(db): month = randint(9, 10) day = randint(20, 30) start_time = int(time.time()) system(‘%s %s -e "select sum(use_unbind) from t_log_consume_gold where year=2013 and month=%s and day=%s"‘ % (MYSQL, db, month, day)) print ‘%s select sum(use_unbind) from t_log_consume_gold where year=2013 and month=%s and day=%s use %d sec.‘ % (db, month, day, int(time.time()) - start_time) def run3(db): p_id = randint(139249095950, 139349095950) start_time = int(time.time()) try: system(‘%s %s -e "insert into t_log_item (pid) values(%s)"‘ % (MYSQL, db, p_id)) except: print "insert into t_log_item failed" else: print ‘%s insert into t_log_item (pid) values(%s) use %d sec.‘ % (db, p_id, int(time.time()) - start_time) def run4(db): role_level = randint(1, 15) start_time = int(time.time()) try: system(‘%s %s -e "delete from t_log_item where role_level=%s;commit"‘ % (MYSQL, db, role_level)) except: print "delte from t_log_item failed" else: print ‘%s delete from t_log_item where role_level=%s use %d sec.‘ % (db, role_level, int(time.time()) - start_time) def run5(db): role_level = randint(16, 20) server_id = randint(1, 100) start_time = int(time.time()) try: system(‘%s %s -e "update t_log_item set server_id=%s where role_level=%s;commit"‘ % (MYSQL, db, server_id, role_level)) """cxn.commint() cxn.close()""" except: print "update t_log_item failed" else: print ‘%s update t_log_item set server_id=%s where role_level=%s use %d sec.‘ % (db, server_id, role_level, int(time.time()) - start_time) if __name__ == ‘__main__‘: threads = [] funcs = [run1, run2, run3, run4, run5] dbs = [‘tx_ljxz_71‘, ‘tx_ljxz_71‘, ‘tx_ljxz_71‘,‘tx_ljxz_71‘, ‘tx_ljxz_71‘] for i in range(50): threads.append(threading.Thread(target=funcs[randint(0,4)], args=(dbs[randint(0,4)],))) for i in range(50): threads[i].start() for i in range(50): threads[i].join()
本文出自 “运维小卒” 博客，转载请与作者联系！
原文：http://8721349.blog.51cto.com/8711349/1611203
内容总结
以上是互联网集市为您收集整理的在线修改表结构mysql5.5版本和pt-online-schema-change全部内容，希望文章能够帮你解决在线修改表结构mysql5.5版本和pt-online-schema-change所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。