MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL Installation and More

with 5 comments

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:

mysqlinstall1

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.

mysqlinstall2

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.

mysqlinstall2a

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.

mysqlinstall3

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.

mysqlinstall4

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

mysqlinstall5

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.

mysqlinstall5a

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.

mysqlinstall5b

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

mysqlinstall6

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.

mysqlinstall6a

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.

mysqlinstall7

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

mysqlinstall8

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

mysqlinstall9

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.

mysqlinstall10

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

mysqlinstall11

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.

mysqlinstall12

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).

mysqlinstall14

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.

mysqlinstallation16

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.

mysqlinstall15

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.

mysqlinstall17

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.

mysqlinstall18

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.

mysqlinstall19

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.

mysqlinstall20

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

mysqlinstall21

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

mysqlinstall22

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.

mysqlinstall23

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.

(a). You connect as the superuser by calling the client-side mysql program, like the following (assumes you set the password for the root user to cangetin). You don’t need to provide the port number if it is the default 3306 but will need to provide it for any other port that the mysqld service is listening on.

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

You can confirm your the root user by running the following query:

mysql> SELECT CURRENT_USER();

(b). You create a database with the following syntax:

mysql> CREATE DATABASE sampledb;

(c). You create a user with the trivial password of the user’s name, which is a really bad example when you’re doing anything but writing documentation. The following creates the user and then grants the user all privileges on their database.

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

(d). You sign-off as the root user by typing quit, like

mysql> QUIT;

(e). You sign-on as the restricted myuser with the following syntax:

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

You can confirm your the restricted user by running the following query:

mysql> SELECT CURRENT_USER();

(f). You can’t do much except explore generic metadata at this point because you must select a database. You select the sampledb database by doing the following:

mysql> USE sampledb;

You could now create a sample table that uses automatic numbering, insert values, and query the contents by using the following syntax.

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        |
+-----------+-------------+

Alternatively, you could place those three commands (minus the “mysql>” on each line) in a file and run the file from the command line. Just so there’s no misunderstanding, the file would look like this:

-- 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

If you named the file sample.sql and it is found in the C:\Data directory, you’d run it like this:

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.

Written by maclochlainn

May 24th, 2009 at 10:29 pm

Posted in Microsoft XP,MySQL