Dabbling in Live Databases: MySQL

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:

1. Create the skeletal structure for the data. Remember that data in a relational database is stored inside one or more tables, and each table has a data structure. The table's structure defines what data fields are available for each record, and what types of data may reside in these fields (integers, real numbers, text, and so on).

2. Once the table structure is established, you can fill or populate the table with actual data. Sounds simple enough.

   How do you populate a table with data using the MySQL server? There are two possibilities:

   1. The SQL insert/replace statements are used to add data to an existing table, either in new rows or overwriting existing rows.

   2. If your data already exists in a delimited text file, such as tab-delimited or comma-delimited, you can import it into an existing MySQL table. Your MySQL distribution may include a standalone utility named mysqlimport which can insert your text file into a named table. Alternatively, you can use the load data infile statement from within a MySQL client to import data from a text file into a table. Although there are no filters provided for importing other database formats, such as Access or Oracle, you can usually export from those databases into a text file, and then import that text file into MySQL after you have recreated those table structures in a MySQL database.

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!