Connecting to remote MySQL servers so they appear as though they are local

Simple! Just create an SSH tunnel. Or a couple of them.

Create SSH tunnel to MySQL server running on port 3306 on 192.168.213.207 so that server appears to be running locally on port 3307:

  • ssh -L 3307:127.0.0.1:3306 root@192.168.213.207 -NnT

Create SSH tunnel to MySQL server running on port 3306 on machine2 so that server appears to be running locally on port 3308:

  • ssh -L 3308:127.0.0.1:3306 root@machine2 -NnT

Compare the fancyDatabaseName database on each machine using the mysqldbcompare tool (part of the mysql-utilities package):

  • /usr/local/bin/mysqldbcompare --server1=root@127.0.0.1:3308 --server2=root@127.0.0.1:3307 fancyDatabaseName

Explanation of flags to ‘ssh‘:

-L [bind_address:]port:host:hostport
Specifies that the given port on the local (client) host is to be forwarded to the given host and port on the remote side. This works by allocating a socket to listen to port on the local side, optionally bound to the specified bind_address. Whenever a connection is made to this port, the connection is forwarded over the secure channel, and a connection is made to host port hostport from the remote machine. Port forwardings can also be specified in the configuration file. IPv6 addresses can be specified with an alternative syntax:
[bind_address/]port/host/hostport or by enclosing the address in square brackets. Only the superuser can forward privileged ports. By default, the local port is bound in accordance with the GatewayPorts setting. However, an explicit bind_address may be used to bind the connection to a specific address. The bind_address of ”localhost” indicates that the listening port be bound for local use only, while an empty address or ‘*’ indicates that the port should be available from all interfaces.

-N will disable the ability to execute a remote command.

-n will prevent reading from stdin.

-T will disable the pseudo-terminal allocation.

Chrome still opens last session instead of home page

This is still a problem exhibited in Chrome 55 on the Mac (I know, I know, not the very latest available at this moment, but still, pretty new):

“I was prompted (the little up arrow on the wrench icon) to update yesterday (Sept 28 2012), so I did. Now when I open Chrome after shutting it down, it opens my last session. This is not what I want to happen. I have always set Chrome to open my home page on start. I have confirmed my setting is still set to open my home page. I did change the setting to something else and change it back, it didn’t help. Version 22.0.1229.79 m on Windows 7 Ultimate 64 bit”

Can’t type into Spotlight? On 10.12?

This afternoon I found myself without the ability to type into Spotlight on my new iMac running macOS 10.12.3. All other apps received keyboard input just fine. At the time I was using Spotlight to do some basic calculations involving adding two sums of money up. A bit of Googling lead me to this page, one answer for which was to restart the Spotlight process using the Terminal command “killall Spotlight”. That did the trick for me, Spotlight once again accepted keyboard input.

Mac files, Windows and removable media

A good post on this topic by Brian Dickens over at HostileFork: http://blog.hostilefork.com/trashes-fseventsd-and-spotlight-v100/

If you’re reading this, you have probably found some USB stick or external drive with files named .Trashes, .fseventsd, .Spotlight-V100 (and possibly even the more rarely reported ._.Trashes) on it. You might also be annoyed to see files in various directories called .DS_Store. Right?

Moved site to https

screen-shot-2016-10-26-at-1-16-51-pm

Steps

  1. Generated TLS/SSL certificate (free thanks to “Let’s Encrypt“, and easy thanks to Dreamhost’s built-in, couple-click-install Let’s Encrypt assistant).  This sent me an email with my site’s public key. The private key can be downloaded from Dreamhost’s web panel.
  2. Switched WordPress’ main URLs from http:// to https:// in WordPress’ settings
  3. Loaded my site’s main page and used Chrome to identify page elements that weren’t using https. In my case one of those items was a Google font pack that was referenced insecurely by my WordPress theme (Origami). Thankfully, updating the theme to the latest version alleviated that problem, but if it doesn’t for you with your theme this page describes how to fix the offending bits manually.
  4. Loaded some of the archived pages and noticed they weren’t all fixed.  Fixed a few posts to use protocol-relative URLs (//<host/path> instead of http://<host/path>), then decided that would take too much time and found this guide by Chris Coyier of CSS-TRICKS which provided a couple handy SQL snippets to fix all of the archived posts in one go.  I’ve reproduced them here in case they go away.  Note that I had to change `wp_posts` in the snippet below to `wp_ft7r2p_posts`, which is what the table was called in my Dreamhost “One-click” WordPress install. I ran the query by logging into Dreamhost’s control panel and launching phpmyadmin for the WordPress database in question. This let me simulate the query before actually running it. This is also where I found out the table was called wp_ft7r2p_posts rather than wp_posts.  Note that it could be done via the command-line mysql client just as well:
    • Fix up image source URLs:
      • UPDATE wp_posts SET post_content = ( Replace (post_content, 'src="http://', 'src="//') ) WHERE Instr(post_content, 'jpeg') > 0 OR Instr(post_content, 'jpg') > 0 OR Instr(post_content, 'gif') > 0 OR Instr(post_content, 'png') > 0;
      • Same query except catch single quotes:
        • UPDATE wp_posts SET post_content = ( Replace (post_content, "src='http://", "src='//") ) WHERE Instr(post_content, 'jpeg') > 0 OR Instr(post_content, 'jpg') > 0 OR Instr(post_content, 'gif') > 0 OR Instr(post_content, 'png') > 0;
    • Fix custom fields (I didn’t have any of these):
      • UPDATE wp_postmeta SET meta_value=(REPLACE (meta_value, 'iframe src="http://','iframe src="//'));
    • I also adapted the query above to fix images that were directly-linked incorrectly:
      • UPDATE wp_ft7r2p_posts SET post_content = ( Replace (post_content, 'href="http://krishengreenwell', 'href="//krishengreenwell') ) WHERE Instr(post_content, 'jpeg') > 0 OR Instr(post_content, 'jpg') > 0 OR Instr(post_content, 'gif') > 0 OR Instr(post_content, 'png') > 0
      • Same query except catch single quotes:
        • UPDATE wp_ft7r2p_posts SET post_content = ( Replace (post_content, "href='http://krishengreenwell", "href='//krishengreenwell") ) WHERE Instr(post_content, 'jpeg') > 0 OR Instr(post_content, 'jpg') > 0 OR Instr(post_content, 'gif') > 0 OR Instr(post_content, 'png') > 0
    • And one more to catch places where I hadn’t yet dropped use of ‘www’ in front of my domain.. and fix that while I’m at it:
      • UPDATE wp_ft7r2p_posts SET post_content = ( Replace (post_content, 'href="http://www.krishengreenwell', 'href="//krishengreenwell') ) WHERE Instr(post_content, 'jpeg') > 0 OR Instr(post_content, 'jpg') > 0 OR Instr(post_content, 'gif') > 0 OR Instr(post_content, 'png') > 0
      • Same query except catch single quotes:
        • UPDATE wp_ft7r2p_posts SET post_content = ( Replace (post_content, "href='http://www.krishengreenwell", "href='//krishengreenwell") ) WHERE Instr(post_content, 'jpeg') > 0 OR Instr(post_content, 'jpg') > 0 OR Instr(post_content, 'gif') > 0 OR Instr(post_content, 'png') > 0
    • Finally a catchall, as it turns out I missed a bunch:
      • UPDATE `wp_ft7r2p_posts` SET `post_content` = REPLACE(`post_content`, 'http://krishengreenwell.com', '//krishengreenwell.com') WHERE `post_content` LIKE '%http://krishengreenwell.com%' COLLATE utf8mb4_bin
      • Finally get rid of the www’s:
        • UPDATE `wp_ft7r2p_posts` SET `post_content` = REPLACE(`post_content`, '//www.krishengreenwell.com', '//krishengreenwell.com') WHERE `post_content` LIKE '%//www.krishengreenwell.com%' COLLATE utf8mb4_bin
  5. There are still a number of manually-generated legacy HTML pages from back when this site was run on MovableType that have http links that I’ll eventually get around to changing.
  6. I’ll probably back up my public/private keys on a USB key and store them somewhere for safekeeping.

That’s it!  Happy green lock everywhere!

Server-side rendered Javascript apps

While learning about the origins of Node (getting out from beneath my rock), stumbled across this article by Tom Dale, which I found informative and well-written. Here’s a quote:

For me, who always had a relatively modern device, this stuff was super fast. But Dan explained that they had users all around the world clicking on links to Twitter, some of them in internet cafes in remote areas running PCs from 1998. They were seeing times of over 10 seconds just to download, evaluate, and run the JavaScript before the user saw anything.

I really appreciated the nuance this article brought. The real world is complicated, and this article reflected that.

Tech & life musings