1、首先进行数据训的XltraBackup备份,有备无患,切记切记!
2、
mysql -uroot -pD********
-- 导出csv文件
use dsideal_db;
MariaDB [dsideal_db]> SELECT * from t_resource_info INTO OUTFILE "/usr/local/mysql/t_resource_info.txt" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK,1582463 rows affected (29.97sec)
3、切分csv文件,这样分批导入更快,更方便,参考这里:http://www.nowamagic.net/librarys/veda/detail/2495,但是不能使用按容量啊,一定要按行。说日志太大拿不回来的,罚面壁一小时!
mkdir/usr/local/huanghai -p
split-a 2 -d -l 50000 /usr/local/mysql/t_resource_info.txt /usr/local/huanghai/prefix2-3秒吧
3、清空原表,修改字段,反正有备份,不怕的
truncate t_resource_info;alter table t_resource_info add huanghai_test int;
4、优化环境配置,准备开始导入
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;set sql_log_bin=0;
SET @innodb_additional_mem_pool_size=26214400;set @innodb_buffer_pool_size=1073741824;set @innodb_log_buffer_size=8388608;set @innodb_log_file_size=268435456;
load data infile'/usr/local/huanghai/prefix00' IGNORE into table dsideal_db.t_resource_info_huanghai fields terminated by ',' enclosed by '"';
load data infile'/usr/local/huanghai/prefix01' IGNORE into table dsideal_db.t_resource_info_huanghai fields terminated by ',' enclosed by '"';
load data infile'/usr/local/huanghai/prefix02' IGNORE into table dsideal_db.t_resource_info_huanghai fields terminated by ',' enclose