Installing MySQL

This chapter describes:

• What is MySQL?
• How to install MySQL on a Windows system.
• How to work with the MySQL server.

What is MySQL?

MySQL - An open source database management system developed by MySQL AB, http://www.mysql.com. MySQL was started with mSQL with fast low-level (ISAM) routines. So MySQL and mSQL are having almost the same application programming interfaces and command line interfaces.

Main features of MySQL (extracted from MySQL manual):

• Works on many different platforms.
• APIs for C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl are available.
• Fully multi-threaded using kernel threads.
• Provides transactional and non-transactional storage engines.
• Uses very fast B-tree disk tables (MyISAM) with index compression.
• Very fast joins using an optimized one-sweep multi-join.
• SQL functions are implemented using a highly optimized class library and should be as fast as possible.
• Support for aliases on tables and columns as required by SQL-92.
• Handles large databases. We use MySQL Server with databases that contain 50 million records. We also know of users that use MySQL Server with 60,000 tables and about 5,000,000,000 rows.
• Clients may connect to the MySQL server using TCP/IP sockets on any platform.
• The Connector/ODBC interface provides MySQL support for client programs that use ODBC (Open-DataBase-Connectivity) connections.
• The Connector/JDBC interface provides MySQL support for Java client programs that use JDBC connections.

Installing MySQL 4.0.18

1. Go to http://dev.mysql.com/downloads and select the latest production release.

2. Go to "Windows downloads" and select "Without installer (unzip in C:\)" entry.

3. Save the downloaded file, mysql-4.0.18-win-noinstall.zip, on your hard disk.

4. Unzip mysql-4.0.18-win-noinstall.zip to "\" and rename "\mysql-4.0.18" to "\mysql".

5. Open a command window and run "\mysql\bin\mysqld" to start MySQL server:

\mysql\bin\mysqld --console
nnnnnn 22:16:53  InnoDB: Started
\local\mysql\bin\mysqld: ready for connections.
Version: '4.0.18-max-debug'  socket: ''  port: 3306

6. Open another command window and run "\mysql\bin\mysqladmin" to administrate MySQL server:

\mysql\bin\mysqladmin ping
mysqld is alive

\mysql\bin\mysqladmin shutdown

If everything works correctly, you should see MySQL server printing notice and shutting down in the first command window.

Creating First Table in MySQL

In the previous section, we learned how to start and shutdown MySQL server. Now let's see how we can use MySQL client interface to create a table and run queries. First start the MySQL server in one command window, then run the start the MySQL client interface in another command window, and run the following MySQL client commands:

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

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

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.06 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table hello (message varchar(80));
Query OK, 0 rows affected (0.58 sec)

mysql> insert into hello (message) values ('Hello world!');
Query OK, 1 row affected (0.38 sec)

mysql> select * from hello;
+--------------+
| message      |
+--------------+
| Hello world! |
+--------------+
1 row in set (0.04 sec)

mysql> drop table hello;
Query OK, 0 rows affected (0.34 sec)

mysql> quit;
Bye

Conclusions:

• MySQL is indeed easy to install and easy to use.
• The MySQL server, mysqld, runs as a daemon and listens on IP port 3306 for client program connections.
• MySQL offers an administrative client program, mysqladmin, to operate the MySQL server.
• MySQL offers a simple client program, mysql, to run SQL statements against the MySQL server.