Dumping MySQL Stored Procedures, Functions and Triggers

Below is the command, Dumping StoredProcedures along with database.

-R - Dump the stored procedures, functions and triggers

 --single-transaction - This command is needed when we take mysqldump to avoid transactions locking tables. you can add --quick for large tables. This option should not use for clustered tables.


Dump the db and skip the definer before we do for the views
mysqldump -u -p -h -R  --single-transaction  | sed 's/DEFINER=[^*]*\*/\*/' > outputfile.sql

Command to take a backup of only stored procedures and functions

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt --skip-trigger > outputfile.sql

--no-data --routines --events --triggers

#databases=`mysql --user="" --password="" --host="" -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

username=$1

password=$2

databases=("db1" "db2" )

for db in ${databases[@]}; do

  if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] && [[ "$db" != "$IGNORE_DATABASE" ]] && [[ "$db" != "sys" ]]; then

echo "Dumping DDL database: $db"

# No Data

mysqldump --user="${username}" --password="${password}" --host="${host}" --no-data --routines --events --triggers "$db" > ./"V1__create_initial_scheme_${db}".sql

  fi

done

mysqldump --user="${username}" --password="${password}" --host="${host}" --no-create-info --skip-triggers --compact "alerts_configs" alert_types alert_notification_templates > ./"default-master-data_alerts_configs".sql

Dump selected tables
mysqldump -uuser -ppwd --tables tblName1 tblName2 > dbname_tables.sql

Issue i faced when i export routines and import.

This version of MySQL doesn’t yet support ‘multiple triggers with the same action time and event for one table’

Use –skip-triggers also. To avoid above error. Because, triggers are default loaded in mysqldump.

Importing outputfile.sql is usual as MySQL database import.
use information_schema;
select ROUTINE_NAME from ROUTINES where ROUTINE_SCHEMA = '' ;
select TRIGGER_NAME from TRIGGERS where TRIGGER_SCHEMA = '' ;


Stored procedure in mysql introduced after 5.x

-- Stored Procedure
DELIMITER $$
DROP PROCEDURE IF EXISTS MIGRATE_CUSTOMER_FIELD_MAPPING_DATA$$
CREATE PROCEDURE MIGRATE_CUSTOMER_FIELD_MAPPING_DATA()
BEGIN
-- Declare the variables in the beginning
DECLARE no_more_customer INT DEFAULT 0;
DECLARE customer_id int(11);

-- Declare the cursor
DECLARE cur_customer CURSOR FOR
  SELECT ID FROM CUSTOMER;

-- Declare handler for exception
  DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_customer = 1;

-- Open the cursor
OPEN cur_customer;
-- Fetch the data from cursor into variable
FETCH cur_customer INTO customer_id;
REPEAT
 INSERT INTO CUSTOMER_FIELD_MAPPING(CUSTOMER_ID, DEVICE_TYPE_ID, SERIAL_NUMBER_UNIQ, ZIGBEE_MAC_ID_UNIQ,CREATED_ON)
       VALUES (customer_id, 1, "TRUE", "TRUE", NOW());

 INSERT INTO CUSTOMER_FIELD_MAPPING(CUSTOMER_ID, DEVICE_TYPE_ID, SERIAL_NUMBER_UNIQ, ZIGBEE_MAC_ID_UNIQ,CREATED_ON)
       VALUES (customer_id, 2, "TRUE", "TRUE", NOW());

FETCH cur_customer INTO customer_id;
UNTIL no_more_customer = 1
END REPEAT;
-- close the cursor
CLOSE cur_customer;

-- End the stored procedure with delimiter
END$$

DELIMITER ;

call MIGRATE_CUSTOMER_FIELD_MAPPING_DATA();

DROP PROCEDURE IF EXISTS MIGRATE_CUSTOMER_FIELD_MAPPING_DATA;