MySQL on Windows

MySQL Installation

MySQL has a number of products these days, including a the 5.1 and 5.5 branches of its community-based server. This document describes a recent 5.1 version. The package you'll need is the MySQL installation file (from the course website). This software is available (in other versions) is available from the MySQL site (although it's sometimes not obvious how to get it): Start by double-clicking the ".msi" file. Make these choices:

• Setup Type: the default is Typical, which will be sufficient for our purposes.
• Several other panels follows which you can go through, and then
• See Wizard Completed with these options, then click Finish.

  Configure the MySQL server now Configure now. You can reconfigure it through a program menu item later as well.

  Register the MySQL Server now Uncheck it now. If you want, you can do it later.

Here are the configuration steps:

1. Select Configuration Type: take the default, Detailed Configuration. It's a good idea just to see the choices that you're making even if you take the defaults.
2. Server Type: probably the default, Developer Machine.
3. Database Usage: probably the default, Multifunctional Database.
4. InnoDB Tablespace Settings: Take the defaults.
5. Concurrent Connections Settings: Take the default, Decision Support (DSS/OLAP)
6. Networking Options: Keep the defaults, Enable TCP/IP Networking and Enable Strict Mode.
7. Default Character Set: probably the default, Standard Character Set.
8. Windows options: want both

   Install As Windows Service

         and

   Include Bin Directory in Windows PATH

   The default service name is simply MySQL. If you had a previous installation of MySQL, you may need to choose a different name from the list, such as MySQL5.
9. Security Options: Uncheck this (to not modify the empty root password)

   Modify Security Settings

10. Ready To Execute: Go for it! Press the Execute button.
    If the "Start Service" fails, go back to the Windows Options panel and select a different name for the Windows service. If the Apply security settings fails, it may be that you already have a password.

The installation of binaries, configuration files, etc., is the folder:

\Program Files\Mysql\MySQL Server 5.5

The actual databases reside in a subfolder named data which is typically in an "Application Data" folder , like this:

\Documents and Settings\All Users\Application Data\MySQL Server 5.5\data

The Windows Programs menu contains the 3 items:

• MySQL command-line client: (accessses the command-line mysql as administrative)
• MySQL manual
• MySQL Server Instance Config Wizard (the majority of the steps we just ran)

After the installation MySQL will probably start up automatically as a background service. Look at: Look for MySQL in the list of services, the startup type should be "automatic" and it should be "started" after installation.

Command line management of MySQL

The binaries in the bin subfolder of the installation folder. Hopefully MySQL has already installed this folder in your PATH environment variable. If not, add it through:

The MySQL shell client

Assuming that the MySQL installation directory's bin subdiretory in your PATH, you can use the mysql executable to connect to and deliver SQL commands to your MySQL database via a command-line SQL interpreter.

The MySQL adminstrator's user name is "root". The MySQL DBMS contains an adminstrative database named mysql in which all access information is stored. The root user has global priviledges to do any modifications or additions.

We only ever want to use the administrative database, mysql, for it's intended purpose, to control access to other databases. Furthermore, we usually want to avoid accessing the MySQL DBMS as root user because there is always the remote possibility that we inadvertently alter the tables in the mysql database and thereby foul up the DBMS — not every author shares my reticence of being MySQL root user.

To run some basic tests it's useful to have an unpriviledged, password-less, "guest" user which can access a test database. On other systems, the MySQL initialization may create the test database which can be accessed by any MySQL user. An anonymous user may also often created. The recent Ubuntu versions do none of these creations.

The following sequence of commands are meant to create some basic setup features and show the effects. There are a number of repeated commands which can easily be accessed by using the "up arrow" key.

$ mysql -u root
mysql> show databases;
mysql> create database test;
mysql> show databases;
mysql> select user,host,password from mysql.user;
mysql> create user guest@localhost;
mysql> select user,host,password from mysql.user;
mysql> select user,host from mysql.db;
mysql> grant all on test.* to guest@localhost;
mysql> select user,host from mysql.db;
mysql> quit

Test the effectiveness by accessing the test database as the guest user:

$ mysql -u guest test

If for some reason this doesn't work, try forcing a reload of the administrative database:

$ mysqladmin -u root reload

Compare the differences in priviledges between the root and guest users (the \G statement terminator is used to field information in list form instead of tabular form):

$ mysql -u root mysql
mysql> select * from user where user='root' and host='localhost' \G
mysql> select * from user where user='guest' and host='localhost' \G

SQL syntax learning examples

Here are some examples you can use to help learn basic SQL commands. The test database accessible by the guest user without password is assumed. Start a command shell and, from the command-line, execute:

$ mysql -u guest               
mysql> use test;
mysql> create table things (thing varchar(10), qty int);
mysql> show tables;
mysql> describe things;
mysql> insert into things values ( 'book', 10 );
mysql> insert into things values ( 'pencil', 4 ), ( 'book', 5 );
mysql> select * from things;
mysql> delete from things where thing='book';
mysql> select * from things;
mysql> insert into things values  ('table', 2), ('chair',12);
mysql> select * from things;
mysql> update things set qty=qty-1 where qty>10;
mysql> select * from things;
mysql> update things set qty=qty+1 where thing='table';
mysql> select * from things;
mysql> drop table things;
mysql> quit

Using passwords with mysql client

If a user access is password-protected, the mysql client requires the usage of the -p option to provide the password. This can be done in one of two ways:

1. The simple, unadorned -p option, with prompt:

   $ mysql -p  -u ....
   Enter password: MY-PASSWORD
   

2. The -p option with password appended (useful for testing, but not a good idea in general):

   $ mysql -pMY-PASSWORD  -u ....
   

GUI Tools

Navicat is a very good GUI tool for manipulating databases. Unfortunately, the "Lite" version, free for non-commercial usage, is no longer supported by the company. A frozen-in-time (c. 2010) version can be obtained here: Install by double-clicking and following the installation wizard.

Start it up Create a root connection. Press the Connection button.

1. Select MySQL from the choices.
2. Set the Connection Name to root. Everything else should be in place already.
3. Click the Test Connection button to double-check.
4. Make sure the Save Password checkbox is checked.

Double-click on the root connection on the left-hand side to open it and see the databases (mysql and, perhaps, test).

Clean up the mysql database user table

MySQL installations usual create more user/password entries than are necessary, or even a good idea. You can clean up the initial user database with the mysql shell client as follows:

$ mysql -u root
mysql> delete from user where host != 'localhost' or user = '';

Open the mysql database, find the user table and delete all of the following records

• those for which the host field is not explicitly "localhost"
• those with empty user field

Discussion points

Creating a MySQL users with a password

If you want to create a user some_user with the password some_password, the replacement command would be this:

mysql> create user some_user@localhost identified by 'some password';

Establishing priviledges for a mysql user

We saw above how the mysql command-line interpreter can grant priviledges by which users can access the databases. Here are some other examples:

1. To create a new priviledged user, priv@host (host the desired entry host):

   $ mysql -u root
   mysql> grant all on *.* to priv@host;
   

2. To give a user restricted@host "read only" priviledges on somedb, we replace the "all" in grant by the desired restricted access:

   $ mysql -u root
   mysql> grant select on somedb.* to restricted@host;
   

MySQL Command-line administration

MySQL software provides a number of useful commands to manipulate its databases, including:

• mysqladmin: basic administration commands
• mysqldump: dump the contents of table(s) in a database
• mysqlshow: show table/fields in a database
• mysql command line interpreter for entering SQL commands

You can see the entire set of choices from the shell using tab completion by doing:

$ mysql[TAB][TAB]

For example, try these commands:

$ mysqlshow -u root
$ mysqlshow -u root mysql
$ mysqlshow -u root mysql user
$ mysqldump -u root mysql user

To ensure that changes made to the mysql database are not picked up, do one of this::

$ mysqladmin -u root reload

There's also the "refresh" command which has a somewhat different outcome.

Backup and reload

The mysql commands provide an excellent scheme by which a database can be "backed up" and then "reloaded". Do the backup like this:

$ mysqldump -u root somedb > somedb.sql

The somedb.sql file contains the data in all the tables plus the commands needed to recreate these tables. A restoration from an non-existent database would be done like this:

$ mysql -u root 
mysql> create somedb
mysql> use somedb
mysql> source somedb.sql

MySQL access principles

MySQL is a network-oriented DBMS. Client programs may reside on different hosts than the server. The access rights of MySQL client has to a MySQL DBMS database is determined by three factors:

• the user specified by the client
• the host on which the client operates
• the database that the client is trying to access

The mysql database consults three tables to determine access rights:

1. user table: When the client on host connects to the MySQL server, the pair

   (user, host)
   

   is matched against the (user,host) values in the rows of the user table. If no such user or host exists, an empty is used to match. If the password field is non-empty the client must provide the password. The client obtains global priviledges from the remaining fields in the record.
2. host table: When a client on host attempts to use a specific database, the pair:

   (host, database)
   

   is used to match against the entries in the host table, and thereby augment the priviledges available to the client when accessing from this host.
3. db table: Each database, with user information is listed in the db table.

   (user, database)
   

   is used to match against the entries in the db table, and therefore further augment the priviledges available to the client as this particular user.

Of the three tables, The db table is the most common place where priviledges are assigned for non-root users since it is the most specific to the database.

A few tests exhibit some of the ideas discussed here:

$ mysql -u root mysql
mysql> describe user;
mysql> select user,host,password from user;
mysql> describe host;
mysql> select host,db from host;
mysql> describe db;
mysql> select user,db from db;
mysql> select * from db where db='test' \G          (mostly 'Y')

The root user has all global priviledges and the anonymous user has none. Nevertheless, the anonymous user gains priviledges for the test database by matching an entry in the db table.

---

© Robert M. Kline