Mysql繁忙主从库在线修改表结构与添加索引问题

原文地址：http://www.itpub.net/thread-1841034-1-1.html



本帖最后由 jan_1985 于 2014-1-15 13:28 编辑


Mysql繁忙主从库在线修改表结构与添加索引问题
一直以来，生产情况下都有修改索引和修改字段的需求，但是对锁表引起的访问不便是会严重影响到在线业务的。
以前对于索引修改都是在其中一台从库中执行，然后将查询转到相应的服务器上，这种方式不便于服务器变更的情况，也不能满足表结构的修改的情形。
经过测试openark-kit这个套件下oak-online-alter-table这个工具可以实现在线修改表结构，我已经正式在主库使用了4次，目前来说运行良好。
应用环境: Mysql 5.1
工具版本:   openark-kit-180
工具的局限性:
    The table must have at least one UNIQUE KEY (may expand one or more columns)
    The altered table must share a UNIQUE KEY with the original one
    No 'AFTER' triggers may be defined on the table
    Foreign keys currently not supported
    Table name must be at most 57 characters long
工具的说明页面 http://code.google.com/p/openarkkit/wiki/OakOnlineAlterTableSteps


安装工具:
wget http://openarkkit.googlecode.com/files/openark-kit-180.tar.gz
yum install MySQL-python -y
tar -zxvf openark-kit-180.tar.gz
cd openark-kit-180
python setup.py install

运行:
oak-online-alter-table -H localhost -u myuser --ask-pass -S /tmp/mysql.sock \
      -d dbname --table=dbname.tablename -c 3000
      --alter="add column signup_ip int(11) unsigned NOT NULL DEFAULT '' AFTER signup_time;"



工具的实现原理:
oak-online-alter-table 首先新建一个镜像表，将alter应用上去。再将数据从原表同步到镜像表，最后以镜像表替代原表。
一次从原表取出来到镜像表的记录数量 chunk值的设置依据整个表的记录数量和正常读写量不同而设置合适的配置。
当然，做为一个好的DBA，永远都要有两手准备。预案是少不了的。每次修改前我将备份表，并暂停一个备份从库的同步进程，直到执行完没有问题。

工具执行的SQL:
# 建立包含新结构的表
CREATE TABLE dbname.__oak_tablename LIKE dbname.tablename;
ALTER TABLE dbname.__oak_tablename add column signup_ip int(11) unsigned NOT NULL DEFAULT '' AFTER signup_time;
# 建立触发器，使新表与旧表在工具执行期间保持数据同步
CREATE DEFINER=`root`@`%` TRIGGER dbname.tablename_AD_oak AFTER DELETE ON dbname.tablename
        FOR EACH ROW
            DELETE FROM dbname.__oak_tablename WHERE (id) = (OLD.id);
CREATE DEFINER=`root`@`%` TRIGGER dbname.tablename_AU_oak AFTER UPDATE ON dbname.tablename
        FOR EACH ROW
        BEGIN
            DELETE FROM dbname.__oak_tablename WHERE (id) = (OLD.id);
            REPLACE INTO dbname.__oak_tablename (id, signup_time, login_disid, signup_siteman, login_time, signup_mid, signup_gid, signup_webads, signup_disid, login_gid, signupfrom) VALUES (NEW.id, NEW.signup_time, NEW.login_disid, NEW.isolduser, NEW.signup_siteman, NEW.login_time, NEW.signup_mid, NEW.signup_gid, NEW.signup_webads, NEW.signup_disid, NEW.member_id, NEW.login_gid, NEW.signupfrom);
        END;
CREATE DEFINER=`root`@`%` TRIGGER dbname.tablename_AI_oak AFTER INSERT ON dbname.tablename
        FOR EACH ROW
            REPLACE INTO dbname.__oak_tablename (id, signup_time, login_disid, signup_siteman, login_time, signup_mid, signup_gid, signup_webads, signup_disid, login_gid, signupfrom) VALUES (NEW.id, NEW.signup_time, NEW.login_disid, NEW.isolduser, NEW.signup_siteman, NEW.login_time, NEW.signup_mid, NEW.signup_gid, NEW.signup_webads, NEW.signup_disid, NEW.member_id, NEW.login_gid, NEW.signupfrom);

#将数据从旧表插入到新表 chunk模式插入数据.并且随时保持同步。
SET @`unique_key_range_start_0`:=1/*!*/;
SET @`unique_key_range_end_0`:=3000/*!*/;

SET TIMESTAMP=1357542299/*!*/;
INSERT IGNORE INTO dbname.__oak_tablename (id, signup_time, login_disid, signup_siteman, login_time, signup_mid, signup_gid, signup_webads, signup_disid, login_gid, signupfrom)
            (SELECT id, signup_time, login_disid, signup_siteman, login_time, signup_mid, signup_gid, signup_webads, signup_disid, login_gid, signupfrom FROM dbname.tablename
            WHERE
                (((id > @unique_key_range_start_0) OR ((id = @unique_key_range_start_0)))
                AND
                ((id < @unique_key_range_end_0) OR ((id = @unique_key_range_end_0))))
            )
/*!*/;
........... 略
#delete 那些在旧表中不存在(执行期间被删除的记录)的数据.
SET @`unique_key_range_start_0`:=1/*!*/;
SET @`unique_key_range_end_0`:=3000/*!*/;
DELETE FROM dbname.__oak_tablename
        WHERE
            (((id > @unique_key_range_start_0) OR ((id = @unique_key_range_start_0)))
            AND
            ((id < @unique_key_range_end_0) OR ((id = @unique_key_range_end_0))))
        AND (id) NOT IN
            (SELECT id FROM dbname.tablename
            WHERE
                (((id > @unique_key_range_start_0) OR ((id = @unique_key_range_start_0)))
                AND
                ((id < @unique_key_range_end_0) OR ((id = @unique_key_range_end_0))))
            );
SET @`unique_key_range_start_0`:=3000/*!*/;
SET @`unique_key_range_end_0`:=6000/*!*/;
DELETE FROM dbname.__oak_tablename
        WHERE
            (((id > @unique_key_range_start_0))
            AND
            ((id < @unique_key_range_end_0) OR ((id = @unique_key_range_end_0))))
        AND (id) NOT IN
            (SELECT id FROM dbname.tablename
            WHERE
                (((id > @unique_key_range_start_0))
                AND
                ((id < @unique_key_range_end_0) OR ((id = @unique_key_range_end_0))))
            );

........... 略
#替换旧表
RENAME TABLE
            dbname.tablename TO dbname.__arc_tablename,
            dbname.__oak_tablename TO dbname.tablename;
DROP TABLE IF EXISTS dbname.__arc_tablename;


Mysql, 主从, 在线, 索引, 生产