Allowing Remote MySQL Connections on Amazon AWS and Windows Server 2016

I ran into one of those situations today where it took me forever to figure out why a MySQL database I spun up on Amazon AWS Windows Server 2016 wasn’t allowing remote connections. I finally figured it out on my own today — no thanks to the internet. Hopefully this helps you.

There are a few obvious things you need to check first and which you probably already know about to enable remote MySQL connections:



1. MySQL Account with Remote Access: First, you need a MySQL user on the database server that can connect from a host other than locally (a la “@localhost”). So, a user like “myUser@%”. The ‘%’ after the “@” is a wildcard that allows the user to connect to the server from any IP address in the world. Ideally, you would want to lock this done further than that, but it’s great to toss the wildcard in there for troubleshooting.

2. Amazon Security Groups: Ensure the Amazon security group your instance is attached to has a special inbound entry that allows TCP traffic on port 3306 from your IP address (or all IP addresses). This is easily done through the Amazon EC2 interface and there is a special type called “MySQL/Aurora” that you can select from the drop-down that will pre-fill the port-number .

3. Windows Firewall: Finally, and this is the gotcha that took me forever to figure out, you must check your server’s local inbound firewall rules. I saw a bunch of “allow” entries for MySQL already in there so I assumed it was fine. However, by default it creates these rules under the profiles “Domain” and “Private”. You have to explicitly add in another one within the profile “Public”, or edit one of the existing rules to also allow public, to finally enable remote connections.

After I did the final step, it worked like a charm.

Next Steps in a Donald Trump Presidency

Last night, my wife and I watched the election results as our newborn daughter slept in the crib next to us. Donald Trump is our next president. This much is now certain. What this means for our daughter’s future however, is less certain — like all futures.

Whether you voted for Hilary or whether you voted for Trump, all-in-all, it’s important not to overstate the importance of a single four-year presidential term of one person on your life. Yes, the President of the United States is like a Keystone species: with a disproportional large effect relative to size. Yet, still no one person is all-powerful. In fact, I think about some of the most notable individuals throughout history that have changed society for the better: those who have brought about social change, or have opened our eyes to new perspectives. Heroes like Dr. Martin Luther King, Rosa Parks, Susan B. Anthony, Ghandi, Mother Teresa, Nelson Mandela or Albert Einstein. None of those heroes were US Presidents. Additionally, all of these individuals suffered severe discrimination in their lifetimes: Einstein fled Germany from Adolf Hitler’s regime, Mandela was jailed for 27 years, Ghandi and King were assassinated. My point is that it’s not from a position of comfort that brings out the best in us — provoking society to change for the better — but significant change often comes from a position of discomfort and suffering: extraordinary achievements from seemingly ordinary individuals.

If you didn’t vote for Trump, and you don’t like the political trajectory of the nation in the upcoming years, four years is not a long time to wait for another election. I have many close friends who are both republicans and democrats. Even my family, although we share DNA, we don’t always share similar feelings on politics. Yet we still talk to one another, love and care for one another, all despite our politics. I think the same goodwill applies to the nation as a whole. Sure, there are gray dark spots on the fringes: those groups mired in exceptional hate. They will always exist in some form, but they are diminishing.

We all know John F. Kennedy famously remarked that the country is more about the individual’s endeavors rather than what an amorphous “country” might do for (or against) us. But another lesser known quote by JFK that I like even more, is that it is better to light a candle than to curse in the darkness. We can watch what happens in the next four years passively, or we can be an active participant of the future of our nation, and the future of our children. What candle will we light? What specific cause close to our hearts will we take up in the next four years and what passions will we inspire to that cause? That, my friends, is ultimately the thing that will matter most to our onlooking sons and daughters — that is, when they wake up in the morning.

How to Force a link to Download Instead of Play in Browser

In an earlier post, I included a link to a large MP3 file on my server. I noticed that whenever I clicked on it in Chrome, the file would not download, but would simply play within my browser. That is not the behavior I wanted: playing in-browser like that over time might cause unnecessary bandwidth expenditure, but more importantly, I felt that playing in-browser would not be as convenient for some users that wanted to simply download the file. I wanted to give the user the option. Anyway, it’s really easy to force an HTML-5 enabled browser (like Chrome) to force a link to download a file instead of playing in-browser. All you need to do is include the word “download” anywhere in the anchor tag:

<a download href="yourfile.mp3">Download this File!</a>

Now if only all things were that easy.

How to Backup MySQL and FTP Scripting with PowerShell

I’m a .NET developer, not a system admin. As such, I never properly learned how to write anything aside from “echo” this, “dir” that, and “ipconfig -toomuchinfo” to a command window. But now, since I manage my own server on Amazon AWS, I wanted a neat non-c# console-app way of backing up my MySQL Database and FTP-ing the resultant SQL file to another location. Because Amazon storage is expensive and my other hosting provider (HostGator) has almost unlimited storage space, I wanted to FTP things there, as a safe, inexpensive backup solution. This is the poor man’s disaster recovery, which like most poor-man solutions, works surprisingly well.

I challenged myself to do this whole task in just Powershell and windows commands on the task scheduler, and the solution I came up with seems to work really well. Here it is:

1. First, I backup my MySQL database. Create a backup directory, like: “c:\backup\”. Then, use the MySQL backup utility: this is a file called “mysqldump.exe”. If you installed MySQL on your computer/server, the exe is somewhere on your computer. Just find it and copy it into your shiny new backup folder. Open up Notepad or your favorite text-editor (like VIM if you still use that) and create a simple “.BAT” file that runs mysqldump on your database which spits out a SQL backup file in your backup folder. I name the file after the current date: so today’s file would be “backup.20161101.sql”. The backup script is really just one line of text that reads like the following:

mysqldump.exe --single-transaction -u[username] -p[password] [your_database_name] > "C:\backup\backup.%date:~10,4%%date:~7,2%%date:~4,2%.sql"

(Note: replace the contents in [brackets] with your actual situation. Don’t include the “[]” and don’t add any spaces. Notice, that username and password arguments strangely don’t have a space between the “-u” or “-p”).

2. Double-click the BAT file to make sure it works and creates a SQL file for you.

3. Next, prepare your Powershell file that will FTP your file away. Powershell files are plain-text files which end in a “.ps1” extension. Before you get started with Powershell, you need to change the execution policy of Powershell on the server. The execution policy is by default crippled for security reasons. To change this, open up Powershell command prompt as administrator and run this command:

PS C:\> Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

This will allow you to run your own Powershell commands but still requires downloaded scripts to have a signed certificate to execute. You will likely get a scary warning when you change this. (Please read up more on execution policies before doing this.)

4. Finally, create your Powershell script that will FTP your SQL files to your FTP server. Because the file name scheme I have is predictable, I know what the name of the file is that I want uploaded each day. Here is the script I created; just copy this to a text file and replace the [brackets] with your situation and save the file. I called the file “FtpDataFile.ps1”:

#we specify the directory of the file we want to upload
$Dir="D:/backup/"

#ftp server
$ftp = "ftp://[ftpUrl]"
$user = "[FTPusername]"
$pass = "[FTPPassword]"

$webclient = New-Object System.Net.WebClient
$webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass)
$item = "backup.$(Get-Date -Format 'yyyyddMM').sql"

"Uploading $($Dir + $item) ..."
$uri = New-Object System.Uri($ftp+$item)

$webclient.UploadFile($uri, $Dir + $item)

5. Set up another task that runs your Powershell file on the server (space it out at least 15 minutes after the MySQL backup task). In order to run Powershell on the command window, you need to essentially execute the “Powershell.exe” process and pass the script file as an argument. So it would look like this:

c:\> PowerShell -File .\FtpDataFile.ps1

(Note: The “.\” is a relative directory symbol meaning, “directory above this one”; you may need to adjust your path.)

You can easily consolidate all this to just one Powershell script. And if you do, be sure to put in a wait/sleep command in there between the two tasks to make sure the SQL file is finished building before FTP-ing it away.

Baby Shh Sounds

What is it about the sound, “Shh”, or “Shhh” or “Ssh” that causes babies to calm down and if you’re lucky, sleep. I’m not sure why it’s so effective. I hear it may be related to it’s similarity to the sounds made in the womb: of the muffled rushing of liquids in the body that a baby associates with comfort and safety. It seems to work on crowds too. We were all once a baby — so they say. Maybe we never quite shuffled off that instinctive feeling of quiet and calm when we hear that sound. And so we relent, trance-like when our neighbor shushes us during Zoolander 2.

For our daughter, in the middle of the night — say 3AM — if she is still wide awake after a feeding and I’m trying to get her to sleep, I will play a recording of this sound over and over again. I will raise the volume and slide the phone under her cradle. This works most of the time. If you’re in a similar situation with a midnight-jazzed baby, you can download an hour-long MP3 of the shh recording that I use below and let it play in the crib. Load it on your phone or tablet and hopefully it works well for you and your little one.

Shushing Baby MP3 (86 MB; 59 minutes) play | download