I need to create a dump file that contains a single stored procedure from a database, not all routines.
I've tried something like this:
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
Here test_fshop is my DB name and SP_ITEM_SALES is my SP. I want my SP as .sql file.
You cannot use mysqldump to do this.
You can use the mysql client. First, find out if the Stored Procedure exists. If it does, then fetch the type of Procedure it is (PROCEDURE or FUNCTION). Then, issue the SHOW CREATE for the Stored Procedure. You will have to strip the top 3 and bottom 3 lines:
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}
Here is a sample run:
[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@***]#
Here is a script that will dump every Stored Procedure into separate SQL files:
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
Give it a Try !!!
I got the following from stack overflow (but lost the link)
mysqldump --compact --no-create-info \
--where="db='serialize' AND type='PROCEDURE' AND name IN ('sp_abraca_dabra')" \
--databases mysql --tables proc > outputfile.sql
Change "serialize" and "sp_abraca_dabra" to suit your database and stored procedure respectively.
Link is : https://stackoverflow.com/a/7080270
If you want to dump ONLY one procedure from a database, this line should do the trick. It is not the mysqldump utility, but the mysql interactive application itself.
mysql --user=root mydb -e "SHOW CREATE PROCEDURE myprocedure;" > myprocedure.sql
Here you can read my full answer for Windows and batch: https://stackoverflow.com/a/25629708/488117
No perfect solution, but I wrote this simpler script which gives a usable output (pipe to mysql again )
getproc.sh SCHEMA PROC
mysql -rBN << !
select concat('
delimiter $
drop procedure if exists ',name,'$
create procedure ',name,' ( ',param_list,' ) ',body,' $
delimiter ;
')
from mysql.proc where db='$1' and name='$2';
!
Not tested with fancy character sets, and one may want to add the 'definer' as well...
