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 …
Most IDEs nowadays work nicely with remote servers. But how about MySQL? phpMyAdmin is ok when you only have to use it once in a while, but a pain in the a… for everyday work.
So I wondered if my favorite MySQL Client HeidiSQL (which has a slightly stupid name, but this shouldn’t keep you off from trying it!) was able to help…
… and it was! You simply have to use the connection type “MySQL (SSH tunnel)” and set up the two tabs in the Connection Manager correctly:
1. Tab "Settings":
- Hostname: 127.0.0.1
- User: [mysql-user]
- Password: [mysql-pwd]
- Port: 3306 (default)
2. Tab "SSH tunnel"
- SSH Host: [server-name]
- Port: 22 (default)
- Username: [ssh-user]
- Password: [ssh-pwd]
- Local port: 3306
(thanks to Thomas Haller for the nice aggregation!)
It simply works like a charm (given a decent internet connection, of course) and gives you all the comfort a lazy Windows user wants (and needs :-).
Another tip from Thomas blog: if you experience problems with the connection, try to delete the path to the plink.exe. This will show the error messages which otherwise would be suppressed.
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"