MySQL in a Nutshell (2008)

Part I. Introduction and Tutorials

Chapter 2. Installing MySQL

The MySQL database server and client software work on several different operating systems, notably Linux, FreeBSD, and a wide range of Unix systems: Sun Solaris, IBM AIX, HP-UX, and so on. MySQL AB has also developed a Mac OS X version, a Novell NetWare version, and several MS Windows versions. You can obtain a copy of the community version of MySQL from MySQL AB’s site (http://dev.mysql.com/downloads).

This chapter briefly explains the process of installing MySQL on Unix, Linux, Mac OS X, NetWare, and Windows operating systems. For some operating systems, there are additional sections for different distribution formats. For any one platform, you can install MySQL by reading just three sections of this chapter: the next section on Choosing a Distribution”; the section that applies to the distribution that you choose; and the section on Postinstallation” at the end of the chapter.

Choosing a Distribution

Before beginning to download an installation package, you must decide which version of MySQL to install. The best choice is usually the latest stable version recommended by MySQL AB on its site. This is the GA (Generally Available) release. It’s not recommended that you install a newer version unless you need some new feature that is contained only in one of the newer versions, such as the beta version or the RC (Release Candidate) version. It’s also not recommended that you install an older version unless you have an existing database or an API application that won’t function with the current version.

When installing MySQL, you also have the option of using either a source distribution or a binary distribution. It’s easier, and recommended, for you to install a binary distribution. However, you may want to use a source distribution if you have special configuration requirements that must be set during the installation or at compile time. You may also have to use a source distribution if a binary distribution isn’t available for your operating system.

Unix Source Distributions

The steps for installing MySQL on all Unix types of operating systems are basically the same. This includes Linux, Sun Solaris, FreeBSD, IBM AIX, HP-UX, etc. It’s recommended that you install MySQL with a binary distribution, but as explained in the previous section, sometimes you may want to use a source distribution. To install a source distribution, you will need copies of GNU gunzip, GNU tar, GNU gcc (at least version 2.95.2), and GNU make. These tools are usually included in all Linux systems and in most Unix systems. If your system doesn’t have them, you can download them from the GNU Project’s site (http://www.gnu.org).

Once you’ve chosen and downloaded the source distribution files for MySQL, enter the following commands as root from the directory where you want the source files stored:

groupadd mysql

useradd -g mysql mysql

tar xvfz /tmp/mysql-version.tar.gz

cd mysql-version

The first command creates the user group mysql. The second creates the system user mysql and adds it to the group mysql at the same time. The next command uses the tar utility (along with gunzip via the z option) to unzip and unpack the source distribution file you downloaded. You should replace the word version with the version number—that is to say, you should use the actual path and filename of the installation file that you downloaded for the second argument of the tar command. The last command changes to the directory created by tar in the previous line. That directory contains the files needed to configure MySQL.

This brings you to the next step, which is to configure the source files to prepare them for building the binary programs. This is where you can add any special build requirements you may have. For instance, if you want to change the default directory from where MySQL is installed, use the --prefix option with a value set to equal the desired directory. To set the Unix socket file’s path, you can use --with-unix-socket-path. If you would like to use a different character set from the default of latin1, use --with-charset. Here is an example of how you might configure MySQL with these particular options before building the binary files:

./configure --prefix=/usr/local/mysql \

            --with-unix-socket-path=/tmp \

            --with-charset=latin2

You can also enter this command on one line without the backslashes.

Several other configuration options are available. To get a complete and current listing of options permitted, enter the following from the command line:

./configure --help

You may also want to look at the latest online documentation for compiling MySQL at http://dev.mysql.com/doc/mysql/en/compilation_problems.html.

Once you’ve decided on any options that you want, run the configure script with these options. It will take quite a while to run, and it will display a great deal of information, which you can ignore usually if it ends successfully. After the configure script finishes, the binaries will need to be built and MySQL needs to be initialized. To do this, enter the following:

make

make install

cd /usr/local/mysql

./scripts/mysql_install_db

The first command builds the binary programs. If it’s successful, you need to enter the second line to install the binary programs and related files in the appropriate directories. In the next line, you’re changing to the directory where MySQL was installed. If you configured MySQL to be installed in a different directory, you’ll have to use that one instead. The last command uses a script provided with the distribution to generate the initial privileges or grant tables.

All that remains now is to change the ownership of the MySQL programs and directories. You can do this by entering the following:

chown -R mysql /usr/local/mysql

chgrp -R mysql /usr/local/mysql

The first command changes ownership of the MySQL directories and programs to the mysql user. The second command changes the group owner of the same directory and files to mysql. These file paths may be different depending on the version of MySQL you installed and whether you configured MySQL for different paths.

With the programs installed and their file ownerships properly set, you can start MySQL. You can do this in several ways. To make sure that the daemon is restarted in the event that it crashes, enter the following from the command line:

/usr/local/mysql/bin/mysqld_safe &

This starts the mysqld_safe daemon, which will in turn start the MySQL server mysqld. If the mysqld daemon crashes, mysqld_safe will restart it. The ampersand at the end of the line instructs the shell to run the daemon in the background.

To have MySQL started at boot time, copy the mysql.server file, located in the support-files subdirectory of /usr/local/mysql, to the /etc/init.d directory. To do this, enter the following from the command line:

cp support-files/mysql.server /etc/init.d/mysql

chmod +x /etc/init.d/mysql

chkconfig --add mysql

The first line follows a convention of placing the startup file for the server in the server’s initial daemons directory with the name mysql. You should change the file paths to the equivalent directory on your system. The second command makes the file executable. The third sets the run level of the service for startup and shutdown.

Now that MySQL is installed and running, you need to make some postinstallation adjustments that are explained in the last section of this chapter (Postinstallation”).

Unix Binary Distributions

Installing MySQL with a binary distribution is easier than using a source distribution and is the recommended choice if a binary distribution is available for your platform. The files are packaged together into an archive file and then compressed before being put on the Internet for downloading. Therefore, you will need a copy of GNU tar and GNU gunzip to be able to unpack the installation files. These tools are usually included on all Linux systems and most Unix systems. If your system doesn’t have them, though, you can download them from the GNU Project’s site (http://www.gnu.org).

Once you’ve chosen and downloaded the installation package, enter something like the following from the command line as root to begin the MySQL installation process:

groupadd mysql

useradd -g mysql mysql

cd /usr/local

tar xvfz /tmp/mysql-version.tar.gz

The first command creates the user group mysql. The second creates the user mysql and adds it to the group mysql at the same time. The next command changes to the directory where the MySQL files are about to be extracted. In the last command, you use the tar utility (along with gunzipvia the z option) to unzip and unpack the source distribution file that you downloaded. The word version in the name of the installation file is replaced with the version number—that is to say, use the actual path and name of the installation file that you downloaded as the second argument of the tar command. For Sun Solaris systems, you should use gtar instead of tar.

After running the previous commands, you need to create a symbolic link to the directory created by tar in /usr/local:

ln -s /usr/local/mysql-version /usr/local/mysql

This creates /usr/local/mysql as a link to /usr/local/mysql-version, where mysql-version is the actual name of the subdirectory that tar created in /usr/local. The link is necessary because MySQL is expecting the software to be located in /usr/local/mysql and the data to be in/usr/local/mysql/data by default. It should be noted that for some versions of MySQL, a different directory is expected and used. So consult MySQL’s online documentation to be sure.

At this point, MySQL is basically installed. Now you must generate the initial privileges or grant tables, and change the file ownership of the MySQL programs and datafiles. To do these tasks, enter the following from the command line:

cd /usr/local/mysql

./scripts/mysql_install_db

chown -R mysql /usr/local/mysql

chgrp -R mysql /usr/local/mysql

The first command changes to the directory containing MySQL’s files. The second command uses a script provided with the distribution to generate the initial privileges or grant tables, which consist of the mysql database with MySQL’s root user. The third command changes the ownership of the MySQL directories and programs to the mysql user. The last command changes the group owner of the same directory and files to mysql.

With the programs installed and their ownerships properly set, you can start MySQL. This can be done in several ways. To make sure that the daemon is restarted in the event that it crashes, enter the following from the command line:

/usr/local/mysql/bin/mysqld_safe &

The mysqld_safe daemon, started by this command, will in turn start the MySQL server mysqld. If the mysqld daemon crashes, mysqld_safe will restart it. The ampersand at the end of the line instructs the shell to run the command in the background.

To have MySQL started at boot time, copy the mysql.server file located in the support-files subdirectory of /usr/local/mysql to the /etc/init.d directory. To do this, enter the following from the command line:

cp support-files/mysql.server /etc/init.d/mysql

chmod +x /etc/init.d/mysql

chkconfig --add mysql

The first line follows a convention of placing the startup file for the server in the server’s initial daemons directory with the name mysql. Set the file path according to your system, though. The second command makes the file executable. The third sets the run level of the service for startup and shutdown.

Now that MySQL is installed and running, you need to make some postinstallation adjustments that are explained in the last section of this chapter (Postinstallation”).

Linux RPM Distributions

If your server is running on a version of Linux that installs software through the RPM package format (where RPM originally stood for Red Hat Package Manager), it is recommended that you use a package instead of a source distribution. Currently, RPMs are provided based on only a couple of different Linux distributions: various versions of Red Hat Enterprise Linux and SuSE Linux Enterprise. For all other distributions of Linux, MySQL RPMs are based on the Linux kernel or the type of libraries installed on the server. For each version of MySQL, there are a few RPM files that you can download. The primary two contain the server and client files. Their naming scheme is MySQL-server-version.rpm and MySQL-client-version.rpm, where version is the actual version number. In addition to these main packages, you may also want to install some of the other RPM files that are part of a distribution. There’s an RPM for client-shared libraries (MySQL-shared-version.rpm), another for libraries and C API include files for certain clients (MySQL-devel-version.rpm), and another for benchmarking and other MySQL performance tests (MySQL-bench-version.rpm).

To install RPM files after downloading them to your server, enter something like the following from the command line in the directory where they’re located:

rpm -ivh MySQL-server-version.rpm \

         MySQL-client-version.rpm

If an earlier version of MySQL is already installed on the server, you will receive an error message stating this problem, and the installation will be canceled. If you want to upgrade an existing installation, you can replace the i option in the example with an uppercase U.

When the RPM files are installed, the mysqld daemon will be started or restarted automatically. Once MySQL is installed and running, you need to make some postinstallation adjustments that are explained in the last section of this chapter (Postinstallation”).

Macintosh OS X Distributions

On recent versions of Mac OS X, MySQL is usually installed already. However, in case it is not installed on your system or you want to upgrade your copy of MySQL by installing the latest release, directions are included here.

As of version 10.2 of Mac OS X and version 4.0.11 of MySQL, binary package (PKG) files are available for installing MySQL. If your server is using an older version of Mac OS X, you need to install MySQL using a Unix source or binary distribution, following the directions described earlier in this chapter for those particular packages. If your server is not running a graphical user interface (GUI) or a desktop manager, you can instead install MySQL on a Macintosh system with a TAR package. This can be downloaded from the download page on MySQL’s web site. Explanation of that method of installation is included here.

If an older version of MySQL is already installed on your server, you will need to shut down the MySQL service before installing and running the newer version. You can do this with the MySQL Manager Application, which is a GUI application. It’s typically installed on recent versions of Mac OS X by default. If your server doesn’t have the MySQL Manager Application, enter the following from the command line to shut down the MySQL service:

mysqladmin -u root shutdown

Incidentally, if MySQL isn’t already installed on your system, you may need to create the system user, mysql, before installing MySQL.

To install the MySQL package file, from the Finder desktop manager, double-click on the disk image file (the .dmg file) that you downloaded. This will reveal the disk image file’s contents. Look for the PKG files; there will be at least two. Double-click on the one named MySQL followed by the version numbers. This will begin the installation program. The installer will take you through the installation steps from there. The default settings are recommended for most users and developers. You will need an administrator username and password. To have MySQL started at boot time, add a StartupItem. Within the disk image file that you downloaded, you should see an icon labeled MySQLStartupItem.pkg. Just double-click it, and it will create a StartupItem for MySQL.

To install the TAR package instead of the PKG package, download the TAR file from MySQL’s site and move it to the /usr/local directory, and then change to that directory. Next, untar and unzip the installation program like so:

cd /usr/local

tar xvfz mysql-version.tar.gz

In this example, change version to the actual version number. From here create a symbolic link for the installation directory. Then run the configuration program:

ln -s /usr/local/mysql-version /usr/local/mysql

cd /usr/local/mysql

./configure --prefix=/usr/local/mysql \

   --with-unix-socket-path=/usr/local/mysql/mysql_socket \

   --with-mysqld-user=mysql

Depending on your needs, you might provide other options than just these few. Next, you should set the ownership and group for the files and directories created to the mysql user and group, which should have been created by the installation program. For some systems, you may have to enable permissions for the hard drive or volume first. To do that, use the vsdbutil utility. If you want to check if permissions are enabled on the volume first, use the -c option; to just enable it, use the -a option for vsdbutil. You should also make a link to the mysql client and tomysqladmin from the /usr/bin directory:

vsdbutil -a /Volumes/Macintosh\ HD/

sudo chgrp -R mysql /usr/local/mysql/.

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

ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

ln -s /usr/local/mysql/bin/mysqladmin /usr/bin/mysqladmin

Of course, change the name of the hard drive and its path to how it reads on your system. At this point, you should be able to start the daemon and log into MySQL. This is the same for both the TAR and the PKG method of installation on a Macintosh system:

sudo /usr/local/mysql/bin/mysqld_safe &

mysql -u root -p

Depending on the release of MySQL, the file path for a PKG installation may be different than shown here. An ampersand (&) sends the process to the background.

Once MySQL is installed and running, you need to make some postinstallation adjustments that are explained in the last section of this chapter (Postinstallation”).

Novell NetWare Distributions

If your server is using Novell NetWare 6.0 or later, and the required Novell support packs have been installed, you can install MySQL on it. For version 6.0 of NetWare, you need to have Support Pack 4 installed and updated along with the current version of LibC. For version 6.5 of NetWare, Support Pack 2 needs to be installed and updated along with the current version of LibC. You can obtain support packs from Novell’s site (http://support.novell.com). You can find the latest version of LibC at http://developer.novell.com/wiki/index.php/Libraries_for_C_(LibC). Another requirement for installing MySQL is that the MySQL server and data be installed on a Novell Storage Services (NSS) volume.

If an older version of MySQL is already installed and running on your server, you need to shut down the MySQL service before installing and running the newer version. You can do this from the server console like so:

mysqladmin -u root shutdown

Next, you need to log on to the server from a client that has access to the location (SYS:MYSQL) where MySQL is to be installed. Unpack the compressed binary package to that location. When the ZIP file has finished unpacking, you can establish a search path for the directory that holds the MySQL NetWare Loadable Modules (NLMs) by entering the following from the server console:

SEARCH ADD SYS:MYSQL\BIN

At this point, MySQL is basically installed. Now you need to generate the initial privileges or grant tables. You can do this by entering the following from the server console:

.\scripts\mysql_install_db

The mysql_install_db utility is a script provided with the distribution to generate the initial privileges or grant tables (i.e., the mysql database). Once this is done, MySQL is ready to be started. To do this, just enter the following from the server console:

mysqld_safe

To have MySQL started at boot time, you must add the following lines to the server’s autoexec.ncf file:

SEARCH ADD SYS:MYSQL\BIN

MYSQLD_SAFE --autoclose --skip-external-locking

The first line establishes the search path for MySQL. The second line starts the mysqld_safe daemon at startup. The first option in this command instructs the server to close MySQL automatically when the server shuts down. The second option instructs the server not to allow external tablelocking. (External locks can cause problems with NetWare version 6.0.) Both of these options are recommended.

Once MySQL is installed and running, you will need to make some postinstallation adjustments that are explained in the last section of this chapter (Postinstallation”).

Windows Distributions

Installing MySQL on a server using Windows is fairly easy. If MySQL is already installed and running on your server and you want to install a newer version, you will need to shut down the existing one first. For server versions of MS Windows (e.g., Windows NT), MySQL is installed as a service. If it’s installed as a service on your server, you can enter the following from a DOS command window to shut down the service and remove it:

mysqld -remove

If MySQL is running, but not as a service, you can enter the following from a DOS command window to shut it down:

msyqladmin -u root shutdown

MySQL AB’s site (http://dev.mysql.com/downloads/) contains three installation packages: a Windows Essential package, a standard Windows package, and a standard Windows package without the installer. The Windows Essential package is the recommended format. It contains only the essential files for running MySQL. This includes the usual command-line utilities and the header files for the C API. The standard Windows package contains the essential files, as well as documentation, the MySQL Administrator, the embedded server, the benchmark suite, and a few other useful scripts. The standard Windows package without the installer contains the same binary files and other related files for MySQL, but not an installer. You’ll need to extract and copy the files into the c:\mysql directory. Then, you must create a my.ini file in the c:\windows directory. Several examples showing different server usage come with the distribution package.

The Windows Essential package is a file called MySQL-version.msi. From the Windows desktop, just double-click this file’s icon and the Windows Installer program will start.

The standard Windows installation package is a compressed file from which you have to extract the installation files. To do this, you need a utility such as WinZip (http://www.winzip.com) to uncompress the files. One of the files is named setup.exe. Double-click it to start the installer for this package. From this point, the installation process is pretty much the same for the packages that use the installer.

Once you’ve started the installer, a dialog box appears that offers you three general choices. The Typical choice is the recommended one, but it will omit the installation of C API include files and other client libraries. For the standard Windows package, this choice will also omit installation of the embedded server, the benchmark suite, and several other scripts. The Complete installation choice instructs the installer to install everything that’s included in the distribution package that you downloaded. The Custom choice allows you to choose from a list of programs and libraries to install. On the same screen is a button labeled Change that lets you change the directory in which MySQL will be installed. Older versions of MySQL use c:\mysql as the default. Recent versions install MySQL by default in directories like c:\Program Files\MySQL\MySQL Server version\, where the word version is replaced with the version number.

After you choose what to install and where, the files are installed. When the installer is finished, the MySQL Server Instance Configuration Wizard is started. It asks you a series of questions to create a server configuration file (my.ini), which, by default, is stored in c:\windows. The questions are based on the intended usage of the MySQL server, and your answers determine the contents of the configuration file. You will also be allowed to change the default location of the datafiles, the TCP/IP port used, and a couple of other settings.

To invoke the command-line utilities without having to enter the file path to the directory containing them, enter the following from the command line:

PATH=%PATH%;c:\Program Files\MySQL\MySQL Server version\bin

You should replace the word version with the version number—that is to say, you should enter the path to the MySQL installation. If you changed location when you installed MySQL, you need to use the path that you named. Older versions of Windows may not accept long directory names in the startup file. Therefore, you may need to abbreviate the line shown previously so that it looks something like this:

PATH=%PATH%;c:\Program~1\MySQL\MySQLS~1.1\bin

The characters ~1 are substitutes for the extra characters of a directory name that follow the first seven characters. An S is a substitute for any space that occurs in the first seven characters of a directory name. If the directory name ends in a dot and more characters, the last dot and characters are given. For example, a directory named “MySQL Server 4.1” would be entered as “MySQLS~1.1,” as shown in the previous command. To make this new path available at boot time, you may want to add it to the c:\autoexec.bat file.

Once you’ve finished installing MySQL and you’ve set up the configuration file, the installer will start the MySQL server automatically. If you’ve installed MySQL manually without an installer, enter something like the following from a DOS command window:

mysqld --install

net start mysql

All that remains are some postinstallation adjustments that are explained in the next section.

Postinstallation

After you’ve finished installing MySQL on your server, you should perform a few tasks before allowing others to begin using the service. You may want to configure the server differently by making changes to the configuration file. At a minimum, you should change the password for theroot user and add some nonadministrative users. Some versions of MySQL are initially set up with anonymous users. You should delete them. This section will briefly explain these tasks.

Although the MySQL developers have set the server daemon to the recommended configuration, you may want to set the daemon differently. For instance, you may want to turn on error logging. To do this, you will need to edit the main configuration file for MySQL. On Unix systems, this file is /etc/my.cnf. On Windows systems, the main configuration file is usually either c:\windows\my.ini or c:\my.cnf. The configuration file is a simple text file that you can edit with a plain-text editor, not a word processor. The configuration file is organized into sections or groups under a heading name contained in square brackets. For instance, settings for the server daemon mysqld are listed under the group heading [mysqld]. Under this heading, you could add something like log = /var/log/mysql to enable logging and to set the directory for the log files to the one given. You can list many options in the file for a particular group. For a complete listing and explanation of these options, see Chapter 15.

You can change the password for the root user in MySQL in a few ways. One simple way is to log in to MySQL through the mysql client by entering the following from the command line:

mysql -u root -p

On a Windows system, you may have to add the path c:\mysql\bin\ to the beginning of this line, if you haven’t added it to your command path. After successfully entering the command, you will be prompted for the root user’s password. This is not the operating system’s root user, but the rootuser for MySQL. Initially there is no password, so press Enter to leave it blank. If everything was installed properly and the mysqld daemon is running, you should get a prompt like this:

mysql>

This is the prompt for the mysql client interface. You should set the password for all root users. To get a list of users and their hosts for the server, execute the following command from the mysql client:

SELECT User, Host FROM mysql.user;

+------+-----------------------+

| User | Host                  |

+------+-----------------------+

| root | 127.0.0.1             |

| root | russell.dyerhouse.com |

| root | localhost             |

+------+-----------------------+

The results from my server are shown here. After installing, I have three user and host combinations. Although 127.0.0.1 and localhost translate to the same host, the password should be changed for both along with the one for my domain. To change the root user’s password, enter the following at this prompt:

SET PASSWORD FOR 'root'@'127.0.0.1'=PASSWORD('password');

SET PASSWORD FOR 'root'@'russell.dyerhouse.com'=PASSWORD('password');

SET PASSWORD FOR 'root'@'localhost'=PASSWORD('password');

Replace the word password in quotes with the password that you want to use for root. On some systems, the wildcard % is used to allow root login from any host. After you change all of the root passwords, log out of the mysql client and log back in with the new password.

On some older systems or versions of MySQL, there are anonymous users. (Newer editions don’t have them.) They will appear in the results of the SELECT statement shown earlier with blank fields for usernames. You should delete them by entering the following from the mysql client:

DELETE FROM mysql.user WHERE User='';

DELETE FROM mysql.db WHERE User='';

FLUSH PRIVILEGES;

The first two statements here delete any anonymous users from the user and db tables in the database called mysql—that’s where the privileges or grant tables are stored. The last line resets the server privileges to reflect these changes.

The next step regarding users is to set up at least one user for general use. It’s best not to use the root user for general database management. When you set up a new user, you should consider which privileges to allow her. If you want to set up a user who can view only data, you should enter something like the following from the mysql client:

GRANT SELECT ON *.* TO 'kerry'@'localhost' IDENTIFIED BY 'beck123';

In this line, the user is kerry from the localhost and her password is beck123. If you want to give a user more than viewing privileges, you should add additional privileges to the SELECT privilege, separated by commas. To give a user all privileges, replace SELECT with ALL. Here’s another example using the ALL flag:

GRANT ALL ON db1.* TO 'kerry'@'localhost' IDENTIFIED BY 'beck123';

In this example, the user kerry has all basic privileges, but only for the db1 database and only when logged in from the localhost, not remotely. This statement adds the user kerry to the table user in the mysql database, if there is already a row for her in it, but with no privileges. It will also add a row to the db table in the mysql database indicating that kerry has all privileges for the db1 database. See the explanation of GRANT in Chapter 4 for more options.

If you have any existing MySQL datafiles from another system, you can copy the actual files to the directory where MySQL data is stored on your server—but this is not a recommended method. If you do this, be sure to change the ownership of the files to the mysql user and mysql group with the chown system command after you copy them to the appropriate directory. If your existing datafiles are dump files created by the mysqldump utility, see the explanation regarding that utility in Chapter 16. If your data needs to be converted from a text file, see the explanation of theLOAD DATA INFILE statement in Chapter 6. You probably should also check the online documentation (http://dev.mysql.com/doc/mysql/en/Upgrade.html) on upgrading from a previous version to a current one, especially if you are migrating across major versions. If you have existing data, always upgrade one release at a time. Don’t skip any or you may have problems with tables, passwords, or any applications you’ve developed.

With the MySQL installation software downloaded and installed and all of the binary files and data in their places and properly set, MySQL is now ready to use. For an introduction to using MySQL, see the next chapter.