MySQL Installation on Windows

Windows Command Window with Administrative Right

Some Windows commands require administrator right

To open a Windows command window with administrator right:

  • Select Start up menu -> Command Prompt -> (Right Click) -> Run as Administrator
  • If the Command Prompt is not there, select Start up menu -> type cmd -> select cmd.exe on the top -> (Right Click) -> Run as Administrator

Commands require administrator right will be documented as:

admin>

Pre-Installation

To set the desktop development environment to a known clean state, remove older version(s) of MySQL.

Remove old MySQL Windows Service

  1. Control Panel -> Classic View -> Administrative Tools -> Services
  2. If MySQL exists,
    1. Stop the MySQL Service if it is running
    2. Open a Windows command window with Administrative Right
    3. Delete the service
      admin> sc delete MySQL
      
    4. Reboot the machine

Remove old MySQL installation(s)

  1. Verify whether MySQL has been installed. Control Panel -> Control Panel Home -> Programs -> Uninstall a program
  2. If any version(s) of MySQL Server exists, uninstall the MySQL installation(s)
  3. Delete the directory where the older version(s) of MySQL is installed

Install MySQL v5.1

Installation Directory

Path Description
c:\mysql\mysql-5.1\ MySQL installation directory
c:\mysql\data\ Data directory (Storing the DB data)

Install MySQL

  1. Download the MySQL Community Server
    1. Goto http://dev.mysql.com/downloads/
    2. Select the MySQL Community Server
    3. Select the Windows Platform
    4. Download the Windows MSI Installer
  2. Run the installer when the download is completed
  3. Select Custom for Setup Type, and click Next
  4. Select MySQL Server (if not select already) and click Change...
  5. Change the Folder name to c:\mysql\mysql-5.1
  6. Select MySQL Server -> MySQL Server Datafiles and click Change...
  7. Change the Folder name to c:\mysql
  8. Click Next and Install
  9. Follow the Setup Wizard
  10. For a Web application's DB, accept the default settings with the following exceptions:
    1. Select Transactional Database Only
    2. Select Online Transaction Processing
    3. Select Best Support For Multilingualism
    4. Un-check Install As Windows Service
    5. Enable Include Bin Directory in Windows PATH *

* Further instructions are based on the assumption that MySQL's bin directory is in the Windows PATH.

Test MySQL Installation

  1. Locate mysqld in a Windows command window
    cmd> where mysqld
    c:\mysql\mysql-5.1\bin\mysqld.exe
    

    If the location is wrong,

    1. Make sure c:\mysql\mysql-5.1\bin is at the beginning of the System PATH environment variable
    2. Remove any older version(s) of MySQL
  2. Start MySQL
    cmd> mysqld --console
    ...
    Version: '5.1.37-community'  socket: ''  port: 3306  MySQL Community Server (GPL)
    
    Note: --console logs message in the Windows command window
    
  3. Test the server
    cmd> mysqladmin version status
    
    Output:
    ...
    TCP port                3306
    Uptime:                 6 sec
    ...
    
  4. If any steps above failed, examine the log message in the console window
  5. Stop the server
    cmd> mysqladmin -u root shutdown
    

Run MySQL Server as a Windows Service (Optional)

For desktop developer environment, we do not install MySQL as a service to reduce system resource and boot up time

However, if Windows Service is desirable:

Verify whether MySQL is already installed as Service

  1. Control Panel -> Classic View -> Administrative Tools -> Services
  2. If MySQL exists, double click on MySQL
    1. If Path to executable is "c:\mysql\mysql-5.1\bin\mysqld MySQL", the service already exists and nothing need to be done
    2. Otherwise, following instructions in previous section to un-install the service

Install MySQL as a Windows service

  1. Open a Windows command window
  2. Ensure the server is down
    cmd> mysqladmin -u root shutdown
    
  3. Open a Windows command window with adminstrative right
  4. Install MySQL as Service

    MUST use the full path for mysqld
    admin> "c:\mysql\mysql-5.1\bin\mysqld" --install
    

Remove MySQL Server as Windows service (Optional)

  1. Open a Windows command window with administrative right
  2. Remove the Service
    admin> mysqld --remove
    

Secure MySQL User Account

  1. Start MySQL in a Windows command window if it is not running
    cmd> mysqld --console
  2. In another Windows command window, connect to the MySQL server
    cmd> mysql -u root
  3. Remove anonymous access
    mysql> DROP USER '';
    mysql> DROP USER ''@'localhost';
    
  4. Set root password
    mysql> UPDATE mysql.user SET password = PASSWORD('somenewpassword') WHERE user = 'root';
    mysql> FLUSH PRIVILEGES;
    
  5. Remove test database
    mysql> DROP DATABASE test;
  6. Exit
    mysql> exit
    
  7. Once the password is set, use the -p to indicate password is required for all MySQL client applications
    cmd> mysql -u root -p
    cmd> mysqladmin -u root -p shutdown
    

    If -p is omitted, the command will fail with

    'Access denied for user 'root'@'localhost' (using password: NO)'

  8. To start MySQL for development
    cmd> mysqld --console