How to Install MySQL

In the first of a series of MySQL articles, we will discover how easy it is to install the database system on your development PC.

Why MySQL?

MySQL is undoubtedly the most popular and widely-used open source database:

• it is simple to set up and use
• it is recognised as one of the fastest database engines
• most Linux (and many Windows-based) web hosts offer MySQL
• MySQL is closely integrated with PHP, which makes it an ideal candidate for many web applications.

Why Install MySQL Locally?

Installing MySQL on your development PC allows you to safely create and test a web application without affecting the data or systems on your live website (I will cover installing a web server and PHP in a later articles).

This article describes how to install MySQL on Windows, but versions are available for Mac, Linux, and several other operating systems.

All-in-One packages

There are some excellent all-in-one Windows distributions that contain Apache, PHP, MySQL and other applications in a single installation file, e.g. XAMPP, WampServer and Web.Developer. There is nothing wrong with using these packages, but manually installing MySQL will help you learn more about the system and give you more control.

The MySQL Installation Wizard

An excellent .msi installation wizard is available for MySQL. The wizard creates the my.ini configuration file and installs MySQL as a service. This option is certainly recommended for novice users or perhaps those installing MySQL for the first time.

Manual Installation

Manual installation offers several benefits:

• backing up, reinstalling, or moving databases can be achieved in seconds (see 8 Tips for Surviving PC Failure)
• you have more control over how and when MySQL starts
• you can install MySQL anywhere, such as a portable USB drive (useful for client demonstrations).

Step 1: download MySQL
Download MySQL from dev.mysql.com/downloads/. Follow MySQL Community Server, Windows and download the “Without installer” version.

As always, virus scan the file and check the its MD5 checksum using a tool such as fsum.

Step 2: extract the files
We will install MySQL to C:\mysql, so extract the ZIP to your C: drive and rename the folder from “mysql-x.x.xx-win32″ to “mysql”.

MySQL can be installed anywhere on your system. If you want a lightweight installation, you can remove every sub-folder except for bin, data, scripts and share.

Step 3: move the data folder (optional)
I recommend placing the data folder on another drive or partition to make backups and re-installation easier. For the purposes of this example, we will create a folder called D:\MySQLdata and move the contents of C:\mysql\data into it.

You should now have two folders, D:\MySQLdata\mysql and D:\MySQLdata\test. The original C:\mysql\data folder can be removed.

Step 4: create a configuration file
MySQL provides several configuration methods but, in general, it is easiest to to create a my.ini file in the mysql folder. There are hundreds of options to tweak MySQL to your exact requirements, but the simplest my.ini file is:

[mysqld]
# installation directory
basedir="C:/mysql/"

# data directory
datadir="D:/MySQLdata/"

(Remember to change these folder locations if you have installed MySQL or the data folder elsewhere.)

Step 5: test your installation
The MySQL server is started by running C:\mysql\bin\mysqld.exe. Open a command box (Start > Run > cmd) and enter the following commands:

cd \mysql\bin
mysqld

This will start the MySQL server which listens for requests on localhost port 3306. You can now start the MySQL command line tool and connect to the database. Open another command box and enter:

cd \mysql\bin
mysql -u root

This will show a welcome message and the mysql> prompt. Enter “show databases;” to view a list of the pre-defined databases.

Step 6: change the root password
The MySQL root user is an all-powerful account that can create and destroy databases. If you are on a shared network, it is advisable to change the default (blank) password. From the mysql> prompt, enter:

UPDATE mysql.user SET password=PASSWORD("my-new-password") WHERE User='root';
FLUSH PRIVILEGES;

You will be prompted for the password the next time you start the MySQL command line.

Enter “exit” at the mysql> prompt to stop the command line client. You should now shut down MySQL with the following command:

mysqladmin.exe -u root shutdown

Step 7: Install MySQL as a Windows service
The easiest way to start MySQL is to add it as a Windows service. From a command prompt, enter:

cd \mysql\bin
mysqld --install

Open the Control Panel, Administrative Tools, then Services and double-click MySQL. Set the Startup type to “Automatic” to ensure MySQL starts every time you boot your PC.

Alternatively, set the Startup type to “Manual” and launch MySQL whenever you choose using the command “net start mysql”.

Note that the Windows service can be removed using:

cd \mysql\bin
mysqld --remove

See also:

• MySQL: the Pros and Cons of MyISAM Tables
• MySQL: the Pros and Cons of InnoDB Tables
• How to Use MySQL Foreign Keys for Quicker Database Development
• How to Install Apache
• How to Install PHP

Related posts:

1. How to Install Apache Web Server on Windows Professional web developers need a web server and Apache is...
2. How to Install PHP on Windows In his final installation tutorial, Craig provides a step-by-step guide...
3. How to Administer a Remote MySQL Database using SSH Tunneling Configuring remote MySQL databases with the command line or phpMyAdmin...
4. How to Install PHP 5.3 on Windows PHP 5.3 is the most significant update since version 5.0....
5. MySQL Cross-Platform Table Naming Craig highlights the biggest potential pitfall of porting your MySQL-based...

This entry was posted on Tuesday, March 24th, 2009 at 4:38 pm, contains 840 words, 8 images, and is filed under Web Tech. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed. The views and opinions in this blog post are those of its author.