MYSQL -node-net database design

MySQL is a fast, easy-to-use RDBMS (Relational Database Management System) used for databases on many Web sites. Speed was the developers' main focus from the beginning. In the interest of speed, they made the decision to offer fewer features than their major competitors (for instance, Oracle and Sybase). However, even though MySQL is less full featured than its commercial competitors, it has all the features needed by the large majority of database developers. It's easier to install and use than its commercial competitors, and the difference in price is strongly in MySQL's favor.

MySQL is developed, marketed, and supported by MySQL AB, which is a Swedish company. The company licenses it two ways:

Open source software: MySQL is available via the GNU GPL (General Public License) for no charge. Anyone who can meet the requirements of the GPL can use the software for free. If you're using MySQL as a database on a Web site, you can use MySQL for free, even if you're making money with your Web site.

Commercial license: MySQL is available with a commercial license for those who prefer it to the GPL. If a developer wants to use MySQL as part of a new software product and wants to sell the new product, rather than release it under the GPL, the developer needs to purchase a commercial license. The fee is very reasonable.

Finding technical support for MySQL is not a problem. You can join one of several e-mail discussion lists offered on the MySQL Web site. You can even search the e-mail list archives, which contain a large knowledge base of MySQL questions and answers. If you're more comfortable getting commercial support, MySQL AB offers technical support contracts — five support levels, ranging from direct e-mail support to phone support, at five price levels.

Advantages of MySQL

MySQL is a popular database with Web developers. Its speed and small size make it ideal for a Web site. Add to that the fact that it's open source, which means free, and you have the foundation of its popularity. Here is a rundown of some of its advantages:

It's fast. The main goal of the folks who developed MySQL was speed. Consequently, the software was designed from the beginning with speed in mind.

It's inexpensive. MySQL is free under the open source GPL license, and the fee for a commercial license is very reasonable.

It's easy to use. You can build and interact with a MySQL database by using a few simple statements in the SQL language, which is the standard language for communicating with RDBMSs.

It can run on many operating systems. MySQL runs on a wide variety of operating systems — Windows, Linux, Mac OS, most varieties of UNIX (including Solaris, AIX, and DEC UNIX), FreeBSD, OS/2, Irix, and others.

Technical support is widely available. A large base of users provides free support via mailing lists. The MySQL developers also participate in the e-mail lists. You can also purchase technical support from MySQL AB for a very small fee.

It's secure. MySQL's flexible system of authorization allows some or all database privileges (for example, the privilege to create a database or delete data) to specific users or groups of users. Passwords are encrypted.

It supports large databases. MySQL handles databases up to 50 million rows or more. The default file size limit for a table is 4GB,but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).

It's customizable. The open source GPL license allows programmers to modify the MySQL software to fit their own specific environments.

How MySQL works

The MySQL software consists of the MySQL server, several utility programs that assist in the administration of MySQL databases, and some supporting software that the MySQL server needs (but you don't need to know about). The heart of the system is the MySQL server.

The MySQL server is the manager of the database system. It handles all your database instructions. For instance, if you want to create a new database, you send a message to the MySQL server that says "create a new database and call it newdata." The MySQL server then creates a subdirectory in its data directory, names the new subdirectory newdata,and puts the necessary files with the required format into the newdata subdirectory. In the same manner, to add data to that database, you send a message to the MySQL server, giving it the data and telling it where you want the data to be added.

Before you can pass instructions to the MySQL server, it must be running and waiting for requests. The MySQL server is usually set up so that it starts when the computer starts and continues running all the time. This is the usual setup for a Web site. However, it's not necessary to set it up to start when the computer starts. If you need to, you can start it manually whenever you want to access a database. When it's running, the MySQL server listens continuously for messages that are directed to it.

Communicating with the MySQL server

All your interaction with the database is done by passing messages to the MySQL server. You can send messages to the MySQL server several ways, but one of the easiest ways to send messages is by using PHP. The PHP software has specific statements that you use to send instructions to the MySQL server.

The MySQL server must be able to understand the instructions that you send it. You communicate by using SQL (Structured Query Language), which is a standard language understood by many RDBMSs. The MySQL server understands SQL. PHP doesn't understand SQL, but it doesn't need to: PHP just establishes a connection with the MySQL server and sends the SQL message over the connection. The MySQL server interprets the SQL message and follows the instructions. The MySQL server sends a return message, stating its status and what it did (or reporting an error if it was unable to understand or follow the instructions).