Eli Bendersky's website While installing and configuring MySQL server (version 5) on my home PC (Windows), I ran into many small and annoying problems and gained some useful insights. This post shall be a protection from repeating these mistakes in the future.
First of all, I downloaded the Windows auto-installer from MySQL's website and ran through the installation. The server executable suitable for Windows XP machines is mysqld-nt.exe, located in the bin directory of the installation. It is designed to be run as a Windows service (background process). To register it as a service from the command line, run:
mysqld-nt --install
This installs MySQL server as a service that will run every time Windows starts. If you don't want that to happen, then open the Services configuration tool (Control Panel -> Administrative Tools -> Services), find the MySQL service and modify its Startup properties. This Services tool can then be used to manually start and stop the service when needed. In any case, it is also possible to start and stop the service quickly from the command line:
net start mysql net stop mysql
More details about post-installation configuration (like setting the root password) can be found in the MySQL docs. Here I'll focus on the specific problem I ran to.
Once the server is up and running, it is easy to access it from the command line, by calling:
mysql -u root -p
And entering the password. This command connects to 'localhost' by default, which is what we need. However, creating and viewing databases via a command-line tool isn't too convenient, so I was looking for a GUI to do that, and found MySQL Administrator, which is part of MySQL GUI Tools, downloadable from the MySQL website. However, after installing the tool I had a problem connecting to the server - I kept getting the following error:
ERROR 2003: Can't connect to MySQL server (10061)
This was very annoying since I was able to connect via the command line, so why
not with a GUI ? What made the problem stranger is that I could use the GUI to
connect to online databases which were not on my PC. I googled a lot, did some
research, used nmap and mysqladmin to investigate how
the server runs and which ports it listens to. To cut a long story short:
By default (at least in my installation), MySQL server is configured not to use
a TCP/IP connection but rather a local named pipe (which is called 'mysql',
BTW). The command line mysql tool is aware of that and connects to
the server without any problems. However, to use MySQL Administrator, one has to
specify the exact connection details.
You can ask MySQL Administrator to use the named pipe to connect (via Details in
the connection dialog), but I wanted to set up my server to listen to TCP (on
MySQL's default port - 3306). To do that, it is possible to modify settings in
the my.ini configuration file. I'm sure the MySQL docs explain all
about that, though the file can get pretty complex.
Fortunately, on Windows at least, there's a simpler way. MySQL comes with a tool
named MySQLInstanceConfig, which can be found in the bin directory
and in the folder MySQL creates in the Start menu. Using MySQLInstanceConfig, it
is possible to configure the MySQL server in a friendly manner. I asked it to
listen to port 3306 instead of a named pipe, and MySQL Administrator connected
successfully, allowing me to add and edit tables in my DB easily.
- If MySQL Administrator complains about setup of "InnoDB", this is tweakable in the MySQLInstanceConfig tool.
mysqladmin versionis a useful command to execute to see some information about the currently running MySQL server, including the port it listens to.