Night Dreaming (by Sudar)

During my days with dreamhost, in order to access MySQL from my local machine, I used to add my ip to the allowed host list. Even though security is compromised here, I really liked to use HeidiSQL for accessing MySQL database server instead of the built in MySQL console. But after my move to SliceHost, I found a little trick using which I can continue to use HeidiSQL from my local machine without adding my ip to the allowed host list.

This nice little trick is called port forwarding. Let me show you how I configured PuTTY so as to enable port forwarding.
First install MySQL and then configure SSH to use key based authentication and change the default port by following the articles at Slicehost. After installing MySQL and configuring SSH, download and install PuTTY from its download page. I recommend you to download the zip file containing all the files.

Then create a new session in PuTTY by entering the ip address and also the port. Then choose Connection -> SSH -> Tunnels. In the source port field enter a valid port number like 8600. In the destination field enter the value 127.0.0.1:3306. 3306 is the default port in which MySQL runs. The reason why I asked you to enter a different port in the source is that, in future if you run a MySQL server in your local machine for testing, it will clash with your port forwarding. Click the Add button and then start the session. Don’t forget to save the session.

Now open your favourite MySQL GUI client. Mine is HeidiSQL. In the connection settings, enter 127.0.0.1 as the Hostname and enter the port which you specified in the source field in PuTTY (8600) as port. Also enter your username, password, default database name and the click connect.

Now the request which goes to port 8600 of your local machine is forwarded to port 3600 of your MySQL server by PuTTY and you can safely use a GUI client for MySQL without adding any ip to the allowed host list. Note that it will work only when PuTTY is having the session opened.

I hope this is of help to you and let me know how it is working for you. Happy PuTTYing

Tags: HeidiSQL, Linux/Unix, MySQL, PuTTY, slicehost, SSH

Recently I moved my WordPress blog to a new server. I took the dump of the old database and imported it into the new MySQL server. Everything was fine except that I stated getting some strange characters in my posts. For instance I was getting (Nov — 3rd — 2007) instead of (Nov - 3rd - 2007).

It took me lot of research and googling to find the solution and so I thought of sharing it here so that it would be helpful for others who might face the same problem.

First I raised a support request in WordPress, but didn’t get a reply. After some googling, I found that it was due to wrong character set in my new MySQL server. Instead of having utf8 as the character set the MySQL database server was running in the default latin1 character set.

I changed the character set in the MySQL my.conf file and also in the wp-config.php file of WordPress and re imported the tables. Even this didn’t solve my problem. Later I found that there were certain characters in my wp-posts table which were encoded in latin1 character set even though the table is set to utf8 character set.

I then exported the table using a tool called Heidisql (which is by the way an excellent alternative to the command line MySQL client). I then opened the sql file in a text editor and changed all instances of latin1 to utf8 (basically a find/replace). I saved the file and imported the tables again and the junk characters are gone.

So the lesson learned the harder way, KEEP EVERYTHING IN UTF-8, ABSOLUTELY EVERYWHERE, FROM DAY ONE. You’ll be glad you did some day.

Tags: Character, MySQL, set, utf8, WordPress