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

来源：互联网发布：淘宝搜索词分析收费编辑：程序博客网时间：2024/05/22 00:22

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.listi=1cnt=0for tab in $(cat /root/tv/data/schema_name.room_info.list)docol_num=$itab_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++ ))doneecho 最后的计算数据是：$cnt

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

#!/bin/bashrm -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.listi=1for tab in $(cat /root/tv/data/schema_name.room_info.list)docol_num=$itab_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

0 0