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:mysqldata into it.

You should now have two folders, D:MySQLdatamysql and D:MySQLdatatest. The original C:mysqldata 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:
Report Advertisement

[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:mysqlbinmysqld.exe. Open a command box (Start > Run > cmd) and enter the following commands:

cd mysqlbin
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 mysqlbin
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 mysqlbin
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”.