35.1. Introduction to MySQL

MySQL is a free, easy-to-use database server that supports multiple databases and tables, and allows clients to query them with SQL. It is most useful for programmers writing applications that need to use a simple database to store information. Popular languages like Perl, C, Java, and PHP all have APIs for accessing a MySQL database.

A MySQL server can host multiple databases and each database can contain multiple tables. A table in turn contains fields, each of which has a type and size. Tables contain records, each of which usually contains information about some object, such as a person, product, or purchase. Fields can be thought of as the columns in a table, and the actual records of data as the rows.

SQL (which stands for Structured Query Language) is a language for extracting data from—or updating data in—a database. Almost all databases use SQL and its syntax is generally the same across all the different database packages such as Oracle, PostgreSQL, and MySQL. This chapter does not cover the SQL syntax, however, as it is too complex. There are plenty of other good books devoted entirely to it.

Compared to other databases, MySQL lacks some features. It does not support transactions for most table types, which means that every SQL command is executed immediately and cannot be undone. It cannot execute certain complex SQL commands, particularly those that involve nested queries. Other databases like PostgreSQL and Oracle support transactions and more complex SQL and deal better with extremely large tables. Chapter 36 explains how to use PostgreSQL, but Oracle (being an expensive commercial product) is not covered in this book.

Packages for MySQL come with almost all Linux distributions and it can be compiled on most UNIX variants. Its behavior is identical on all systems, with the exception that some versions of UNIX support larger table sizes due to their filesystems' support for larger files. On a standard ext2 or ext3 Linux filesystem, a table cannot be bigger than 4 GB. The Webmin MySQL module, however, will behave exactly the same on all operating systems.

MySQL is divided into two parts—the server that manages the actual files containing tables and records, and client programs that communicate with a server. The standard mysql client program allows users to execute SQL commands and display their results, while the mysqladmin program is for performing basic administrative tasks and the mysqldump program is for making backups. Other applications that query the database (such as Webmin itself) are also clients.

The data files in which tables are actually stored are located in subdirectories under a directory like /var/lib/mysql or /usr/local/mysql/var. These files are never read or written to by any programs except the MySQL server and should not even be copied for backup purposes unless the server process has been shut down.