MySQL 不停服务 在线进行100亿数据迁移切换
背景:
促销库压力巨大,单实例已经不足以提供写入性能,虽然有256个分表,但是经常io打满,总数据量已经达到100亿,单实例io已经支撑不住,所以方案是从促销库里面迁移2张压力大的业务分表到新的16个实例里面,2张分表的个数大概是256个,剩余的3000多个表不迁移,任留在原库,要求是尽量不停服,把影响尽量限制在这迁移的分表的业务上面。
大概思路是,锁这些迁移的分表,停止这些分表业务,然后把业务迁移到新的16个实例上,在新的实例上功能测试ok,手动kill原库上因为lock write而阻塞的sql线程,然后unlock加的分表锁。
大概流程是:(1)锁住256个表的write脚本准备,停止分表的业务;\n\n(2)停止数据原库到16个新实例的数据同步功能;\n\n(3)rename新实例的分表,将名字统一改成业务的xxx_2049 ... xxx_2065;\n\n(4)启动分表的业务,配合开发人员观察业务功能,同时观察原库的cpu压力,如果压力过高,直接kill因为加锁而导致的阻塞的sql线程;\n\n(5)功能ok后,先kill掉加锁后阻塞的sql线程,然后kill加锁的操作记录;123456789
脚本准备:
生成锁表脚本locktablessql.sh#!/bin/bashfor((i=0;i<128;i++))doinsno=`expr $i % 16`\ntbno_add=`expr $i / 16 + 1025 `echo "lock tables coupon_$i write;" >>"sql/locktables1.sql"donefor((i=0;i<128;i++))doinsno=`expr $i % 16`\ntbno_add=`expr $i / 16 + 1025 `echo "lock tables coupon_record_$i write;" >>"sql/locktables1.sql"done1234567891011121314
执行锁表操作脚本run_locktables.shcat sql/locktables1.sql |while read sql;domysql -h172.20.xxx.xxx -uroot -pxxxxxx promotion -e "$sql select sleep(10000);" & \ndone1234
kill阻塞线程脚本kill_online_lock_sql.sh#!/bin/bashhost=172.20.xxx.xxx\nuser=rootpwd=xxxx\nmysql -h$host -u$user -p$pwd promotion -N -e "select concat('kill ',id,';') from information_schema.processlist where info like ('%coupon_%' ) and time >30" |grep kill > sql/killonline.sql\nmysql -h$host -upromotionapi -pxxxxxx promotion < sql/killonline.sql123456
LOCK方式选择用lock tables coupon_record_$i write;不用flush tables coupon_$i with read lock;主要是因为flush table需要清空以前执行的链接,否则会报错。