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

change your default font in windows 10

Starting from a tutorial I found recently, I want to share how to change your default font in Windows 10 – but in a shorter edition than that long one (and in, I think, a less-confusing way).

Back in the Good Ole Days™, you could easily change system font preferences by right-clicking on your desktop, and going into the themes and personalization tab to set whatever you wanted however you wanted (this is also where you could turn off (or back on) icons on your desktop (like My Documents), set window border widths, colors for everything, etc).

Windows 10 doesn’t let you do that through any form of Control Panel anymore, so you need to break-out the Registry Editor*.

0th, Start regedit

WindowsKey-R brings up the Run dialog – type regedit to start the Registry Editor

2016-07-27 (3)

NOTE: you should back-up any keys you plan to edit, just in case you forget what you did, want to revert, or make a mistake.

1st, Navigate to the right key area
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\FontSubstitutes

2016-07-27&&

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Fonts

2016-07-27 (1)Are where you’ll need to be to make these changes.

2nd, Blank entries for Segoe UI

For all of the “Segoe UI” entries in Fonts, change their Data field to blank (“”)

3rd, Add a Segoe UI substitute font

In FontSubstitutes, click Edit->String Value. Name it “Segoe UI” (without the quotes). In the “Value data” field, enter your preferred font name. I used Lucida Console.

2016-07-27 (2)

4th, Logout, or reboot, and login again to see your changes take effect.

* You can also download my registry keys, which have the substitution already done here. And you can pick any other font instead of Lucida Console you like – just edit the key file in your favorite text editor (I like TextPad) before merging into your Registry.

turn on spf filtering with postfix and centos 7

After running my new server for a while, I was noticing an unusually-high level of bogus email arriving in my inbox – mail that was being spoofed to look like it was coming from myself (to myself).

After a great deal of research, I learned there is a component of the DNS specification that allows for TEXT or SPF records. Sender Policy Framework was developed to help mail servers identify whether or not messages are being sent by authorized servers for their representative domains.

While there is a huge amount of stuff that could be added into a SPF record, what I am using for my domains is:

"v=spf1 mx -all"

Note: some DNS providers (like Digital Ocean) will make you use a TEXT record instead of a dedicated SPF record (which my registrar / DNS provider Pairnic supports).

If they require it be via TEXT record, it’ll look something like this: TXT @ "v=spf1 a include:_spf.google.com ~all"

Starting with this old how-to I found for CentOS 6, I added the policy daemon for Postfix (though it’s now in Python and not Perl) thusly:

yum install pypolicyd-spf

(I already had the EPEL yum repository installed – to get it setup, follow their directions, found here.)

Then I edited the master.cf config file for Postfix, adding the following at the bottom:

policy unix - n n - 0 spawn user=nobody argv=/bin/python /usr/libexec/postfix/policyd-spf

Note: those are actually tabs in my config file – but spaces work, too.

When you’re done with your edits and record additions, restart Postfix:

systemctl restart postfix

Then you’ll see messages like this in your /var/log/maillog file:

Apr 23 18:58:59 khopesh postfix/smtpd[18199]: NOQUEUE: reject: RCPT from unknown[197.27.40.169]: 550 5.7.1 <warren@datente.com>: Recipient address rejected: Message rejected due to: SPF fail - not authorized. Please see http://www.openspf.net/Why?s=mfrom;id=warren@datente.com;ip=197.27.40.169;r=warren@datente.com; from=<warren@datente.com> to=<warren@datente.com> proto=ESMTP helo=<[197.27.40.169]>

And if you follow the directive to go visit the “Why” page on OpenSPF, you’ll see something like this explanation:


Why did SPF cause my mail to be rejected?

What is SPF?

SPF is an extension to Internet e-mail. It prevents unauthorized people from forging your e-mail address (see the introduction). But for it to work, your own or your e-mail service provider’s setup may need to be adjusted. Otherwise, the system may mistake you for an unauthorized sender.

Note that there is no central institution that enforces SPF. If a message of yours gets blocked due to SPF, this is because (1) your domain has declared an SPF policy that forbids you to send through the mail server through which you sent the message, and (2) the recipient’s mail server detected this and blocked the message.

warren@datente.com rejected a message that claimed an envelope sender address of warren@datente.com. warren@datente.com received a message from 197.27.40.169 that claimed an envelope sender address of warren@datente.com.

However, the domain datente.com has declared using SPF that it does not send mail through 197.27.40.169. That is why the message was rejected.


improve your entropy pool in linux

A few years ago, I ran into a known issue with one of the products I use that manifests when the Red Hat Linux server it’s running on has a low entropy pool. And, as highlighted in that question, the steps I found 5 years ago didn’t work for me (turns out modifying the t parameter from ‘1’ to ‘.1’ did work (rngd -r /dev/urandom -o /dev/random -f -t .1), but I digress (and it’s no longer correct in CentOS 7 (the ‘t’ option, that is))).

In playing around with the Mozilla-provided SSL configurator, I noticed a line in the example SSL config that referenced “truerand”. After a little Googling, I found an opensource implementation called “twuewand“.

And a little more Googling about adding entropy, and I came across this interesting tutorial from Digital Ocean for “haveged” (which, interestingly-enough, allowed me to answer a 6-month-old question on Server Fault about CloudLinux).

Haveged “is an attempt to provide an easy-to-use, unpredictable random number generator based upon an adaptation of the HAVEGE algorithm. Haveged was created to remedy low-entropy conditions in the Linux random device that can occur under some workloads, especially on headless servers.”

And twuewand “is software that creates hardware-generated random data. It accomplishes this by exploiting the fact that the CPU clock and the RTC (real-time clock) are physically separate, and that time and work are not linked.”

For workloads that require lots of entropy (generating SSL keys, SSH keys, PGP keys, and pretty much anything else that wants lots of random (or strong pseudorandom) seeding), the very real problem of running out of entropy (especially on headless boxes or virtual machines) is something you can face quite easily / frequently.

Enter solutions like OpenRNG which are hardware entropy generators (that one is a USB dongle (see also this skh-tec post)). Those are awesome – unless you’re running in cloud space somewhere, or even just a “traditional” virtual machine.

One of the funny things about getting “random” data is that it’s actually very very hard to get. It’s easy to describe, but generating “truly” random data is incredibly difficult. (If you want to have an aneurysm (or you’re like me and think this stuff is unendingly fascinating), go read the Wikipedia entry on “Cryptographically Secure Pseudo Random Number Generator“.)

If you’re in a situation, though, like I was (and still am), where you need to maintain a relatively high quantity of fairly decent entropy (probably close to CSPRNG level), use haveged. And run twuewand occasionally – at the very least when starting Apache (at least if you’re running HTTPS – which you should be, since it’s so easy now).

how to turn a google+ community into a quasi “mailing list”

Spurred by a recent question from an acquaintance in town, I asked on Google+ whether or not you can enable emailed notifications for a Community. This led to the elaborate Settings page for G+.

It turns out that if you combine enabling a Community’s “Community notifications” vertical-ellipsiscommunity-settings (under the specific Community’s settings (which you find by clicking the vertical ellipsis button on the Community page) with the following tree in your general Google+ settings, Notifications -> Email -> Communities -> Shares something with a community you get notifications from, notifications-emailyou get a “mailing list” of sorts from your Community, which, niftily enough, also allows you to comment on the post via email (at least on the first notification of said post)!

an even cleaner facebook most recent feed

Several months ago, I wrote-up a brief how-to on just showing the most recent news feed on Facebook.

I added a new Chrome extension today that helps speed-up your Facebook experience – Facebook Flat. It makes your Facebook views “flat” from a design perspective (no pun intended, but the extension falls a little flat when on highres screens with a fully-expanded browser window): it removes ads, reduces the color scheme, and generally makes it smoother.

If you combine this extension with loading https://m.facebook.com/home.php?sk=h_chr as your Facebook view (the mobile web edition in chronological order), the posts no longer fully-fill the screen, but instead stay centered as just a news feed in the middle of your screen.

Combine with something like Auto Refresh, and you can automate a clean view for your Facebook feed.

putting owncloud 8 on a subdomain instead of a subdirectory on centos 7

After moving to a new server, I wanted to finally get ownCloud up and running (over SSL, of course) on it.

And I like subdomains for different services, so I wanted to put it at sub.domain.tld. This turns out to be not as straight-forward as one might otherwise hope, sadly – ownCloud expects to be installed to domain.tld/owncloud (and plops itself into /var/www/owncloud by default (or sometimes /var/www/html/owncloud).

My server is running CentOS 7, Apache 2.4, and MariaDB (a drop-in replacement for MySQL). This overview is going to presume you’re running the same configuration – feel free to spin one up quickly at Digital Ocean to try this yourself.

Start with the ownCloud installation instructions, which will point you to the openSUSE build service page, where you’ll follow the steps to add the ownCloud community repo to your yum repo list, and install ownCloud. (In my last how-to, 8.0 was current – 8.2 rolled-out since I installed 8.1 a couple days ago.)

Here is where you need to go “off the reservation” to get it ready to actually install.

Add a VirtualHost directive to redirect http://sub.domain.tld to https://sub.domain.tld (cipher suite list compiled thusly):


<VirtualHost *:80>
ServerName sub.domain.tld
Redirect permanent / https://sub.domain.tld/
</VirtualHost>

Configure an SSL VirtualHost directive to listen for sub.domain.tld:


<VirtualHost *:443>
SSLCertificateFile /etc/letsencrypt/live/sub.domain.tld/cert.pem
SSLCertificateKeyFile /etc/letsencrypt/live/sub.domain.tld/privkey.pem
SSLCertificateChainFile /etc/letsencrypt/live/sub.domain.tld/fullchain.pem
DocumentRoot /var/www/subdomain
ServerName sub.domain.tld
ErrorLog logs/subdomain-error_log
CustomLog logs/subdomain-access_log "%t %h %{SSL_PROTOCOL}x %{SSL_CIPHER}x \"%r\" %b"
ServerAdmin user@domain.tld
SSLEngine on
SSLProtocol all -SSLv2 -SSLv3
SSLCipherSuite ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256
SSLHonorCipherOrder on
SSLOptions +StdEnvVars
<Directory "/var/www/cgi-bin">
SSLOptions +StdEnvVars
SetEnvIf User-Agent ".*MSIE.*" nokeepalive ssl-unclean-shutdown downgrade-1.0 force-response-1.0
# allow .htaccess to change things
<Directory "/var/www/subdomain">
Options All +Indexes +FollowSymLinks
AllowOverride All
Order allow,deny
Allow from all
</Directory>
</VirtualHost>

Comment-out every line in (or remove) /etc/httpd/conf.d/owncloud.conf.

Move /var/www/html/owncloud/* to /var/www/subdomain.

Make sure permissions are correct on /var/www/subdomain:

  • chown -R :apache /var/www/subdomain

Run the command-line installer: /var/www/subdomain/occ maintenance:install

Fix ownership of the config file, /var/www/subdomain/config/config.php to root:apache.

In config.php,

  • change trusted domains from ‘localhost‘ to ‘sub.domain.tld
  • make sure ‘datadirectory‘ is equal to /var/www/subdomain/data
  • change ‘overwrite.cli.url‘ from ‘localhost‘ to ‘https://sub.domain.tld

Navigate to http://sub.domain.tld, and follow the prompts – and you should be a happy camper.