shell 脚本多进程创建 mysql 测试数据

# ------------------------------------------------------------------

#创建 taskNum * perFileRecord 条测试数据，taskNum 为进程并发数，最好与你的cpu个数一致

#数据字段：| id | sex | age | name | email | start_date | end_date |

#id int 唯一，sex enum，age 0~100，name、email基本唯一，start_date 0~1000天前的日期

?

<pre name="code" class="plain">#!/usr/bin/env bash
# numProcessor=`grep "processor" /proc/cpuinfo | wc -l`
# genfile.sh
stime=$(date +%s)
taskNum=$1
perFileRecord=$2
offset=$3
 
colorEcho(){
    echo -e "\e[31;1m$1\e[0m"
}
 
usage(){
    colorEcho " Usage:"
    colorEcho " bash fileName taskNum(1-99) perFileRecord(>0) [offset(>0)]"
    exit 1
}
 
initEnv(){
    [[ "$1 $2 $3" =~ ^[0-9]{1,2}" "[0-9]{1,} ]] || usage
    rm -f [0-9]*.txt
}
 
createRecord(){
    start=$1
    end=$2
    for i in `seq -f%.10g $start $end`
    do
        id=$i
        sex=`echo $i|awk '{print ($0%2?"male":"female")}'`
        age=`echo $(($RANDOM/500))`
        emailName=`head -c8 /proc/sys/kernel/random/uuid`
        name=$emailName
        rand1000=`echo $(($RANDOM/50))`
        rand100=$age
        rand900=`echo "$rand1000 $rand100"|awk '{s=$1-$2;print (s>0?$2:0)}'`
        start_date=`date -d"$rand1000 days ago" +%Y%m%d`
        end_date=`date -d"$rand900 days ago" +%Y%m%d`
        echo -e "$id\t$sex\t$age\t$name\t$emailName@qq.com\t$start_date\t$end_date"
    done
}
 
process(){
    for i in `seq $taskNum`
    do
        st=`echo "$i $perFileRecord $offset"|awk '{print ($1-1)*$2+1+$3}'`
        ed=`echo "$i $perFileRecord $offset"|awk '{print $1*$2+$3}'`
        echo $st $ed
        createRecord $st $ed > $i.txt &
    done
    wait
}
 
finally(){
    cat `seq $taskNum|sed 's/.*/&.txt/'` > record_`date -I`.txt
    echo "————————————————————————————————————————————————————————————"
    file=record_`date -I`.txt
    line=`echo $taskNum $perFileRecord|awk '{print $1*$2}'`
    size=`ls -hl $file |awk '{print $5}'`
    etime=$(date +%s)
    diffTime=$(($etime-$stime))
    echo "--------------->>        FileName: $file"
    echo "--------------->>        FileLine: $line"
    echo "--------------->>        FileSize: $size"
    echo "--------------->>        CostTime: $diffTime"
    echo "————————————————————————————————————————————————————————————"
}
 
initEnv $taskNum $perFileRecord $offset
process
finally

#!/usr/bin/env bash
# mgr.sh
 
param1=$1
param2=$2
fileName=record_`date -I`.txt
 
colorEcho(){
    echo -e "\e[31;1m$1\e[0m"
}
 
usage(){
    colorEcho " Usage:"
    colorEcho " bash fileName [-c int(1-99)|-k processName]"
    exit 1
}
 
cpMergeFiles(){
    rm -f $fileName"_"*
    for i in `seq $param2`
    do
        cp $fileName $fileName"_"$i &
    done
    wait
     
    cat ${fileName}"_"*|awk -vOFS="\t" '{$1=NR;$0=$0;print}' > $fileName"_merge"
    echo "copy finished, and FileName is: "$fileName"_merge"
}
 
killTask(){
    echo "$param2 is number: "`ps -ef|grep $param2|wc -l`
    echo "will kill..."
    pkill -9 -f $param2
    # here has a bug ... 2 cmds will not exec...
        # Because "bash mgr.sh -k genfile.sh" will kill itself...
    echo "kill finished..."
    echo "$param2 is number: "`ps -ef|grep $param2|grep -v grep|wc -l`
}
 
execTask(){
    if [[ $param1 == "-c" && $param2 =~ [0-9]{1,2} ]]
    then
        cpMergeFiles
    elif [[ $param1 == "-k" && $param2 =~ .{1,} ]]
    then
        killTask
    else
        usage
    fi
}
 
execTask

?

在一台 2 核的机器上测试结果如下：

# ------------------------------------------------------------------

#建表，存储引擎请自己指定

createtablet_test(
    idint,
    sex enum('male','female'),
    age tinyint unsigned,
    namechar(8),
    emailvarchar(16),
    start_dateint,
    end_dateint
)engine = MYISAM CHARACTERSETutf8;

?

# ------------------------------------------------------------------

#加载数据：

use test;
LOADDATALOCALINFILE"/root/record.txt"INTOTABLEt_test fields terminated by' ' lines terminated by'\n';

#mysqlimport -f -uroot -h127.0.0.1  --fields-terminated-by=@  click  /tmp/tb_click_stream_tool.txt

# ------------------------------------------------------------------

#1、单纯的删掉主键属性，不删字段:

alter table t_test modify id int ,drop PRIMARY key;

#2、设置已有字段为主键、自增属性:

alter table t_test change id id int primary key auto_increment;

#3、创建联合索引：

alter table t_test add index id_sex_age (id,sex,age);

#4、删除联合索引：

alter table t_test drop index id_sex_age;

#5、查看索引：

show index from t_test; 

# ------------------------------------------------------------------

#创建关联表

create table t_test1 as select * from t_test order by rand() limit 1000;

# ------------------------------------------------------------------

#最终数据如下

mysql> select * from t_test limit 5;

+------+--------+------+----------+-----------------+------------+----------+

| id   | sex    | age  | name     | email           | start_date | end_date |

+------+--------+------+----------+-----------------+------------+----------+

|    1 | male   |   52 | e67bcd5f | e67bcd5f@qq.com |   20101120 | 20110111 |

|    2 | female |    2 | cc92a107 | cc92a107@qq.com |   20120123 | 20120125 |

|    3 | male   |   35 | 2ec6b1d8 | 2ec6b1d8@qq.com |   20120406 | 20120511 |

|    4 | female |   31 | 4c67aade | 4c67aade@qq.com |   20120302 | 20120402 |

|    5 | male   |   19 | b518a6a3 | b518a6a3@qq.com |   20120814 | 20120830 |

+------+--------+------+----------+-----------------+------------+----------+

5 rows in set (0.00 sec)

mysql> 

参考文献： http://my.oschina.net/leejun2005/blog/76631