You'll use MySQL for the database examples in this book, so you need to get it up and running.
At time of writing, the latest production release is MySQL 4.0, and you can find the server files (both as source code and as precompiled binaries for a wide range of platforms) on the MySQL site at www.mysql.com/downloads/mysql-4.0.html.
Details of installation (such as how you install it, and where the crucial files end up) can vary quite a lot, depending on factors such as the operating system you use and the particular type (source code or precompiled) of MySQL package you've downloaded, so let's look at several different versions of the installation process.
Installing on Windows
The Windows binary is provided as a ZIP file (for example, mysql-4.0.16-win.zip) containing a number of files including an executable called setup.exe. Run this file, and an installation wizard prompts you with a few settings options. These just determine which files are to be installed and where they are to be stored. The defaults (a Typical install in C:\MySQL) should be fine.
All of the core MySQL programs are stored in the bin directory of your installation. Assuming you've gone with the default settings, you'll find them in C:\mysql\bin. Most of these programs (such as mysql.exe, mysqladmin.exe, and mysqld.exe) are command-line tools, so do not try to run them by double-clicking their icons. You'll see them in action a little later on.
One very useful tool that comes with the standard Windows release of MySQL is the snappily titled winmysqladmin.exe, which provides you with a useful interface for controlling the MySQL server program. It's a graphical program, so you can run it by double-clicking as normal.
The first time you run it, you're prompted to enter a username and password, which generate a database user account for the administration tool to use. The administration tool then starts the MySQL server program, which, if installed properly, should come up first time.
Once it's running, winmysqladmin hides itself in your system tray (at the right-hand end of your Start bar), where it's represented by a traffic light. A green (go) light shows when the server's running, and a red (stop) light when it's not.
Right-clicking the icon brings up a menu that enables you to start and stop the server, and shows the winmysqladmin window from which you can view and edit various parameters on the server.
Installing on Linux
Linux users can install MySQL from a package or by compiling from source code. If you're after RPMs, then make sure you download the server, the client, the include files and libraries, and the client shared libraries for the correct platform. You should end up with the following four files (although the exact names may vary according to the MySQL version and OS you're working with):
- MySQL-4.0.16.i386.rpm
- MySQL-client-4.0.16.i386.rpm
- MySQL-devel-4.0.16.i386.rpm
- MySQL-shared-4.0.16.i386.rpm
If you're after the source code, you just need the tarball, mysql-4.0.18.tar.gz.
Installing MySQL using RPMs
Install RPMs using the following command:
> rpm -Uvh filename.rpm
Install them in the order listed in the preceding section.
When you install the first package, which contains the MySQL server, the following documentation appears on the screen:
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
This is done with:
/usr/bin/mysqladmin -u root password 'new-password'
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available on the web at http://www.
mysql.com
Support MySQL by buying support/licenses at http://www.tcx.se/license.htmy.
Starting mysqld daemon with databases from /var/lib/mysql
The mysqladmin program is one of the client tools, so you have to wait to set your password until after you install the client package, which you'll do a little later in this chapter. However, the RPM immediately starts up the MySQL server program mysqld (for MySQL daemon). It also creates the startup and shutdown script /etc/rc.d/init.d/mysql, that ensures that MySQL starts whenever your computer is booted, and shuts down conveniently whenever it is halted. You can use this script to start and stop mysqld, with the commands:
> /etc/init.d/mysql start
> /etc/init.d/mysql stop
Now install the MySQL-client, MySQL-devel, and MySQL-shared packages, and you are ready to move on to Configuring MySQL section of this chapter.
Installing MySQL From Source
It's fairly simple to install MySQL source code. Downloaded the mysql tarball from the MySQL Web site and install it like this:
> tar -zxvf mysql-4.0.18.tar.gz
> cd mysql-4.0.18
> ./configure --prefix=/usr
> make
If make fails, it is often because of a lack of memory, even on fairly high-spec machines. In that case, try:
> rm -f config.cache
> make clean
> ./configure --prefix=/usr --with-low-memory
> make
Assuming configure and make have run without a hitch, simply run the following statements to complete the installation:
You need some scripts to start and stop the MySQL server, mysqld. Here's one typical startup script:
#!/bin/bash
/usr/bin/mysqld_safe &
And one typical script to shut the server down:
#!/bin/bash
kill 'cat /usr/var/$HOSTNAME.pid'
These scripts are in the source code download for this book (www.wrox.com). If you choose to create startup and stop scripts yourself, produce the files using your favorite text editor, and then use the chmod command to tell the system that they are executable scripts. If you saved the startup script as startmysqld, for example, you would need to type:
> chmod ugo+rx startmysqld
Configuring MySQL
With the MySQL database engine installed and running on your local test machine, take a few moments to configure the system.
MySQL, like most networked systems (including other client-server databases, operating systems, and many more besides), requires you to log in with a specific user account before doing anything else. This is a fairly obvious security measure, and it limits access to the data by specifying permissions for each account. For example, one user may only have permission to view existing data; another may have permission to add new data, and perhaps even change other users' permissions.
root is the name traditionally given to a system's most senior user, who automatically has permission to view and modify all data and settings: a powerful position to be in. When MySQL installs, it creates the root account automatically, but doesn't set a password for it! Right now, your pristine installation could be wide open to use and abuse by anyone with a MySQL client and a network connection to the server, and you need to do something about that.
To set up a root account from the command line, follow these steps:
- Call up a command prompt and navigate to the directory containing the MySQL program files (typically C:\mysql\bin\ on Windows or /usr/bin/ on Linux).
- Type in the following command, substituting a suitably obscure password of your own in place of elephant:
> mysqladmin -uroot password elephant
With the command line still in place, try out a few commands. For example, executing the command mysqlshow gives you a list of the databases currently available through your server:
Right now it shows that the server has already created two databases for itself. The first, called mysql, is where it stores all the information it uses to authenticate users. The second is an empty test database, mysqlshow can also show you what's inside a database, as long as you have the correct password:
> mysqlshow -uroot -p mysql
Enter password: ********
Database: mysql
+--------------+
| Tables |
+--------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+--------------+
So here's another clue about what's lurking inside that mysql database—all the system data is arranged into six named tables. This is your first direct glimpse of the data storage model that MySQL uses to organize its data. Before you dig any deeper, let's take a quick look at some more theory, and see how collections of tables such as these help you store data efficiently.
