shell脚本实现mysql大数据量导出导入,自动化更新大数据表数据

         针对MyISAM引擎百万级甚至亿级数据量表高效率更新，可以采用 select into outfile方式将所需数据进行产出文件， 采用load data infile对产出文件进行导入操作。具体操作与解释如下：

1.编写数据更新运行shell脚本a_run.sh

#!/bin/sh
starttime=`date +'%Y-%m-%d %H:%M:%S'` #记录开始时间
echo "正在导出a表所需信息";
rm -f /home/a_old.dat  #删除之前存在的产出文件a_old.dat
sh /home/export_shell/export_a.sh $database  #执行导出a.dat文件脚本  $database为所选数据库名
wait;  #等待导出脚本执行完毕
if [ $? -eq 0 ];then  #执行成功后
   wc -l /home/a_new.dat  #查看导出脚本a_new.dat行数
   echo "a_new.dat export success path:/home/a_new.dat";
else
  echo "fail";
  exit;
fi
echo "创建a_new表==>sh /home/create_a_new.sh $database";
sh /home/create_a_new.sh $database
wait;
if [ $? -eq 0 ];then
   echo "create a_new success";
else
  echo "fail";
  exit;
fi
echo "刷新a_new表==>sh /home/flush_a_new.sh $database";
sh /home/flush_a_new.sh $database
wait;
echo "删除a_new表索引"
myisamchk --keys-used=0 -rq /home/database/$database/a_new
wait;
echo "插入数据";
sh /home/import_shell/insert_a_new.sh  $database
wait;
if [ $? -eq 0 ];then
   echo "a_new import success";
else
  echo "fail";
  exit;
fi
echo "插入数据成功后重新组建a_new表索引";
myisamchk -rf /home/database/$database/a_new --sort_buffer_size=10G
wait;
echo "再次刷新a_new表";
sh /home/flush_a_new.sh  $database
wait;
echo "备份a_old到a_old_copy"
mysql -uuser -ppwd <<EOF  #连接数据库，进行旧表a的备份，并重命名新表a到旧表a
    USE  $database;
    RENAME TABLE a_old_name TO a_old_name_copy,
                 a_new TO a_old_name,
                 a_old_name_copy TO a_new; 
EOF
endtime=`date +'%Y-%m-%d %H:%M:%S'`
start_seconds=$(date --date="$starttime" +%s);
end_seconds=$(date --date="$endtime" +%s);
echo "本次运行时间： "$((end_seconds-start_seconds))"s";
echo "结束时间："$(endtime);

2.利用 select into outfile编写导出数据shell脚本export_a.sh

#!/bin/bash
mysql -uuse -ppwd <<!  #连接数据库
    USE ${1};   #设置数据库名参数
    SELECT t1.id,substring_index(substring_index(REPLACE(REPLACE(t1.company_org_type,'（','('),'）',')'),')',1),'(',-1),REPLACE(REPLACE(IFNULL(t2.status_value,''), CHAR(10), ''), CHAR(13), ''),IF(t1.company_org_type LIKE '%个体%' OR t1.company_org_type in('个人','个人经营'),1,2),REPLACE(REPLACE(IFNULL(t3.province_id,''), CHAR(10), ''), CHAR(13), '')    
    INTO OUTFILE '/home/a_new.dat' FIELDS TERMINATED BY '\t' ESCAPED BY '' OPTIONALLY ENCLOSED BY '^K' LINES TERMINATED BY '\n'  #定义输出文件名
    FROM company t1 LEFT JOIN reg_status t2 ON(t1.reg_status=t2.reg_status) LEFT JOIN company_base_code t3 ON(t1.base = t3.base);
!
exit;

3.利用 load data infile编写导入数据shell脚本 insert_a_new.sh

#!/bin/bash
mysql -uuser -ppwd <<EOF
        use ${1};
        LOAD DATA INFILE "/home/a_new.dat" REPLACE INTO TABLE a_new FIELDS TERMINATED BY '\t' ESCAPED BY '' OPTIONALLY ENCLOSED BY '^K' LINES TERMINATED BY '\n'
        (@column_names1,@column_names2,@column_names3,@column_names4,@column_names5,@update_time)
        set
        column_names1=if(@column_names1='',NULL,@column_names1),
        column_names2=if(@column_names2='',NULL,@column_names2),
        column_names3=if(@column_names3='',NULL,@column_names3),
        column_names4=if(@column_names4='',NULL,@column_names4),
        column_names5=if(@column_names5='',NULL,@column_names5),
        update_time=now();  #插入录入时间；
EOF
exit;

 4.编写创建表shell脚本create_a_new.sh

#!/bin/bash
mysql -uuser -ppwd <<EOF

    USE ${1};
    CREATE TABLE IF NOT EXISTS a_new LIKE a_old; #复制a_old表结构
    TRUNCATE TABLE a_new;  #若存在则清空数据

EOF

5.编写刷新表shell脚本flush_a_new.sh

#!/bin/sh
mysql -uuser -ppwd <<EOF

    USE ${1};

    FLUSH TABLE a_new; 

EOF

 6.输入crontab -e设置定时

40 15 * * * sh /home/a_run.sh

crontab时间格式说明

        minute(分)可以设置0-59分

        hour（小时）可以设置0-23小时

        day of month（日期）可以设置1-31号

        month（月份）：可以设置1-12月

        day of week（星期）：可以设置0-7星期几，其中0和7都代表星期天，或者也可以使用名称来表示星期天到星期一，例如sun表示星期天，mon表示星期一等等
 

crontab范例

    每五分钟执行  */5 * * * *

    每小时执行     0 * * * *

    每天执行        0 0 * * *

    每周执行       0 0 * * 0

    每月执行        0 0 1 * *

    每年执行       0 0 1 1 *