我需要創建一個轉儲文件，其中包含來自數據庫的單個儲存過程，而不是所有常式。

我試過這樣的事情：

mysqldump -u root -pssmart --skip-triggers --no-create-info --no-data \
   --no-create-db --skip-opt test_fshop SP_ITEM_SALES_REPORT -R \
   > test_fshop_routines.sql 

這test_fshop是我的數據庫名稱，SP_ITEM_SALES也是我的 SP。我希望我的 SP 作為 .sql 文件。

壞消息

您不能使用 mysqldump 來執行此操作。

好消息

您可以使用 mysql 客戶端。首先，找出儲存過程是否存在。如果是，則獲取它是（PROCEDURE或FUNCTION）的過程類型。SHOW CREATE然後，為儲存過程發出。您必須去掉前 3 行和下 3 行：

DBNAME=${1}
SPNAME=${2}
SPFILE=${DBNAME}_${SPNAME}.sql
SPTEMP=${DBNAME}_${SPNAME}.tmp
MYSQL_CONN="-u... -p..."
SQLSTMT="SELECT COUNT(1) FROM mysql.proc WHERE db='${DBNAME}' AND name='${SPNAME}'"
PROC_EXISTS=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | awk '{print $1}'`
if [ ${PROC_EXISTS} -eq 1 ]
then
   SQLSTMT="SELECT type FROM mysql.proc WHERE db='${DBNAME}' AND name='${SPNAME}'"
   PROC_TYPE=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | awk '{print $1}'`
   SQLSTMT="SHOW CREATE ${PROC_TYPE} ${DBNAME}.${SPNAME}\G"
   mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > ${SPFILE}

   #
   # Remove Top 3 Lines
   #
   LINECOUNT=`wc -l < ${SPFILE}`
   (( LINECOUNT -= 3 ))
   tail -${LINECOUNT} < ${SPFILE} > ${SPTEMP}

   #
   # Remove Bottom 3 Lines
   #
   LINECOUNT=`wc -l < ${SPTEMP}`
   (( LINECOUNT -= 3 ))
   head -${LINECOUNT} < ${SPTEMP} > ${SPFILE}
else
   echo "Stored Procedure ${DBNAME}.${SPNAME} Does Not Exist"
fi
rm -f ${SPTEMP}

這是一個範例執行：

[root@***]# ./GetMyProc.sh common DMSPushers
[root@***]# cat common_DMSPushers.sql
CREATE DEFINER=`pma`@`10.%` PROCEDURE `DMSPushers`()
BEGIN

 DECLARE cntr INT DEFAULT 0;
 DECLARE dealerName VARCHAR(50);
 DECLARE dealerID INT;

 DECLARE done boolean DEFAULT false;
 DECLARE dealers CURSOR FOR SELECT bdd.dealer_id, ta.name FROM `b_dealer__dmsaccount` bdd left join t_dmsaccount ta on bdd.dmsaccount_id = ta.id where ta.dms_type = 1 order by bdd.dealer_id desc;

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := true;

 OPEN dealers;

 read_loop: LOOP
       FETCH dealers INTO dealerID, dealerName;
       SET cntr = cntr + 1;

   IF done OR cntr > 200 THEN
     LEAVE read_loop;
   END IF;

       IF dealerID > 0 AND dealerID < 664 THEN
               IF dealerName IS NULL THEN
                       SET dealerName = '';
               END IF;
               SET dealerName = REPLACE(dealerName,'''','''''');

               -- Does the Database even exist???
               SET @query = CONCAT('SELECT COUNT(SCHEMA_NAME) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ''D',dealerID,''' INTO @schemaExists');
               PREPARE stmt1 FROM @query;
               EXECUTE stmt1;

               IF @schemaExists > 0 THEN
                       SET @query = CONCAT('INSERT dealers_that_push (dealerID, dealerName, pushDate) SELECT ', dealerID, ',''', dealerName ,''',date FROM D',dealerID,'.contactLog where message like ''%pushed to the DMS%''');
                       PREPARE stmt1 FROM @query;
                       EXECUTE stmt1;
               END IF;

       END IF;

 END LOOP;

 CLOSE dealers;
END
[root@***]#

好消息

這是一個腳本，它將每個儲存過程轉儲到單獨的 SQL 文件中：

MYSQL_CONN="-u... -p..."
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"`
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 -${LINECOUNT} < ${SPFILE} > ${SPTEMP}
   #
   # Remove Bottom 3 Lines
   #
   LINECOUNT=`wc -l < ${SPTEMP}`
   (( LINECOUNT -= 3 ))
   head -${LINECOUNT} < ${SPTEMP} > ${SPFILE}
   rm -f ${SPTEMP}
done
ls -l

試一試 ！！！

我從堆棧溢出中得到以下資訊（但失去了連結）

mysqldump  --compact --no-create-info \
   --where="db='serialize' AND type='PROCEDURE' AND name IN ('sp_abraca_dabra')" \
   --databases mysql --tables proc > outputfile.sql

分別更改“serialize”和“sp_abraca_dabra”以適合您的數據庫和儲存過程。

連結是：https ://stackoverflow.com/a/7080270

引用自：https://dba.stackexchange.com/questions/34222