Dabbling in Live Databases: MySQL
October 11, 1999
The MySQL Model
This article was developed in a Linux environment with MySQL 3.22; because we're beginning with the basics, most of the details should apply to MySQL in any Unix environment, and probably even MySQL in Windows. MySQL is not a subject that computer novices are likely to take up on a holiday weekend, so we necessarily assume certain background experience, including basic Perl, Unix/Linux, and databases in general.
To best understand MySQL we should start from above, like surveying a cityscape from a helicopter. MySQL operates on a client-server model, not unlike many other network applications. The MySQL server is the Grand Central Station for the city of MySQL -- the server handles incoming and outgoing traffic to and from a database. Any machine which you wish to process queries of a MySQL database must be running the MySQL server; in other words, Grand Central Station must be open for business for any traffic to arrive or depart.
The MySQL server is all-powerful: it can create and destroy. Databases are built, queried, and demolished using this server. One potentially confusing matter for some newcomers is that MySQL has no graphical interface; when we think of databases in Windows such as Access, we think of database creation and management in the model of the spreadsheet-like visual interface. Not so with MySQL, whose server takes action by commands only. Eventually, you may find that this is not nearly so horrible as it sounds.
Like any network server, the MySQL server, named mysqld, "listens" on a particular port for incoming connection requests -- the default port for MySQL being 3306. If you think of the MySQL server like the ticket manager at Grand Central Station, the listening port is roughly akin to which window the ticket taker is serving from. Unless you manually reconfigure to which port the MySQL server listens, you won't need to worry about specifying a port when using a MySQL client.
The "client" in MySQL is whatever application is sending a request to the database via the server. There are several possible clients, then: your Perl script could be a client, courtesy of the DBI module, for example. The MySQL distribution also includes its own command-driven client, eponymously named mysql, which is simply an interactive interface for sending queries to a database. Anytime you send commands to the MySQL server you are using a client to do so, whether that is a Perl script or the included client, think of the client as the messenger boy between your wishes and the all-powerful server.
Starting at Zero: Birth of a Database
So you're starting from nothing -- how do you go about creating a database with MySQL? After all, you can't query anything if there is nothing yet to query! We'll detail this process shortly, but first let's consider the bird's eye view. At the most abstract level, creating a database is a two-step process:
That's the short of it. Now we're ready for the details, the dirty gritty bits that get stuck between the keys. In other words, the fun stuff!