Working with MySQL on Windows - Part 1

This is the first post in an attempt to write about how to get along with MySQL on Windows. Before I start, I want to stress that the focus here is seasoned Windows users getting started with MySQL. As for MySQL, I am prett much Operating System agnostic, having used Linux / Unix for some 25 - 30 years ( started using Unix as of Version 6. That's OLD). But I have also been a Windows user and developer since Windows 3.0 days.

Also, when I say I'm a Windows developer, let me tell you where I come from. I come from learning Win16 in C. Hardcoded message loops and message handlers and that kind of stuff. And that is how I still develop on Windows, but these days with Win32 / Win64 and exposing more Windows APIs. So I am no C# or .NET expert by any means.

So, you are ready to get started with MySQL on Windows? OK, first you should download it, possibly from here: http://dev.mysql.com/downloads/mysql/. And no, you don't have to pay anything, MySQL is free for us developers. And no, you will not need to compile anything from source or something, quite the opposite, MySQL comes with a very nice installer. Just download the MSI installer package for your platform (32 or 64 bit that is), and get started.

Usually when Installing MySQL, you can choose most of the defaults. After the installation, a configuration application starts, and you can usually choose most of the defaults for a starter here also. One things that your might want to look at is the character set selection. Latin1 is one option that is usually OK and that is similar odl and tried Windows CP1252. It's good enough as a start. If you plan to develop Web applications, then you might want to choose UTF8.

OK, assuming all went well, where do a get started with really using MySQL? Well, there isn't much in terms of a Windows GUI for MySQL installed as part of MySQL. And not only that, if you are a Visual Studio user, possibly developing in C#, there is very little in Visual Studio that shows that there is a new database server installed? Well, this we will fix, all this stuff sure is available from MySQL, you just have to know where to look.

First, as for a MySQL GUI for Windows, you have a few choices. If you want a complete MySQL design and SQL tool, then look no further than MySQL Workbench from MySQL themselves. This is a database design tool for MySQL that is loaded with cool features. Again, you can download it and use it at no cost, it is available here: http://dev.mysql.com/downloads/workbench/5.2.html

There are alternatives to MySQL Workbench, and many of them are also free and Open Source. HeidiSQL is one such option, and I have a tool myself called MyQuery. Both of these are a little less ambitious than MySQL Workbench, but also has features of their own. HeidiSQL is very much DBA focused and has loads of DBA style functions. My own MyQuery is focused on working with MySQL Scripts and shines when it comes to extensibility among other things. MyQuery can be downloaded here: http://sourceforge.net/projects/myquery/

But what about your applications? How is a database connection to MySQL achieved? Well, there are several ways to connect to MySQL. When you download the MySQL Server, it comes with a C API. If you are an old man like me, that might well be the way to go. The C API is OK and is well documented here: http://dev.mysql.com/doc/refman/5.5/en/c.html. The C API comes in static and dynamic shape. If you don't know the the difference or don't really care, then use the dynamic build. Using the static library is more demanding. MySQL itself, including the C API, is built with Visual Studio, so if that is what you use for your application (and I guess you are), that is OK. And you can do with the Express editions of Visual Studio.

Now, chances are you are not an old C hacker like myself, but a more modern person, using C#, Visual Basic or Java. If that is the case, MySQL provides help even here. If you are using Visual Studio with any of these languages, you want to download and install MySQL Connector/Net. A Connector is what MySQL calls their database drivers. And yes, even if you are on Java you want the .Net connector. The reason is that the .Net connector also includes the Visual Studio integration, although here it doesn't seem that the Express editions are enough. Connector/.Net can be downloaded here: http://dev.mysql.com/downloads/connector/net/.

And then for Java, you need Connector/J, available here: http://dev.mysql.com/downloads/connector/j/

Now, one word of caution before I leave you for this time: Both Connector/Net and Connector/J does some cool things for you. If you look at how MySQL works, or have a look at the MySQL C API, you realize that MySQL has some very particular ways of dealing with some things, like array inserts, prepared statements and things like that. To make life as a MySQL developer easier, both of these connectors do some very cool things to isolate you from that. Also, these connectors to an extent address some things beyond MySQL itself, such as high availability. And thirdly, tuning the Connectors is often just as important as tuning the server! What I am saying here is, in short, something as unknown and arcane as read the documentation. In particular this is true for Connector/J, which has many parameters, is very advanced, but also very fast and can help you speed up your application if you know how to work it.

Bye for now, I'll be back in a jiffy with some more Windows specific MySQL notes
/Karlsson