MacLochlainns Weblog

Installing MySQL is pretty straightforward, especially on a Windows operating system. However, recently I was surprised to find out that there were folks who didn’t know you could install multiple version on their Windows development machine. Here’s are the step-by-step instructions to do so. At the end of the screen shots you’ll find out how to sign-on and create your first database and user account. Naturally, it’s all at the command line because they work on any platform the same way.

1. After you download an Microsoft Software Installation (MSI) file or the zip file, you launch the installer by clicking the Next button:

2. If you want to install only one copy of the software at any given time, you can accept the default Typical radio button choice. This way, you can simply uninstall the software before you install a new version. When you want to install multiple versions on a single machine, you should check the Custom radio button. This choice lets you maintain multiple versions on your single development machine and check changes between releases. After choosing a direction, you click the Next button to continue.

3. The first thing you should do is upgrade the Developer Components. You do that by clicking on the down arrow to the right of the X

, which yields the context menu below. Place your mouse over the top most This feature will be installed on local hard drive. After that you can begin to customize the installation.

4. This is a step that you’ll see when you opt to do a Custom installation. It is important that you click the Change button and change the default directory for the installation. When you click the Change button it will launch a File Finder window.

5. You can put the files anywhere you choose but I’d recommend that you opt to use something like this directory path:

C:\Program Files\MySQL 5.1\MySQL Server 5.1\

As you’ll see in the screen shot mine is slightly different because my virtual Windows XP machine is a 64-bit operating system. That’s why you’ll see the (x86) as part of the Windows Program Files directory path. It indicates that MySQL will be installed in the 32-bit executable folder. You click the OK button to accept the chosen directory and return control back to the MySQL installation program.

6. The choice of your file directory is now displayed below the caption Install to:, and you click the Next button to continue.

7. Before you click that Next button, you have one more step if you plan on having multiple instances installed on your machine. You need to change the default storage location for data files. You do that by clicking on the MySQL Server Datafiles. When you click on that, you’ll see the following default file location below the caption Install to:

C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\

You click the Change button to enter another location for the database files.

8. After you click on Change button, you’ll see the File Finder. You can then enter the directory that you’d prefer for the installation. My suggestion is the following:

C:\Data\MySQL 5.1\MySQL Server 5.1\

Click on the OK button to complete the selection of a customized database file location.

9. You should now see the wizard screen again. Choose the Next button to cotinue.

10. This is the screen where you see what you’re about to do, before you do it. It should look like this if you’re following my instructions or like whatever you’ve opted to use in your installation. Click the Install button to begin the installation.

11. The installation starts and you’ll see a dialog with a creeping status bar. It should take about 2-3 minutes on many computers but it may seem longer.

After the status bar, you’ll see the following marketing screen. Click the Next button to continue.

12. The second marketing dialog then shows up, just click the Next button to continue.

13. The installation is now complete. You should ensure you’ve checked the Configure the MySQL Server now box. Click the Finish button to end the installation of the file system and begin the configuration of the MySQL server.

14. The next dialog is the configuration welcome. Click the Next button to continue.

15. This is an important dialog because you must choose between a Standard or Detailed configuration. You should check the Detailed radio button When you plan to install more than one MySQL version on your computer because you’ll need to control the listening port for the server. A default MySQL Sever installation sets the listening port at 3306. Typically, you’ll want the main version to run on port 3306 and set the other versions to 3307, 3308, et cetera.

16. This dialog lets you choose how to configure performance characteristics of your MySQL Server. As you become more skilled at MySQL, you’ll find that these are configurable in a file, but for now using the interface is probably your best bet. The choice you make here sets how much memory MySQL will take while running. Typically, you should choose Developer Machine unless this is a server installation or you have a log of extra memorty to dedicate to MySQL Server (that’s the mysqld process).

17. This dialog lets you configure the Inno DB files location. You should make sure that you ensure they’ll be different if you’re installing multiple releases. You click the drop down arrow for the Installation Path box, choose one and manually edit it, like shown for MySQL 5.1.

18. This dialog governs configuration parameters about concurrency. If this is for a developer machine click the Decision Support (DSS)/OLAP radio button. It generally gives you more than enough connections for development activities.

19. This is the dialog where you enable a firewall exception and set a port. Since MySQL 5.1 is my main testing environment, port 3306 (the default) is selected. You also want to enable both check boxes.

20. This dialog lets you choose a multiple byte unicode character set or the standard latin1. For those coming from an Oracle world, that’s WEISO8859-1 character set.

21. This dialog names your Microsoft Windows Service for MySQL. It is important that you change this to represent the release. The default for every release is MySQL. You should also uncheck the Include Bin Directory in Windows PATH, and set that manually in a command shell file (e.g., a mysql51.bat file). You can run the batch file when you want to work in this environment. If you allow this to append to the system path, make sure you know which one you’re using when working with MySQL.

22. This dialog sets the root password. My advise is that you leave unchecked the Create An Anonymous Account and the Enable root access from remote machines boxes. Make sure you use a password that you can remember, even if it is your personal machine.

23. You’re almost done. The next dialog confirms you want to configure MySQL. Click the Execute button to configure the server.

After the configuration, you’ll see the following dialog. It means you’re done.

24. If you encounter a failure starting the service, it’s not infrequent in any number of MSI files. I wrote another post on how to work around it here. The following is the error screen that may occur.

25. Here are the steps to create your own database and user. It also shows you how to grant privileges to the new user on only their database.

C:\Data> mysql -uroot -pcangetin -P3306

mysql> SELECT CURRENT_USER();

mysql> CREATE DATABASE sampledb;

mysql> CREATE USER 'myuser' IDENTIFIED BY 'myuser';
mysql> GRANT ALL ON sampledb.* TO 'myuser'@'localhost' IDENTIFIED BY 'myuser';

mysql> QUIT;

C:\Data> mysql -umyuser -pmyuser -P3306

mysql> SELECT CURRENT_USER();

mysql> USE sampledb;

mysql> CREATE TABLE sample ( sample_id   INT PRIMARY KEY AUTO_INCREMENT
                           , sample_text VARCHAR(20));
mysql> INSERT INTO sample (sample_text) VALUES ('One'),('Two'),('Three'),('Four'),('Five');
mysql> SELECT * FROM sample;

+-----------+-------------+
| sample_id | sample_text |
+-----------+-------------+
|         1 | One         |
|         2 | Two         |
|         3 | Three       |
|         4 | Four        |
|         5 | Five        |
+-----------+-------------+

-- Open the database.
USE sampledb;
 
-- Split standard out, and write a log file to the relative directory.
TEE C:/DATA/sample.txt
 
-- Run code.
DROP TABLE IF EXISTS sample;
CREATE TABLE sample ( sample_id   INT PRIMARY KEY AUTO_INCREMENT
                    , sample_text VARCHAR(20));
INSERT INTO sample (sample_text) VALUES ('One'),('Two'),('Three'),('Four'),('Five');
SELECT * FROM sample;
 
-- Turn off tee, close file to automatic appending.
NOTEE

mysql> SOURCE C:/DATA/sample.sql

This completes the basic steps, I’ll put a link here for a cross platform comparative tutorial on client-side command line interfaces, like Oracle’s sqlplus, MySQL’s mysql, and Microsoft SQL Server 2008’s sqlcmd.