MYSQL線上の大表フィールド変更案

質問:MySQLでALTER TABLE操作を実行する場合、MySQLは元のテーブルの一時的なコピーを作成することで動作することを知っています.テーブル構造の変更をレプリカで実行し、新しいテーブルを元のテーブルに置き換えます.ロックテーブルが生成され、ユーザーは元のテーブルからデータを読み込むことができ、ユーザーの更新と書き込み操作はlockされ、新しいテーブルが準備されたら新しいテーブルに書き込まれます.この過程でテーブルがロックされるからです.現在の操作のテーブルにデータを書き込むことができず、ユーザーの使用に影響します.元のテーブルのデータを中間テーブルにコピーする必要があるため、テーブルのデータ量が大きいほど待ち時間が長くなり、そこでカードが死んでしまう(ユーザーはupdateとinsert操作を拒否され、遅延してずっと待っていることを示している).解決方法:

は、まず、サービスを提供するマシン上でalter table操作を実行し、その後、サービスを提供するメインライブラリと

を切り替える.

シャドウコピー(元のテーブルに関係のない新しいテーブルを要求されたテーブル構造で作成し、テーブル構造の名前を変更および削除する操作で2つのテーブルを交換します)は、いくつかの補助ツールで支援できます.eg:フェイスブックデータ運営チームが開発した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 '
' 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 '
' ;

完了