21

I use mysqldump with MySQL 5.0 and I back it up every day, but do not understand the method that only stored procedure backs up.

How can I back it up?

Stéphane
  • 3,884
  • 1
  • 30
  • 27
freddiefujiwara
  • 57,041
  • 28
  • 76
  • 106

3 Answers3

47

I'm not sure whether you're asking to back up stored procedures as well as everything else, or just the stored procedures on their own...

Stored procedured in dump with everything else:

mysqldump -R <dbname> #or
mysqldump --routines <dbname>

Just the stored procedures:

mysqldump -n -t -d -R <dbname> #or
mysqldump --no-create-db --no-create-info --no-data --routines <dbname>

Does that help?

Stobor
  • 44,246
  • 6
  • 66
  • 69
  • 1
    In my version of mysqldump (mysqldump Ver 10.13 Distrib 5.1.69, for debian-linux-gnu (x86_64)), at least, that should be -R, not -r. From the doc: -R, --routines Dump stored routines (functions and procedures). -r, --result-file=name Direct output to a given file. This option should be used in MSDOS, because it prevents new line '\n' from being converted to '\r\n' (carriage return + line feed). – davej May 23 '13 at 14:02
  • 1
    @davej Good point - I guess I missed that somehow. (I would like to think I tested it, but I guess not...) Thanks for the note. – Stobor May 24 '13 at 02:26
  • DOS>mysqldump -uroot -p -n -t -d -R test > test_procs.sql – Park JongBum Aug 31 '21 at 04:13
0
mysqldump - u dbusername (ex: -uroot) -ppassword (ex:-pmysql@dbpas) --routines <dbname>

use the username and password could be more helpful.

Irvin Dominin
  • 30,819
  • 9
  • 77
  • 111
manoj
  • 1
0

You can also put routines=true in the [mysqldump] section of your my.cnf file (you may have to add this section as it is not usually present in a virgin my.cnf file) to include routines in a normal dump.

David G
  • 5,408
  • 1
  • 23
  • 19