{"id":775,"date":"2013-05-08T18:49:06","date_gmt":"2013-05-08T16:49:06","guid":{"rendered":"http:\/\/www.netexpertise.eu\/en\/?p=775"},"modified":"2021-08-09T08:45:34","modified_gmt":"2021-08-09T06:45:34","slug":"mysql-stored-procedures-backup","status":"publish","type":"post","link":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html","title":{"rendered":"Include Stored Procedures in Mysql Backups"},"content":{"rendered":"\n<p>Most of us use mysqldump utility to backup <a href=\"\/en\/category\/database\/mysql\">Mysql<\/a> databases. Be aware because <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/mysqldump.html\" target=\"_blank\" rel=\"noreferrer noopener\">mysqldump<\/a> includes triggers by default but stored procedures and functions are ignored.<br>Stored procedures and functions are called routines in Mysql all together. Run <em>mysqldump &#8211;help<\/em> and you will see FALSE for routine&#8217;s default value.<\/p>\n\n\n\n<p><br>You can include stored procedures and functions adding the <em>&#8211;routines<\/em> switch when running a backup.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">$ mysqldump -u user -p my_database --routines backup.sql<\/code><\/pre>\n\n\n\n<p><br>Better, if you don&#8217;t want to type in the option &#8211; or forget &#8211; everytime you run a backup, Mysqldump default&#8217;s behaviour can be changed adding a mysqldump section in the my.cnf file, usually in your homedir.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">$ cat ~\/.my.cnf\n[mysqldump]\nroutines = true<\/code><\/pre>\n\n\n\n<p><br>Make sure mysqldump default behaviour is now what you think:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">$ mysqldump --print-defaults \nmysqldump would have been started with the following arguments:\n--routines=true<\/code><\/pre>\n\n\n\n<p><br>Check your backup scripts before you run into a disaster recovery!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Most of us use mysqldump utility to backup Mysql databases. Be aware because mysqldump includes triggers by default but stored procedures and functions are ignored.Stored procedures and functions are called routines in Mysql all together. Run mysqldump &#8211;help and you will see FALSE for routine&#8217;s default value. You can include stored procedures and functions adding [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[4],"tags":[58,385,354],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.8.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Netexpertise - Include Stored Procedures in Mysql Backups<\/title>\n<meta name=\"description\" content=\"Mysqldump ignores stored procedures and functions by default. Here&#039;s how to include them in your daily backups\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Netexpertise - Include Stored Procedures in Mysql Backups\" \/>\n<meta property=\"og:description\" content=\"Mysqldump ignores stored procedures and functions by default. Here&#039;s how to include them in your daily backups\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html\" \/>\n<meta property=\"og:site_name\" content=\"Netexpertise\" \/>\n<meta property=\"article:published_time\" content=\"2013-05-08T16:49:06+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-08-09T06:45:34+00:00\" \/>\n<meta name=\"author\" content=\"dave\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@netexpertise\" \/>\n<meta name=\"twitter:site\" content=\"@netexpertise\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html\",\"url\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html\",\"name\":\"Netexpertise - Include Stored Procedures in Mysql Backups\",\"isPartOf\":{\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#website\"},\"datePublished\":\"2013-05-08T16:49:06+00:00\",\"dateModified\":\"2021-08-09T06:45:34+00:00\",\"author\":{\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa\"},\"description\":\"Mysqldump ignores stored procedures and functions by default. Here's how to include them in your daily backups\",\"breadcrumb\":{\"@id\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/www.netexpertise.eu\/en\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Include Stored Procedures in Mysql Backups\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#website\",\"url\":\"http:\/\/www.netexpertise.eu\/en\/\",\"name\":\"Netexpertise\",\"description\":\"Systems \/ Networks \/ DevOps\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/www.netexpertise.eu\/en\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa\",\"name\":\"dave\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g\",\"caption\":\"dave\"},\"sameAs\":[\"http:\/\/www.netexpertise.eu\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Netexpertise - Include Stored Procedures in Mysql Backups","description":"Mysqldump ignores stored procedures and functions by default. Here's how to include them in your daily backups","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html","og_locale":"en_US","og_type":"article","og_title":"Netexpertise - Include Stored Procedures in Mysql Backups","og_description":"Mysqldump ignores stored procedures and functions by default. Here's how to include them in your daily backups","og_url":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html","og_site_name":"Netexpertise","article_published_time":"2013-05-08T16:49:06+00:00","article_modified_time":"2021-08-09T06:45:34+00:00","author":"dave","twitter_card":"summary_large_image","twitter_creator":"@netexpertise","twitter_site":"@netexpertise","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html","url":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html","name":"Netexpertise - Include Stored Procedures in Mysql Backups","isPartOf":{"@id":"http:\/\/www.netexpertise.eu\/en\/#website"},"datePublished":"2013-05-08T16:49:06+00:00","dateModified":"2021-08-09T06:45:34+00:00","author":{"@id":"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa"},"description":"Mysqldump ignores stored procedures and functions by default. Here's how to include them in your daily backups","breadcrumb":{"@id":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-stored-procedures-backup.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.netexpertise.eu\/en"},{"@type":"ListItem","position":2,"name":"Include Stored Procedures in Mysql Backups"}]},{"@type":"WebSite","@id":"http:\/\/www.netexpertise.eu\/en\/#website","url":"http:\/\/www.netexpertise.eu\/en\/","name":"Netexpertise","description":"Systems \/ Networks \/ DevOps","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.netexpertise.eu\/en\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa","name":"dave","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/image\/","url":"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g","contentUrl":"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g","caption":"dave"},"sameAs":["http:\/\/www.netexpertise.eu"]}]}},"_links":{"self":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts\/775"}],"collection":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/comments?post=775"}],"version-history":[{"count":0,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts\/775\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/media?parent=775"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/categories?post=775"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/tags?post=775"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}