{"id":3020,"date":"2016-07-29T06:39:08","date_gmt":"2016-07-29T10:39:08","guid":{"rendered":"https:\/\/antipaucity.com\/?p=3020"},"modified":"2016-07-29T06:39:08","modified_gmt":"2016-07-29T10:39:08","slug":"automating-mysql-backups","status":"publish","type":"post","link":"https:\/\/merikebi.warrenmyers.com\/?p=3020","title":{"rendered":"automating mysql backups"},"content":{"rendered":"

I want to backup all of the {{MySQL}} databases on my server on a routine basis.<\/p>\n

As I started asking how to get a list of all databases in MySQL on Stack Overflow, I came across this previous SO question, entitled, “Drop All Databases in MySQL<\/a>” (the best answer<\/a> for which, in turn, republished the kernel from this blog post<\/a>). Thinking that sounded promising, I opened it and found this little gem:<\/p>\n

mysql -uroot -ppassword -e \"show databases\" | grep -v Database | grep -v mysql| grep -v information_schema| |gawk '{print \"drop database \" $1 \";select sleep(0.1);\"}' | mysql -uroot -ppassword<\/code><\/p>\n

That will drop all databases. No doubt about it. But that’s not what I want to so, so I edited the leading command down to this:<\/p>\n

`mysql -uroot -e \"show databases\" | grep -v Database | grep -v mysql| grep -v information_schema| grep -v test | grep -v OLD | grep -v performance_schema
\n<\/code><\/p>\n

Which gives back a list of all the databases created by a user.<\/p>\n

Now I need a place to keep the dumps .. \/tmp<\/code> sounded good.<\/p>\n

And each database should be in its own file, for I need mysqldump $db.identifier.extension<\/code><\/p>\n

Made the ‘identifier’ the output of date +%s<\/code> to get seconds since the Unix epoch (which is plenty unique enough for me<\/em>).<\/p>\n

All of which adds up to this one-liner:<\/p>\n

for db in `mysql -uroot -e \"show databases\" | grep -v Database | grep -v mysql| grep -v information_schema| grep -v test | grep -v OLD | grep -v performance_schema`; do mysqldump $db > \/tmp\/$db.dump.`date +%s`.sql; done<\/code><\/p>\n

Plop that puppy in root’s crontab<\/a> on a good schedule for you, and you have a hand-free method to backup databases.<\/p>\n

Thought about using xargs<\/code>, but I couldn’t come up with a quick\/easy way to uniquely identify each file in the corresponding output.<\/p>\n

Might consider adding some compression and\/or a better place for dumps to live and\/or cleaning-up ‘old’ ones (however you want to determine that), but it’s a healthy start.<\/p>\n

\n

You can also do mysqldump --all-databases<\/code> if you think you want to restore all of them simultaneously … I like the idea of individually dumping them for individual restoration \/ migration \/ etc.<\/p>\n

The full script I am using (which does include backups, etc):<\/p>\n

############################
\n#!\/bin\/bash<\/p>\n

date<\/p>\n

echo 'Archiving old database backups'<\/p>\n

tar zcf mysql-dbs.`date +%s`.tar.gz ~\/sqlbackups
\nrm -f ~\/sqlbackups\/*<\/p>\n

date<\/p>\n

echo 'Backing up MySQL \/ MariaDB databases'<\/p>\n

for db in `mysql -uroot -e \"show databases\" | grep -v Database | grep -v mysql| grep -v information_schema| grep -v test | grep -v OLD | grep -v performance_schema`; do mysqldump $db > ~\/sqlbackups\/$db.dump.`date +%s`.sql; done<\/p>\n

echo 'Done with backups. Files can be found in ~\/sqlbackups'<\/p>\n

date
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

I want to backup all of the {{MySQL}} databases on my server on a routine basis. As I started asking how to get a list of all databases in MySQL on Stack Overflow, I came across this previous SO question, entitled, “Drop All Databases in MySQL” (the best answer for which, in turn, republished the …
Continue reading automating mysql backups<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[41,45],"tags":[120,127,506],"keyring_services":[],"class_list":["post-3020","post","type-post","status-publish","format-standard","hentry","category-technical","category-tutorial","tag-automating","tag-backup","tag-mysql"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=\/wp\/v2\/posts\/3020","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3020"}],"version-history":[{"count":0,"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=\/wp\/v2\/posts\/3020\/revisions"}],"wp:attachment":[{"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3020"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3020"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3020"},{"taxonomy":"keyring_services","embeddable":true,"href":"https:\/\/merikebi.warrenmyers.com\/index.php?rest_route=%2Fwp%2Fv2%2Fkeyring_services&post=3020"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}