MySQL – Love Your Code

Use HeidiSQL with your vagrant box

I already discussed how to use HeidiSQL with a remote server in a former post. I now switched to use a local Vagrant VM instead of a remote VM. Of course I still wanted to use my beloved HeidiSQL. And it is – again – very easy!

On the above page, you need to use the MySQL Username and Password.

On the “SSH tunnel” tab we have to use the Username and Password for the SSH connection. I use a Private Key instead of a password, so I leave the password empty and specify the Private key file instead.

My box is configured to use SSH port 2222 instead of the default port 22. This will most likely differ on other boxes, so it is worth to check this first if the connection is not possible.

That’s it 🙂

Display content of blob column as plain text

Sometimes it can be useful to have the content of MySQL blob columns displayed as plain text. There’s a pretty easy way to do this:

SELECT CONVERT(my_blob_column USING utf8) AS plain_text_from_blob
FROM my_table;

Yes, these little tricks you need once in a lifetime …

Helpful MySQL shell tricks

Although I’m Windows user most of the time, I have to take care of some projects on Linux boxes, so it’s good to have at least a little knowledge of what you’re doing there.

The pipe really is a helpful little tool, which allows to do a mysqldump and directly gzip the result:

mysqldump my_database | gzip > my_dump.sql.gz

This surely saves some time during late night deploys!

You can even do a mysqldump from one server to another:

mysqldump my_database | ssh user@12.34.567.89 "cat >> /tmp/my_dump.sql"

Neor Profile SQL

Recently I discovered a free and easy to use SQL Profiler for Windows, Mac and Linux:

www.profilesql.com

It works as proxy between the Application and MySQL itself, so basically all you need to do is configuring your Application to use it by simply adding the port number 4040 to the host (and configure the Proxy to connect to MySQL, of course).

As inherent to the functional principle of a Proxy, the Profiler has to be running – so don’t forget to remove the port from the host when you don’t need it.