{"id":1237,"date":"2016-03-13T21:34:07","date_gmt":"2016-03-13T19:34:07","guid":{"rendered":"http:\/\/www.shukko.com\/x3\/?p=1237"},"modified":"2016-03-13T21:34:07","modified_gmt":"2016-03-13T19:34:07","slug":"backup-mysql-dump-all-your-mysql-databases-in-separate-files","status":"publish","type":"post","link":"https:\/\/www.shukko.com\/x3\/2016\/03\/13\/backup-mysql-dump-all-your-mysql-databases-in-separate-files\/","title":{"rendered":"Backup (mysql dump) all your MySQL databases in separate files"},"content":{"rendered":"<p>Backup (mysql dump) all your MySQL databases in separate files<\/p>\n<p>Sometimes we would like to dump all the MySQL databases. MySQL provides an easy solution to this problem:<br \/>\n1<\/p>\n<p>mysqldump -u root -p &#8211;all-databases > all_dbs.sql<\/p>\n<p>However this will dump stuff into one file. How to dump all databses into separate files? Well, here is my solution. A small bash script:<\/p>\n<pre class=\"lang:default decode:true \" >\r\n\t\r\n#! \/bin\/bash\r\n \r\nTIMESTAMP=$(date +\"%F\")\r\nBACKUP_DIR=\"\/backup\/$TIMESTAMP\"\r\nMYSQL_USER=\"backup\"\r\nMYSQL=\/usr\/bin\/mysql\r\nMYSQL_PASSWORD=\"password\"\r\nMYSQLDUMP=\/usr\/bin\/mysqldump\r\n \r\nmkdir -p \"$BACKUP_DIR\/mysql\"\r\n \r\ndatabases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e \"SHOW DATABASES;\" | grep -Ev \"(Database|information_schema|performance_schema)\"`\r\n \r\nfor db in $databases; do\r\n  $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip &gt; \"$BACKUP_DIR\/mysql\/$db.gz\"\r\ndone\r\n\r\n<\/pre>\n<p>Be aware, that in order to execute this script from cron, you need to store password in it (so cron won\u2019t be prompted to provide a password). That\u2019s why, you should not use a root account. Instead just create a new user only for backups, with following privileges:<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Backup (mysql dump) all your MySQL databases in separate files Sometimes we would like to dump all the MySQL databases. MySQL provides an easy solution to this problem: 1 mysqldump -u root -p &#8211;all-databases > all_dbs.sql However this will dump stuff into one file. How to dump all databses into separate files? Well, here is [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1237","post","type-post","status-publish","format-standard","hentry","category-kategerisiz"],"_links":{"self":[{"href":"https:\/\/www.shukko.com\/x3\/wp-json\/wp\/v2\/posts\/1237","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.shukko.com\/x3\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.shukko.com\/x3\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.shukko.com\/x3\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.shukko.com\/x3\/wp-json\/wp\/v2\/comments?post=1237"}],"version-history":[{"count":1,"href":"https:\/\/www.shukko.com\/x3\/wp-json\/wp\/v2\/posts\/1237\/revisions"}],"predecessor-version":[{"id":1238,"href":"https:\/\/www.shukko.com\/x3\/wp-json\/wp\/v2\/posts\/1237\/revisions\/1238"}],"wp:attachment":[{"href":"https:\/\/www.shukko.com\/x3\/wp-json\/wp\/v2\/media?parent=1237"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.shukko.com\/x3\/wp-json\/wp\/v2\/categories?post=1237"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.shukko.com\/x3\/wp-json\/wp\/v2\/tags?post=1237"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}