Installing MySQL 5.1 on Windows Server

Traditionally, on Windows - especially Windows Server - people use a product from the same vendor, Microsoft, called SQL Server. Its various editions offer us different limitations and capabilities. There's even a free edition called Microsoft SQL Server Express, which limits administrators and developers by database size, RAM consumption, and number of CPU cores, and in the MSDE2000 edition, also by the number of simultaneous connections. There are also more "capable" products, for which you'll have to shell out already very serious (for Russia - simply wild) money.

However, MS SQL can't do a lot of things. At the very least, it's simply not compatible with MySQL, which is used on the vast majority of hosting providers. Simple queries will work on both, but with more complex queries, these two servers will already refuse to understand each other.

I won't philosophize here about which is better or worse - that's a separate article for separate writers, requiring a careful and detailed approach. I've made my own choice, which I'm not going to announce here (by virtue of my job I administer both MS SQL and MySQL) - everyone picks their server software based on their own needs, abilities, skills, preferences and goals. And on the Windows platform, it's probably even more appropriate to use MS solutions (far, far from always, of course, but otherwise what would Windows even be for?)

But our task here is quite specific - we need MySQL on the Windows Server platform (it doesn't matter which one - be it 2003 Web Edition or 2008 Enterprise). Why exactly isn't all that important (though understanding that will be needed along the way during installation).


Downloading

Go to www.mysql.com, head to the "Downloads" tab, and under "MySQL Community Server" click the "Download" link. The download page opens, from which we can download the current version, or on the right click "Looking for previous GA versions?" to get to the same kind of download page for version 5.1.

Here I'll describe installing based on MySQL 5.1.57, i.e. exactly the "outdated" one, but the one most compatible with what's used on hosting servers.

So, we download it and put it, say, on the desktop.


Installation

Run the installer. When asked "how to install," choose "Typical."

After installation, the installer will ask whether to configure the server now ("Configure MySQL Server now"). Check the box and move on.


Initial configuration

Choose "Detailed configuration" and continue.

1) Server type. We're asked what kind of computer this is:
1a) Developer machine: a software developer's machine that works with MySQL databases. Choose this option if this isn't a server, but just a local MySQL install for a programmer's work.
1b) Server machine: usually we choose this option - this is a server that will run MySQL alongside some other services (for example, a web server).
1c) Dedicated server machine: if nothing but MySQL will run on this machine. I.e., this is a server exclusively for MySQL databases.

2) Predominant database type.
2a) Multifunctional database. A balanced option for use with both InnoDB and MyISAM databases. I recommend choosing this one.
2b) Transactional database. The option if the overwhelming majority of your databases will be in InnoDB format.
2c) NOn-transactional database. The option in case most databases are in MyISAM format.

3) Choose the directory where the MySQL server will store its databases.

4) Number of concurrent connections (concurrent connections). Heavily depends on the purpose of the MySQL server installation.
4a) Decision Support DSS (OLAP). A small number of simultaneous connections (a setting for 20 simultaneous connections will be set).
4b) Online Transaction Processing (OLTP). Better suited for popular web servers and client-server software running as thick clients - this will configure support for 500 concurrent connections.
4c) Manual. You can choose the value you need in the "Concurrent connections" field yourself.

Choose carefully - if the server is set to accept more simultaneous connections, it will consume more memory by letting a larger number of applications connect to it. If there aren't enough connections available, the server will refuse the next client's connection attempt until one frees up. What matters more to you - slowdowns from heavy swapping when the estimated load is exceeded, or simply "dropping" clients - is for you to decide. Try to estimate the real load this server is likely to see.

5) Network configuration.
5a) Enable TCP/IP Networking. If you check this box, the server will be accessible over TCP/IP. It's strongly recommended to leave this enabled even if all the software using MySQL runs on this same server - some clients may not understand anything other than TCP/IP.
5b) Port number. Default is 3306. It's better to leave it at default, otherwise you'll have to fix the settings and specify the new port for all your software and sites. Some software may simply be unable to connect on a non-standard port.
5c) Add firewall exception for this port. It's better to enable this even if the firewall is currently disabled - just in case you turn it on later. This option automatically adds an exception to the Windows firewall so that this port stays open.
5d) Enable strict mode. It's recommended to leave this option enabled. We'll leave it as is.

6) Next - choosing the default character set.
6a) Standard character set. This sets Latin1 as the default character set.
6b) Best support for Multilingualism. Sets UTF-8 as the default character set. Nowadays most software and websites are written for UTF-8, so this is the recommended value for Russia.
6c) Manual selected character set. If you want to specify the character set yourself (for example, KOI8-R or CP1251).

7) Service.
7a) Install as Windows service. Install it as a service in Windows. I recommend doing this - the service starts right after the OS boots, even before any user logs in. For Windows Server, this box must be checked!
7b) Launch MySQL Server automatically. Launch automatically. Well, why else would we install it as a service? Leave the checkbox checked.
7c) Include BIN directory in Windows PATH. By checking this box, you instruct the installer to automatically add the path to the MySQL binaries to the PATH environment variable. I recommend checking this box.

8) Security.
8a) Root password. Enter the root password here (the equivalent of sa in MS SQL).
8b) Enable root access from remote machines. If you check this box, you'll be able to log in to the server as root over the network - not only locally from this machine. For example, if you're installing a MySQL server management tool on another machine (not the server itself), you'll need to check this box. If you only manage MySQL on this server from the server itself, uncheck it - it's safer that way.
8c) Create an anonymous account. Whether to create an anonymous user. For security reasons, it's strongly recommended to disable this option (there are many reasons for that - I won't go into them here).

9) That's it, you'll see a window with an "Execute" button. Click it.


Verification

A "MySQL Command Line Client" item has appeared in the Start menu. Click it and enter the root password (hopefully you haven't forgotten it).

The familiar text console for managing the MySQL server appears before us (familiar to anyone who administers MySQL on Unix, of course).

Let's check:


MySQL is working :)



Graphical interface

Windows users are familiar and comfortable with a graphical interface (GUI). It's not exactly foreign to *nix users either, but we're used to seeing servers as text consoles (the core installation of Windows Server 2008, by the way, isn't an analog of a Unix text console - it's just a GUI with CMD stretched over it, by and large).

Despite the fact that many MySQL administrators handle this server perfectly well from the command line, or if not - through phpmyadmin, on Windows you'll probably want to see something resembling SQL Server Management Studio or Enterprise Manager.

MySQL has developed such a utility and provides it for Windows users (and not only them). Free of charge, of course.

So, we open the website www.mysql.com, go to the "Downloads" section and click the "Download" link under the label "MySQL Workbench (GUI Tool)". On the page that opens we select the option we need (I recommend MSI) and download it.

Yes, it's only available as x86-32 (32-bit), however this utility's functionality has been verified on Windows Server 2008 R2 (which, as we know, is natively 64-bit) - everything works.


Installation

To install this utility you need .NET Framework 4 Client Profile. Don't worry - this isn't the whole .Net Framework 4 - you won't have to install that entire beast. The utility gets by with Framework 3.5

You can download and install the Client Profile from here:
http://www.microsoft.com/downloads/ru-ru/details.aspx?FamilyID=e5ad0459-cbcc-4b4f-97b6-fb17111cf544

After installing the Client Profile - run the Workbench installer. The installation is as simple as it gets - a couple of questions, one of which is where to actually install it, and we end up with a working utility for managing MySQL.


First steps

If you've worked with Enterprise Manager in SQL Server 2000 or SQL Management Studio in SQL Server 2005/2008+, then the essence of the work will be more or less clear to you.

After launching, you'll immediately see a local MySQL server already connected. You log in as the user root. Naturally, you need to know this user's password (the equivalent of sa in MS SQL).

You see three large areas - from left to right:
a) Managing the server's databases
b) EER modeling
c) Administration (start/stop/status, users, etc.)

To connect to the console you need - click on the server's name in this console and enter the password of the user you're logging in as.

Footnote. In the database management console you won't see the system databases. They're hidden. Only user databases will be visible.

At the top you can see a panel with tabs. Open windows will be displayed on them (i.e. you can open several windows at once, for example, working with several database consoles from different servers).

To connect to other (non-local, for example) database servers you need to create "connections" via "New connection" for the database management console or "New server instance" for the administration console.

You can manage existing connections (for example, change the address, port, user, etc.) via "Manage Connections" or "Manage server instances" respectively.


Administration

Having clicked on the server you need (let's start, of course, with "localhost") in the 3rd panel on the "home" tab and entered the password for root, we end up in the server administration panel.

On the "Server Status" page we see the server's current status: resources consumed, the status (I wanted to say "daemon"...) of the service, current connections to the server, etc.

Startup/Shutdown: Accordingly, on this page we can stop the server or start it again.

Status and system variables: Here we can look inside the server's state in real time.

Server logs: If logging is configured to be displayed in a table (table), then it will be visible here. But if the logs are written to a file - too bad, the console won't display them.

Options file: Without digging into the depths of the my.cnf file or whatever replaces it on this MySQL install, we can easily manage the options right from here - from the graphical interface. The "Apply" button in the bottom right is used to save the changes. Don't forget that the server requires a restart (the MySQL server, not Windows Server) for the changes to take effect.

Users and privileges: From here we manage this server's users. From here we create them, grant them access to work with one database or another, allow creating other users, delete them, and assign passwords.

Data export and restore: Essentially, this is a backup or database migration tool. From here we can export or import selected databases to/from a file.


Managing databases (SQL Editor)

Having selected the server we need in the leftmost panel, we end up in the database management console.

From here we can create, modify and delete databases, tables, records in tables, stored procedures and views.

Reminds you a bit of Microsoft's SQL Management Studio, doesn't it?

Databases in Workbench are called Schema. Initially the list of schemas is empty - the system databases aren't shown to administrators of the graphical utility (unlike the text management console, where the system databases can be managed the same way as regular ones... which, of course, can be a recipe for trouble).

The "Add schema" item is used to create a database. Clicking on it takes you to the database creation window, where you'll be asked the standard questions - database name, collation (comparison), encoding.

After creation, the schema will appear in the list of schemas. To select it - double-click on it. The triangle to the left of the name lets you "expand" the schema, revealing its contents.

The "Add table" item is used to add a table. For this the schema must already be selected. Clicking on it opens the table creation window in front of you. It has considerably more options than the database creation window. Here you're asked to set the table name, its structure, key fields, indexes, etc.

Through "Add view" we can, accordingly, create a table view.

"Add routine" gives us the ability to create a stored procedure for this database.

All of a database's elements become visible after clicking the triangle to the left of its name in the list. From here we can see the tables, views and stored procedures (Tables, View and Routines respectively). Right-clicking an element gives you a list of actions you can perform with it. For example, for a table you can choose to view the first 1000 records (Select rows - Limit 1000), invoke the table editor to modify the data in it (Edit table rows), copy various table data to the clipboard (Copy to clipboard), open various data in the built-in SQL editor (Send to SQL Editor), or simply create a new table, modify this one, or delete it.


Learning and using it

This topic isn't meant to be a manual for Workbench - it's just here to answer the question "Is there a GUI for MySQL, and what is this utility?". Working with Workbench is quite simple and intuitive (for someone familiar with technical English and MySQL in general, of course). So - I'm passing the baton to you. The utility, as I already said, is easy to learn.