Tag Archives: MySQL

Navicat SSH Tunnel Error – 2013 Lost connection to MySQL server

by

3

This post is for anyone out there running any Navicat database tools.  The company, PremiumSoft, that makes the line of Navicat tools is probably best known for there incredible database administration tool, Navicat.  That’s where I first found them.  They make a database admin tool that can connect to MySQL, MS SQL Server, Oracle, SQLite and everything in between.  Aside from being able to connect to just about anything that stores data, once connected you can do so many cool things with your databases in the name of database administration, that it would take me a week to create a post for it all.  Besides, this post isn’t a commercial for Navicat, but I did have to share just how good this product is.  Believe me, it is amazing, and now they have this really wicked data modelling tool that works hand in hand with the database admin tool.  You need to see it to believe it.  Check out their site [link], they have very good demos and lots of information about the products.

My apologies, I digress, the main purpose of my post was to inform any people already using Navicat or any of the other PremiumSoft products about a problem I ran into and a way to fix it.  I am using the software with MySQL databases primarily, but I believe the principle of the fix will apply to any database and server out there, especially Linux.

Now, one of the really cool things about the database admin and data modeling tools is that they can connect to your database via a SSH (Secure Shell Port 22) tunnel, instead of the normal default and usually plain text method.  For example, by default, when you connect to a MySQL server, the username and password you give to the server is sent in plain text, so anyone can read it.  Any command you type on that database console is also sent in plain text, so anyone can read it.  Think about the new user you just created for your new web hosting customer. What if their database username and password fell into the wrong hands.  It might be bad, it might not, it might be localized just to that one customer/user which would be bad enough, but suppose they found an exploit and got root on your server.  Now they have all of your data.  Even if you don’t have any data that is secret, just the hassle alone, not to mention explaining all of this to your customer(s) make this a really bad day.

This isn’t usually a big concern if you are running the database on the same server as the web server (which is common practice in many hosting scenarios), and if your database tools are on the server like the MySQL command line tools and such.  But what if you want to connect to the database from say, your PC?  Like you would do if using a database admin tool like Navicat.  You certainly don’t want all of the data that you will be sending back and forth to be in plain text, right?  Well, now you don’t have to leave it in plain text!  You can setup the connection in Navicat to connect to the Secure Shell server, which means you have an encrypted connection and not plain text.  Then, you can use the SSH tunnel that was created to connect to the database server itself.  What this means is that you use the SSH server to redirect your communications to the database server locally, so no one can see it.  Just like you were sitting at the server itself.

I’ll run through it again real quick, see if this makes sense.  The connection between your PC and the server running database is now encrypted and secure from prying eyes because instead of connecting to the database server directly, you are connecting to the Secure Shell server.  It is now the Secure Shell server that takes your communication and hands it off to the database server internally, so it’s safe from anyone watching outside.  It’s really cool, and just another reason I love the Navicat product so much.  Not to mention Linux as well!

The problem that I found was this, when I created the link to the SSH server in order to talk to the MySQL server, it wouldn’t connect.  I would get the connection to the SSH server, but when it then tried to talk to the database server, the database server kicked it out like no connection could be made.  I tried connecting locally from the Linux console think that maybe I killed some MySQL process that listens for connections, but it was working fine.  I tried it again and again but it just didn’t work.  The error I was getting from Navicat was this:

2013 – Lost connection to MySQL server at ‘reading initial communication packet’, system error: 0

I did some digging and found a basic setting to check.  This didn’t fix the problem, but I thought I would share it here since it has to be set in order for the tunnel to work:

  1. In the sshd config file (/etc/ssh/sshd.config) make sure that AllowTcpForwarding is enabled, because the default is disabled in most cases.

What I finally found to be causing the problem, was TCP_WRAPPERS.  Naturally, in my hosts.allow file I had the IP address of my PC in there, so that I could connect to the server.  So at first this seemed odd that this was my problem.  However, when you think about it, it makes sense.  The connection that is coming to the MySQL server originates not from my PC, but from the SSH server itself.  That’s right, because my connection stops at the SSH server, and then the SSH server sends the data to the database server.  This is a simplified view of things, but it should work to illustrate what’s going on.  Therefore, the simple fix was to add mysqld: localhost or 127.0.0.1 to the hosts.allow file in order to allow the traffic to go through TCP_WRAPPERS and to the MySQL server.  I read more about this once I worked it out, and I saw some “technicians” offering the solution of adding mysqld: ALL to their hosts.allow file.  Egads! I said!  Technically that would work, but damn, don’t open it up to allow everyone into your databases!!!  Just add localhost or 127.0.0.1 and you will be fine, and you will keep out the other riff raff.  I hope this helps some of you out there, enjoy!

Oracle Buys Sun Microsystems

by

0

Or, the end of the tech world as we know it.  Maybe that’s too harsh, time will tell.  I know one thing for sure, Oracle stepped up with an offer that was only $400 million more than IBM was tossing around (I know, “only”, but when you are talking $7+ billion it’s not so much), and I bet IBM is now pretty mad at themselves.  Not just because they let Sun get away, but more importantly because Oracle with all of Sun’s technology under their belt, just became a veritable behemoth competitor.

I can’t say that Oracle buying Sun is worse than IBM buying Sun, I think either would have been bad, but I do think that IBM would have made more of the technology that Sun has, especially in the hardware arena.  Most people already run Oracle on Sun, but I think Oracle was angling the software more than the hardware.  Now they have the whole “stack” sewn up.  They have been re-branding Red Hat Linux to provide “their own” operating system, but now they don’t have to because they really do have their own operating system with Solaris.  One that lots of people prefer for running Oracle versus Linux and especially Windows.  Now Oracle can provide the application, the database, the operating system and the hardware platform to run it all on, all in one nice bundle.  I have come to think of it as the “O-Stack”.  Now, instead of a LAMP stack, Oracle will be pushing their O-Stack.

I just hope that the folk out there that have a considerable investment in Sun (me included), not only in SPARC, but also their X86 line, didn’t just get screwed.  Can Oracle keep the support going?  Will they keep the hardware lines going?  What will happen to Solaris, MySQL and Java (to name a few)?  Only time will tell, but I for one am not pleased with this announcement.

I’ll have more updates as I find information to share.

MySQL Database Backup Script

by

1

Here we go folks, I thought I would share a handy little script with you that I use to backup all of the databases on a particular Linux/UNIX server.  I do this by getting a list of the databases, and then using mysqldump to dump them all to a text file.  This seems to be the best way (short of replication) to get good clean backups of the data.  Toss it into a cron job and you can have it done automagically.  There isn’t anything yet to rotate files, but I might add that later.  Also, I am going to try and rewrite this in PERL so our Windows (and other OS’s that don’t have a shell like Bash) brethren can run this script as well.  For now though, it’s written for Bash but almost any shell would work I think.

OK, onto the script.  Continue reading →

Solarum’s MySQL Reference

by

0

Heads up, I have added something that I think you will find useful.  I have added a MySQL reference to the Library!  This is more than a cheat sheet, I try to explain the things that are going on in there as we do it, so it should be a good way to not only see how to do specific things, but also understand them as well.  Check it out here and see what you think, if you see anything wrong with it or you have some tip of you own you’d like me to add, drop a line and let me know.  Enjoy!

Installing a LAMP Stack on Ubuntu

by

0

I am using Ubuntu (Hardy Heron) as my primary desktop OS, and I wanted to install some tools for doing development work. At the same time, I thought I might run through the steps and post them for anyone else that might be looking to do the same thing. I’ll walk you through what I am doing, it’s your typical Linux Apache MySQL PHP (LAMP) stack.

First off, you can use sudo to run these install commands as your own local user, by putting sudo before each command, but I find it easier and more concise to simply use sudo to become root and install that way. I will write this article from that perspective, so if you don’t want to do this as root, simply put sudo before each command here.

You can become root by using this command from a terminal session:

sudo su -

Now that we are root, let us install MySQL which will be our database server:

apt-get install mysql-server

Next, let’s install Apache for our web server:

apt-get install apache2

In my case, I am developing in PHP, so we need to install PHP for Apache:

apt-get install php5 libapache2-mod-php5

Last but not least, if you want a nice database manager, get phpMyAdmin:

apt-get install phpmyadmin

If this is not a box that you have KVM (Keyboard, Video, Mouse) access to, and you will be connecting remotely, you will need to install the OpenSSH server so you can ssh into the server. Do this like so:

apt-get install openssh-server

Once installed, you should be able to log into the server with your user account. Please note that by default, you cannot log in as root via ssh. There you have it, now start writing some cool code!

Great stuff at PortableApps

by

0

In this post, I’d like to do two things.  First off, I want to plug a really cool site called PortableApps.com that has some really cool software in the form of … well, portable apps.  What these are, are common widely used applications that have been transformed in such a way that they can run right off of your thumb drive, no install necessary, hence the term portable.  They have lots of cool stuff that you can download, absolutely free, and use right off your thumb drive, or hard drive, or anywhere really.  It’s nice being able to have firefox and open office (and much more) with you, no matter where you go, even with all of your own settings and customizations.  That’s hard to beat!  Go check it out, you won’t be disappointed I am sure. Continue reading →