≡ Menu

MySQL SSL Setup Debugging

For my own sanity, I’m writing down the steps I took to get SSL working between 2 servers today. I ran into some frustration following the step-by-step instructions on both mysql.com as well as on 1 or two other websites on the web. To add to the fun, it seems sometimes I would get different, no descriptive, errors such as:

  • ERROR 2026 (HY000): SSL connection error (no additional details)
  • ERROR 2026 (HY000): SSL connection error: Unable to get certificate
  • ERROR 2026 (HY000): SSL connection error: protocol version mismatch

So here is what I did, step by step, with multiple test points…

On The MYSQL SERVER  that I wanted to connect to, I logged in and created a self signing Certificate Authority (CA) and then used that to sign a key for my mysql server. The commands looked like this:

openssl genrsa 1024 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem

openssl req -newkey rsa:1024 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

You’ll note that I used 1024 for my key instead of 2048. While 2048 would be more secure, and is what is provided in the example here, it causes my version of OpenSSL to generate in a slightly weird format that the mysql client doesn’t seem to like in some cases. A number of example online said there was a simple work around involving adding “RSA” to the header and footer of the keys, but no matter what I seemed to do, this never worked for me… so I just lowered the key strength to 1024 to get it to generate it the old way. There was also a suggestion to merge the server and client certs into one “CA”… but I didn’t like that either… in fact… I didn’t want a Client cert if if I could avoid it… as I wasn’t authenticating against it… I just wanted the SSL tunnel.

Once I had my certs created, I added these 3 lines to my.cnf. In theory I could have put them in the startup command for the server, but this was the better long term solution:

ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

Note: when I created all my certs above, I was in the folder /etc/mysql/ (in case you didn’t guess that already).

At this point, I restarted the mysql server and ran the following query on it:

show variables like “%ssl%”;

The server came back and would say that it “have_ssl” and “have_openssl” along with the paths to the certs I just loaded in. This was a good sign.

I think modified one of my users to only allow connections using SSL:

grant usage on *.* to example_user require ssl;

I than began testing from client devices. The first stop was the mysql workbench for windows. I loaded it up and tried to log in, and on the first try it failed (this I took to be a good sign, as I hadn’t told it anything about the SSL). I then grabbed the ca-cert.pem file I created on my server, and brought it over to my client box. I then told mysql workbench to use this file for the CA. You’ll note I DID NOT specify a client key or certificate for this. I then tried to connect, and wha-la, it let me in.

To confirm I was in fact transmitting over SSL at this point, I ran this query:

show status like ‘Ssl_cipher’;

and then insured it came back with a value (if it’s blank, your NOT connected over SSL)

I then moved on to my second client, which a LAMP setup. I tried connecting using phpmyadmin, and of course, it failed again as I had told it nothing about the certificate authority. So again, I copied the file over, this time adding the following to my my.cnf:

[client]
ssl-ca=/path/to/ca-cert.pem

I first tested with my mysql client on the machine and it worked great. I did run into a little trouble with phpmyadmin, but I found the connect lines in my config file and basically added this one line before the connection was made, but after the init:

mysqli_ssl_set ($link , NULL , NULL , “/path/to/ca-cert.pem” ,NULL , NULL );

I think this may not have been needed if my version of phpmyadmin had been newer… but not sure. (I was in 3.4.5 at the time, the documentation I was reading would have been for 4.2.7).

Regardless, when I signed off for the night, all my mysql connections that I cared about between my older “clients” and my new mysql server were running over SSL. Tomorrow, I hope to setup some replication between servers over SSL. This may require that I create a client certificate, but until then, I’m happy to not have needed one.

 

 

 

 

{ 2 comments }

A Culture of Pressure

The USA has turned into a culture of pressure. We never “ease up”. We are always “connected”. Etc.

I’m sure I’m one of the worst at this… between my job, my email, and my family, there’s barely enough time to check out the latest humble bundle.

And when I do, I feel guilty, for not doing other things that need doing.

And there is always something that needs doing.

The bible says we should have a day of rest, and up until the last 20 to 30 years, history seems to agree.

I feel like if there was some way to force a “Sunday” on everyone again, or at least get culture to really allow it, that we might see a lot of problems in our world vanish.

Maybe less students and adults snapping on Mondays…

Maybe less people suffering from anxiety.

Maybe a reduction a people addicted to drugs to help them unwind.

I’m sure this has all been said before… but it seems Christmas is a good time to mention it again.

Jesus wasn’t born in the middle of a busy city. He was born pretty much alone with his family (until the visitors started to show up). Different.

Maybe it’s time to reflect both on the peace that the one baby can bring us, and start making some changes in 2014 as a culture…

I guess that starts with changing myself first.

{ 0 comments }

Bitcoin as a Government Currency

I was thinking today about Bitcoin as a government currency. Particularly, when it comes to using it as a government currency.

I think, if I was starting a new country, or looking to replace my countries currency… I would use something based on either Bitcoin or Litecoin. I’ll call it GovCoin for the sake of this blog post.

Here’s my 3 biggest reasons:

No Need For A FED - 

The currency would be self regulated in the same way Bitcoin is. The downside to this is the same downside you have to any currency that can’t be manipulated: you can’t stop panics or bubbles. But I’ll take panics and bubbles any day over having my money easily manipulated by the government.

No Need For The IRS, or Tax Season -

The one change I would make to GovCoin is how the transactions work. I would set it up so that on every transactions, either a fix feed or a percentage was taken out of the transaction. This would basically amount to a sales tax, but done on every transaction, similar to how Visa does discount rates. Every citizen would have their own “pay in” ID that must be included with the transaction that is assigned they are issued a wallet. This would make it possible track how much you’ve paid in easily, either for refund purposes (tax-exempt organizations), or simply for your own reporting. The downside to this is the damage to free trade caused by imposing a transaction tax… but I believe the costs savings of not really needing an IRS could more than make up for it.

The other catch 22… Dealing with other currencies in your country. The only real solution to this problem is to not allow business to be transacted in other currencies… I’m okay with that… There will be international companies that are able to get around taxes by doing things in other currencies… I’m okay with that. As long as they are forced to pay their employees in our country in bitcoin, and all the stores online and off, require govcoin…  If it’s an international transaction, then there would probably need to be some sort of “flat govcoin tax” for bringing it into the country (like reverse postage).

Mining To Balance the Economy

I would use mining to help keep the classes balanced more than they are now. I would look at possibly making mining exponentially harder, the more you tried to do it as a pool or individual… Thus allowing the automated distribution of wealth (lottery) to be equal among the citizens… Maybe even making it somewhat based on how many transactions (or better, how much) you engaged in (inversely)…. This would somewhat create an automated redistribution of wealth… As the richest people in the country would no longer find it worth while to mine (most likely, as their odds would be hurt by the amount of the transactions they engage in), and the poorest people could mine to possibly collect a ‘minimum wage’. Don’t get me wrong… it’s tricky… and you don’t want everyone simply relying on generating currency (that wouldn’t work)… But it’s a more “practical” way to hand out money to people than say… straight up hand outs or subsisdies…. At least this way, if they simply take the money and go and spend it like mad, they have less a chance of getting more free money… But if it works, you’ve pretty much eliminated the need for government programs that are simply there to hand out money. (You might still need some public education programs like… public education… might… being the keyword).

I’m not sure it would work… but… If I had a couple billion dollars and some land to try it out on… It would be a fun experiment… Wonder if a commune could pull it off legally inside state borders.

 

{ 0 comments }

Christmas Is Coming

It has been awhile since I have taken the time to write here. Much has happened over the summer, most of which you could label as “busy”.

I haven’t given up on my home automation dreams, in fact, my latest improvement was moving the Raspberry Pi out of my office and into the basement. There it continues to collect data, and powers my bitcoin miner (which also doubles as a space heater).

In terms of other things I wish to automate going forward… I really want to get to the point where I have my home’s Christmas lights do cool things. That said, I don’t have $1000′s of dollars to spend just yet, so I’m going to take it year by year I guess to make it happen. This year, I focused on getting the trim decked out with some new LED lighting.

The biggest issue I’ve run into though is electricity… For some strange reason, there are no outlets on the outside of our house. At least, non that are useful. Next summer, I’ll need to look into changing that. I’ll try to do it with the goal in mind of being able to run lots of cable (not just electrical) to my future winter scenes.

I’m also kind of wondering what my neighbor’s do for Christmas. This being the first year in our home, and the first year I’ve been able to decorate, I’m kind of concerned that I may eventually do “to much” (is there such a thing)?

In other news: Link continues to get older. Ellie seems to as well, but I swear not as fast as it went with Link. I feel like Link could be starting 1st grade any day now (okay, maybe not). Being a father is a new challenge every day. But it’s one I am very much enjoying.

{ 0 comments }

Home Automation Part 7 – Systems and Circuits

The last few days of work on the house have been moving more into the practical and less into the measuring stages of things.  And both of these tasks have been involving the word “circuits”.

The first task that I’ve been working away at is trying to get my servo rig up and running. I did manage to get a TI MSP-430 chip to control one of my Futuba S3003 servos (which was pretty cool), but I have yet to get that onto my 1 wire network of devices, which basically means I don’t have a good way to control it. I’ll need to do some circuit work to figure out what 1-wire chip I plan on adding that the MSP-430 can get it’s needed state from. I also need to figure out exactly how I plan no attaching this servo to the damper, and mount it to the wall. And lastly, how I plan on powering it, as parasitic power is not going to be an option for this.

Perhaps it might be time to consider some other options for actually controlling the servo’s other than a bunch of MSP-430 chips with 1 wire supplements… Ardunio anyone? It might still be possible to do 1-wire with the MSP-430 alone… but that is proving difficult.

The other circuit that I’ve been messing with is not electrical, and has proven to be much more difficult to manage. That is the circuit of air flowing throw my house.

My first attempt at getting more cool air upstairs has met rather unexpected results… I added a booster fan (150 to 250 CFM) flowing into the room at the “end of the line” (“Girls Room”).  Instead of cooling the room more (which is what I wanted)… Either nothing happened OR it synced temperatures with another room nearby (Link’s Room). I’m leaning towards the syncing given what I think may have happened in terms of air pressures, especially since it’s quite likely that these 2 rooms share both a supply duct and a return duct.

And while the A/C is capable of overcoming this for both rooms, it does seem to provide some sort of balancing between the two rooms.

forblog

The Solid vertical red line is when the fan was kicked on. The 2 lines I’m most interested in on this chart are the green line (The room that has a booster on it now, girls room), and the orange line (Link’s room). While these 2 rooms moved in sync somewhat before with divergence occuring in the morning and evening… it appears that they now *greatly* moved together… so much so that the lines almost trace each other… Despite what the sun might be doing.

I should add for the sake of this that the blower fan is also running constantly pretty much throughout this entire chart. I’ll be curious what the booster does once I shut the blower off. We shall see.

In any case, I’m learning a lot. Last night’s lessons involved primarily how to cut into duct work and how to install a duct fan… 2 things I had never done before last night. And while it’s not perfect, I feel it was a great first attempt at balancing out the system… Now if only I could put some more effort into the servo side of things, I might eventually get my graph to be perfect… we shall see.

{ 2 comments }

It’s been awhile since I last posted… And much has changed.

First, I made the decision to go with a RaspberryPi for the server that would be running everything.  It was so simple to get running right away the way I wanted it’s crazy. So let’s talk about what it is I “got running”.

  • Installed “Raspbian” (Debian for RaspberryPi). This was very very straight forward.
  • Installed my typical LAMP setup (Linux, Apache, Mysql, and PHP). With PHP I included GD libraries (for graphing), and the CURL libraries (for communication with my thermostat).
  • I then installed OWFS. It’s a nifty software package to aid in communicating with 1-wire devices (such as my temperature sensors).
  • I then installed the PHP module for OWFS (so I could talk to it directly from PHP without to much additional hassle.
  • And lastly, I installed phpMyAdmin, just to save myself some time managing the mysql databases I intend to create.

From there, I created a basic PHP script that I ran as a cron job ever 5 minutes, that did the following:
1) Pinged each sensor for it’s current temperature.
2) Logged that to a MySQL table.
3) Pinged my Thermostat with the updated temps to allow me to see the updated temps on the display.

You would think I would stop there… but I have a long term goal of having this data easily viewable from the web. And while remotely accessing my RaspberryPi from outside my home is already possible… it’s not exactly how I want to be serving up webpages to the general public. So I needed to find a way to mirror the data to a real web server, in a near-realtime, fault tolerant way.

I considered simply having my php script talk directly to a remote web server via a remote MySQL connection… but that had the problem that it would stop logging data in the event of an internet outage.

So instead, I went back to my favorite way of replicating MySQL data…  MySQL Replication.

It’s been awhile since I had set that up for a server… so I was pleasantly surprised that since the last time I had done it, phpMyAdmin had added 2 things to it’s interface:

1) A Mysql “Sync” interface to allow you to get 2 servers in sync with one another… and
2) A Mysql Replication interface so you could start and stop replication without having to look up the “index number” and then run the various commands to start the replication.

Those were both some great time savers.

There was one other development in this project that came up in the last week, which I’ll address in my next post. We ended up deciding to replace our Air Conditioner. That’s got it’s own mini story, and isn’t entirely something that was done as part of the home automation project… But it was something that needed to be done non the less.

In any case, more updates to come as I start getting ready to move into the actual “automation” phase of this project (I’ve got the data now, just need to use it!)

{ 0 comments }