MYSQL线上大表字段改动方案

问题：
我们知道在MySQL中如果要执行ALTER TABLE操作，MySQL会通过制作原来表的一个临时副本来工作。对于表结构的修改在副本上施行，然后将新表替换原始表，此时会产生锁表，用户可以从原始表读取数据，而用户的更新和写入操作都会被lock，待新表准备好后写入新表。
由于在这个过程中会锁表。造成当前操作的表无法写入数据，影响用户使用。由于需要复制原表的数据到中间表，所以表的数据量越大，等待的时候越长，卡死在那里(用户被拒绝执行update和insert操作,表现就是延迟了一直在等待)。
解决方式：

1. 先在一台不提供服务的机器上执行alter table操作，然后和提供服务的主库进行切换
2. 影子拷贝（用要求的表结构创建一张和原表无关的新表，然后通过重命名和删除表结构的操作交换两张表），这些可以用一些辅助工具帮助完成。eg：facebook数据运维团队开发的online schema change、Shlomi Noach的openark toolkit以及Percona Toolkit

我们这里借助影子拷贝的思想利用mysql load data 与 select into outfile手动导数据：
导出语法：

SELECT fields INTO OUTFILE 'file_name'
    [{FIELDS | COLUMNS}                     字段
        [TERMINATED BY 'string']                字段之间分隔符号
        [[OPTIONALLY] ENCLOSED BY 'char']       字段被包含在char中间
        [ESCAPED BY 'char']                     忽略字段里出现的char
    ]
    [LINES
        [STARTING BY 'string']              忽略开头是string的行
        [TERMINATED BY 'string']            行分隔符
    ]
FROM test_table;

导入语法：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]                      遇到重复的时候处理方法，替换或者是忽略
    INTO TABLE tbl_name                     导入数据的目的表名
    [PARTITION (partition_name,...)]        分区选择
    [CHARACTER SET charset_name]            字符集
    [{FIELDS | COLUMNS}                     字段
        [TERMINATED BY 'string']                字段之间分隔符号
        [[OPTIONALLY] ENCLOSED BY 'char']       字段被包含在char中间
        [ESCAPED BY 'char']                     忽略字段里出现的char
    ]
    [LINES
        [STARTING BY 'string']              忽略开头是string的行
        [TERMINATED BY 'string']            行分隔符
    ]
    [IGNORE number {LINES | ROWS}]          忽略行/列
    [(col_name_or_user_var,...)]            目的表的表字段名或者用户变量名
    [SET col_name = expr,...]               设置表字段值

原表结构：

CREATE TABLE `user_info` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `id_card` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '身份证',
  `name` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '名称',
  `age` int(5) DEFAULT '0' COMMENT '年龄',
  `sex` varchar(5) CHARACTER SET utf8 DEFAULT '' COMMENT '性别',
  `address` varchar(50) CHARACTER SET utf8 DEFAULT '' COMMENT '住址',
  `company` varchar(20) COLLATE utf8_unicode_ci DEFAULT '' COMMENT '公司',
  `nation` varchar(10) CHARACTER SET utf8 DEFAULT '' COMMENT '民族',
  `education` varchar(10) CHARACTER SET utf8 DEFAULT '' COMMENT '学历',
  `phone` varchar(15) CHARACTER SET utf8 DEFAULT '' COMMENT '联系电话',
  `remark` varchar(50) CHARACTER SET utf8 DEFAULT '' COMMENT '备注',
  `created_on` datetime NOT NULL COMMENT '创建时间',
  `created_user_name` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '创建人name',
  `modified_on` datetime DEFAULT NULL COMMENT '修改时间',
  `modified_user_name` varchar(20) CHARACTER SET utf8 DEFAULT '' COMMENT '修改人name',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_index` (`id_card`) USING BTREE,
  KEY `normal_index` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='人员基本信息';

目标表结构：

CREATE TABLE `user_info_test` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `biz_id` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '业务唯一主键',
  `id_card` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '身份证',
  `name` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '名称',
  `age` int(5) DEFAULT '0' COMMENT '年龄',
  `sex` varchar(5) CHARACTER SET utf8 DEFAULT '' COMMENT '性别',
  `address` varchar(50) CHARACTER SET utf8 DEFAULT '' COMMENT '住址',
  `company` varchar(20) COLLATE utf8_unicode_ci DEFAULT '' COMMENT '公司',
  `nation` varchar(10) CHARACTER SET utf8 DEFAULT '' COMMENT '民族',
  `education` varchar(10) CHARACTER SET utf8 DEFAULT '' COMMENT '学历',
  `phone` varchar(15) CHARACTER SET utf8 DEFAULT '' COMMENT '联系电话',
  `remark` varchar(50) CHARACTER SET utf8 DEFAULT '' COMMENT '备注',
  `created_on` datetime NOT NULL COMMENT '创建时间',
  `created_user_name` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '创建人name',
  `modified_on` datetime DEFAULT NULL COMMENT '修改时间',
  `modified_user_name` varchar(20) CHARACTER SET utf8 DEFAULT '' COMMENT '修改人name',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_index` (`id_card`) USING BTREE,
  KEY `normal_index` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='人员基本信息-数据导入导出测试表';

导出

在mysql命令行执行命令

select id, case age when 10 then 1 when 20 then 2 else 0 end as biz_id, id_card, name, age, sex, address, company, nation, education, phone, remark, created_on, created_user_name, modified_on, modified_user_name into outfile '/tmp/data/datafile' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from user_info;

这一步可能会报错：

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

解决方式：
在/etc/my.cnf配置文件中加入secure-file-priv=''，然后重启mysql服务器，service mysqld restart
也有可能会报错：

ERROR 1 (HY000): Can't create/write to file '/tmp/data/datafile' (Errcode: 13 - Permission denied)

解决方式：
给mysql用户加上/tmp/data目录的rwx权限就可以啦

导入：

load data infile '/tmp/data/datafile' into table user_info_test fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' ;

完成