Store Procedure & Views Backups

How to Backup Store Procedures ?


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

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

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

mysql --user=root mydb -e "SHOW CREATE PROCEDURE myprocedure;" > myprocedure.sql

mysql db_name -e "show create procedure proc_name\G;">/directory/

How to Backup Views ?

mysql db_name -e "show create view view_culture\G;">/directory/

Mysqldump  db_name view_name>/directory/

Simple Bash script for taking views dump

#!/bin/bash -e

mysql --skip-column-names --batch -e \

"select table_name from information_schema.views \

 where table_schema = database()" $* |

xargs --max-args 1 mysqldump $*

save it as mysql-dump-views.sh

$ mysql-dump-views.sh -u user -ppassword databasename >dumpfile.sql

That's It.