MySQL: An Open Source RDBMS

Now that we've finally made MySQL our main database, a review would not be out place. MySQL is a very popular Open Source Relational Database Management System (RDBMS). MySQL is used by many big companies or well known sites such as Tucows Inc, http://www.linux.com, mainly because of it's availability and Open Source nature. While not necessarily free, it costs remarkably less than it's main competitor, Oracle. Since it's "Open Source", it is also easier to find help or addons, saving you the huge overhead and maintainance costs.

With that behind us, let's take a look at the basic installation. You can get the RPM's or tarball packages from the MySQL website. I'll cover the basic installation and offer some examples. For this example I'll be using a tarball (tar.gz) package.

To avoid running MySQL as root create a mysql user and group:

groupadd mysql
useradd -g mysql mysql

Now untar the source:

tar zxvf mysql-3.23.38.tar.gz

Next cd into the newly created mysql-3.23.38 directory and run the configure script:


cd mysql-3.23.38
./configure

Note that the default installation creates a mysql in /usr/share/. To change the path, use the --prefix=/path/here in the configure section (./configure --prefix=/path/here).

Now to build the MySQL installation run:

make

The make command might take a while. On my Dual 1.2 GHz with 1.5Gb of RAM, a basic installation takes about 5 minutes running in the background, then again I'm very impatient :-). Once this is done complete the installation. As root run:

make install

The next step would be to change the ownership of /usr/share/mysql directory to the mysql user:

chown -R mysql.mysql /usr/share/mysql

Now if you've gotten this far without any compilation errors you're almost ready to go. If you did run into any problems see the INSTALL-SOURCE file which contains very detailed information.

Now for the fun part: getting ready to run your databases.
MySQL includes scripts to create the default databases. To run this scipt do the following:

scripts/mysql_install_db

Once this is done, the script displays some useful information about changing the passwords, running the benchmark programs and boot time configuration of MySQL. Let's take a look at some of those.

The first thing to do after creating the default databases, is to set the MySQL root user. To do this run:

/usr/local/bin/mysqladmin -u root -p password new-password

To start the MySQL daemon run: /usr/local/bin/safe_mysqld &

Here's how you log into the default database mysql database (or any database you've created):

mysql -u root -pyourpassword mysql
or mysql -u root -p mysql

I listed two ways to log in to your database. The first one I've listed the password directly after the -p switch and the second one I left out since I'll be prompted for it that way. It's up to you how you want to log in ;-)

If you plan on running the MySQL daemon at boot time, you can copy the ~/mysql-3.23.38/support-files/mysql.server to /etc/init.d. Make this file executable first:

chmod +x /etc/rc.d/init.d/mysql.server

You would also have the create these symbolic links by issuing the following:

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

This concludes the basic installation portion of the MySQL review. The next parts will cover a more detailed server configuration, system tweaks and sample application with an intro to web integration of MySQL and PHP.