MySQL on Windows

Should I use this instead of XAMPP?

The answer is probably no. XAMPP is a much simpler, fully integrated package which is a fit for the needs of this course. You can, however, substitute actual MySQL for the MariaDB used in XAMPP and use the remaining Apache/Php part of XAMPP, or, make separate installations of those components.

MySQL Installation

This document describes the installation of a recent MySQL 5.7 version. This software (in other versions) is available from the MySQL site:
MySQL home: http://www.mysql.com
Downloads: http://www.mysql.com/downloads/mysql
The package you'll need is the MySQL installation file (choose the larger one, not the smaller "-web-" version):
mysql-installer-community-5.7.17.0.msi

Visual C++ 2013 Runtime

Having Visual C++ 2013 Runtime installed will install for you, in addition to MySQL, the useful MySQL Workbench software plus other features. Visual C++ 2013 Runtime may installed from here:
http://www.microsoft.com/en-us/download/details.aspx?id=40784.
Look for the file (assuming 64-bit), download and install it:
vc_redist_x64.exe

MySQL Installation

Start by double-clicking to execute the ".msi" file. With VC Runtime in place, the features that it cannot install are irrelevant to our needs. The "Connector/NET" part of the installation most likely requires a .NET installation on your system. We, however, will not need it.

Take all the defaults during installation. During the product configuration section you need to supply a MySQL root password (which can be changed later). Here are the installation steps:
  1. Confirm the License Agreement.
  2. Setup Type: Developer Default
  3. Requirements:
  4. Installation: Execute. When complete: Next.
  5. Product Configuration: Next.
  6. Type and Networking: Next.
  7. Accounts and Roles. Here is where you have to enter the root password you've chosen, which we'll assume is
    foobar
    
  8. Windows Service: The installs MySQL as a windows service. You can always turn if off temporarily or permanently. Hit Next.
  9. Plugins and Extensions: Just ignore this. Hit Next.
  10. Apply Server Configuration: Hit Execute, then Finish.
  11. Product Configuration: Hit Next
  12. Connect to Server: Hit Check, then Next.
  13. Apply Server Configuration: Hit Execute, then Finish.
  14. Product Configuration: Hit Next.
  15. Finish. Finally!

MySQL Client Executables

The MySQL Client executables are located in the directory
C:\Program Files\MySQL\MySQL Server 5.7\bin
The most important one is the MySQL client program, mysql, which makes this the command-line access you'd have to key in to a shell:
> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe"
Using this is unacceptable. Here are two ways to make this access simply be "mysql":
  1. The best solution is to put the directory into your PATH. Start from Control Panel ⇾ System and Security and access:
    System ⇾ Advanced System Settings ⇾ Advanced ⇾ Environment Variables
    Find the Path variable in the System Variables. Select it and click Edit. It's best to prepend the new Path component with this addition:
    C:\Program Files\MySQL\MySQL Server 5.7\bin;
    
    The Home button on a Microsoft keyboard is a good way to get to the beginning of the Path expression. Alternatively, you can append the new Path component as:
    ;C:\Program Files\MySQL\MySQL Server 5.7\bin
    
    Then "OK" your way out
  2. Another way is to make a convenience batch executable in the directory where your shell opens up into (it should be your home directory). Go to your home directory C:\Users\YOUR_LOGIN (or wherever your shell opens into) and create the file
    mysql.bat
    
    Make sure file extensions are shown. Create an empty text tile (New Text Document.txt) and rename the whole file to mysql.bat (Windows will complain!). Make the contents be:
    @echo off
    "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" %*
    
In either case, start up a new command shell, and test this usage which should get you in as root (assuming "foobar" is the password you gave).
> mysql -u root -pfoobar

Password-less access

Create the following file (assuming, again that the MySQL root password is "foobar"):

c:\my.cnf
[client]
user=root
password='foobar'
With this in place, you get password-less MySQL root access from a command shell with just this:
> mysql
Alternative locations and names of this initialization file can be discovered in the online help:
> mysql --help

Change root password

Suppose I want to change the root password to "Foo.Bar". Use this command:
> mysql
mysql> alter user root@localhost identified by 'Foo.Bar';
mysql> quit

Create test database

The database we use for our purposes is test, accessible by the guest user with empty password. You can create this as follows:
> mysql
mysql> create database test;
mysql> create user guest@localhost;
mysql> grant all on test.* to guest@localhost;
mysql> quit
Check password-less entry into the test database as guest user. With an empty password, you cannot provide it on the command line.
> mysql -u guest test -p
Password: ENTER
mysql> quit

Using Cygwin for shell access

If, like me, you prefer the Cygwin shell, you can still get access to your Windows MySQL database via a mysql client. Although the Windows mysql client is is accessible though the Windows PATH, it is not the one to use.

Instead, install the mysql client package in Cygwin, but don't install the mysql server package. Then you will be able to access the Windows MySQL database with an added option like this:
$ mysql --protocol TCP ...
To simplify this usage, create an alias by adding this line to your ~/.bashrc file:
alias mysql='mysql --protocol TCP'
Open a new Cygwin shell and my can now use the mysql client as you would on a Windows command shell, e.g.:
$ mysql -u root


© Robert M. Kline