Using EFF’s CertBot with Apache 2.2 and CentOS 6

EFF has created a wonderful tool called CertBot to automate the retrieval and installation of letsencrypt certificates. The documentation is really good but it did require a little trial and error to get things working. Here’s a walkthrough with some of the gotcha’s I encountered.

Why do I need CertBot?

Letsencrypt certificates are only valid for 90 days. The verification and update process is a tedious process to complete manually. CertBot automates the retrieval and renewing certificates, it has a built-in webserver which can stand-in during the verification step, and it can modify your web server config and install the certificates if you happen to be using nginx or apache 2.4


Before you start, you should apply all software updates for your OS and restart. One of the updates overwrote a config file I customized which made the web server fail to start.

I was using CentOS 6 which doesn’t have a built-in package of CertBot. EFF also provides a version which can install its own dependencies called “CertBot Auto”.

Here’s how to install that:

[email protected]:~$ wget
[email protected]:~$ chmod a=rx ./certbot-auto
[email protected]:~$ ./certbot-auto --help

I’d also recommend moving “certbot-auto” into “/usr/local/bin” so it’s available to other users or cron scripts.

[email protected]:~$ mv ./certbot-auto /usr/local/bin/certbot


The docs are great. Have a look.

My site redirects all traffic to https which breaks the verification process which needs to happen over http. The “standalone” mode solves this. It requires that you stop the web server and cert-bot will then answer on port 80 to complete the registration process. The CLI also defines pre-update and post-update “hooks” which can be used to stop and start your web server automatically to minimise down time.

For my domain this looked something like:

[email protected]:~$ certbot certonly --standalone -d --pre-hook "service httpd stop" --post-hook "service httpd start"

If you’re setup includes both http and https, you can use apache to serve the verification files.

sudo certbot-auto certonly --webroot -w /var/www/vhosts/default/html -d -d

You can include up to 10 domains on each certificate.

Where does your certificate get stored?

Good question. On CentOS, certbot creates a /etc/letsencrypt folder. Each certificate you generate will have a folder in the “live” folder. The commands above created a /etc/letsencrypt/live/ with your certificates files.

Here’s what you’ll need to add to your httpd.conf or virtual host to use this new certificate.

#Using letsencrypt certificates.
SSLCertificateFile /etc/letsencrypt/live/
SSLCertificateKeyFile /etc/letsencrypt/live/
SSLCertificateChainFile /etc/letsencrypt/live/


The last thing to setup is cron task to automate renewal of our certificate(s).
“certbot renew -n” attempts to renew any certificates expiring in less than 30 days. The -n makes it non-interactive which is ideal cron task.
And that’s it. You’re all setup to take advantage of a letsencrypt certificate with centos and apache 2.2.
It was much easier than I though and after a lot of poking through the documentation I was able to piece together what needed to be done. Hopefully this saves you a few hours and a couple of server alerts.

Just one more thing. Ever heard of CCA?

One more wrinkle here is CCA checking. These freely available certificates mean that it’s not that hard to create a certificate which appears valid for a popular website. CCA checking adds a DNS record which informs certbot and other clients which authorities are allowed to register certificates for a given domain. All letsencrypt CAs will require this step after Sept. 2017. Because this requires a new DNS record type, not every host or registry is ready for this. I’m using namescheap for my .site address and it’s currently unsupported. Check with your domain registrar or whoever is providing your DNS service if they support CCA records. You might have to choose a new DNS provider if your registrar doesn’t if you want to continue using letsencrypt certificates.

I love Cockpit CMS

I've had a lot of fun playing with Cockpit CMS.
It's a "An API-driven CMS without forcing you to make compromises in how you implement your site."

Its a CMS without a front end. The front end is completely up to you. This tool takes care of all the back-end stuff. It provides a UI for building content types, uploading media, editing media and content, doing site backups and authentication. How the data is displayed is entierly up to you.

Built with a custom microframework called "Lime" which looks alot like slim2 and a storage system called "Mongo lite", it provides everything you need to build small sites that are a joy to work with.

The documentation includes a walk-through on how to build a simple blog.

Working with Bitfields in MySQL

Problem: You are working with an antique data model where someone thought is was a good idea to store true/false data as bits in an integer value.

Bitfields are a kind of composit column which stores many logical values in one physical column as an integer value. It allows you to store many yes/no options in a very small space. When looking at this data you'll see integers but what is really important is the bits that make up that number.

Lets say I want to store event level info in a log table.

CREATE TABLE eventlog(
level INT(11) NOT NULL,
msg varchar(32) NOT NULL

bit 1 (0001) = fatal error (int 1)
bit 2 (0010) = warning (int 2)
bit 3 (0100) = error (int  4)
and bit 4 (1000) = user error (int 8)

What if I have both a user error and a warning error? That's int 6.
Or all options? That's int 15.
Or all the level's with a warning logged? That's int 2,3,7 and 15

Querying or filtering on a single bit is hard. It's as difficult to work with as CSV string columns. If this data had been normalized properly, each part would have its own column in the database.

MySQL has a few built in functions for working with bitfield columns:

EXPORT_SET(level,1,0,'',4) AS debugBits,
SUBSTRING(EXPORT_SET(level,1,0,'',4),1,1) AS hasFatal,
SUBSTRING(EXPORT_SET(level,1,0,'',4),2,1) AS hasWarning,
SUBSTRING(EXPORT_SET(level,1,0,'',4),3,1) AS hasError,
SUBSTRING(EXPORT_SET(level,1,0,'',4),4,1) AS hasUserError
FROM eventlog;

which results in:

msg debugBits hasFatal hasWarning hasError hasUserError
test1 0000 0 0 0 0
test2 1010 1 0 1 0
test3 0010 0 0 1 0

We're taking the bitfield column, turning the column into strings and pulling the string apart. Notice here that bit 1 is on the far right.

What happens if I want to add context to this data. That's what MAKE_SET is for.

'userError') AS levelTextCsv
FROM warnings;

which will output your bitfield value as a single CSV column with text for each selected option.

Bitfields aren't ideal. Combining data into a single column is an anti-pattern in SQL. Should you encounter this in the wild, you'll know what to do.


Rename a MySQL database


Problem: You need to change the name of a mysql database without reloading it.

MySQL used to contain a "RENAME DATABASE" command but it would occasionally hose the database.

RENAME {DATABASE | SCHEMA} db_name TO new_db_name;

This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23. … Use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present.

So what should you do instead?

If all your tables are physically located on the same disk, you can use "create db" + "rename table" to move tables between databases without reloading them.

-- ensure the char set and collate match the existing database.
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

CREATE DATABASE `database2` DEFAULT CHARACTER SET = `utf8` DEFAULT COLLATE = `utf8_general_ci`;
RENAME TABLE `database1`.`table1` TO `database2`.`table1`;
RENAME TABLE `database1`.`table2` TO `database2`.`table2`;
RENAME TABLE `database1`.`table3` TO `database2`.`table3`;

This answer is also posted on stackoverflow.

Make MySQL 5.7+ behave like MySQL 5.6

You've upgraded your environment to MySQL 5.7 and everything is broken.

MySQL 5.7 includes a number of changes to the sql mode which are talked about here:

The old defaults can be set via the sql_mode property on a per-query basis or server wide via the my.cnf file.

To apply the old defaults:

  • Open your my.cnf (usually found at /etc/mysql/my.cnf )
  • Add a line under [mysqld] section. sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION
  • Close your file and restart mysql.


Thanks to Ian and Logan from Austin PHP for this solution.