Exporting create procedure to text file in MySQL

So there’s this handy dandy code:

mysql› show create procedure myProcedure; 

That will output the recipe with which myProcedure was created.  But what if you want to export 1200 lines of code to a text file.  You do that from terminal.

$ mysql -h example.com -P 3306 -u myUser -p -e "show create procedure myDatabase.myProcedure" ›› myExport.txt 

So in this case, -h is the host, you can put an IP address, domain, or AWS dns there, -P is the port, -u is the user, lowercase -p is the password (leave it blank to be prompted to enter it), then -e says run this query, and >> says put the contents in this file.  Note that you will have to add the database name to avoid the no database selected error.

If you are just connecting to a local MySQL server, leave the hostname out and port out, and use sudo credentials, like this:

$ sudo mysql -u myUser -p -e "show create procedure myDatabase.myProcedure" ›› myExport.txt 

Open the file with your Vim editor.

That brings up something quasi like this.


create procedure myProcedure\nbegin\nselect * from myTable\nend

All of the \n’s can be a problem if you are exporting thousands of lines of code.  For that, Vim to the rescue.  Type into your Vim editor:

:%s /\\n/\r/g

You need two backslashes on n like \\n because the export has escaped the backslash already.  But you only use 1 backslash on \r because you want and actually line break, not to print the characters.  and the final /g allows you to search through one very long line of text.

To find your line breaks again it is a search for \n with one backslash.