Dabbling in Live Databases: MySQL

Monday Oct 11th 1999 by Aaron Weiss
Share:

MySQL is hot stuff. Hot, not in the catwalks-in-Paris sense, but among the 'does this shirt looks clean enough?' set, MySQL is definitely all the rage. With good reason -- who can resist the allure of a free (usually), stable, fast, scalable, and mature relational database server and management system?

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:

  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!

MySQL Prep

You're going to need the MySQL distribution of course. If this comes as news then the remainder of this article may be aimed a tad high. First, locate your closest MySQL mirror site and bookmark it as your MySQL home page. From there, proceed to the Downloads page and, assuming you are on a Unix system, turn your attention to the link named "Downloads for MySQL 3.22 versions" and follow it right along. In the future, this page may recommend a newer version of the software but at the time of writing 3.22 is the newest stable release.

Confused yet? The MySQL distribution is a suite of programs, namely the server and client(s); furthermore, this suite is available in a variety of packaged formats. You'll have to decide which format is right for your environment, which is a combination of your operating system and your personal tolerance levels. Roughly, there are three formats for the MySQL suite:

  • Tarball: This archive contains the source code for the MySQL server and supporting client utilities. If you are comfortable compiling and installing Unix software distributions you can download this tarball. Compiling MySQL is not difficult on a properly configured Unix system, if you follow the included instructions, but novices who run into difficulties with the compilation may feel lost at sea.

  • Users of RedHat Linux can take advantage of the RPM architecture -- which basically allows you to install and uninstall software packages in a (somewhat) user friendly manner -- can download the server RPM and client RPM files. If you're on PC hardware (Intel/AMD) then choose the i386 RPM's. The advantage of using RPM's is that the installation is all pre-configured and easily undone. The disadvantage is that you have no say as to where the various files and programs are placed in your system.

  • Standard binary distributions contain the MySQL software already compiled for specific platform architectures. You can download one of these distributions if you are using a supported platform; for example, a user with a Pentium PC running Linux can download the linux-gnu on i686 distribution. RedHat Linux users can stick with RPM's if they prefer, or use one of these standard binary distributions.

Once you have the MySQL distribution installed, you'll be able to find -- somewhere on your system depending on the installation -- a mysql subdirectory within which there is, among other things, a bin folder with all the executables. The noteworthy executables in your mysql/bin directory should include mysqld (the MySQL server), mysql (an interactive command-driven client), as well as other helpful utilities, such as the aforementioned mysqlimport. The core of this system, though, is mysqld, since is the server, the Big Daddy.

With a fresh MySQL installation, the first thing you must do is run the script mysql_install_db located in mysql/scripts. This script will briefly launch the server and establish "grant tables" -- a security model that we'll discuss shortly. The server will then exit and MySQL will be setup for initial use.

You may wish to have mysqld automatically startup when you boot your system. Although the exact details for starting servers with the system vary from system to system, read the mysql.server file located in mysql/support files, which contains clues on triggering mysqld with boot-up.

Prudently speaking, we need to take a few moments to consider the security model implemented in MySQL. After all, the MySQL server listens to a network port, and thus anyone could conceivably connect to the server. But you probably don't wish any random person to be able to query your database or, worse, write to your database, adding or deleting data. MySQL uses a security model based on usernames, password, and privileges quite similar to Unix's own security model.

An individual accessing the MySQL server has three identifying traits: a username, a password, and an originating host. When you assign privileges with MySQL, they are matched to the combination of a guest's username, password, and host. Sensible enough. For instance, you may wish to require that user "NiceGuy" with the password "ToughPass" connecting from any host in the nice.net domain, has read-only access to a database. There are actually quite a few possible permutations of these privileges ... you can read the whole mindnumbing treatise in the MySQL manual's Privilege System documentation.

When you first install MySQL it is insecure, as the root account has no password. So your first act should be to create a root password. Do remember that these MySQL user accounts are in no way connected to the user accounts in your Unix system, they are simply similar in nature. You'll find in mysql/bin a utility named mysqladmin, which is a quick way to access certain administrative functions of the server (you can also use the mysql/bin/mysql interactive client but the commands are longer). So, create a new root password:

/path/to/mysql/bin/mysqladmin -u root password new_password

For example:

/usr/local/mysql/bin/mysqladmin -u root password my!dog#99

Remember this root password, since it is the key to your master account with which you can administrate all your MySQL databases, and create, remove, or modify other MySQL access accounts.

Your first MySQL Database

Hoping that you've successfully installed the MySQL distribution onto your system, let's start creating your first MySQL database. Last month we used Microsoft Access to create a simple Clients database which contained two tables -- we'll recreate that pleasantly simple structure here in MySQL. Although this is a simple example, it should lay the groundwork for any other relational database you wish to create and manage in MySQL.

To recap from last month, let's look once again at the Clients database, and the two tables it consists of:

Clients database with two tables.
billing: a sample database table
ClientName ClientEmail Invoice Total Paid
Loganberry Inc. logan@pluc.net 099050101 50.00 Yes
Smart Corp. smarties@pants.net 099050102 450.00  
Arlene Von Smitten arly@frau.net 099060101 500.00 Yes
Sanford & Son rfoxx@pickup.com 099060102 75.00  
B. Gates bill@me.com 099060103 25000.00  
Fanny Farmer ffarmer@sausagelinks.net 099070101 55.50 Yes
invoices: a sample database table
Invoice Hours Rate
099050101 1 50
099050102 9 50
099060101 12.5 40
099060102 1 75
099060103 50 500
099070101 3.7 15

We'll assume that the data for these tables now resides in tab-delimited text files; in other words, the text file containing the data for billing would look like:

Loganberry Inc.  logan@pluc.net  099050101  50.00  1

As summarized earlier, we need to first establish this database in MySQL and create the skeletal structure for the tables -- then we can import the raw data.

Step 1: Create a new database using mysqladmin.

/path/to/mysql/bin/mysqladmin -u root -p rootpassword create Clients

By default, the MySQL server will create a subdirectory for the new database, Clients, in mysql/data. If you would like to store your databases somewhere other than the MySQL installation path, you need to start mysqld with the --datadir parameter; simply execute mysqld --help on a command line to view usage details.

Step 2: Create the structure for each table, in this case, billing and invoices. Consider billing -- we can describe its structure as follows:

billing: table description
Field
Name
Field
Type
Can be
null?
ClientName text no
ClientEmail text no
Invoice integer no
Total real no
Paid boolean yes

Now, to actually create these structures in MySQL we need to concoct an SQL statement which defines the above:

CREATE TABLE billing
(ClientName CHAR(255) NOT NULL,
ClientEmail CHAR(255) NOT NULL,
Invoice INTEGER NOT NULL,
Total DECIMAL(6,2) NOT NULL,
Paid TINYINT NULL,
INDEX IDX_INVOICE (Invoice)
)

You have quite a bit of discretion in specifying your table's structure. We've made some decisions here, because MySQL offers many different types of fields, where in our table description we were using general terms. For instance, we've decided to use a CHAR field with a width of 255 characters for our text fields; there are several possible field types that would work here, and the differences are subtle. Generally, you want to select a field type that minimizes the amount of memory required to hold the possible data values -- for instance, if we had a field which only contained text less than 10 characters long, it would be wasteful to choose a CHAR(255) field type. The Column Types section in the MySQL reference manual details the differences between the many field types available.

Lastly, we created an index of the "Invoice" field -- indexing a field can speed up future queries, at the expense of a larger database, if those queries involve that field. Creating an index for your most queried fields may be a good idea, but these performance issues are matters that may require some experimentation, and tasty snack foods.

We're now ready to actually send this SQL statement to the MySQL server, thus creating the structure for billing. We'll use the interactive mysql client, which you can launch from mysql/bin/mysql in your MySQL installation directory. This client offers a command line environment:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 3.22.20

Type 'help' for help.

mysql>

Tell the client to use the Clients database:

mysql> \u Clients

And now enter the SQL statement to prepare:

mysql> create table billing (ClientName CHAR(255) NOT NULL,
ClientEmail CHAR(255) NOT NULL,Invoice INTEGER NOT NULL,
Total DECIMAL(6,2) NOT NULL,Paid TINYINT NULL
,INDEX IDX_INVOICE (Invoice))

Note that we typed the above statement in one line and the linewraps above were added for legibility on this web page. If you miss commas or parenthesis in the right places the MySQL server will reject the statement with an error, so pay careful attention to syntax. Also note the use of case -- specifically, it is the case of your table and field names that matters. Our table is called "billing", which is not the same as "Billing". The same applies to the field names. Hit Enter after you type the above and you'll be back at the command-line: now you tell the client to execute the statement:

mysql> \g
Query OK, 0 rows affected (0.06 sec)

If all goes well, MySQL will return a terse report and we can sleep well tonight knowing that the billing table structure is in place. Moving along, we lather, rinse, and repeat for invoices.

mysql> CREATE TABLE invoices 
(Invoice INTEGER NOT NULL,Hours DECIMAL(8,2) NOT NULL,
Rate DECIMAL(6,2) NOT NULL,INDEX IDX_INVOICE (Invoice))
mysql> \g
Query OK, 0 rows affected (0.06 sec)

A hearty pat on the back! That's two table structures in the can, and we can finally get to the meat of this here feast, importing the raw data into these tables.

This is the easy part. We're still within the mysql interactive client, so we'll import the raw data from here. You could alternatively use the mysqlimport utility as a standalone client, which has its own syntax. Simply:

Click here for code example.

Sometimes the server will report one or more warnings when importing raw data. Possible reasons include extra data in the raw data, compared to the table structure, or that the server felt some of the data wasn't a perfect fit for the field type defined. Warnings are not fatal problems, and in our case, the data imported just fine even if the server was a bit skeptical.

Just to be sure, we can run a little test: send a simple query from the mysql client.

mysql> select hours from invoices where invoice=99070101
    -> \g
+-------+
| hours |
+-------+
|  3.70 |
+-------+
1 row in set (0.05 sec)

Yee-ha! It's alive ... it's alive.

Protecting the Kingdom

Now, to prevent others from getting their grubby little hands on our precious data. Remember earlier we looked at the MySQL security model and we created a root password. Now that we have a database in the shed, we should create a MySQL account for querying it. Actually, we'll create two accounts with access to Clients -- one will be a simple read-only account, which our query scripts will use, and the other will be a more powerful account which someone could use to manage the database.

You are welcome, of course, to read the MySQL reference documentation on setting up user accounts and privileges. Be warned, though, that it may cause serious and longlasting mental damage. Here, we distill what you need to know to create some basic user accounts.

First, a read-only account named serf which can access the Clients database, and execute SQL Select statements against any of its tables. This account cannot access any other database supported by this MySQL server, nor can it modify or delete the Clients tables (nor add new tables). First, launch the mysql client as the root user.

/path/to/mysql/bin/mysql -u root -p
password: ***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 3.22.20

Type 'help' for help.

Now, we use the GRANT statement to construct the privilege list for the user serf, whose password will be "readonly", may have only SELECT privilege to tables within Clients, and who may connect from any remote host:

mysql> GRANT SELECT ON Clients.* TO serf@'%' 
       IDENTIFIED BY 'readonly'

The bit following the @ symbol defines which remote hosts serf can originate from. The % is a wildcard and thus means "any host". We can create more limited ranges, such as all remote hosts from the domain "safe.net", with the syntax:

mysql> GRANT SELECT ON Clients.* TO serf@'%.safe.net' 
       IDENTIFIED BY 'readonly'

Caveat: the user serf can connect to the MySQL server from a remote machine, but not the machine running the server itself, known as localhost. To allow access from localhost, we need to explicitly grant that in another GRANT statement:

mysql> GRANT SELECT ON Clients.* TO serf@localhost 
       IDENTIFIED BY 'readonly'

A more powerful account, such as lord, might be used to make changes to the Clients database.

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 
ON Clients.* TO lord@localhost IDENTIFIED BY 'fiefdom'

Should a user lord login from the localhost machine, s/he could have free reign over this database, but no other databases on the server (unless we issues additional GRANT statements).

Of course, you could create an even more powerful user who can modify all databases on this server:

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 
ON *.* TO king@'%' IDENTIFIED BY 'kingdom'

There are so many permutations you can easily see how fun it would be to spend all day creating user accounts. Alas, we must press onwards -- just keep in mind security issues as applicable to your data, and create access accounts accordingly.

Where's the Perl?

We've practically burned through this entire article and haven't seen a whit of Perl yet ... in the Perl You Need to Know series no less!? What is going on? The good news is that you already know most of the Perl you need to. Last month on Database Journal, in fact, we looked at using the DBI module to construct SQL statements with Perl. There is little change here, because of the wonderful fact that DBI is a database-independent programming interface. Last month Access, this month MySQL, and DBI doesn't miss a beat.

The real effort this time around was learning to setup and use MySQL in the first place. Now, we've got that settled and sorted out. We've even got the Clients database re-created. So let's get ready to query.

As with last month, you will need to retrieve and add some Perl modules to your Perl installation, all of which are available from the Perl Modules page on the Downloads page at your nearest MySQL mirror:

  • If your Perl installation is version 5.004 or older, you'll need the DataDumper module. The related DataShowTable module is optional but recommended.

  • You definitely need the DBI module!

  • You certainly want the Msql-MySQL modules (this includes the DBD::MySQL module).

Each of these modules contains simple installation instructions, and all are standard procedure if you've ever previously compiled and installed a Perl module in Unix.

We begin and end this article with a simple Perl script, which simply queries the billing table for all fields and dumps the output to the screen. Last month's coverage of the DBI module contains everything else you need to know to write your own Perl scripts to query this database, be it MySQL, Access, or whatever other database you install a DBD module for.

#Perl-based Perl/DBI/MySQL example
use DBI;

#open connection to Access database
$dbh = DBI->connect('dbi:mysql:Clients','serf','readonly');

The syntax of our DBI connect() call should be straightforward. Whereas we specified the dbi:odbc interface last month, this time around we call on dbi:mysql, courtesy of the DBD::MySQL module which you installed from the Msql-MySQL module package. For the sake of prudence, we connect to the database using our simpleton read-only account named serf, password "readonly", created earlier in this article.

What if, you wonder, the query script is on a different host than the MySQL server? You can specify the network location of the MySQL server in your DBI connect() call. Of course, be sure that the MySQL user account you specify is allowed to connect from the remote host on which the script is running!

$dbh = DBI->connect
('dbi:mysql:Clients:fast.server.com','serf','readonly');

The above would attempt to connect to the MySQL server running on the machine fast.server.com. This assumes the default MySQL server port 3306. You can also specify an alternate port, such as 4040:

$dbh = DBI->connect
('dbi:mysql:Clients:fast.server.com:4040','serf','readonly');

Once the database connection is made, you can simply rely on the DBI standard methods to prepare, execute, and retrieve the results of your SQL statements, exactly as we did last month. Here's our full script, basically last month's with only the MySQL modifications at the beginning.

#Unix-based Perl/DBI/MySQL example
use DBI;

#open connection to Access database
$dbh = DBI->connect('dbi:mysql:Clients','serf','readonly');

#prepare and execute SQL statement
$sqlstatement="SELECT ClientName,ClientEmail FROM billing";
$sth = $dbh->prepare($sqlstatement);
$sth->execute || 
      die "Could not execute SQL statement ... maybe invalid?";

#output database results
while (@row=$sth->fetchrow_array)
 { print "@row\n" }

In addition to the standard DBI methods, the DBD::MySQL module offers several MySQL-specific methods. You may not need to use these methods at all -- and probably shouldn't if you need to write Perl code that is truly database independent -- but you can survey them at the DBI Interface section of the MySQL reference manual.

Conclusion

Perl, as a vehicle for sending SQL statements to a database and retrieving the results, relies on a database-independent programming interface thanks to the DBI module. This means you can rely on essentially the same scripts to query an MS Access database or a MySQL database. What varies between databases is working with the beasts themselves -- that is, setting up the database applications and the databases themselves. Most of this article has focused on setting up MySQL and understanding its various management complexities. Once prepped and ready, sending queries its way is exactly the same as sending queries to the MS Access database seen in last month's installment.

Additional Resources:

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved