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

#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