mysql导出存储过程及函数视图_MySQL 导出视图、函数、存储过程、事件、触发器的定义...

#!/bin/bash

# ./output_db_object_definition.sh > /dev/null 2>&1

db_user=""

db_pwd=""

db_host=""

db_port=3306

db_name=""

save_file="/root/${db_name}_object_definition.sql"

output_type='view,function,procedure,event,trigger' # view,function,procedure,event,trigger

(cat <

/*

ouput object‘s definition for database "$db_name"

ouput time: $(date "+%Y-%m-%d %H:%M:%S")

ouput object type: $output_type

*/

out

)>$save_file

echo "">> $save_file

echo "">> $save_file

# 视图

if [[ $output_type == *"view"* ]]

then

echo "-- ------------------------------------------------------------" >> $save_file

echo "-- views" >> $save_file

echo "-- ------------------------------------------------------------" >> $save_file

mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \

-e "select concat('SHOW CREATE VIEW ',table_schema,'.',table_name,';') from information_schema.views where table_schema='$db_name'" |\

sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd |\

sed 's/Create View: /kk_begin\n/g' | sed 's/[ ]*character_set_client:/;\nkk_end/g' |\

sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}' >> $save_file

fi

# 函数

if [[ $output_type == *"function"* ]]

then

echo "-- ------------------------------------------------------------" >> $save_file

echo "-- function" >> $save_file

echo "-- ------------------------------------------------------------" >> $save_file

mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \

-e "select concat('SHOW CREATE FUNCTION ',routine_schema,'.',routine_name,';') from information_schema.routines where routine_schema='$db_name' and ROUTINE_TYPE='FUNCTION'" |\

sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd |\

sed 's/Create Function: /kk_begin\ndelimiter $$\n/g' | sed 's/[ ]*character_set_client:/$$ \ndelimiter ;\nkk_end/g' |\

sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}' >> $save_file

fi

# 存储过程

if [[ $output_type == *"procedure"* ]]

then

echo "-- ------------------------------------------------------------" >> $save_file

echo "-- procedure" >> $save_file

echo "-- ------------------------------------------------------------" >> $save_file

mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \

-e "select concat('SHOW CREATE PROCEDURE ',routine_schema,'.',routine_name,';') from information_schema.routines where routine_schema='$db_name' and ROUTINE_TYPE='PROCEDURE'" |\

sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd |\

sed 's/Create Procedure: /kk_begin\ndelimiter $$\n/g' | sed 's/[ ]*character_set_client:/$$ \ndelimiter ;\nkk_end/g' |\

sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}' >> $save_file

fi

# 事件

if [[ $output_type == *"event"* ]]

then

echo "-- ------------------------------------------------------------" >> $save_file

echo "-- event" >> $save_file

echo "-- ------------------------------------------------------------" >> $save_file

mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \

-e "select concat('SHOW CREATE EVENT ',EVENT_SCHEMA,'.',EVENT_NAME,';') from information_schema.events where EVENT_SCHEMA='$db_name'" |\

sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd |\

sed 's/Create Event: /kk_begin\ndelimiter $$\n/g' | sed 's/[ ]*character_set_client:/$$ \ndelimiter ;\nkk_end/g' |\

sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}' >> $save_file

fi

# 触发器

if [[ $output_type == *"trigger"* ]]

then

echo "-- ------------------------------------------------------------" >> $save_file

echo "-- trigger" >> $save_file

echo "-- ------------------------------------------------------------" >> $save_file

mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \

-e "select concat('SHOW CREATE TRIGGER ',TRIGGER_SCHEMA,'.',TRIGGER_NAME,';') from information_schema.triggers where TRIGGER_SCHEMA='$db_name';" |\

sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd |\

sed 's/SQL Original Statement: /kk_begin\ndelimiter $$\n/g' | sed 's/[ ]*character_set_client:/$$ \ndelimiter ;\nkk_end/g' |\

sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}' >> $save_file

fi

# ^M, you need to type CTRL-V and then CTRL-M

sed -i "s/\^M//g" $save_file