shell实现，将mysql每个存储过程导出为单个文件

最新推荐文章于 2021-02-02 22:20:14 发布

置顶弘恩

最新推荐文章于 2021-02-02 22:20:14 发布

阅读量2.4k

点赞数

本文链接：https://blog.csdn.net/c_enhui/article/details/40379711

版权

#shell实现，将mysql中存储过程代码直接导出为文件
dbcn="mysql -h172.16.1.194 -uroot -p123456 ";
db=BCReport_Sync_Executor;
ii=0;
ct=`$dbcn -N -e " select count(1) from mysql.proc as p where 1=1 and p.db='$db' and p.type like 'P%';"`;
mkdir -p /chenenhui/$db

while true;
do
if [ $ii -lt $ct ]
then 
p=$ii;
let ii++;
echo p=$p 
echo ii=$ii

spname=`$dbcn -N -e " select p.name from mysql.proc as p where 1=1 and p.db='$db' and p.type like 'P%' limit $p,1;"`;
echo spname=$spname
sleep 0;
ss=`$dbcn -N -e " 
SELECT   
CONCAT(
'
DELIMI','TER ',REPEAT(CHAR(36),2),'

USE ',CHAR(96),  p.db,CHAR(96),REPEAT(CHAR(36),2),'

DROP PROCEDURE IF EXISTS ',CHAR(96),p.name,CHAR(96), REPEAT(CHAR(36),2),'

CREATE DEFINER=',CHAR(96),
LEFT(DEFINER, -1+LOCATE('@',DEFINER)),
CHAR(96),'@',CHAR(96),
RIGHT(DEFINER,LENGTH(DEFINER)-LOCATE('@',DEFINER)),
CHAR(96)
,' PROCEDURE ',CHAR(96),p.name ,CHAR(96),'(',p.param_list,')
', p.body ,REPEAT(CHAR(36),2) ,'

DELIMI','TER ;
' ) AS sql_create 
FROM mysql.proc AS p 
WHERE 1=1 
and p.db='$db' 
and p.type LIKE 'P%'
AND p.name ='$spname'
;"
`
echo -e "$ss" > /chenenhui/$db/$spname.sql
echo $spname
else 
echo '_while finished';
exit 0;
fi
done