Installing and Using MySQL

You will learn in this tutorial how to install the MySQL data base server and client in Windows 2000 and XP. There is another version to install MySQL in Linux. You will also see the basics of using SQL to create a data base, insert and update data, perform queries, and drop (delete) a data base. Most of the commands used in this tutorial are part of the standard SQL specifications, but you may use the MySQL specific commands if you wish. Another tutorial is available from the people who developed MySQL. You should consider MySQL as a serious contender in the DB arena, considering that Yahoo and NASA, among others, use it and both the client and server are free for UNIX/Linux and Windows NT/2000 for non-comercial uses (see MySQL products and services for commercial use).

Downloading

I suggest you download the new stable version of MySQL: MySQL-4.0 and save it to the desktop or any temporary directory.

Installing MySQL in Windows

The basic reference for installing MySQL in Windows is part of the MySQL documentation: item 2.2.1.

You will need a program to unzip and install the MySQL file you downloaded (something like mysql-4.0.21-win.zip). You can find a freeware to unzip the file at Nonags. You should be able to double-click on the zip file and the program should unzip and install MySQL in c:\mysql if you do not change the defaults.

When you finish installing mysql it is not yet running as a service and you will need to start setting it up, as explained below.

Setup of the MySQL server

Log as administrator or with an account with administrator privileges.

First I strongly suggest you add the MySQL binary directory to the Windows Environment path as follows:

Rightclick on MyComputer and select Properties, or System in Control Panel and select Open. You will see this dialog.
Select the Advanced tab, then select Environment Variables and you will see a dialog like this one.
Scroll down in the System variables area and locate the Path variable, select it and click on Edit. Add C:\mysql\bin; to the end of the Path string, as shown in this image.
Since this is Windows: reboot to make the new path effective.

Note

Once mysql is installed you need to make it run as a service in Windows 2000 and XP.The basic reference for this step is item 2.2.1.7. Open a command prompt (DOS Window) and type the following:

mysqld --install

You will receive a message back: " Service successfully installed."

Open Computer Manager, Select Services, then select MySQL and double-click to edit the startup of the MySQL service. I recommend that during testing, setup and customization you set the MySQL service to start manually, rather than automatically as in normal production use, as shown in this image. Everything is now installed and the basic setup done.

Securing your MySQL server

When the MySQL server is installed it does not have a password for the root user, so you can log typing at a shell prompt: mysql -u root mysql (you are asking to connect as user root and use the data base mysql, the basic DB installed by the server). You are placed at the mysql> prompt. The first thing you should do is to change the password like we did in the example below:

C:\> mysql -u root mysql

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.21-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set password for root=Password('pwserver');
Query OK, 0 rows affected (0.52 sec)

mysql> quit
Bye

Once I changed the root password I quit to test the new password. I logged in with the new password as shown below:

C:\> mysql -u root -p
Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.37

Type 'help;' or '\h' for help. Type '\c' to clear the buffer
mysql>

The new password worked and now you can issue commands. MySQL comes with two blank (anonymous) user accounts and they should be deleted for security reasons, as follows:

First find out what databases where created by MySQL installation:

mysql> show databases;

+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+

2 rows in set (0.02 sec)

Then check what and if anonymous, blank, accounts were created:

mysql> SELECT Host, User FROM mysql.user;

+-----------+------+
| Host      | User |
+-----------+------+
| build     |      |
| build     | root |
| localhost |      |
| localhost | root |
+-----------+------+

4 rows in set (0.05 sec)

Since two blank accounts exist we should delete them:

mysql> DELETE FROM mysql.user WHERE User = '';
Query OK, 2 rows affected (0.01 sec)

mysql>FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)

Finally test if you really deleted the blank accounts:

mysql> SELECT Host, User FROM mysql.user;

+-----------+------+
| Host      | User |
+-----------+------+
| build     | root |
| localhost | root |
+-----------+------+
2 rows in set (0.01 sec)

mysql> quit
Bye

Server Administration

When you just type mysqladmin (see below) you obtain a list of all commands available for server administration through this command.

C:\> mysqladmin  

Administration program for the mysqld daemon. Usage: mysqladmin [OPTIONS] command command....

  -#, --debug=...       Output debug log. Often this is 'd:t:o,filename`
  -f, --force           Don't ask for confirmation on drop database; with
                        multiple commands, continue even if an error occurs
  -?, --help            Display this help and exit
  --character-sets-dir=...
                        Set the character set directory
  -C, --compress        Use compression in server/client protocol
  -h, --host=#          Connect to host
  -p, --password[=...]  Password to use when connecting to server
                        If password is not given it's asked from the tty
  -P  --port=...        Port number to use for connection
  -i, --sleep=sec       Execute commands again and again with a sleep between
  -r, --relative        Show difference between current and previous values
                        when used with -i. Currently works only with
                        extended-status
  -E, --vertical        Print output vertically. Is similar to --relative,
                        but prints output vertically.
  -s, --silent          Silently exit if one can't connect to server
  -S, --socket=...      Socket file to use for connection
  -u, --user=#          User for login if not current user
  -v, --verbose         Write more information
  -V, --version         Output version information and exit
  -w, --wait[=retries]  Wait and retry if connection is down

Default options are read from the following files in the given order:
/etc/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqladmin client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit
--no-defaults           Don't read default options from any options file
--defaults-file=#       Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read

Possible variables for option --set-variable (-O) are:
connect_timeout       current value: 0
shutdown_timeout      current value: 3600

Where command is a one or more of: (Commands may be shortened)
  create databasename   Create a new database
  drop databasename     Delete a database and all its tables
  extended-status       Gives an extended status message from the server
  flush-hosts           Flush all cached hosts
  flush-logs            Flush all logs
  flush-status          Clear status variables
  flush-tables          Flush all tables
  flush-threads         Flush the thread cache
  flush-privileges      Reload grant tables (same as reload)
  kill id,id,...        Kill mysql threads
  password new-password Change old password to new-password
  ping                  Check if mysqld is alive
  processlist           Show list of active threads in server
  reload                Reload grant tables
  refresh               Flush all tables and close and open logfiles
  shutdown              Take server down
  status                Gives a short status message from the server
  start-slave           Start slave
  stop-slave            Stop slave
  variables             Prints variables available
  version               Get version info from server

You can run any one of them by typing mysqladmin -u root -p command name. You will be asked for the MySQL root password, and once you type it in, the command will execute.

Stop and start the MySQL server

To properly start and shutdown the server use the Services dialog in Computer Management.

Creating and droping data bases

You create a new database by typing: mysqladmin -u root -p create dbname. For example:
C:\>mysqladmin -u root -p create MYTEST
Enter password: ********
creates a new data base MYTEST, which we will use in other examples.
You drop (delete) a database by typing: mysqladmin -u root -p drop dbname

Display existing data bases


C:\>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 4.0.21-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| mytest   |
| test     |
+----------+
3 rows in set (0.00 sec)

Please note that our recently created MYTEST database is listed as mytest because Windows is not really case sensitive for file names.

Adding users

See URL http://www.mysql.com/doc/A/d/Adding_users.html for more details. The example below creates a new user abento, which can only login from the localhost (to make it to be from any host it would have been written abento@'%' instead of abento@localhost), with the password equal to pwserver (MySQL encrypts this password using a different algorithm than UNIX).

The abento user was GRANTED select,insert,update,delete,create and drop permissions on all data bases (*.*). If we had a table people in the MYTEST data base, and we only wanted abento to have rights in the table people, we would have written: ON MYTEST.people. We tested if the user had been added by loging as the new user abento, and typing a command to see the existing data bases (to test if the permissions were granted).


mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON *.* TO abento@localhost IDENTIFIED BY 'pwserver';
Query OK, 0 rows affected (1.39 sec)

mysql> SELECT Host, User FROM mysql.user;
+-----------+--------+
| Host      | User   |
+-----------+--------+
| build     | root   |
| localhost | abento |
| localhost | root   |
+-----------+--------+
3 rows in set (0.00 sec)
 
mysql> quit
Bye
C:\>mysql -u abento -ppwserver
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 4.0.21-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| mytest   |
| test     |
+----------+
3 rows in set (0.00 sec)

Others

C:\>mysqladmin -u root -p version
Enter password: ********
mysqladmin  Ver 8.40 Distrib 4.0.21, for Win95/Win98 on i32
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          4.0.21-debug
Protocol version        10
Connection              localhost via TCP/IP
TCP port                3306
Uptime:                 1 hour 50 min 9 sec

Threads: 1  Questions: 24  Slow queries: 0  Opens: 14  Flush tables: 1  Open tab
les: 2  Queries per second avg: 0.004  Memory in use: 8344K  Max memory used: 84
00K

To see all threads and processes running use:


C:\>mysqladmin -u root -p processlist
Enter password: ********
+----+------+----------------+----+---------+------+-------+------------------+
| Id | User | Host           | db | Command | Time | State | Info             |
+----+------+----------------+----+---------+------+-------+------------------+
| 15 | root | localhost:1270 |    | Query   | 0    |       | show processlist |
+----+------+----------------+----+---------+------+-------+------------------+

You can explicitly include the password in your login as shown below:

C:\>mysql -u abento -ppwserver
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 4.0.21-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

But this is a security risk. You may use the explicit password value in a script, which you keep in a secure directory, but avoid using it at the shell prompt.

Creating and manipulating tables

Using MySQL to create and manipulate tables. See http://www.mysql.com/doc/R/e/Reference.html for more details. For a review of SQL commands see this gentle tutorial or this more detailed tutorial.

If you recall, in a prior example a user abento was created as follows:


mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON *.* TO abento@localhost IDENTIFIED BY 'pwserver';
Query OK, 0 rows affected (1.39 sec)

Lets see the user abento login, use the MYTEST data base (created previously by root), create a table, and ask for the display of the table structure:


C:\>mysql -u abento -ppwserver
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24 to server version: 4.0.21-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \u MYTEST
Database changed

mysql> CREATE TABLE people (userid INT NOT NULL PRIMARY KEY, name CHAR(25), address CHAR(45));
Query OK, 0 rows affected (0.13 sec)

mysql> desc people;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| userid  | int(11)  |      | PRI | 0       |       |
| name    | char(25) | YES  |     | NULL    |       |
| address | char(45) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

Let us see the user abento try to change the definition of the table he created and fail. Why? Then root logs in and do the change. Why it worked now?


mysql> ALTER TABLE people MODIFY address VARCHAR(100);
ERROR 1044: Access denied for user: 'abento@localhost' to database 'MYTEST'

mysql> quit
Bye

C:\>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25 to server version: 4.0.21-debug
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer
 
mysql> use MYTEST
Database changed
mysql> ALTER TABLE people MODIFY address VARCHAR(100);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc people;

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| userid  | int(11)      |      | PRI | 0       |       |
| name    | varchar(25)  | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
 
mysql>

Of course, the secret was that the user abento was not granted permission to ALTER the table. So although it seemed that the user abento could do whatever he needed with the table, this was not the case. Be very careful how you grant permissions in data bases.

Inserting data into the tables

Once we create table structures (design, define the table) we are ready to add data to the tables. Lets see an example in MySQL

mysql> INSERT INTO people (userid,name,address) VALUES (347,'John Morris','1420 N. Charles St, Baltimore, MD 21201');
Query OK, 1 row affected (0.82 sec)

mysql> INSERT INTO people
 VALUES (458,'Al Bento','1420 N. Charles St, Baltimore, MD 21201');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO people VALUES (569,'Milt Jenkins','1420 N. Charles St, Baltimore, MD 21201');
Query OK, 1 row affected (0.00 sec)

Querying the table


mysql> SELECT * FROM people;
+--------+--------------+-----------------------------------------+
| userid | name         | address                                 |
+--------+--------------+-----------------------------------------+
|    347 | John Morris  | 1420 N. Charles St, Baltimore, MD 21201 |
|    458 | Al Bento     | 1420 N. Charles St, Baltimore, MD 21201 |
|    569 | Milt Jenkins | 1420 N. Charles St, Baltimore, MD 21201 |
+--------+--------------+-----------------------------------------+
3 rows in set (0.01 sec)
 
mysql>  SELECT * FROM people WHERE name = 'Al Bento';
+--------+----------+-----------------------------------------+
| userid | name     | address                                 |
+--------+----------+-----------------------------------------+
|    458 | Al Bento | 1420 N. Charles St, Baltimore, MD 21201 |
+--------+----------+-----------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT userid,name FROM people WHERE address = '1420 N. Charles St, Baltimore, MD 21201';
+--------+--------------+
| userid | name         |
+--------+--------------+
|    347 | John Morris  |
|    458 | Al Bento     |
|    569 | Milt Jenkins |
+--------+--------------+
3 rows in set (0.00 sec)

Updating the tables

Updating is also very straightforward in MySQL:


mysql> UPDATE people SET name = 'Al Bento' WHERE userid = '347';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM people WHERE name = 'Al Bento';
+--------+----------+-----------------------------------------+
| userid | name     | address                                 |
+--------+----------+-----------------------------------------+
|    347 | Al Bento | 1420 N. Charles St, Baltimore, MD 21201 |
|    458 | Al Bento | 1420 N. Charles St, Baltimore, MD 21201 |
+--------+----------+-----------------------------------------+
2 rows in set (0.00 sec)
 
 
mysql> UPDATE people SET name = 'John Morris' WHERE userid = '458';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> SELECT * FROM people;
+--------+--------------+-----------------------------------------+
| userid | name         | address                                 |
+--------+--------------+-----------------------------------------+
|    347 | Al Bento     | 1420 N. Charles St, Baltimore, MD 21201 |
|    458 | John Morris  | 1420 N. Charles St, Baltimore, MD 21201 |
|    569 | Milt Jenkins | 1420 N. Charles St, Baltimore, MD 21201 |
+--------+--------------+-----------------------------------------+
3 rows in set (0.00 sec)

Looking into the user table of MySQL

Do not forget that all control tables of MySQL are MySQL tables! You can see their content and the permissions users were assigned, for example, when you read the table user.

mysql> use mysql
Database changed

mysql> SELECT * FROM user;

+------------------+--------+------------------+-------------+-------------+-------------+--------------------+-----------+-------------+---------------+
| Host        | User      | Password         | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv      | Shutdown_priv |
 Process_priv | File_priv | Grant_priv       | References_priv | Index_priv | Alter_priv |
+------------------+--------+------------------+-------------+-------------+-------------+--------------------+-----------+-------------+---------------+
| localhost   | root      | 1b50d85063ea8a6e | Y           | Y           | Y           | Y           | Y           | Y         | Y                | Y           |
  Y           | Y         | Y                | Y           | Y           | Y           |
| %           | abento    | 1b50d85063ea8a6e | Y           | Y           | Y           | Y           | N           | N         | N                | N           |
  N           | N         | N                | N           | N           | N           |
| localhost   | abento    | 1b50d85063ea8a6e | Y           | Y           | Y           | Y           | Y           | Y         | N                | N           |
  N           | N         | N                | N           | N           | N    |
+------------------+--------+------------------+-------------+-------------+-------------+--------------------+-----------+-------------+---------------+

6 rows in set (0.30 sec)

This page is maintained by Al Bento who can be reached at abento@ubalt.edu. This page was last updated on October 16, 2004. Although we will attempt to keep this information accurate, we can not guarantee the accuracy of the information provided.