Hey! Leggo MySQL!

MySQL is fast becoming the most popular open source database solution available on Linux, and some say it's Linux's next killer app. Once you start to work with it, you'll understand why.

Back in our September 2000 issue, we gave our Tuxie Editors’ Choice Award for “Best Database” to MySQL. We’ve decided that it’s time to feature some of the attributes that won MySQL that award. With easy installation and features such as database replication and integration with Perl, MySQL provides a scalable database solution for everyone from small developers to large database dependent applications.

First, if you don’t have MySQL on your system already, you can download it from http://www.mysql.com/downloads. The latest, official, stable release (as of this writing) is 3.22.32. However, the newer 3.23.xx releases are now listed as “gamma” and are quite stable and recommended for new installations. The MySQL development team labels their releases conservatively. Therefore, in this article we will be working with the 3.23.30 release.

Basic Installation

The MySQL Web site has pre-compiled MySQL versions available in both RPM and .tar.gz (tarball) packages. Like all free software, the source to MySQL is also available. We’ll walk through the standard configure, make, and install, starting from the source distribution. MySQL should compile and install on any Linux distribution that has been released in the last few years, provided that you have gcc installed.

For our basic installation we will install MySQL into /usr/ local. We’ll also create a mysql account and group that MySQL will run under so that we’re not needlessly running it as root.

> groupadd mysql
> useradd -g mysql mysql

Once the group and user are added, unpack the MySQL source.

> tar -zxvf mysql-3.23.30-gamma.tar.gz

cd into the newly created mysql-3.23.30-gamma directory and configure the installation.

> ./configure –prefix=/usr/local/mysql

There are many other options you can set at compile-time using additional command-line arguments to configure. They are all documented in the INSTALL-SOURCE file (which is a very detailed README).

If you have little memory in your system, you may need to add the –with-low-memory option to configure. Otherwise, gcc may run out of memory during the compilation. In any case, once you’re done configuring the installation, it’s time to build MySQL. Execute the command:

> make

Depending on your hardware and system configuration, it will take anywhere from five minutes to an hour to build MySQL.

If the make fails for any reason, consult the INSTALL-SOURCE file for help. Most common compilation problems are listed with known solutions.

Assuming MySQL compiled without error, execute the following commands to complete the installation:

> su
> make install

After that’s done, we’ll need to run one of the scripts included with MySQL to create the default databases. For our purposes, we’ll use the mysql_install_db script, which you can execute by issuing the command:

> scripts/mysql_install_db

Next, we need to change ownership of the newly created MySQL directory and files to the mysql user we created earlier.

> chown -R mysql.mysql  /usr/local/mysql

Finally, it’s time to start the database server:

> /usr/local/mysql/bin/safe_mysqld –user=mysql &

If something goes wrong, examine the contents of the error file in /usr/local/mysql/var.

In case you are wondering, safe_mysqld is simply a shell script that launches the MySQL server process mysqld.

Before going any further, you should change the root password for the database.

> /usr/local/mysql/bin/mysqladmin -u root password new-password

MySQL does not rely on Linux’s usernames and passwords. Each user needing access to the database must be listed in MySQL’s internal user database.

You should now be able to log in to the MySQL database via the command:

> /usr/local/mysql/bin/mysql -u root -p mysql

As you might have guessed, the -u specifies the username you’d like to use when logging in. However, the -p tells the mysql command-line tool that you’d like it to prompt you for a password in this case. You can put the password on the command line, but there must be no space between the -p and the password. The mysql at the end of that command specifies which of the databases on your database server you’d like to connect to. Unless told otherwise, the mysql command-line tool assumes that you will be connecting to the MySQL server on the local machine.

So when you execute that command, you will be prompted for the password. Simply enter the new-password you just created.

Congratulations! You now have a fully functional MySQL database server running. It’s now a good idea to set up the MySQL server to start at boot time and, more importantly, shutdown when the system is halted.

For Red Hat and other SYSV R4-type systems, simply copy the file mysql-3.23.30-gamma/support-files/mysql.server to /etc/init.d. Next, symbolically link S99mysql into /etc/rc.d/ rc3.d and S01mysql to /etc/rc.d/rc0.d as follows:

> ln -s /etc/init.d/mysql.server /etc/rc.d/rc3.d/S99mysql
> ln -s /etc/init.d/mysql.server /etc/rc.d/rc0.d/S01mysql

Remember to make /etc/rc.d/init.d/mysql.server executable; it’s not executable by default.

> chmod 755 /etc/rc.d/init.d/mysql.server.

For systems that keep their init scripts in a different location, you’ll need to consult your local documentation.

Server Configuration

Like many daemons, when mysqld starts up it looks for a configuration file. It will look for the file /etc/my.cnf by default. So to make things run smoothly on your system, you’ll need to build your own copy of /etc/my.cnf. The MySQL distribution comes with four sample configuration files in the support-files directory. You should select one of those to use as the basis for your own. Which one to select depends on your hardware resources and whether MySQL is the primary service running on the system or if it must share resources with other services (such as Apache or Sendmail). The four included sample files are:

  • my-huge.cnf: For systems with 1 GB or more RAM where MySQL is the primary service running on the machine.
  • my-large.cnf: For any systems with 512 MB of RAM where MySQL is the primary service running on the machine.
  • my-medium.cnf: For systems with less memory (32 MB – 128 MB) where MySQL is sharing resources with other services.
  • my-small.cnf: For systems with less than 64 MB of memory where MySQL is sharing resources with other services and will not be utilized much.

For our purposes, we’ll copy the medium file to my.cnf and edit it to suit our needs.

> cp my-medium.cnf my.cnf.

The Customizing my.cmnf contains a list of the configuration options contained in my.cnf and their defaults as they appear in the [mysqld] section. In this article, we are only going to focus on the options that you are likely to customize in a new installation. As you gain experience with MySQL, you may find reasons to alter some of the options not listed here.

Customizing my.cnf

Open my.cnf in your favorite editor and focus on the [mysqld] section, which is the section that deals with the MySQL daemon. The other sections are specific to various command-line utilities and are beyond the scope of this article.


The key_buffer tells MySQL how much memory it is allowed to use for caching index data. Increasing this value can often yield dramatic improvements in MySQL performance. However, if this value is set too high it can adversely affect system performance (there won’t be enough memory available for Linux and other processes). Our test system has 256 MB RAM, so we’ll change this value to 64 M.


The table_cache is the number of simultaneously open tables that MySQL can have. MySQL requires two file descriptors for each unique open table plus one for each concurrent client using a table. For our larger installation, we will probably need more than 64, so we’ll increase this value to 128.


If you plan to frequently use ORDER BY or GROUP BY within your queries, you may benefit from increasing the size of sort_ buffer. For our database installation, we will leave the sort buffer value at its default.


myisam_sort_buffer_size determines the size of the buffer used when repairing a table or creating an index. For our installation, the default value will be fine.

One important value missing from my.cnf is the variable that tells MySQL which user it should run as. Because we created a mysql user earlier, add the following line to the [mysqld] section of your my.cnf file.

user = mysql.

When you have finished editing the values that are listed in Customizing my.cnf, you’ll need to copy the my.cnf file to /etc.

> cp my.cnf /etc/my.cnf

Now MySQL is configured to automatically startup and shutdown when your system does. When MySQL starts it will use the configuration options in /etc/my.cnf.


Like any service, MySQL is only as secure as the machine on which it is installed. While keeping your system up-to-date with recent security fixes is important, you also need to understand the basics of MySQL’s security system so that you can keep your data safe. MySQL provides extensive host, database, table, and user-level security, which can all be applied to make the data and the server more secure.

Network Security

If the host that runs the MySQL database is not located behind a firewall, one effective security measure is to simply change the default TCP port (3306) that MySQL uses to listen for connections. Edit the /etc/my.cnf file and change the value of port= from 3306 to an unused port on your system. Make note of the port you change the MySQL server to, as you will need to apply the change to any remote clients that need to connect to your server.

If your MySQL server is behind a firewall and will not need to answer requests from outside that firewall, it is best for the firewall to block connections to TCP port 3306.

Database-Level Security

Upon receiving a connection request, MySQL checks to see whether or not the user is allowed to connect to the database they’ve requested. This check involves not only the username and password that the client provides, but also the host from which they are attempting to connect. If the combination of user, password, and host is not allowed, MySQL simply closes the connection.

Table-Level Security

When a user attempts to execute an SQL query, MySQL checks the user’s privileges to see if the user is allowed to perform the requested operation. Some users may only be able to read (SELECT) data, while others might be given full read and write access to the tables. In fact, MySQL allows you to set privileges for each of the common SQL queries: SELECT, INSERT, UPDATE, and DELETE. In addition to those, MySQL defines the privileges: ALTER, CREATE, DROP, GRANT, FILE, INDEX, PROCESS, REFERENCES, RELOAD, SHUTDOWN, and USAGE.

The rule of thumb with MySQL security is the same as that of any good system security policy: Only give users the access they need — no more. You will find that most users should only need SELECT, INSERT, UPDATE, and DELETE privileges.

Adding an Administrative User

Let’s add a new user to the database. This user will be a database administrator who will be allowed to add databases and other users.

Log in to the MySQL server:

> /usr/local/mysql/bin/mysql -u root -p mysql

mysql> GRANT ALL PRIVILEGES ON *.* TO admin@localhost

Query OK, 0 rows affected (0.03 sec)

Exit and then log in as the new administrative user to make sure it works:

mysql> exit

> /usr/local/mysql/bin/mysql -u admin -p mysql

Adding a Normal User

Now we’ll add a normal (non-administrative) user who is allowed to add, update, and modify data stored in any table in the users database that we’ll create in the next step.

TO dbuser@localhost IDENTIFIED BY ‘password’;

Query OK, 0 rows affected (0.01 sec)

It’s useful to know that MySQL will let you assign permissions on objects (databases, tables) before they exist. This allows for planning security in advance of setting up your databases.

Creating a Sample Database

It’s now time to create a database. We’ll create a simple database called users that other services (like RADIUS or POP3 servers) might use for authentication.

If you are still logged in, exit out of the MySQL client. Now you can create the database from the command-line as follows:

> /usr/local/mysql/bin/mysqladmin -u admin -p create users

Or, if you are already logged in to MySQL, you can use the CREATE DATABASE statement:

mysql> CREATE DATABASE users;

Query OK, 1 row affected (0.06 sec)

That’s all there is to it. If you disconnected, you can reconnect to the new database with this command:

> /usr/local/mysql/bin/mysql -u admin -p users

Now we’ll create a passwd table in which we can store basic user information:

username varchar(8) not null,
crypt varchar(128) not null,
realname varchar(64) not null,
shell varchar(20) not null

Adding Data

Now we have a working administrative (admin) user, a new database (users) in which to store user information, and a user with minimal privileges (dbuser) to enter that information. Next, we’ll insert a record in the passwd table using the MySQL MD5() function in an INSERT query. This will generate an encrypted password.

First, you will need to exit MySQL and log back in again as dbuser:

mysql> exit

> /usr/local/mysql/bin/mysql -u dbuser -p users

Now you can add a user to the passwd table in the users database and verify that the data is there with a SELECT query, as illustrated in Figure One.

Figure One

mysql> INSERT INTO passwd VALUES (‘steve’,md5(‘badpassword’),
‘Steve Suehring’,'/bin/zsh’);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM passwd;

| username | crypt | realname | shell |
| steve | 31edaffbaba455bc30c52681ceb1ea9d | Steve Suehring | /bin/zsh |
1 row in set (0.00 sec)

For the complete guide to MySQL security, be sure to read the GRANT and REVOKE Entries in the MySQL Manual (see the Resources sidebar, pg. 42).


Having a database isn’t terribly useful unless you can use it from your favorite programming language. One of the best features of MySQL is that we can use it from a wide variety of programming languages (Perl, Python, PHP, Java, C, C++, etc.).

We’ll walk through the process of installing the necessary Perl modules that will allow you to use MySQL from a Perl script. Then we’ll look at a simple Perl script that uses the modules to gather some information about your MySQL server.

Module Installation

If you haven’t already downloaded the Perl modules from the MySQL Web site, you can find them at http://www.mysql.com/downloads/api-dbi.html. The modules are also available from any CPAN mirror site.

MySQL.com recommends downloading at least the following modules:

  • Data-Dumper
  • DBI
  • Msql-Mysql-modules

We also recommend that you download the Data-ShowTable module as well. These modules should all be installed in the following order:

  • Data-Dumper
  • DBI
  • Data-ShowTable
  • Msql-Mysql-modules

Once you’ve downloaded the modules, you’ll need to be logged in as root to start the process of installing them. Like most Perl modules, these all follow the standard installation process, starting with the Data-Dumper module:

> tar -zxvf Data-Dumper-<version>.tar.gz
> cd Data-Dumper-<version>
> perl Makefile.PL
> make
> make test
> make install

Simply repeat the same steps for the DBI and Data-ShowTable modules.

The installation of the Msql-Mysql-modules is slightly more involved. You’ll need to make sure that your MySQL server is running before beginning the installation of Msql-Mysql-modules.

The Makefile is interactive and will ask some questions about the install, as illustrated in Figure Two.

Figure Two

> perl Makefile.PL

Which drivers do you want to install?

1) MySQL only
2) mSQL only (either of mSQL 1 or mSQL 2)
3) MySQL and mSQL (either of mSQL 1 or mSQL 2)
4) mSQL 1 and mSQL 2
5) MySQL, mSQL 1 and mSQL 2

Enter the appropriate number: 1

Do you want to install the MySQLPerl emulation? You might keep
your old MySQL module (to be distinguished from DBD::mysql!) if
you are concerned about compatibility to existing applications! [n]

Where is your MySQL installed? Please tell me the directory that
contains the subdir ‘include’. [/usr/local/mysql]

Which database should I use for testing the MySQL drivers? [test] mysql

On which host is database mysql running (hostname,
ip address or host:port) [localhost]

User name for connecting to database mysql? [undef] root

Password for connecting to database mysql? [undef] <new-password>

Creating files for MySQL ………………..
Checking if your kit is complete…
Looks good
Warning: prerequisite Data::ShowTable 0 not found at (eval 14) line 228.
Using DBI 1.14 installed in /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI
Writing Makefile for DBD::mysql
Writing Makefile for Msql-MySQL-modules

A Sample Script

Now it’s time to build our first script. In Figure Three we create a script that will connect to the mysql database on the local MySQL server and list each of the tables it contains.

Figure Three

#!/usr/bin/perl -w

use strict;
use DBI;

my $user = “root”;
my $password = “YOUR_PASSWORD”;
my $database = “mysql”;

my $dbh = DBI->connect(“dbi:mysql:$database”,$user,
$password)ordie”Cannot connectto database: $!”;

my $query = $dbh->prepare(“SHOW TABLES”);

while (my $tablename = $query->fetchrow_array) {
print “Database $database contains a table
called $tablename\n”;



Here is what the output of this script looks like:

> perl dbtest

Database mysql contains a table called columns_priv
Database mysql contains a table called db
Database mysql contains a table called func
Database mysql contains a table called host
Database mysql contains a table called tables_priv
Database mysql contains a table called testac
Database mysql contains a table called testad
Database mysql contains a table called user

DBI is Perl’s Database Interface. It abstracts the specifics of most database APIs (MySQL, Oracle, PostgreSQL, etc.) so that you can write scripts that are largely database independent with relatively little effort. To learn more about DBI, be sure to read the on-line documentation that was installed with the modules:

> perldoc DBI

Using the script in Figure Three, some basic SQL knowledge, and the DBI documentation, you can start building applications with Perl and MySQL in no time.

Linux’s Next Killer App?

There’s obviously much more to say about MySQL but this is all the space we have at the moment. Considering MySQL’s ease of use and flexibility, it’s easy to see why many people are calling it “Linux’s next killer app.” Look for more articles in upcoming issues.


Steve Suehring is a systems engineer at CoreComm. He can be reached at suehring@dangermen.com.

Comments are closed.