MySQL Cheat Sheet

Installing MySQL on Windows Server

The price of Microsoft’s SQL Server is ludicrous, as is the price of Oracle’s RDBMS; really, any amount for a DBMS is really too much to pay. All of the functionality of relational database servers such as SQL Server and Orable is available in the free, open-source MySQL relational database server. Quite a cost savings indeed. This article describes how to install and configure MySQL on a Windows Server box.

Environment (Updated 3/29/09)

• • Windows Server 2008 + SP1 (amd64)

  • MySQL 5.1.32 (MSI installer)

Procedure

• 1. Download MySQL (take note of the community download links). Also note that MySQL is available in both 32-bit (AMD64) and 64-bit (x86) versions for Windows. I prefer to use the MSI installer and is what this article describes.

  2. Run the .msi file.

  3. In the Welcome window, click the Next button.

  4. In the Setup Type window, select the Custom option, then click the Next button.

  5. In the Custom Setup window, select the options you wish to have installed. I recommend installing all the options that are selected by default, along with the Developer Components option if you are installing MySQL on a computer where software development will be done. After selecting your desired options, click the Next button.

  6. Review your installation options in the Ready to Install window, then click the Install button. If you already have MySQL installed (you are doing an upgrade) then a window will probably appear indicating the MySQL process is running on the system. If this happens, then stop the MySQL service using Window’s Services control panel applet, then click the Retry button in the Files in Use window.

  7. In the MySql Enterprise window, click the Next button.

  8. In the MySql Enterprises Monitor window, click the Next button.

  9. In the Wizard Completed window, make sure the Configure the MySql Server now option is not selected, then click the Finish button.

Configuration

Now that MySQL is installed, you should run the configuration wizard. The configuration wizard is an easy way to maintain the my.ini MySQL configuration file, which is typically located here:

C:\Program Files\MySQL\MySQL Server x.x\my.ini

Follow these steps to configure your MySQL server:

• 1. Run the configuration wizard (Start → MySQL Server x.x → MySQL Server Instance Configuration Wizard).

  2. In the Welcome window for the configuration wizard, click the Next button.

  3. If you are upgrading MySQL then the Maintenance Option window allows you to specify whether you want to reconfigure the currently installed MySQL instance or if you wish to remove the currently installed instance. Choose the Reconfigure Instance option, then click the Next button.

  4. In the Configuration Type window, make sure the Detailed Configuration option is selected, then click the Next button.

  5. In the Server Type window, choose the type of computer that MySQL is being installed on (developer, server, or dedicated database server), then click the Next button.

  6. In the Database Usage window, select the type(s) of databases that will be in use on this computer (multifunctional, transactional, or non-transactional; I typically always select the multifunctional option), then click the Next button.

  7. If the InnoDB Tablespace Settings window appears, you can specify a different disk drive and directory in which to place InnoDB database files by clicking the Modify button and then browsing to the directory where you would like to have the files stored. Then click the Next button.

  8. In the Concurrent Connections window, specify how many concurrent connections you would like this server to support (this can always be changed later), then click the Next button.

  9. In the Networking Options window, select the TCP/IP Networking and Strict Mode options, then click the Next button. You may also change the port number to be used for incoming TCP/IP connections and whether the specified port number should be added to the Windows Firewall configuration. Typically, I will keep the port number at the default value (3306) but enable the Add fiewall exception for this port option.

  10. In the Default Character Set window, leave the Standard Character Set option enabled, then click the Next button.

  11. In the Windows Options window, make sure that the Include Bin Directory in Widows PATH option is selected, then click the Next button.

  12. In the Securiy Options window, select the Modify Security Settings option, and enter the root access password. I like to enable root access from remote machines but recommend you do not select the Create An Anonymous Account option. Click the Next button.

  13. The Ready to execute window will appear, click the Execute button.

  14. If the service does not start up, I’ve had to do this on 64-bit systems:

  15. http://forums.innodb.com/read.php?3,315,339#msg-339

  16. And this:

  17. http://forums.mysql.com/read.php?22,144428,241055#msg-241055

  18. Set the root account passwords as follows:

      1. shell> mysql -u root

      2. mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new password');

      3. mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('new password');

  19. Create any necessary user accounts:

• • 1. mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'password';

    2. mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;

    3. mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'password';

    4. mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' WITH GRANT OPTION;

References

1. Wikipedia

   1. Free community edition at mysql.com.