antipaucity

fighting the lack of good ideas

automating mysql backups

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

automated let’s encrypt ssl certificate renewal on centos 7

In my how-to for Let’s Encrypt, I gave an example script that can be called via cron (or manually) which will renew Let’s Encrypt SSL certificates under CentOS 6.

If you want to do it on CentOS 7 (which is what I am now running), use the following:

cd ~/letsencrypt
git pull
systemctl stop httpd.service
~/letsencrypt/letsencrypt-auto --agree-tos --keep --rsa-key-size 2048 --standalone certonly -m user@domain.tld -d domain.tld [-d sub.domain.tld [-d ...]]
systemctl start httpd.service

Now, what does this script do? Step by step:

  1. clear-out the last grab of the Let’s Encrypt git repo (there’s probably a better way to do this, but I don’t know what it is)
  2. go to root’s home (/root)
  3. clone-down the Let’s Encrypt toolset
  4. stop httpd (Apache in my case, though you might be running nginx or something else
  5. run the cert tool in automated form:
    1. agree to terms of service
    2. keep current cert if it doesn’t need to be updated
    3. key size of 2048 bits
    4. run the standalone webserver to verify “ownership” of the domain
    5. generate just the cert
    6. administrative email (optional, but “encouraged”)
    7. domain(s) to issue cert for (must be individually identified with successive -d flags; LE does not support wildcard certs)
  6. restart httpd

I set mine to run @weekly in cron@monthly is likely good enough, but since it’s “free” to run, running slightly more than is necessary seems good to me. Plus, if you’re getting SSL certs for many domains all being served from the same server, they may have different expiration dates, so running more often is better.

My crontab entry for renewing certs:

@weekly /root/renew-le-ssl.sh

let’s encrypt centos 6 – truly free ssl

There’s been quite a bit of excitement surrounding Let’s Encrypt recently – a truly 100% free SSL issuer.

Last week I helped a friend of mine get his first Let’s Encrypt certificate generated and configured for his website. One of the things I found incredibly frustrating is that Let’s Encrypt does not have a package for Red Hat/CentOS/Fedora! Ignoring such a massive installed base seems monumentally dumb – so I hope that they correct it soon. Until they do, however, here’s a tutorial that should cover the gotchas for getting Let’s Encrypt to work on a CentOS 6 server with Apache 2.

The documentation (as of 06 Jan 2015) on the Let’s Encrypt website is in error in a few places (or, at least, not as correct as is could/should be). One big thing to note, for example, is that it says Python 2.6 is supported (the current release for RHEL/CentOS 6). If you run the certificate generator without the --debug flag, though, it will error-out saying Python 2.6 is not supported.

While I used an existing CentOS 6 server, I’ll start this tutorial as I have many others – by telling you to go get a CentOS 6 server from Digital Ocean or Chunk Host.

Preliminaries

Login as root (or a sudo-privileged account – but root is easier), and install Apache, Python, and SSLyum install httpd python mod_ssl.

Also enable the EPEL repository: yum install epel-repository (available from the CentOS Extras repository. I’m going to assume you are familiar with configuring Apache, and will only provide the relevant snippets from ssl.conf herein.

Now that the basics are done, let’s move to Let’s Encrypt. I ran the tool in interactive mode (which is going to require ncurses to be available – it’s probably already installed on your system) – but you’ll want to add a crontab entry since Let’s Encrypt certs expire after 90 days, so I’ll compact the interactive session into a single command-line call at the end, which you’ll need to “know” how to do, since the --help argument doesn’t do anything yet (that I could find).

Initial Certificate Creation

First, grab the latest Let’s Encrypt from GitHub:
git clone https://github.com/letsencrypt/letsencrypt && cd letsencrypt

Stop Apache: service httpd stop. Let’s Encrypt is going to try to bind to ports 80 and 443 to ensure you have control the domain.

Now run the letsencrypt-auto tool – in debug mode so it’ll work with Python 2.6: ./letsencrypt-auto --debug certonly.

Use certonly because the plugins to automate installing for Apache and Nginx don’t work on CentOS yet.

Enter your domain name(s) for which you want to issue a certificate. If you accept incoming connections to www.domain.tld and domain.tld, be sure to put both in the list (likewise, if you have, say, blog.domain.tld that you want included).

Enter an administrative email address.

When the tool finishes, it’ll put symlinks in /etc/letsencrypt/live/domain.tld, with the “actual” certs in /etc/letsencrypt/archive/domain.tld. We’re going to reference the symlinks in /etc/letsencrypt/live/domain.tld next.

Edit /etc/httpd/conf.d/ssl.conf (I prefer emacs – but use whatever you prefer), and add the following lines in your VirtualHost directive:
SSLCertificateFile /etc/letsencrypt/live/domain.tld/fullchain.pem
SSLCertificateKeyFile /etc/letsencrypt/live/domain.tld/privkey.pem
SSLCACertificateFile /etc/letsencrypt/live/domain.tld/cert.pem

Restart Apacheservice httpd start.

Try hitting https://domain.tld in your web browser – and you should be golden!

Automating Renewal

Create a small shell script called renew-LE-certs.sh somewhere you’ll remember where it is – like /root:
service httpd stop
# add additional '-d' entries for more subdomains
/path/to/letsencrypt/letsencrypt-auto --debug --keep --agree-tos --rsa-key-size 2048 certonly -m ssladmin@domain.tld -d domain.tld -d www.domain.tld
service httpd start

For your crontab entry, do the following to setup monthly cert renewal:
@monthly /path/to/renew-LE-certs.sh

automatically extract email attachments with common linux tools

I had need to automatically process emails to a specific address to pull attachments out, and this is how I did it:

$ yum install mpack

$ cat extract-attach.sh 
#!/bin/bash
rm -rf ~/attachtmp
mkdir ~/attachtmp
mv ~/Maildir/new/* ~/attachtmp
cd ~
munpack ~/attachtmp/*
rm -rf ~/attachtmp

$ crontab -l
*/5 * * * *	~/extract-attach.sh

Why, you may ask? Because I get a report a few times per day to the email address in question.


Note – this runs in my crontab every 5 minutes on a CentOS 6 x64 server; I’m sure the process is similar/identical on other distros, but I haven’t personally tried.