Dump all stored procedures from MySQL database

dump stored procedures

MYSQL_CONN="-uroot -p..................."
DBNAME="test_LCX"
SQLSTMT="SELECT COUNT(1) FROM mysql.proc"
PROCCOUNT=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" |  awk '{print $1}'`
if [ ${PROCCOUNT} -eq 0 ] ; then exit ; fi
SPLIST=""
for DBSP in `mysql ${MYSQL_CONN} -ANe"SELECT CONCAT(type,'@',db,'.',name) FROM mysql.proc" | grep ${DBNAME}`
do
    SPLIST="${SPLIST} ${DBSP}"
done
for TYPEDBSP in `echo "${SPLIST}"`
do
    DB=`echo "${TYPEDBSP}" | sed 's/@/ /' | sed 's/\./ /' | awk '{print $2}'`
    SP=`echo "${TYPEDBSP}" | sed 's/@/ /' | sed 's/\./ /' | awk '{print $3}'`
    SQLSTMT=`echo "SHOW CREATE ${TYPEDBSP}\G" | sed 's/@/ /'`
    SPFILE=${DB}_${SP}.sql
    SPTEMP=${DB}_${SP}.tmp
    echo Echoing ${SQLSTMT} into ${SPFILE}
    mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > ${SPFILE}
    #
    # Remove Top 3 Lines
    #
    LINECOUNT=`wc -l < ${SPFILE}`
    (( LINECOUNT -= 3 ))
    tail -n+3 < ${SPFILE} > ${SPTEMP}
    #
    # Remove Bottom 3 Lines
    #
    LINECOUNT=`wc -l < ${SPTEMP}`
    (( LINECOUNT -= 3 ))
    head -n -3 < ${SPTEMP} > ${SPFILE}
    rm -f ${SPTEMP}
done
ls -l