What Is MySQL?

MySQL is the world's most popular open source relational database management system (RDBMS). It has every quality to be an RDBMS for business but, unlike its rivals, can be used for free under the GNU General Public License (GPL).

MySQL—pronounced “my-ess-queue-ell”—takes its name from SQL, the language for database querying developed by IBM. The “My” is said to come from the name of the daughter, My, of MySQL's Finnish designer, Michael “Monty” Widenius.

MySQL has a history stretching back over 10 years. It was conceived when Monty and TcX, the Swedish consultancy for which he worked, were looking for a database system to solve particular business problems for TCX's customers. They tried using a similar database product called mSQL, but found it couldn't quite do the job. So they created a new system and called it MySQL.

MySQL had some similarities with mSQL and retains some of them today, though these are mostly for compatibility. Apart from that, MySQL has a heritage all its own.

Although MySQL was written by the developers of TcX to provide solutions for their customers, it was made available as open source software. It evolved rapidly and gained a reputation among the developer community for its robustness, speed, and ease of use. Because MySQL was open source, developers enjoyed the ease with which they could apply it and adapt it to all sorts of database uses, and without having to pay for it.

More recently, things have stepped up a gear. The company MySQL AB was formed (AB being the Swedish form of “incorporated” or “limited company”). Privately owned, MySQL AB owns the source code and the MySQL trademark. In 2001, the company received venture capital backing, appointed a CEO, and watched its employees grow in numbers, in several countries. MySQL as a product emerged from the relative obscurity of the open source world and stepped into the broader commercial limelight.

The product now boasts an estimated four million installations worldwide, with around 27,000 downloads from the MySQL Web site per day. MySQL is the de facto open source database leader and is quickly becoming the database behind many high-volume, business-critical applications. Major corporations such as Yahoo!, Motorola, NASA, Silicon Graphics, Hewlett Packard, Xerox, and Cisco rely on it because of its high speed and high reliability.

MySQL as a Relational Database Management System

What do we mean by relational database management system, or even database for that matter?

A database is a suite of structured files on a computer that are organized in such a way that information can be accessed in a structured manner. It's difficult to go to the bank, shop at a store, or surf the World Wide Web without encountering a database. Databases are efficient storage houses of information that can make information available in just about any way imaginable.

The particular kind of database we're interested in here is a relational database, though there are other kinds. The relational database model was developed by E. F. Codd in the early 1970s. Although databases at the time were typically just hierarchical file systems—highly inefficient and difficult to manage—the relational model changed the way information was structured.

Relational databases comprise one or more tables, which themselves are two-dimensional matrices consisting of rows and columns. A row of data is often called a record, and where that row intersects with a column, there is a field.

What makes a database relational is the capability to cross-reference between data in one table and data in another. Access to the tables and their data, whether to a single table or several, is made possible by queries. Queries are executed by the relational database management system.

A database management system is a different thing from a database. It's the system that makes a database appear out of what's essentially just a bunch of files on a computer disk. It creates a “window” through which you can look, making those files look like structured information. It runs queries on the tables, putting data in and getting data out.

But more than that, such a system manages a database, or indeed several databases. This means more than just processing queries: It implies a system doing many more tasks, such as controlling access to databases, performing administration tasks, logging activity, and managing runtime resources such as memory and disk usage.

Fundamental Features of MySQL

MySQL has all the features of a relational database management system. Let's look a little closer at what it can do.

MySQL is a database server (though it does come with a number of simple client programs). It is typically used in thin client environments. In other words, it is used in client-server systems where the bulk of the processing and storage takes place on the server, and the client is little more than a dumb terminal. Although dumb terminals were the norm in the 1970s and 80s, they drifted out of popularity with the advent of the personal computer. However, they are again in vogue, with Web browsers such as Netscape and Microsoft Internet Explorer being the ubiquitous faces of thin clients at the beginning of the 21st century.

Importantly, MySQL performs multithreaded processing, which means that it allows multiple clients to connect to it and run queries simultaneously. This makes it extremely fast and well suited to client-server environments such as Web sites and other environments that process numerous transactions for multiple users.

MySQL features a user permissions system, with which it can control users' access to any number of its databases. So sophisticated is the system that few competing RDBMSs can match its levels of security and the granularity to which user permissions can be set. Recent developments have also added the capability for MySQL to handle encrypted connections, and SSL (Secure Sockets Layer) and X509 certification can be used to protect data in transit as it passes between server and client.

MySQL competes with other products such as Oracle, Sybase, DB2, and Microsoft SQL Server. They are all relational database servers. However, products such as Microsoft Access and Filemaker are quite different. They are still databases but concentrate their processing on the client front-end; although they come in shareable versions, they lack the management systems that are a key part of a true RDBMS, such as the control of user access and multithreaded processing capability.

The Benefit of Being Open Source

Although MySQL is developed by a commercial operation, it is freely downloadable and usable as open source software.

Open source doesn't simply mean “free”—we'll look at MySQL from a commercial perspective in a moment—there's more to the philosophy than that. By being open source, MySQL has an inherent degree of transparency and adaptability not present in closed-source products.

A few people—particularly among those who are used to buying software from a large commercial vendor—think that open source products let hackers find the holes in the system and exploit them. They assume that because of this, open source products are less secure.

The first part of this understanding is absolutely true, but the assumption is wrong. It's true to say that security holes can become apparent far earlier in the life of a product when it's open source; this is because its source code can be read and analyzed, and vulnerabilities easily spotted. But precisely because of this transparency, any weakness quickly gets fed back to the developers who can react to eliminate the problem.

The Web is busy with people installing, applying, and testing open source products, and those same people feed their questions and experiences into public newsgroups and discussion forums. Any weakness in an open source product quickly becomes known—and when it does, it gets talked about in a big way, and the developers notice. Some users even fix the problem themselves and in turn make the code available for integration with the original product.

There's just no chance of covering up a minor problem, let alone a security flaw! The developer of the product has no choice but to incorporate a fix as quickly as possible. Contrast this with products where the source code—sometimes even the specifications of file format or functionality—is unavailable to outsiders. Only a small band of in-house developers have access to this information, yet they would be in the best position to identify problems. Bugs can therefore take longer to come to light and be fixed, and when a security flaw affects many users, the effect can be catastrophic.

A Solution for Business

Business problems usually require solutions that are upgradable to cope with changing needs. A rip-and-replace product is largely unsatisfactory, even if the product that replaces the obsolete system is from the same vendor. MySQL responds to these demands by having adaptability in many ways.

MySQL is highly scalable. It can be run on a single-user desktop machine, or it can be run on a server for access by millions of clients.

And it doesn't stop there: Newer versions of MySQL support replication. This means that a whole suite of servers can run in concert, with client requests being shared among them and the servers working together to feed each other data. By doing this, MySQL satisfies the needs of high-volume transaction processing environments, such as ISPs, banks, and retailers.

Replication also provides a further benefit: high availability for mission-critical applications. Distributing user queries across multiple servers not only balances server load, but also builds more robustness into the system. With geographical dispersal of servers connected via the Internet, it is possible to build a system that is virtually unbreakable.

MySQL can also adapt to unusual and specialist demands. Any developer can add to MySQL's function library without difficulty; this extendibility is a key part of the MySQL philosophy. MySQL's code can also be changed at a more fundamental level to suit particular requirements. Contrast this with closed-source products, where such adaptability is out of the question, and only the vendor can add a new core feature to its software.

MySQL can be run on just about any platform that exists. It runs on all types of Unix, including Solaris, IBM-AIX, Irix, and HP-UX to name but a few; on all flavors of Linux and BSD; on Windows 95, 98, Me, NT, 2000, and XP; on Mac OS X; and on Amiga. You can therefore run it on just about anything from a simple Windows desktop PC to multiprocessor server hardware running Unix or Linux.

What if there's a platform you want to use that's not listed here, such as an old operating system, a mobile device, or an embedded system? Quite possibly MySQL will run: The answer is, try it! Where a precompiled MySQL binary isn't available, you can simply compile the source code for your platform.

Application-level interfaces to MySQL are many and varied. Connecting from a Unix-type system is easy, with languages such as C and PHP having connectivity built-in, and Perl and Java needing only the addition of an API. If your application can't connect directly, you can use ODBC to connect from products such as Filemaker and Access, and from programs made using development tools such as Visual Basic.

What about interoperability? If you start to use MySQL, can you still use other databases alongside it? MySQL doesn't force you to be MySQL-only. You can run MySQL alongside databases from other vendors, and this is true both in terms of the applications you run and data storage.

MySQL uses an SQL language that complies with the ANSI-92 standard for SQL (with a few minor omissions and some small extensions), so you should be able to pick up your application code and switch its SQL queries from your old database to MySQL with minimal changes.

But changing to MySQL is not a one-way street: You can pick up code written for MySQL and run it on something else, again with minimal change. Although MySQL uses its own formats for data storage, you can import and export with ease, with MySQL recognizing a wide range of data formats.

Are there things missing from MySQL? Yes, a few features of other databases are missing at the time of writing (the version described in this book is 4.0.2).

Most notably, subselects (the capability to run a SELECT query on the resultset of another SELECT query, in a single SQL statement) are not yet available, though probably will be by the time you read this.

Views, stored procedures, and triggers are also not available at the time of writing, but features such as these are specialist requirements rather than ordinary database needs. There's hardly an application that cannot be written without them, and they are generally demanded by large-system people who want to port a system to MySQL without any change in their application code.

MySQL as a realistic business offering is developing fast. Transaction-safe tables, perceived as an important omission for many business systems, have been incorporated into MySQL using the InnoDB table type, and replication is now fully implemented for the demands of load-balanced or high-availability systems, with even more functionality due in the forthcoming releases.

Another new dimension that has been opened up recently is the capability for MySQL to be compiled into another piece of software; it can be used as an “embedded server” and therefore lets you compile an entire MySQL database server into another system. You could distribute the software, perhaps an application that you install on a Windows PC and double-click to start, without the end user ever needing to know that at its heart is a MySQL database.

As you can see, things that even one or two years ago were minor omissions have now been made standard features, or are due to be incorporated in the near future.