The MySQL database is an Open Source application which boasts millions of installations worldwide and is available free under the GPL from the MySQL AB website. (It is also available under a commercial license, but unless you're planning to use it in a closed-source application, this shouldn't be of concern.) It's a popular choice in particular for use on the backend of websites, especially in combination with the PHP scripting language, but it's also compatible with Perl, Python, C, Ruby, and just about any programming language that supports ODBC. MySQL runs on over a dozen operating platforms, including Linux (of course), Mac OS X, Solaris, and Windows, and its behaviour varies very little between operating systems. In fact, in many cases, you can copy MySQL database files between machines running MySQL on different operating systems, restart MySQL, and have immediate access to the data. (If you've been running MySQL on another platform, this makes it easy to migrate your databases to Linux.)

MySQL is fast and tends to be economical of system resources. It is also relatively easy to install, configure and use. It has many features of sophisticated non-Open and non-free databases (especially with the coming release of MySQL 5.0) but without many of the restrictions and licensing fees.

Another key (and unique) feature of MySQL is that it supports a number of storage engines with varying features, and it's even possible (if you're a C or C++ programmer) to create your own. Among these storage engines are:

MySQL supports several other storage engines for various specialised purposes. You can find out more about them at http://dev.mysql.com/.

As mentioned previously, you can download MySQL from MySQL AB's website, but this often isn't necessary just to get started, as MySQL is included with a great many Linux distros, so getting it may be as simple as selecting an option when you install the operating system, or using your distro's package manager - such as YaST, yum, up2date, or apt-get - to install it on your existing setup.

However, MySQL follows the Open Source philosophy of “Release early and often”, which means that your distro might not have the latest version. For that, you'll need to visit http://dev.mysql.com/downloads/, where you'll find what might seem to be a dizzying array of choices.

First, you need to decide which release series you want: For a production server, that should probably be the most recent GA (Generally Available) release. If you're interested in the latest features, then you'll want the Development release. At the time this was written, version 4.1 was the latest production version, with MySQL 5.0 being the Release Candidate, but 5.0 was expected to reach GA status shortly.

Next, you'll need to decide which installation method you prefer, download the appropriate files, then perform the actions needed to install MySQL on your system. For Linux, there are three choices:

Which installation method do I prefer? For most purposes, I just use the RPMs. MySQL's RPMs are already compiled to be have the features suited to the vast majority of users, and with the most common optimisations for speed and stability. The only time I do otherwise is when I need to do documentation and/or testing of new features that aren't yet in the released version. Installing from the RPMs is quick and easy. So unless you can't for some reason, use them, and save yourself some time and potential aggravation.

A database wouldn't do you much good if you had no tools to use with it. MySQL comes with a number of command-line tools to administer the server and to help you get data and and out of it.

The most important of these is mysql, also known as the MySQL Monitor. With this application, you can administer database users, check configuration settings, and run queries against the database. Before getting into its other functions, we'll take care of first things first, and use this utility to set up basic security for your MySQL server.

Assuming that you've installed MySQL using the RPM, there should be a symlink to mysql, so that you can invoke it from anywhere on your system. Normally, you do so like this:

> mysql -u myname -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.13-rc-Standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

(Note that you won't see anything when typing in a MySQL password - I've just added the ******** for illustrative purposes.)

Here, myname stands for a MySQL username, and the -p switch tells MySQL to prompt you for a password. It's important to realise that MySQL has a users and privileges system that is in some ways analogous to that of Linux and other Unix-style operating systems. (It's also important to remember that MySQL users and privileges are entirely separate from operating system users and privileges.) Each user of the MySQL server has a name and password, and is further identified with one or more hosts. This allows for a very fine-grained level of control over users and their privileges. As on a Linux system, the most important MySQL user is root; it's this user that has complete control over the MySQL server and all other users. MySQL also has by default an anonymous user, which means that you can log in to MySQL without a username or password. However, this “no-name” account has very limited privileges in Unix versions of MySQL; it can access only the test database or databases whose names begin with test_.

mysql>

When you install MySQL, the first thing you should do is to secure your installation. Start by setting a root password to keep unauthorised users from making changes to MySQL. To do this, first log in as root (without using the -p switch):

> mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.2-alpha-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Once you're logged in as root set the root password as shown here, replacing newpassword with whatever password you want to use for the root account:

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> UPDATE user SET Password = PASSWORD('newpassword') WHERE User = 'root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Be very careful when you do this - if you make a mistake in typing newpassword, you might not be able to log in as root again afterwards! The FLUSH PRIVILEGES command forces MySQL to reload all user account data, including passwords.

Notice that MySQL reports that two rows have been changed. This is because there are two actually account entries for root - one for root@localhost and one for root@hostname, where hostname is your machine's hostname or network IP address.

Now exit the MySQL Monitor by typing \q or quit at the prompt. You should exit to your system shell again, like this:

mysql> quit
Bye
jon@gigan:~> 

Now try logging into the MySQL Monitor again using mysql -u root (again, with no -p). You should see something like this:

jon@gigan:~> mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
jon@gigan:~>  

After this, you should not be able to log in to MySQL as root except by using the mysql -u root -p command and supplying the correct root password when prompted to do so. Once you've made sure this is the case, you should either set a password for the anonymous user account (username '') or get rid of it. I usually do the latter, like so:

jon@gigan:~> mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.1.2-alpha-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DELETE FROM user WHERE User='';
Query OK, 2 rows affected (0.00 sec)

Next, you should add a regular user account that doesn't have root privileges. You should use this account, and not root, for testing and such. In this example, I'll create a user named jon with the password “vegemite”, and give this user full rights over the test database but no other privileges:

mysql> GRANT ALL PRIVILEGES ON test.* TO 'jon'@'localhost' IDENTIFIED BY 'vegemite';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye
jon@gigan:~> mysql -u jon -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.1.2-alpha-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Let's see what happens when jon tries to access a database other than test:

mysql> USE mysql;
ERROR 1044 (42000): Access denied for user 'jon'@'localhost' to database 'mysql'

That's exactly what we want to see - we don't want jon getting into things that he's not supposed to. But this user can access the test database without any trouble.

mysql> USE test;
Database changed
mysql>

Using the jon account, you can create tables in the test database, populate them with data, run queries to extract just the data you're interested in, and modify or delete data as well.