Mysql分表数据通过Shell进行导出与统计

1、分表数据的统计
[root@master tv]# cat submeter_data_static.sh 

#!/bin/bash

#/usr/bin/mysql -hsrchostip -P50506 -usrcmysqluser -ppasswordstring -N -e"select table_name from information_schema.tables where table_schema='schema_name' and table_name like 'room_info_%';" > /root/tv/data/schema_name.room_info.list

i=1
cnt=0

for tab in $(cat /root/tv/data/schema_name.room_info.list)
do

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}'  /root/tv/data/schema_name.room_info.list)
#echo $tab_name

#room_info data proc ...
cnt_value=$(/usr/bin/mysql -hsrchostip -P50506 -usrcmysqluser -ppasswordstring -N -e"select count(*) from schema_name.$tab_name where substr(updated_time,1,10) ='2016-09-28' and state='0';")

cnt=`expr $cnt + $cnt_value`
echo $cnt

: $(( i++ ))
done

echo 最后的计算数据是：$cnt

2､分表数据的导出与导入
[root@master tv]# cat submeter_data_export&import.sh 

#!/bin/bash

rm -rf /root/tv/data/schema_name.room_info.dat

/usr/bin/mysql -hsrchostip -P50506 -usrcmysqluser -ppasswordstring -N -e"select table_name from information_schema.tables where table_schema='schema_name' and table_name like 'room_info_%';" > /root/tv/data/schema_name.room_info.list

i=1

for tab in $(cat /root/tv/data/schema_name.room_info.list)
do

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}'  /root/tv/data/schema_name.room_info.list)
echo $tab_name

#room_info data proc ...
/usr/bin/mysql -hsrchostip -P50506 -usrcmysqluser -ppasswordstring -N -e"select CONCAT(ifnull(\`room_id\`,''),'|',ifnull(\`uid\`,''),'|',ifnull(\`state\`,''),'|',ifnull(\`created_time\`,''),'|',ifnull(\`updated_time\`,'')) from schema_name.$tab_name;" >> /root/tv/data/schema_name.room_info.dat

: $(( i++ ))
done

/usr/bin/mysql -hstatic_database_ip -uroot -p123 -e "use guanzhu;truncate table room_info;load data local infile '/root/tv/data/schema_name.room_info.dat' into table room_info fields terminated by '|' enclosed by '' lines terminated by '\n' ignore 0 lines;"

if false;then
#room_info data proc ...
/usr/bin/mysql -hsrchostip -P50506 -usrcmysqluser -ppasswordstring -N -e"select CONCAT(ifnull(\`room_id\`,''),'|',ifnull(\`uid\`,''),'|',ifnull(\`state\`,''),'|',ifnull(\`created_time\`,''),'|',ifnull(\`updated_time\`,'')) from schema_name.room_info_0;" >> /root/tv/data/schema_name.room_info.dat
/usr/bin/mysql -hstatic_database_ip -uroot -p123 -e "use guanzhu;load data local infile '/root/tv/data/schema_name.room_info.dat' into table room_info fields terminated by '|' enclosed by '' lines terminated by '\n' ignore 0 lines;"
fi