So You'd Like To Use MySQL

Getting Started with the World's Most Popular Open Source Database

      In this article, Jon Stephens shares how you can obtain and install a MySQL database for your Linux system.


He provides lots of beginner instructions including use of the MySQL Monitor, a tool for using and adminstering MySQL that's part of the basic distribution.


The article also delves into the introduction for securing your MySQL database. Written to encourage further use of LAMP (Linux/Apache/MySQL/PHP), the article helps anyone apply the power of MySQL in their business.

Table of Contents

What is MySQL?

How I Get MySQL? How Do I Install It?

Securing Your New Installation

What's Next?

Errors & Troubleshooting

What is MySQL?

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:

• MyISAM: This is MySQL's default storage engine, and is designed to be especially fast and conservative of memory and storage space. It's a good choice in situations where you're running applications (such as a Web server) on the same machine as the database, where speed is important, and transactions or foreign keys are not essential to your application. It also supports fulltext searching, which makes it particularly useful for websites and other applications that handle large amounts of textual data.
• InnoDB: This storage engine provides support for foreign keys and transactions, which are a requirement for applications where it's important that different sets of data relate to each other according to certain rules, and that groups of operations be performed as a unit.
• MEMORY: As the name suggests, this keeps data in RAM and doesn't save it to disk. This storage engine is useful for temporary storage of data that doesn't need to be preserve beyond the lifetime of the current session or instance of an application.

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

How I Get MySQL? How Do I Install It?

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.

Warning

Before proceeding, you should determine whether or not you already have MySQL installed on your system. If you do, and you want to upgrade to a version provided by MySQL, you should uninstall your vendor's version first. This is because some vendors place the MySQL binaries and/or data files in non-standard locations.

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:

• RPM installer: If your system supports RPM installers, this is by far the quickest and easiest way to get started. There are a number of RPM files for different architectures and purposes. At a minimum, you'll need a Server RPM and a client RPM. The Server RPM will install the MySQL Server as a daemon process so that it starts automatically when your system boots. The Client RPM installs a number of command-line tools including mysql, the interactive MySQL client. (We'll talk more about these programs shortly.) Optionally, you can also install the -max Server RPM which provides some extras, but most people don't need these.
  To install the RPMs, you can use the command line like so:
  
  

  jon@gigan:~> cd downloads/mysqldir
  jon@gigan:~/downloads/mysqldir> rpm -uHv MySQL-Server-5.0.13-0.i386.rpm
  jon@gigan:~/downloads/mysqldir> rpm -uHv MySQL-Client-5.0.13-0.i386.rpm
  

  Here, ~/downloads/mysqldir represents the directory where you downloaded the MySQL Server and Client RPMs. The version numbers may be different, depending on which release number you've downloaded but the filenames should be in the form MySQL-Server-#.#.#-#.i386.rpm and MySQL-Client-#.#.#-#.i386.rpm, where #.#.#-# is the version number.
  
  

  Warning

  Make sure that the Server and Client RPMs have the same three-part version number. Trying to install mismatched RPMs is almost certain to lead to problems, and may leave you with a MySQL installation that doesn't work at all.
  
  
  
  Most modern Linux distros make this task easier: Just start up your GUI software management application, point it at the RPMs, and tell it to install them. With SuSE, and RedHat and its derivatives like Fedora and CentOS, it's even simpler than that - using Konqueror or Nautilus, navigate to the directory where you downloaded the RPMs, and click or double-click them to open them. Your package manager should spring to life and offer to perform the installations for you. Note that it's preferable to install the Server RPM first.
• Binary (.tar.gz) installer: If your system doesn't support RPM, then you can use this installation method, which is a bit more complicated, because you have to set up and configure the MySQL server daemon yourself. If you're comfortable with administering Linux system users and groups, and with editing configuration files by hand, then you will probably be okay with this method.
  You can find detailed instructions on installation using the binary installer at http://dev.mysql.com/doc/mysql/en/quick-standard-installation.html.
• Installation from source: MySQL is Open Source, so of course you can download the sources and compile it yourself. Most people, however, don't need to do this, and it's time-consuming as well. For me, it takes anywhere from 45 minutes to 2 hours to build the server and all the client programs from source. So unless you're just wanting the geek points or you've a thing about wanting to kill large amounts of time watching your machine chew up CPU, I advise you not to bother. Seriously! It is very unlikely that you'll get a “better” MySQL installation as a result.
  If you're leet enough to want or need to install from source anyway, than you probably don't need me to tell you how. If you need a hand with configure options and such, see http://dev.mysql.com/doc/mysql/en/installing-source.html.

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.

Securing Your New Installation

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_.

Note

You can always tell the MySQL command prompt from your Linux system prompt because the MySQL prompt is displayed like this:

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.

TROUBLESHOOTING & ERRORS
Have you encountered an error such as: ERROR 1045 or ERROR 1044 or simply Access denied

Most of these errors can be solved with one of the following:

You are not properly logging in as root user, try using: mysql -u root -p

OR you are not using a superuser or root account that gives full access

Or you need to disable the SELinux, found under security settings

Finding files is reasonably easy. Notice that MySQL uses the following directories:

/etc/my.cnf - the configuration file

/var/lib/mysql - the actual DataBase locations

/usr/libexec/mysqld - the mysqld daemon

What's Next?

There are plenty of things you can do with MySQL, and plenty of places on the Web where you can learn more about how to use it. One of the best sources of information about MySQL is, of course, the MySQL AB website, which includes two resources of particular interest if you're just getting started:

• MySQL Tutorial: This will help you get up to speed on what SQL queries are and how to use them, and show you some additional things you can do with the mysql client program.
• MySQL New Users Forum: Meet other new MySQL users, and get help with your basic MySQL questions from more experienced users as well as people who work for MySQL AB.