mysql导出视图创建语句

概述

写这个脚本是因为如果用navicat迁移视图，可以发现修改视图内容很不方便，所以导出其中定义来改，如果用navicat是无法实现的。

所以就顺便整了一键导出视图、函数、存储过程、事件、触发器的定义脚本，下面一起来看看吧~

一键导出脚本

脚本明细如下：

#!/bin/bash# ./output_db_object_definition.sh > /dev/null 2>&1db_user="root"db_pwd="xxxxb"db_host="172.26.151.88"db_port=3306db_name="ycapp_prod" save_file="/home/scripts/${db_name}_object_definition.sql"# view,function,procedure,event,triggeroutput_type=‘view,function,procedure,event,trigger‘ (cat > $save_fileecho "">> $save_file # 视图if [[ $output_type == *"view"* ]]thenecho "-- ------------------------------------------------------------" >> $save_fileecho "-- views" >> $save_fileecho "-- ------------------------------------------------------------" >> $save_filemysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \-e "ｓｅｌｅｃｔ ｃｏｎｃａｔ(‘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 $db_name |\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_filefi # 函数if [[ $output_type == *"function"* ]]thenecho "-- ------------------------------------------------------------" >> $save_fileecho "-- function" >> $save_fileecho "-- ------------------------------------------------------------" >> $save_filemysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \-e "ｓｅｌｅｃｔ ｃｏｎｃａｔ(‘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 $db_name |\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"* ]]thenecho "-- ------------------------------------------------------------" >> $save_fileecho "-- procedure" >> $save_fileecho "-- ------------------------------------------------------------" >> $save_filemysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \-e "ｓｅｌｅｃｔ ｃｏｎｃａｔ(‘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 $db_name |\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_filefi # 事件if [[ $output_type == *"event"* ]]thenecho "-- ------------------------------------------------------------" >> $save_fileecho "-- event" >> $save_fileecho "-- ------------------------------------------------------------" >> $save_filemysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \-e "ｓｅｌｅｃｔ ｃｏｎｃａｔ(‘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_filefi # 触发器if [[ $output_type == *"trigger"* ]]thenecho "-- ------------------------------------------------------------" >> $save_fileecho "-- trigger" >> $save_fileecho "-- ------------------------------------------------------------" >> $save_filemysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \-e "ｓｅｌｅｃｔ ｃｏｎｃａｔ(‘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 $db_name|\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_filefi # ^M, you need to type CTRL-V and then CTRL-Msed -i "s/\^M//g" $save_file

执行过程如下：

后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下~