Skip to content
  • Stuff
  • Travel
  • Beverages
  • Support Antipaucity
  • Projects
  • About

antipaucity

fighting the lack of good ideas

automating mysql backups

Posted on 29 July 201628 July 2016 By antipaucity

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 kernel from this blog post). Thinking that sounded promising, I opened it and found this little gem:

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

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:

`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

Which gives back a list of all the databases created by a user.

Now I need a place to keep the dumps .. /tmp sounded good.

And each database should be in its own file, for I need mysqldump $db.identifier.extension

Made the ‘identifier’ the output of date +%s to get seconds since the Unix epoch (which is plenty unique enough for me).

All of which adds up to this one-liner:

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

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

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

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.


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

The full script I am using (which does include backups, etc):

############################
#!/bin/bash

date

echo 'Archiving old database backups'

tar zcf mysql-dbs.`date +%s`.tar.gz ~/sqlbackups
rm -f ~/sqlbackups/*

date

echo 'Backing up MySQL / MariaDB databases'

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

echo 'Done with backups. Files can be found in ~/sqlbackups'

date

technical, tutorial Tags:automating, backup, mysql

Post navigation

Previous Post: change your default font in windows 10
Next Post: here seems like it would be perfect for pilots

More Related Articles

hybrid cloud for dummies by judith hurwitz et al books
programming your home by mike riley books
automation commentary
thoughts on vilt commentary
i’m a medium plogger now* cool
avnet buying seamless technologies hmmm
July 2016
S M T W T F S
 12
3456789
10111213141516
17181920212223
24252627282930
31  
« Jun   Nov »
RSS Error: WP HTTP Error: cURL error 60: SSL: no alternative certificate subject name matches target hostname 'paragraph.cf'

Books

  • Debugging and Supporting Software Systems
  • Storage Series

External

  • Backblaze
  • Cirkul
  • Fundrise
  • Great Big Purple Sign
  • Password Generator
  • PayPal
  • Tech News Channel on Telegram
  • Vultr
  • Wish List

Other Blogs

  • Abiding in Hesed
  • Chris Agocs
  • Eric Hydrick
  • Jay Loden
  • Paragraph
  • skh:tec
  • Tech News Channel on Telegram
  • Veritas Equitas

Profiles

  • LinkedIn
  • Server Fault
  • Stack Overflow
  • Super User
  • Telegram
  • Twitter

Resume

  • LinkedIn
  • Resume (PDF)

Services

  • Datente
  • IP check
  • Password Generator
  • Tech News Channel on Telegram

Support

  • Backblaze
  • Built Bar
  • Cirkul
  • Donations
  • Fundrise
  • PayPal
  • Robinhood
  • Vultr
  • Wish List

35-questions 48laws adoption automation blog blogging books business career centos cloud community documentation email encryption facebook google history how-to hpsa ifttt linux money networking politics prediction proxy review scifi security social social-media splunk ssl startup storage sun-tzu tutorial twitter virtualization vmware wordpress work writing zombie

Copyright © 2025 antipaucity.

Powered by PressBook Green WordPress theme