表某个分片主备损坏后挽救可用分片方法

因硬件或操作系统文件系统故障，可能会造成表某个分片主备损坏，使用该表会报错无法使用，可使用以下方法和脚本挽救可用分片数据。
1、给数据节点创建导出数据目录/opt/exporterrdata
2、编辑确定恢复的表
cat exp_tables.txt
-----------------------------------------

chnasp.d_rnt_pim_switch_port_d
chnasp.d_enp_zmcc_office_outgoing_15m
2、导出正常分片数据脚本
cat exportdata.sh

--------------------------------
mygbased=`/sbin/pidof gbased`
if [ $mygbased -le 0 ];then
 exit
fi
while read table
do
 table=`echo $table|sed 's/ //g'`
 dbname=`echo $table|awk -F"." '{print $1}'`
 tablename=`echo $table|awk -F"." '{print $2}'`
 if [[ "$dbname" == ""  || "$tablename" == "" ]];then
   continue
 fi
 gncli -uroot -N -e"show tables from $dbname like '${tablename}%'" > ./exp_fptables.txt
 if [ $? -ne 0 ];then
   continue
 fi
 #onetable=`head -n 1 ./fptables.txt`
 #onetable=${dbname}.$onetable
 while read fptable
 do
 if [[ "$fptable" == "" ]];then
   continue
 fi
 #column_name_ext="concat('ifnull(column_name',"
 fpfields=`gncli -uroot -N -e"select group_concat(concat('ifnull(',column_name,';\'\\N\'',')')) from information_schema.columns where table_schema='$dbname' and table_name='$fptable'"`
 fpfields=`echo $fpfields|sed "s/,/,'@@',/g"`
 fpfields=`echo $fpfields|sed "s/;/,/g"`
 fpfields=`echo $fpfields|sed "s/'N'/'null'/g"`
 fpfields="concat("$fpfields")"
 echo "export data "${dbname}.${fptable}
 gncli -uroot -N -e"select $fpfields from ${dbname}.$fptable  " > /opt/exporterrdata/${dbname}.${fptable}.txt
 done<`pwd`/exp_fptables.txt
done<`pwd`/exp_tables.txt

3、将exp_tables.txt、exportdata.sh推到各数据节点/home/gbase
4、执行各节点exportdata.sh 在指定目录生成导出文件
cexec data: 'sh /home/gbase/exportdata.sh'
5、取得各节点主分片文件
cat cgetfpfile.sh 
---------------------------------------------------

sdatadir="/opt/exporterrdata"
gccli -uroot -N -e"show nodes" > ./ip_nodefp.txt
while read ip_fp
do
 ip=`echo $ip_fp|awk '{print $2}'`
 fp=`echo $ip_fp|awk '{print $4}'`
 fp1=`echo $fp|awk -F"," '{print $1}'`
 fp2=`echo $fp|awk -F"," '{print $2}'`
 fp1=$sdatadir/*_${fp1}.txt
 fp2=$sdatadir/*_${fp2}.txt
 echo gbase@$ip:$fp1 = gbase@$ip:$fp2
 scp gbase@$ip:$fp1  gbase@$ip:$fp2 /opt/20230711/
done <./ip_nodefp.txt

安装c3工具可以使用cget批量拷贝数据。

6、取得主分片文本文件后加载进表
gccli -e "load data infile 'sftp://root:password@10.76.*.*//opt/20230711/chnasp.d_enp_zmcc_office_outgoing_15m_n*.txt' into table chnasp.d_enp_zmcc_office_outgoing_15m_bak fields terminated by '@@' NULL_VALUE 'null'" -vvv

7、验证

gccli -uroot -e"select * from chnasp.d_enp_zmcc_office_outgoing_15m"