Preview: Click "print" to continue or "done" to close this window.

Warning: A significant amount of this paper is unoriginal. You may need to check your source(s) and perhaps revise or rewrite your paper. Learn More Close Warning

WriteCheck

Title: Chapter 2

Processed: 02-16-09 at 9:00 PM

64% Matches Other Sources ( items)

what does this mean?

IN THIS CHAPTER Choosing the Installation Format Choosing the MySQL Version MySQL Support The MySQL Changelog Downloads Installation on GNU/Linux Installation on Solaris Installation on Unix-based Systems Installation on Windows Installation on OS X Initial Configuration Scripted Installation Upgrading MySQL Server Troubleshooting Installation @@@Keith: The In This Chapter list should probably be limited to no more than five entries. The above list is too long. Ed@@@ @@@Keith/Sheeri/Ed: My general thoughts on this chapter are that it's quite repetitive and often states what is true (and often obvious) about something, rather than giving guidance or advice or helping people understand installation etc. While this is not a problem a TE should solve, I think it's something I should point out. It is also light on substance and not well organized. I don't mean to be harsh but it doesn't have many of the important things people ought to know about installation - and it completely skips source-based installs. I think you need to completely rethink this chapter.  

There are a lot of different things to talk about

- a lot of diferent ways to approach all of these things - and most of them are 80% redundant to each other, which is a real challenge to write about clearly. If you can pull it apart and reduce the redundancy, and design it so at every point you have covered everything relevant to the reader's progress so far, that will be ideal. It will be hard but if you get it right it'll be worth it. BPS 2008-07-19@@@ Chapter 2 Chapter 2: Installing and Upgrading MySQL Server @@@Keith: Please review chapter for use of Code in Text. There are numerous queries throughout the chapter that need your response. There are two installation formats for MySQL, and the MySQL server itself runs on over sixteen different operating system platforms. The top four operating system platforms for MySQL will be covered in some depth: GNU/Linux, Microsoft Windows, Sun Solaris and Apple OS X. Automatic installation will be covered. We will take you through the initial configuration of the MySQL server. This will go through the bare bones of what you need to get MySQL up and running. For more complete configuration tuning, see ReferenceEFERENCE. We then delve into upgrading MySQL, which is very similar to installation. The chapter ends with troubleshooting assistance. @@@Keith: The section on Reference? Ed@@@ Choosing the Installation Format There are two basic installation formats to choose from when performing an installation --- source code installation and binary file installation.. A source code installation means that you download the actual source code and then compile the code on your server in order to actually install the software. With a binary installation the server software is pre-compiled and ready to install. Think of it as the difference between preparing a meal by combining ingredients and cooking them and getting takeout from the local Chinese restaurant. When you receive the food it is already pre-cooked and ready for eating. Source Code vs. Binary File A binary file is a file that contains binary data. It is not readable by humans, but a machine can read binary data very efficiently. Binary data is more compact than regular text. Many programs are run in binary format. A binary file does not start out that way, though. A software developer writes some code in a text file, and then generates a binary file from that text file. The code is called "source code" because it  

is the source of the binary file. Generating the binary file is

called "compiling.". To compile source code you need a special program called a compiler. If you are new to compiling files on your operating system, it is best to download the binary files and use them. Compiling MySQL from source is an advanced technique not covered by this book. When you cook your own food it takes longer than picking up the telephone and placing an order with the restaurant. However you can make the food exactly as you want and not depend on the restaurant's idea of what is good. The difference between source and binary installs is the same. With source code installations it will take longer and also requires more expertise. However the benefit is you can control every configuration option for the compilation process. With the binary installation you can be up and running much faster, but those compilation choices are made by someone else. This is not always optimal for your situation. Almost every commercial installation of MySQL server is a binary installation and not compiled internally by the company. WeI think this choice has a lot to do with the perception that it is not worth the extra work that source code installation requires. Even if the administrator has the experience needed to be able to compile the server from source for optimal performance the gains might only be a few percent speedup. If an administrator chooses to use binary format installations there is often another decision: use a binary from Sun or binaries from the operating system vendor. For the most popular platforms Sun provides a binary installation. Some vendors choose to simply reuse this package while others actually compile the source code and create their own package. The decision to use source code or binary installations ultimately lies with the administrator. @@@Keith, is this necessary to say if you've decided not to cover compilation? Also, why shouldn't the Bible cover this topic? In practice a lot of people do build their own. TE@@@ Best PracticeTIP We recommend that you keep your installations as uniform as possible. Even if your servers are running on different operating systems, uniformity is possible with locations of data directories, log directories and configuration files. This makes it easier to administer the systems in the long term. Choosing the MySQL Version @@@Keith: All headings require text following them to prevent the stacking of headings. A line or two of text is required here. Ed@@@ 1 GA, RC, Beta and Alpha Sun has four release levels for the MySQL server. These levels are GA ("General Availability"), RC ("Release Candidate"), beta and alpha. Software that is considered to be ready for production is labeled GA. The qualification for GA is that it has no known bugs that would cause severe production issues. It does not mean that the code is bug free. In the past, when a version of the server is declared GA it is typically quite solid. RC is just as it sounds --- it is a release candidate, meaning that it is high enough quality that MySQL thinks it is a possibility for GA. There might be a few serious, severe or critical bugs left in the code, but they do not affect all users, just those using a few rare features. Beta software is a step below RC in terms of quality. There are known issues (documented at http://bugs.mysql.com) with the server. It is not advisable to use beta software in production. As a database administrator, however, it would be prudent to test the beta software and following its development. This software will be GA-level code in the future and  

it is a good idea to be familiar with

its capabilities and improvements. @@@Keith: "follow its development" correct? Ed@@@ Alpha software is where new features are added, and thus is very much experimental. You will never want to run alpha-level software in production. It should strictly be considered a test bed of server features. Running Alpha vs. Beta Software In early to mid 2008 Sun had version 5.0 of the server software designated as GA release code. Version 5.1 was still in beta releases and version 6.0 was only in alpha releases. Some companies had specific business needs to use features found in only in version 5.1, and used the beta version. They accepted the higher number of outstanding bugs and associated risks. Companies testing new features in 6.0 such as Falcon or online backup could not use 6.0 in production. There were simply too many issues with the software when it was in the alpha release stages. MySQL Support There are several types of support available for MySQL server. There areis various resources freely available on the Internet including mailings lists, forums, online reference manuals from Sun and many websites with MySQL information. REF TO APPENDIX There are also companies who provide one time or on-going consulting services for MySQL and Sun also provides support through their Enterprise program for MySQL server. @@@Keith: Refer reader to the specific Appendix. "Please see Appendix X for further information." Ed@@@ 1 The MySQL Changelog The changelog is a list of all the changes from a previous version of MySQL server software to a new version. It lists incompatible changes from previous versions, bug fixes and new features. You will need to go through this changelist carefully and understand the changes that might affect your servers and the applications that interact with them. This process is of critical importance to database administrators responsible for upgrading servers to new versions of the server software. As an example, to upgrade from a MySQL server version 5.1.3 to version 5.1.25 there are 22 changelog pages to read (see  

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-x.html).

While reading those, you willould learn that there is an incompatible change listed about prepared statements. If your application uses prepared statements the upgrade could cause a problem in the way your application works. TIPBest Practice A good database administrator will take the time to read and understand the bug fixes and new features added to each version before upgrading. This may take an hour or more, but it is well worth it to discover incompatible changes before an upgrade, instead of afterwards by upset customers. Downloads The official download location for MySQL is at  

http://dev.mysql.com/downloads. Here you will find

current and past versions of MySQL Community server. In addition, there is information about the Enterprise version of the server. Installation Regardless of operating system, the MySQL server is initialized with users who have unsecure permissions. Make sure to refer to the installation instructions for your operating system as well as the generic post-install instructions. 1 GNU/Linux There are three basic installation methods for GNU/Linux platforms. Besides the source installation method, which we do not cover here, GNU/Linux distributions package up the various MySQL programs in their format of choice (rpm, deb, tar.gz etc.). The third installation method is using a binary package from Sun. We will cover both binary installation methods in detail with an example of a rpm package installation on a CentOS distribution and a second example using the binary package from Sun. 1 MySQL Installation Using  

RPM Packages on GNU/Linux The recommended way to install MySQL on rpm-based GNU/Linux distributions is by using the rpm packages. The rpms provided by Sun should work on all versions of GNU/Linux that support rpm packages and use the glibc 2.3 library. Sun provides platform-specific and generic rpms; the difference is that a platform-specific rpm dynamically links to libraries found on a platform whereas a generic rpm is linked statically with LinuxThreads.

@@@Keith: I think you need to say what's important about LinuxThreads vs. NPTL TE@@@ Note Warning: The MySQL rpms used by the various GNU/Linux distributions are sometimes packaged by the vendor. Vendor packaged software  

may differ in features and capabilities from those built by Sun. Often the version numbers do not correspond with the official MySQL version numbers, so consider carefully any policies to use third-party packages of MySQL. Many organizations have been unhappily surprised when they thought they installed a specific MySQL version with features they wanted, but installed a third-party package by a vendor and were unhappily surprised. In addition, third-party packages are often out of date. If you are installing vendor-supplied packages, the instructions in this manual do not necessarily apply to installing them. The vendor's instructions should be consulted instead. To ensure you have an official Sun package, download directly from http://dev.mysql.com/downloads. In most cases, you only need to install the MySQL-server and MySQL- client packages to get a functional MySQL installation. Other packages

(mysql-devel, mysql-shared-compat, mysql-shared-community) are not required for a standard installation. {SOULD THIS BE MOVED TO THE UPGRADE SECTION?} For upgrades, if your installation has multiple  

packages it is best to upgrade all the packages at the same time, not just some. For example, if you previously installed the server and client rpms, do not upgrade just the server rpm,

upgrade both the server and client rpms.  

If you get a dependency failure when trying to install MySQL packages (for example, error: removing these packages would break dependencies: libmysqlclient.so.10 is needed by ...), you should also install the MySQL-shared-compat package, which includes both the shared libraries for backward compatibility (libmysqlclient.so.12 for MySQL 4.0 and libmysqlclient.so.10 for MySQL 3.23).

The available rpm packages are shown in Table 2-1 the following list. Each package except the source code package has a suffix with the version, platform (such as Red Hat Enterprise version 4) and library (such as glibc2.3). Finally, it will have an abbreviation for the processor type for which the rpm was compiled and packaged. An example package file name is MySQL 

-server-community-5.1.25- 0.rhel3.x86_64.rpm. @@@Keith: the above RPM filename doesn't have the library TE@@@ The source code package has the form of MySQL- VERSION.platform.src.rpm, such as MySQL-community-5.1.25- 0.rhel4.src.rpm.

 

There is no need to specify the library because that

is determined at compile-time. TABLE 2-1 RPM Packages |Package |Description | |MySQL-client |MySQL client package. | |MySQL-debuginfo |Used for debugging problems | | |with both the client and server| | |programs. Can be used to | | |generate extra information with| | |MySQL server crashes. | |MySQL-devel |The libraries needed to compile| | |additional MySQL clients. | |MySQL-embedded |The MySQL embedded server. | |MySQL-ndb-management |Files used by the MySQL cluster| | |server. | |MySQL-server |The MySQL server files. Unless | | |you are  

connecting to a server | | |on another host this is

| | |required. | |MySQL-shared |Shared libraries used by | | |various applications and | | |languages to communicate with | | |MySQL. | |MySQL-shared-compat |This package is a replacement | | |for MySQL-shared-VERSION if | | |your application links | | |dynamically  

against older | | |versions of MySQL but you need | | |to upgrade MySQL server to a | | |newer version. | |MySQL-test |The MySQL test suite. | |MySQL-VERSION.PLATFORM.src.rpm |Source code of all the | | |packages.

| 2 RPM TTutorial @@@Keith: Heading 2 correct? Ed@@@ If you are not familiar with rpm-based distributions it will be helpful to cover a few commands for working with rpms. Executing rpm -qa will list all rpms installed on your system. To see if you have mysql: shell> rpm -qa | grep -i mysql MySQL 

-server-5.0.45-0.glibc23 MySQL-shared-5.0.45-0.glibc23 MySQL-client-5.0.45-0.glibc23 perl-DateTime-Format-MySQL-

0.04-1.el5.rf MySQL-devel-5.0.45-0.glibc23 perl-DBD-MySQL-3.0007-1.fc6 In this example, there are four MySQL packages installed and two perl libraries installed. The Pperl libraries are third-party packages for being able to connect perl with MySQL, and are not actually a part of a MySQL installation. If you see existing MySQL installations on your system, refer to the"Upgrading MySQL" section.REFERENCE. @@@Keith: Give specific section title here. The reader should know exactly where you are referring them to. Perl initial cap ok? Ed@@@ To install an rpm package, run rpm -i file.rpm.. You can list multiple packages separated by spaces and rpm will install them at the same time. The -v option gives more verbose output, which is useful in case anything goes wrong. The -h option shows installation progress using hash (#) marks. For a basic setup of the MySQL server, install the server and client rpms using the rpm command with the -ivh options: shell> rpm -ivh MySQL-server-VERSION.PLATFORM-PROCESSOR.rpm  

MySQL- client-VERSION.PLATFORM-PROCESSOR.rpm The server rpm places the data files in the /var/lib/mysql directory. By default the pid file for the

mysql daemon and the error logs are located in this directory. Each database has a subdirectory, so the mysql and test subdirectories will be created. The rpm creates an operating system account with username mysql if one  

does not exist. The mysql user is used by the

operating system to run the MySQL server daemon.  

A startup script is created in the /etc/init.d directory;

this  

can be used to start the server automatically at boot time. The mysqld server daemon will start, and

if there were no issues you can begin using MySQL. @@@Keith: how do I know if there were issues? How can I tell the installation was successful? TE@@@  

Solaris If you install MySQL using a binary tarball distribution on Solaris, you may run into trouble even before you get the MySQL distribution unpacked, as the Solaris tar cannot handle long filenames. This means that you may see errors when you try to unpack MySQL. If this occurs you must use GNU tar (gtar) to unpack the distribution. You can find a precompiled copy for Solaris at http://dev.mysql.com/downloads/os-solaris.html. You can install MySQL on Solaris using a binary package in PKG format instead of the binary tarball distribution. Before installing using the binary PKG format, you should create the mysql user and group, for example: shell> groupadd mysql shell> useradd -g mysql mysql Some basic PKG-handling commands follow: * . To add a package: pkgadd -d package_name.pkg * . To remove a package: pkgrm package_name * . To get a full list of installed packages: pkginfo * . To get detailed information for a package: pkginfo -l package_name * . To list the files belonging to a package: pkgchk -v package_name * . To get packaging information for an arbitrary file: pkgchk -l -p file_name

@@@Keith: does the Solaris installation create the startup scripts and start mysqld for me? TE@@@ 4  

Installing MySQL from a tar.gz Packages on Unix-Like Systems

A common installation method is to use the  

MySQL binary distributions that are provided for various platforms in the form of compressed tar files.

 

MySQL tar file binary distributions have names of the form mysql-VERSION-OS.tar.gz, where VERSION is a number (for example, 5.1.26), and OS indicates the type of operating system for which the distribution is intended (for example, pc-linux-i686). You need the following tools to install a MySQL tar file binary distribution: * GNU gunzip to uncompress the distribution. * A reasonable tar to unpack the distribution. The GNU tar program is known to work. Some operating systems come with a preinstalled version of tar that is known to have problems. For example, the tar provided with early versions of Mac OS X, SunOS 4.x and Solaris 8 and earlier have problems with long filenames. On Mac OS X, you can use the preinstalled gnutar program. On systems with a problematic tar package you should install GNU tar first. The procedure for setup: @@@Production: Numbered List contains bold code within code. @@@ 1. Add a login user and group for mysqld to run as: shell> groupadd mysql shell> useradd -g mysql mysql These commands add the mysql group and the mysql user. The syntax for useradd and groupadd may differ slightly on different versions of Unix, or they may have different names such as adduser and addgroup.

 

2. Pick the directory under which you want to unpack the distribution and change location into it. In the following example, we will use the the /usr/local directory. It is assumed that you have permission to create files and directories in the /usr/local directory. If that directory is protected you must perform the installation as root. shell> cd /usr/local 3. Obtain the distribution files from http://dev.mysql.com. 4. Unpack the distribution, which creates the installation directory. Then create a symbolic link to that

 

directory: shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf - shell> ln -s full-path-to-mysql-VERSION-OS mysql The tar command creates a directory named mysql-VERSION-OS. The ln command makes a symbolic link to that directory. This allows you refer to the installation directory as /usr/local/mysql. 5. Change location to the installation directory: shell> cd mysql There are several files and subdirectories in the mysql directory. The most important for installation purposes are the bin and scripts subdirectories: The bin directory contains client programs and the server. You should add the full pathname of this directory to your PATH environment variable so that your shell finds the MySQL programs properly. The scripts directory contains the mysql_install_db script used to initialize the mysql database containing the grant tables that store the server access permissions.

6.  

Ensure that the distribution contents are accessible to the mysql user. If you unpacked the distribution as the mysql user no further action is required. @@@Keith: I don't think that is useful to say. A sane installation will have shell = /bin/false anyway and you won't be able to become the mysql user. I think you should assume unpacking as root. Even explicitly tell people to become root first. TE@@@ If you unpacked the distribution as root, the contents will be owned by root. To change ownership to the mysql usrer by executing the following commands as root in the installation directory: shell> chown -R mysql . shell> chgrp -R mysql . The first command changes the owner attribute of the files to the mysql user. The second changes the group attribute to the mysql group. 7. If this is your initial installation of MySQL server you must create the data directory and initialize the grant tables: shell> scripts/mysql_install_db --user=mysql If you run the command as root, include the --user option as shown. If you run the command while logged in as that user, you can omit the --user option. The command should create the data directory and its contents with mysql as the owner. After creating or updating the grant tables, you need to restart the server manually. @@@Keith: we never started it yet? TE@@@ 8. Most of the MySQL installation can be owned by root. The exception is the data directory must be owned by mysql. To accomplish this, run the following commands as root in the installation directory: shell> chown -R root . shell> chown -R mysql data 9. If you want MySQL to start automatically when you boot your machine, you can copy the support-files/mysql.server file to the location where your system has its startup files. This will vary depending on your operating system NOTES HERE ABOUT COMMON OPTIONS After everything has been unpacked and installed, you should test your distribution. To start the MySQL server, use the following command: shell> bin/mysqld_safe --user=mysql & If you run the command as root you must use the --user option as shown above. The user option allows you to begin the MySQL daemon running under another username. In the above example the user that the daemon runs under permissions is the mysql user. If you run the command while logged in as the mysql user you can omit the --user option. TIP If the command fails immediately and prints mysqld ended there should be some information in the host_name.err file located in the data directory.

5  

Windows A native Windows distribution of MySQL has been available since MySQL server version 3.21. It represents a large percentage of

current MySQL server installations.  

In this section we will cover the installation of MySQL

server on Windows. @@@Keith: do you have stats on what % of installations are on Windows? Is it relevant here? It sounds vague and un-authoritative. TE@@@ MySQL server runs on a variety of  

Microsoft operating systems including: * Windows 2000 * Windows XP * Windows Vista

* Windows Server 2003 * Windows Server 2008 There are 32-bit and 64-bit binaries available.  

You should install MySQL on Windows using an account that has administrator rights. If you

do not there will be problems with the installation. Once installation is complete, MySQL does not need to be started by a user with Administrator privileges. MySQL  

server for Windows is available in several distribution formats. As with the

Unix-based systems there is a source code version of MySQL Server for Windows. We do not cover installation using source code. In addition to the source code version there are three different binary distributions. There are two binary distributions containing a setup program  

that installs everything needed so that you can start the server immediately. Another binary distribution called Noinstall Zip Archive contains an archive that you unpack in the installation location and then configure yourself. Typically, you would use one of the two binary distributions that includes an installer. They are easier to use than a manual installation and you need no additional tools to get MySQL up and running. The installer for the Windows version of MySQL works with a Configuration Wizard

to not only install the server program but also create an option file, create the initial user accounts and start the server. 1 Installation Packages For MySQL servers 5.0 - 6 

.0 there are three binary installation packages to choose from when installing MySQL on Windows: * Essentials pPackage: This package

 

contains the minimum set of files needed to install MySQL on Windows along with the Installation and Configuration Wizards. This package does not include optional components such as the embedded server and benchmark suite. This is the recommended method of installation for users unless there are special circumstances. * Complete pPackage: This package contains all files needed for a complete Windows installation along with the Installation and Configuration Wizards. It also includes optional components such as the embedded server and benchmark suite. For a normal server install these optional components will not be necessary. * Noinstall aArchive: This package contains all files found in the Complete install package with the exception of the Installation and Configuration Wizards. This package

must be manually installed and configured.  

The MySQL Installation and Configuration Wizard are available in the Essentials and Complete install packages.

Either the Essentials or Complete package  

are recommended for most standard MySQL installations. Only those who have unusual needs should use the Noinstall package. As an example, if you need to install multiple instances of MySQL server on a single host server

you will need to use the Noinstall package. Multiple instances configuration is not covered in either the Essentials or Complete package and must be manually configured. CautionWarning For server installations on Windows Vista that use TCP/IP for connections you should open the appropriate  

port before beginning the installation. To do this use administrator privileges and go to the Control Panel and double click the Windows Firewall icon. Choose the Allow a program through Windows Firewall option and click the Add Port button. Enter MySQL into the Name text box and 3306 (or the port of your choice) into the Port Number text box. Also check that the TCP/IP Protocol radio button is selected. You can limit access to the MySQL server by choosing the Change Scope button. Confirm your choices by clicking the OK button.

2 Installing MySQL from the Essentials or Complete pPackage The database administrator will typically use the Wizard driven Essentials or Complete package installations. How you start the wizards depends on the which installation package you download. Double- click either the extracted setup.exe file or the msi file that you downloaded to begin installation. When beginning the installation from the Essentials or Complete packages the administrator must first determine what installation type they wish to perform.  

There are three installation types available: Typical, Complete and Custom. * Typical installation will install the MySQL server and the various command-line utilities. * Complete installation installs all components in the installation package. The full package includes: the embedded server library, a benchmark suite, support programs and documentation. * Custom installation will give you complete control over the packages installed and the location of the files installed. If you choose either Typical or Completer installation types and click the Next button you will advance to a verification screen and then begin the installation. If you choose the Custom installation type and click the Next button you will advance to the Custom Installation dialog. With the Custom Installation dialog there is a list of all available components displayed on the left side of the dialog box. Components not selected for installation have a red X icon beside the component name. Components selected for installation have a gray icon. To change the installation state of a component click on that component's icon and choose a new option from the drop-down list that appears. Here is what it looks like: You can change the default installation path by clicking the Change button located on the right of the displayed installation path. After choosing your components and installation path click the Next button to advance to the Verification dialog. Review your selections and click the Install button if all the settings are correct. To make changes to the settings click the Back button or to exit the MySQL Installation Wizard without installing MySQL click the Cancel button.

 

The final screen of the Installation Wizard provides a summary of the installation and gives you the option to launch the MySQL Configuration Wizard. This wizard allows you to create a configuration file, install the MySQL service and configure security settings.

3 MySQL Server for Windows cConfiguration fFile MySQL server uses a central configuration file to allow a database administrator to set various parameters that will persist over server reboots. While most (AllLL??) parameters  

can be set from the command line

when starting the server manually it quickly becomes very cumbersome to dto this. The configuration file makes this much more efficient and less prone to errors. This configuration file can have several locations and the name varies as well between Windows and Unix- based systems. Note: The MySQL configuration file is used on all versions of MySQL Server. This one central file allows a MySQL database administrator to control the behavior and characteristics of the MySQL Server. However the configuration file can have several names and locations. This is because the developers wanted MySQL to conform to the standards of the operating systems on which it runs. Modifications to these configuration files can be done with any text editor. In addition, a Windows based MySQL configuration can be modified using the MySQL Administrator tool (REF CHAPTER ACCESS). On the Windows platform the configuration file  

is named my.ini and by default is placed in the installation directory for MySQL Server. This is done for situations where multiple MySQL servers are running on a single platform. Typically, the Configuration Wizard will create a my.ini file by asking questions of the user and then

creating a  

configuration file that is configured for your installation. The MySQL Configuration Wizard

can be started at the completion of the  

MySQL Installation Wizard or you can launch the Configuration Wizard by clicking the MySQL Server Instance Config Wizard entry in the MySQL section of the Windows Start menu.

 

If the MySQL Configuration Wizard detects an existing my.ini file you will have the option of either reconfiguring your existing server or removing the server instance by deleting the my.ini file and stopping and removing the MySQL service. On Unix-based operating systems the primary configuration file is my.cnf and will typically be placed in the /etc directory. In addition, a user can have another "personal" configuration file in his or her home directory. For example, if we haveI a user account of kmurphy on a server and a home directory of /home/kmurphy my individual configuration file would be /home/kmurphy/.my.cnf. Note the period in front of the my.cnf. It is important!! The contents of this file are read before the main my.cnf configuration file and will override this main file. MySQL client and utility programs such as the mysql and mysqldump command-line clients are not able to locate the my.ini file located in the server installation directory. For configuration of the client and utility applications you can create a new my.ini file in the C:\WINDOWS directory.

---NOT EXACTLY SURE WHY THIS IS @@@Keith: More text to go above? If not this should probably be a regular sentence without the caps. "We are not exactly sure why this is." Ed@@@ 1 Configuration Wizard The Configuration Wizard is used for two reasons, to either configure a new server after installation or to reconfigure an existing server. If the Configuration Wizard is beguin by selecting the  

MySQL Server Instance Config Wizard entry in the MySQL section of the Windows Start menu

after initial installation the Wizard will search for existing installations and you will have the option to either remove or reconfigure the server. If you  

choose the Re-configure Instance option and click the Next button your existing my.ini file will be renamed to mytimestamp.ini.bak. Timestamp is the date and time at which the existing my.ini file was created. To remove the existing server instance just choose the Remove Instance option and click the Next button. @@@Keith: The above sentence should probably be broken up into a few sentences. It may be confusing to the reader as it now is. Ed@@@ If you choose the Remove Instance option you will advance to a confirmation window. When you click the Execute button the MySQL Configuration Wizard stops and removes the MySQL service, and then deletes the my.ini file. Both the server install directory and

the directory containing the database data files are not removed. Choosing  

the Re-configure Instance option will advance you to the Configuration Type dialog where you can choose the type of installation that you wish to configure.

 

When you start the MySQL Configuration Wizard for a new MySQL installation or choose the Re- configure Instance option for an existing installation, you will begin with the Configuration Type dialog. There are two configuration types available: Detailed Configuration and Standard Configuration. The Standard Configuration option is intended for new users who want to get started with MySQL quickly without having to make many decisions about server configuration. The Detailed Configuration option is for users who want more fine-grained control over server configuration. 2 Standard Configuration If you

choose the  

Standard Configuration option the MySQL Configuration Wizard will automatically configure all options except of Service Options and Security Options. The Standard Configuration may cause difficulties with

existing MySQL installations. It is recommended you use the Detailed Configuration option  

if you have an existing MySQL installation on your

host server. 3 Detailed Configuration With the detailed configuration you have much more control over the configuration of your server at the expense of a longer process that will require more understanding of MySQL server. If you are deploying a server for production weI would recommend that you use (and understand) the Detailed Configuration dialogs. 1 The  

Server Type Dialog Box There are three different server types available to choose from. The server type that you choose affects the decisions that the MySQL Configuration Wizard makes with regard to memory, disk, and processor usage. * Developer mMachine: Choose this option for a typical desktop workstation where MySQL is intended for personal use. The assumption is that many other desktop applications are running. The MySQL server is configured to use minimal system resources. * Server mMachine: Choose this option for a server machine where the MySQL server is running alongside other server applications such as FTP, email and Web servers. The MySQL server is configured to use a moderate portion of the system resources. * Dedicated MySQL server mMachine: Choose this option for a server machine that is intended to run only the MySQL server.

 

No other applications should be running as the MySQL server is configured to use all available system resources. 2

 

Database Usage Dialog Box The Database Usage Dialog Box lets you select the storage engines that you expect to use when creating databases tables. The option you choose determines if the InnoDB storage engine is available and what percentage of resources are available to InnoDB. * Multifunctional dDatabase: This option enables both the InnoDB and MyISAM storage engines and divides resources evenly between the two. Recommended for users who use both storage engines on a regular basis. * Transactional dDatabase oOnly: This option enables both the InnoDB and MyISAM storage engines but dedicates most server resources to the InnoDB storage engine. This option is for users who use InnoDB tables almost exclusively and only rarely use the MyISAM tables. * Non-Transactional dDatabase oOnly: This option disables the InnoDB storage engine and dedicates all server resources to the MyISAM storage engine. Recommended for users who do not use InnoDB.

@@@Keith: unless users know what the storage engines are, these bullet points are kind of circular. If they do understand storage engines, I don't think they're helpful. TE@@@ 3 InnoDB Tablespace Dialog Box Sometimes you  

want to place the InnoDB tablespace files in a different location than the MySQL server data directory. Placing the tablespace files in a separate location is useful for situations such as a need for a larger file system partition or the higher performance of a RAID storage system. To change the default location for the InnoDB tablespace files, choose a new drive from the drop-down list of drive letters and choose a new path from the drop-down list of paths. You can also specify a custom path here. If you are modifying the configuration of an existing server, you must click the Modify button before you change the path. You must move the existing tablespace files to the new location manually before starting the server.

4 Concurrent CConnections Dialog Box  

To prevent the server from running out of resources it is important to limit the maximum number of concurrent connections. The

 

Concurrent Connections Dialog Box allows you to choose the expected usage of your server, and sets the limit for concurrent connections accordingly. You can also set the concurrent connection limit manually.

The three options are: *  

Decision Support (DSS)/OLAP: Choose this option if your server does not require a large number of concurrent connections. The maximum number of connections is set at 100. * Online Transaction Processing (OLTP): Choose this option if your server requires a large number of concurrent connections. The maximum number of connections is set at 500. * Manual Setting: Choose this option to set the maximum number of concurrent connections to the server manually. Choose the number of concurrent connections from the drop-down box provided or enter the maximum number of connections into the drop-down box if

needed. 5 Networking Options  

and Strict Mode Options Dialog Box Use the Networking Options Dialog Box to either enable or disable TCP/IP networking and also configure the port that TCP/IP uses to connect to the MySQL server. By default TCP/IP networking will be enabled. To disable TCP/IP networking, uncheck the box next to the Enable TCP/IP Networking option. Port 3306 is used by default

for TCP/IP connections  

To change the port used to access MySQL you choose a new port number from the drop-down box or type a new port number into the drop-down box. If the port number you choose is already in use on the server, you are prompted to confirm your choice of port number. You need to set the Server SQL Mode to either enable or disable strict mode. Enabling strict mode (default) makes MySQL behave more like other database management systems.

6  

Character Set Dialog Box The MySQL server supports multiple character sets and it is possible to set a default server character set that is applied to all tables, columns, and databases unless overridden. Use the Character Set Dialog Box to change the default character set of the MySQL server. Choose

one of the following options from the drop down list: *  

Standard Character Set: Choose this option if you want to use latin1 as the default server character set. The latin1 character set is used for English and many Western European languages. * Best Support For Multilingualism: Choose this option if you want to use utf8 as the default server character set. This is a Unicode character set that can store characters from many different languages. * Manual Selected Default Character Set / Collation: This option allows you to choose the server's default character set manually.

7 Service Options Dialog Box  

MySQL server can be installed as a Windows service. When installed this way the MySQL server can be started automatically during system startup.

The  

MySQL Configuration Wizard installs the MySQL server as a service by default. The service name is MySQL. If you do not wish to install the service just uncheck the box next to the Install As Windows Service option. You can change the service name by picking a new service name from the drop-down box provided or by entering a new service name into the drop-down box. To install the MySQL server as a service but not have it started automatically at startup, uncheck the box next to the Launch the MySQL server Automatically option.

8 Security Options Dialog Box  

The MySQL Configuration Wizard requires by default that you do

 

set a root password. If you do not wish to set a root password, uncheck the box next to the Modify Security Settings option.

From a security perspective this is not a good idea.  

To set the root password you enter the password into the New root password and Confirm boxes. If you are reconfiguring an existing server you will need to enter the existing root password into the Current root password box.

You should  

check the box next to the Root may only connect from localhost option. This prevents root logins from across the network and increases the security of your root account. To create an anonymous user account, check the box next to the Create An Anonymous Account option. Creating an anonymous account decreases server security.

It is not recommended. 9  

Confirmation Dialog Box The final Dialog Box in the MySQL Configuration Wizard is the Confirmation Dialog. To start the configuration process just click the Execute button. To return to a previous dialog you click the Back button. To exit the MySQL Configuration Wizard without configuring the server click the Cancel button. After you click the Execute button, the MySQL Configuration Wizard

begins the configuration process  

and displays the progress as the tasks are performed. The MySQL Configuration Wizard then writes these options to a my.ini file. The final location of the my.ini file is displayed next to the Write Configuration File task. If you chose to create a service for the MySQL server, the MySQL Configuration Wizard creates and starts the service. If you are reconfiguring an existing service, the MySQL Configuration Wizard restarts the service to apply your configuration changes. If you chose to set a root password, the MySQL Configuration Wizard connects to the server, sets your new root password and applies any other security settings you may have selected. After the MySQL Configuration Wizard has completed everything it will display a summary. Click the Finish button to exit the MySQL Configuration Wizard.

@@@Keith: I'm not sure if this entire section is very helpful to users. If I'm a new user and  

I don't already understand things, I think I

won't find it useful because in many cases it just describes "what things are" (e.g. the boggle-the-widget check box boggles the widget) and not why I'd want to choose X or Y. If I know what I'm doing it's just wasted pages. I guess I'm looking for more meat here. I mean it's not necessary to tell me that if I want to exit the installation I can click the Cancel button - your readers have surely walked through a MS Windows installation wizard before. What I think would be most helpful for this entire how-to-install thing is to describe the things that are true about a properly configured server: a) these files are here, those files are there b) these privileges are set on the files c) these users are created d) the path is altered in this-and-such a way.... Then tell me how to accomplish those goals (rather than just going through the steps) for each installation method. It should be fairly obvious to readers that the installation wizard is guiding them through these steps and accomplishing the end goals, then. These comments apply to the next section, too. TE@@@ 4  

Installing MySQL from a Noinstall Zip Archive Users who are installing from the Noinstall package can use the instructions in this section to manually install MySQL. The process for installing MySQL from a Zip archive is as follows: 1. Extract the archive to the desired install directory

1. *  

Make sure that you are logged in as a user with administrator privileges. 2.* Choose an installation location. Traditionally, the MySQL server is installed in C:\mysql. The MySQL Installation Wizard installs MySQL under C:\Program Files\MySQL. If you do not install MySQL at C:\mysql, you must specify the path to the install directory during startup or in an option file. See Section 2.3.7, "Creating an Option File".

@@@Keith: You may want to be more clear on where section 2.3.7 is. It may be confusing to the reader. Ed@@@ 3. *  

Extract the install archive to the chosen installation location using your preferred Zip archive tool. Some tools may extract the archive to a folder within your chosen installation location. If this occurs, you can move the contents of the subfolder into the chosen installation location.

@@@Keith: didn't I already do this by this point? And in step 1 above, it says to extract to the desired directory, but I haven't figured out how to do that yet. I think the first bullet point needs to be reworded to avoid confusion and make it clear that I'm about to learn how to do these things. Perhaps "Prepare the archive for installation" or something TE@@@ 2. Create  

an option file. If you need to specify startup options when you run the server, you can indicate them on the command line or place them in an option file. For options that are used every time the server starts, you may find it most convenient to use an option file to specify your MySQL configuration. This is particularly true under the following circumstances: * The installation or data directory locations are different from the default locations (C:\Program Files\MySQL\MySQL Server 5.1 and C:\Program Files\MySQL\MySQL Server 5.1\data). * You need to tune the server settings, such as memory, cache, or InnoDB configuration information. When the MySQL server starts on Windows, it looks for options in two files: the my.ini file in the Windows directory, and the C:\my.cnf file. The Windows directory typically is named something like C:\WINDOWS. You can determine its exact location from the value of the WINDIR environment variable using the following command: C:\> echo %WINDIR% MySQL looks for options first in the my.ini file, and then in the my.cnf file. However, to avoid confusion, it's best if you use only one file. If your PC uses a boot loader where C: is not the boot drive, your only option is to use the my.ini file. Whichever option file you use, it must be a plain text file. You can also make use of the example option files included with your MySQL distribution.

 

An option file can be created and modified with any text editor, such as Notepad. For example, if MySQL is installed in E:\mysql and the data directory is in E:\mydata\data, you can create an option file containing a [mysqld] section to specify values for the basedir and datadir parameters: [mysqld] # set basedir to your installation path basedir=E:/mysql # set datadir to the location of your data directory datadir=E:/mydata/data Note that Windows pathnames are specified in option files using (forward) slashes rather than backslashes. If you do use backslashes, you must double them: [mysqld] # set basedir to your installation path basedir=E:\\mysql # set datadir to the location of your data directory datadir=E:\\mydata\\data

 

In MySQL 5.1.23 and earlier, the MySQL installer places the data directory directly under the directory where you install MySQL. On MySQL 5.1.24 and later, the data directory is located within the AppData directory for the user running MySQL. If you would like to use a data directory in a different location, you should copy the entire contents of the data directory to the new location. For example, if you want to use E:\mydata as the data directory instead, you must do two things: * Move the entire data directory and all of its contents from the default location (for example C:\Program Files\MySQL\MySQL Server 5.1\data) to E:\mydata. * Use a --datadir option to specify the new data directory location each time you start the server.

@@@Keith: I find this section mostly redundant. I think it's better to be factored out and referred to: e.g. "Create an option file. See (some cross-ref) for more information on this." TE@@@ 3. Choose a MySQL server type.  

Available servers for Windows in MySQL 5.1.20 and earlier. @@@Keith: Tables 2-2 and 2-3 must be sited and described in the text before they appear. Ed@@@ @@@Keith: I think the tables can be combined. TE@@@ Table 2-2 Windows MySQL servers versions 5.1.20 and earlier |Binary File Name |Description | |mysqld |standard mysqld binary | |mysqld |binary compiled with named-pipe| | |support | |mysqld-debug |binary compiled with debugging | | |support | Table 2-3 Windows MySQL servers versions 5.1.21 and later |Binary File Name |Description | |mysqld |binary compiled with named pipe| | |support | |mysqld-debug |binary compiled with debugging

| | |support |  

All Windows MySQL 5.1 servers have support for symbolic linking of database directories.

@@@Keith: that is not clear what that means, or why I'd want to do it. TE@@@ There is support for the  

TCP/IP protocol on all Windows platforms. MySQL servers on Windows support named pipes as indicated in the following list. However, the default is to use TCP/IP regardless of the platform.

The reason why is  

named pipes are slower than TCP/IP in many Windows configurations. Named pipes are enabled only if you start the server with the --enable-named-pipe option. It is necessary to use this option explicitly because some users have experienced problems with shutting down the MySQL server when named pipes were used.

@@@Keith: in the following list? Maybe not clear to the reader. Ed@@@ 4. Start the  

MySQL server. This section gives a general overview of starting the MySQL server.

This informationusefulif you installed MySQL using the Noinstall version.  

The examples in these sections assume that MySQL is installed under the location of C:\Program Files\MySQL\MySQL Server 5.1.

 

If you have MySQL installed in a different location adjust the pathname accordingly. Clients have three connection options. They can use TCP/IP, named pipes if the server supports named-pipe connections,

or  

shared-memory connections if the server is started with the --shared-memory option. Clients can connect using shared memory by using the --protocol=memory option.

ISN"T THIS ONLY IF YOUR CLIENT IS RUNNING ON THE SERVER ITSELF???. @@@Keith: and can you use named pipes across the network? IANAWU  

(I am not a windows user) and so I don't know.

TE@@@  

Testing is best done from a command prompt in a console window. . In this way you can have the server display status messages in the window where they are easy to see. If something is wrong with your configurationthese messages will make it easier for you to identify and fix any problems. To start the server enter this command: C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" -- console For a server that includes InnoDB support, you should see the messages similar to those following as it starts (the pathnames and sizes may differ): InnoDB: The first specified datafile c:\ibdata\ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file c:\ibdata\ibdata1 size to 209715200 InnoDB: Database physically writes the file full: wait... InnoDB: Log file c:\iblogs\ib_logfile0 did not exist: new to be created InnoDB: Setting log file c:\iblogs\ib_logfile0 size to 31457280 InnoDB: Log file c:\iblogs\ib_logfile1 did not exist: new to be created InnoDB: Setting log file c:\iblogs\ib_logfile1 size to 31457280 InnoDB: Log file c:\iblogs\ib_logfile2 did not exist: new to be created InnoDB: Setting log file c:\iblogs\ib_logfile2 size to 31457280 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: creating foreign key constraint system tables InnoDB: foreign key constraint system tables created 011024 10:58:25 InnoDB: Started

@@@Keith: you should get a snip from a server that uses default paths to make this less confusing.TE@@@  

When the server finishes its startup sequence, you should see something like this, which indicates that the server is ready to service client connections: mysqld: ready for connections Version: '5.1.26' socket: '' port: 3306

@@@Keith: Not sure what the reader will see. If they see it it should be code in text. Ed@@@  

The server will continue to write and output to the console

.  

You can open a new console window to run client programs. If you omit the --console option, the server writes diagnostic output to the error log in the data directory . With MySQL server 5.1 this is C:\Program Files\MySQL\MySQL Server 5.1\data by default . The error log is the file with the .err extension. Note The accounts that are listed in the MySQL grant tables initially have no passwords. After starting the server, you should set up passwords for them using the instructions in UPGRADES SECTION

@@@Keith: unless you used the wizard, right?TE@@@  

Starting MySQL from the Windows Command Line @@@@ The MySQL server can be started manually from the command line. This can be done on any version of Windows. To start the mysqld server from the command line just start a console window and enter this command: C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" The path to mysqld may vary depending on the install location of MySQL on your system. Once the MySQL

 

server is started you can stop the server by executing this command: C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqladmin" -u root -p shutdown This command starts the MySQL administrative utility mysqladmin and instructs it to connect to the server and shut the server down. The command connects as the MySQL root user, which is the default administrative account in the MySQL grant system. Note that users in the MySQL grant system (MySQL users) are wholly independent from any login users under Windows. If mysqld doesn't start, check the error log to see whether the server wrote any messages there to indicate the cause of the problem. The error log is located in the C:\Program Files\MySQL\MySQL Server 5.1\data directory. It is the file with a suffix of .err. You can also try to start the server as mysqld --console.

Doing so may allow you to see some useful information on the screen . The  

last option is to start mysqld with the --standalone and --debug options. In this case, mysqld writes a log file C:\mysqld.trace that should contain the reason why mysqld doesn't start. Starting MySQL

 

as a Windows Service On the Windows platform the recommended way to run MySQL is to install it as a Windows service. The MySQL

server will then start and stop  

automatically when Windows starts and stops. A MySQL server installed as a service can also be controlled from the command line using NET commands.

If you prefer a graphical interface you can use the  

Services utility. To install MySQL as a Windows service you should be logged in using an account that has administrator rights. The Services utility (the Windows Service Control Manager) can be found in the Windows Control Panel (under Administrative Tools on Windows 2000, XP, Vista and Server 2003). To avoid conflicts

you should  

close the Services utility while performing server installation or removal operations from the command line. Before installing MySQL as a Windows service, you should first stop the current server if it is running by using the following command: C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqladmin" -u root shutdown. .Install the server as a service using this command: C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" -- install

This does not actually start the service. This only installs the service so that it  

starts and stops automatically when Windows starts or stops.

Note  

To make it easier to invoke MySQL programs, you can add the pathname of the MySQL bin directory to your Windows system PATH environment variable: On your Windows desktop, right-click on the My Computer icon, and select Properties. Next select the Advanced tab from the System Properties menu that appears, and click the Environment Variables button. Under System Variable, select Path, and then click the Edit button. The Edit System Variable dialog box should appear. Place your cursor at the end of the text appearing in the space marked Variable Value. Then enter the complete pathname of your MySQL bin directory (for example, C:\Program Files\MySQL\MySQL Server 5.1\bin), Note that there should be a semicolon separating this path from any values present in this field. Close this dialog box, and each dialog box in turn, by clicking Ok until all of the dialogs that were opened have been closed. You should now be able to invoke any MySQL executable program by typing its name at the DOS prompt from any directory on the system.

There will be no need to to  

supply the path. This includes the servers, the mysql client, and all MySQL command- line utilities such as mysqladmin and mysqldump. You should not add the MySQL bin directory to your Windows PATH if you are running multiple MySQL servers on the same machine.

@@@Keith: why not?TE@@@ CautionWarning  

You must exercise great care when editing your system PATH by hand; accidental deletion or modification of any portion of the existing PATH value can leave you with a malfunctioning or even unusable system. The following additional arguments can be used in MySQL 5.1 {WHAT ABOUT OTHER VERSIONS} when installing the service: * You can specify a service name immediately following the -- install option. The default service name is MySQL. * If a service name is given, it can be followed by a single option. By convention, this should be --defaults-file=file_name to specify the name of an option file from which the server should read options when it starts. The use of a single option other than --defaults-file is possible but discouraged. --defaults-file is more flexible because it enables you to specify multiple startup options for the server by placing them in the named option file. * . You can also specify a --local-service option following the service name. This causes the server to run using the LocalService Windows account that has limited system privileges. This account is available only for Windows XP or newer. If both --defaults-file and --local-service are given following the service name, they can be in any order. For a MySQL server that is installed as a Windows service, the following rules determine the service name and option files that the server uses: * If the service-installation command specifies no service name or the default service name (MySQL) following the --install option, the server uses the a service name of MySQL and reads options from the [mysqld] group in the standard option files. * If the service-installation command specifies a service name other than MySQL following the --install option, the server uses that service name. It reads options from the [mysqld] group and the group that has the same name as the service in the standard option files. This allows you to use the [mysqld] group for options that should be used by all MySQL services, and an option group with the service name for use by the server installed with that service name. * If the service-installation command specifies a --defaults-file option after the service name, the server reads options only from the [mysqld] group of the named file and ignores the standard option files. As a more complex example, consider the following command: C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" -- install MySQL --defaults-file=C:\my-opts.cnf Here, the default service name (MySQL) is given after the --install option. If no --defaults-file option had been given, this command would have the effect of causing the server to read the [mysqld] group from the standard option files. However, because the --defaults-file option is present, the server reads options from the [mysqld] option group, and only from the named file. You can also specify options as Start parameters in the Windows Services utility before you start the MySQL service. Once a MySQL server has been installed as a service, Windows starts the service automatically whenever Windows starts. The service also can be started immediately from the Services utility, or by using a NET START MySQL command. When run as a service, mysqld has no access to a console window, so no messages can be seen there. If mysqld does not start, check the error log to see whether the server wrote any messages there to indicate the cause of the problem. The error log is located in the MySQL data directory (for example, C:\Program Files\MySQL\MySQL Server 5.1\data). It is the file with a suffix of .err. When a MySQL server has been installed as a service, and the service is running, Windows stops the service automatically when Windows shuts down. The server also can be stopped manually by using the Services utility, the NET STOP MySQL command, or the mysqladmin shutdown command. @@@Keith: NET STOP caps above but lower case used below? Ed@@@ You also have the choice of installing the server as a manual service if you do not wish for the service to be started automatically during the boot process. To do this, use the --install-manual option rather than the --install option: C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" -- install-manual To remove a server that is installed as a service, first stop it if it is running by executing 'net stop MySQL'. Then use the --remove option to remove it: C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" --remove

1  

Mac OS X You can install MySQL on Apple Mac OS X 10.3.x ("Panther") or newer using a Mac OS X binary package in PKG format or the binary tarball distribution

we discussed TARBALL INSTALLATION.  

The package is located inside a disk image (.dmg) file that you need to mount by double- clicking the icon in the Finder. OS X should then mount the image and display its contents.

@@@Keith: Why all caps for Tarball Installation? Ed@@@  

Note Before proceeding with the installation, be sure to shut down all running MySQL server instances by either using the MySQL Manager Application (on Mac OS X Server) or via mysqladmin shutdown on the command line. To actually install the MySQL PKG file, double-click on the package icon. This launches the Mac OS X Package Installer thatwhich will guide you through the installation of MySQL. Due to a bug in the Mac OS X package installer, you may see this error message in the destination disk selection dialog box: You cannot install this software on this disk. (null) If this error occurs, click the Go Back button once to return to the previous screen. Then click Continue to advance to the destination disk selection again, and you should be able to choose the destination disk correctly.

The  

Mac OS X PKG of MySQL installs itself into /usr/local/mysql- VERSION and also installs a symbolic link, /usr/local/mysql, that points to the new location. If a directory named /usr/local/mysql exists, it is renamed to /usr/local/mysql.bak first. The installer also creates the grant tables in the mysql database by executing the mysql_install_db script. The installation layout is similar to that of a tar file binary distribution. The MySQL binaries are located in the directory /usr/local/mysql/bin. and the MySQL socket file is created as /tmp/mysql.sock by default. MySQL installation requires a Mac OS X user account named mysql. A user account with this name should exist by default on Mac OS X 10.2 and up. If you

 

want MySQL to start automatically during system startup, you also need to install the MySQL Startup Item. It is part of the Mac OS X installation disk images as a separate installation package. Double- click the MySQLStartupItem.pkg icon and follow the instructions to install it. The Startup Item need be installed only once. There is no need to install it each time you upgrade the MySQL package later. The Startup Item for MySQL is installed into /Library/StartupItems/MySQLCOM. Startup Item installation adds a variable MYSQLCOM=-YES- to the system configuration file /etc/hostconfig. If you want to disable the automatic startup of MySQL, simply change this variable to MYSQLCOM=-NO-. On Mac OS X Server the default MySQL installation uses the variable MYSQL in the /etc/hostconfig file. The MySQL AB Startup Item installer disables this variable by setting it to MYSQL=-NO-. This avoids boot time conflicts with the MYSQLCOM variable used by the MySQL AB Startup Item. However, it does not shut down a running MySQL server. You should do that yourself. After the installation, you can start up MySQL by running the following commands, as a user with Administrator privileges, in a terminal window. If you have installed the Startup Item, use this command: shell> sudo /Library/StartupItems/MySQLCOM/MySQLCOM start (Enter your password, if necessary) (Press Control-D or enter "Eexit" to exit the shell) If you don't use the Startup Item, enter the following command sequence: shell> cd /usr/local/mysql shell> sudo ./bin/mysqld_safe (Enter your password, if necessary) (Press Control-Z) shell> bg (Press Control-D or enter "exit" to exit the shell) You should be able to connect to the MySQL server, for example, by running /usr/local/mysql/bin/mysql. Note The accounts that are listed in the MySQL grant tables initially have no passwords. After starting the server, you should set up passwords for them. This is covered in SECTION POST INSTALL @@@Keith: Exactly where is this section? When a search is done for Post Install no cross reference appears. Ed@@@ You might want to add aliases to your shell's resource file to make it easier to access commonly used programs such as mysql and mysqladmin from the command line. The syntax for bash is: alias mysql=/usr/local/mysql/bin/mysql alias mysqladmin=/usr/local/mysql/bin/mysqladmin For tcsh, use:

@@@Keith: Will the reader know what tcsh is? Should it be in caps? Ed@@@  

alias mysql /usr/local/mysql/bin/mysql alias mysqladmin /usr/local/mysql/bin/mysqladmin

Another option is to  

add /usr/local/mysql/bin to your PATH environment variable. You can do this by modifying the appropriate startup file for your shell.

If you are upgrading an existing installation the  

new MySQL PKG does not remove the directory of an older installation. Unfortunately, the Mac OS X Installer does not yet offer the functionality required to properly upgrade previously installed packages. To use your existing databases with the new installation, you'll need to copy the contents of the old data directory to the new data directory. Make sure that neither the old server nor the new one is running when you do this. After you have copied over the MySQL database files from the previous installation and have successfully started the new server, you should removing the old installation files to both avoid confusion and save disk space. Additionally, you should also remove older versions of the Package Receipt directories located in /Library/Receipts/mysql-VERSION.pkg.

Initial Configuration  

After installing MySQL, there are some issues that you need to address. For example, on Unix, you should initialize the data directory and create the MySQL grant tables. On all platforms there is an important security concern in that the initial accounts in the grant tables have no passwords. You should assign passwords to prevent unauthorized access to the MySQL server. Optionally you can create time zone tables to enable recognition of named time zones. 1 Initializing the

dData dDirectory and gGrant tTable (Unix) On Unix-based systems  

the grant tables are set up by the mysql_install_db script. For some installation methods this program is run for you automatically: * Ffor installations on GNU/Linux using rpm distributions the server rpm runs the mysql_install_db script. * Ffor installations on Mac OS X using a PKG distribution the installer runs mysql_install_db.

* Ffor all other installations  

you will need to run the mysql_install_db script yourself.

To do this just change to the root directory of your MySQL installation (/usr/local/mysql by default). Then run the setup script as follows:  

shell> scripts/mysql_install_db --user=mysql If you run the command as root, include the --user option as shown. If you run the command while logged in as that user, you can omit the -- user option.

This script will  

creates the server's data directory. In the data directory it also creates directories for the mysql database that holds all database privileges and the test database

. In the mysql database  

mysql_install_db creates several tables including user, db, host, tables_priv, columns_priv, func, and

others.  

The script also creates privilege table entries for root and anonymous-user accounts. The accounts have no passwords initially.

@@@Keith: you covered this earlier TE@@@ 2 Setting iInitial pPasswords (aAll pPlatforms)  

If you installed MySQL using the Windows Installation Wizard, you might have assigned passwords to the accounts.

 

Otherwise, use the passwordassignment procedure given in Section 2.10.3, "Securing the Initial MySQL Accounts.".

The  

grant tables define the initial MySQL user accounts and their access privileges. There are accounts with the username root created. These are superuser accounts that can

perform any function on the MySQL server. The  

initial root account passwords are empty, so anyone can connect to the MySQL server as root - without a password - and be granted all privileges. On Windows there is one root account created by default. This account allows connecting from the local host only. The Windows installer will optionally create an account allowing for connections from any host only if the user selects the Enable Root Access From Remote

MachinesNABLE  

ROOT ACCESS FROM REMOTE MACHINES option during installation. On Unix there are

two initial root accounts that only allow  

for connections from the local host. Connections must be made from the local host by specifying a hostname of localhost or the actual hostname or IP address. Besides the root user accounts there are

two anonymous-user accounts created. Each of these accounts has  

an empty username. The anonymous accounts have no password so anyone can use them to connect to the MySQL server. On Windows one anonymous account is for connections from the local host. For MySQL server version 5.1.16 and later this account has no global privileges. Before MySQL 5.1.16 it has all global privileges The other is for connections from any host and has all privileges for the test database and for other databases with names that start with test. On Unix-based systems both anonymous accounts are for connections from the local host. Connections must be made from the local host by specifying a hostname of localhost for one of the accounts, or the actual hostname or IP address for the other. These accounts have all privileges for the test database and for other databases with names that start with test_. You have several options for resolving the problem of the lack of passwords on these accounts. If you want to prevent clients from connecting as anonymous users without a password, you should either assign a password to each anonymous account or, ideally, else remove the accounts. In addition, you should assign a password to each MySQL root account. The following instructions describe how to set up passwords for the initial MySQL accounts, first for the anonymous accounts and then for the root accounts. Replace '"new_password'" in the examples with the actual password that you want to use. The instructions also cover how to remove the anonymous accounts, should you prefer not to allow anonymous access at all. 1 Anonymous aAccount pPassword aAssignment To assign passwords to the anonymous accounts, connect to the server as root and then use either SET PASSWORD or UPDATE. In either case, be sure to encrypt the password using the PASSWORD() function. To use SET PASSWORD on Windows, do this: shell> mysql -u root mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('new_password'); mysql> SET PASSWORD FOR ''@'%' = PASSWORD('new_password'); To use SET PASSWORD on Unix, do this: shell> mysql -u root mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('new_password'); mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('new_password'); In the second SET PASSWORD statement, replace host_name with the name of the server host. This is the name that is specified in the Host column of the non-localhost record for root in the user table. If you don't know what hostname this is, issue the following statement before using SET PASSWORD: mysql> SELECT Host, User FROM mysql.user; Look for the record that has root in the User column and something other than localhost in the Host column. Then use that Host value in the second SET PASSWORD statement. 2 Anonymous aAccount rRemoval If you prefer to remove the anonymous accounts instead, do so as follows: shell> mysql -u root mysql> DROP USER ''; The DROP statement applies both to Windows and to Unix. On Windows, if you want to remove only the anonymous account that has the same privileges as root, do this instead: shell> mysql -u root mysql> DROP USER ''@'localhost'; That account allows anonymous access but has full privileges, so removing it improves security. 3 root Account pPassword aAssignment You can assign passwords to the root accounts in several ways.

Here are several: *  

Use the SET PASSWORD statement * Use the mysqladmin command-line client program * Use the UPDATE statement To assign passwords using SET PASSWORD, connect to the server as root and issue two SET PASSWORD statements. Be sure to encrypt the password using the PASSWORD() function. For Windows, do this: shell> mysql -u root mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password'); mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('new_password'); On Unix-based system do this: shell> mysql -u root mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password'); mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('new_password'); In the second SET PASSWORD statement, replace host_name with the name of the server host. This is the same hostname that you used when you assigned the anonymous account passwords. To assign passwords to the root accounts using mysqladmin, execute the following commands: shell> mysqladmin -u root password "new_password" shell> mysqladmin -u root -h host_name password "new_password" These commands apply both to Windows and to Unix. In the second command, replace host_name with the name of the server host. The double quotes around the password are not always necessary, but you should use them if the password contains spaces or other characters that are special to your command interpreter. You can also use UPDATE to modify the user table directly. The following UPDATE statement assigns a password to both root accounts at once: shell> mysql -u root mysql> UPDATE mysql.user SET Password = PASSWORD('new_password') WHERE User = 'root'; mysql> FLUSH PRIVILEGES; The UPDATE statement applies both to Windows and to Unix. After the passwords have been set, you must supply the appropriate password whenever you connect to the server. For example, if you want to use mysqladmin to shut down the server, you can do so using this command: shell> mysqladmin -u root -p shutdown Enter password: (enter root password ) @@@Keith: Is it necessary to have enter root italic? If it is not part of the password it should be regular text. Ed@@@ @@@Keith: what about mysql_secure_installation ? TE@@@ 3 Automated sStartup (aAll pPlatforms) Both Windows and Unix-based servers have methods that allow for MySQL server to start automatically when the host server is booted. With Windows-based servers you can configure MySQL as a Service. Unix-based servers startup options will vary somewhat depending on the host server configuration. 1 Starting and sStopping MySQL aAutomatically You start the mysqld server several ways: * Invoking mysqld directly. This works on any platform. * Invoking the

 

mysqld_safe script which tries to determine the proper options for mysqld and then runs it with those options. This script is used on Unix and Unix-like systems. * On the Windows platform you can run MySQL server as a Windows service. The service can be set to start the server automatically when the host server starts or

you can start it manually. For setup see SECTION ON SERVICE. @@@Keith: Is this the exact title of the section? Are you referring to a section in this chapter? This has got to be clear to the reader. Ed@@@ * On Apple Macintosh  

OS X you can install a MySQL Startup Item package to automatically start MySQL on system startup. * By invoking mysql.server. This script is used primarily at system startup and shutdown on systems that use System V style run directories. It is typically installed under the name mysql. The mysql.server script starts the server by invoking mysqld_safe.

 

To start or stop the server manually using the mysql.server script, invoke it with a start or stop arguments: shell> mysql.server start shell> mysql.server stop

 

If you want the MySQL server to run as a specific user you should add an appropriate user option to the [mysqld] group of the /etc/my.cnf option file.

 

(It is possible that you will need to edit mysql.server if you've installed a binary distribution of MySQL in a non-standard location. Modify it to CDcd into the proper directory before it runs mysqld_safe. If you do this, your modified version of mysql.server may be overwritten if you upgrade MySQL in the future, so you should make a copy of your edited version that you can reinstall) mysql.server stop stops the server by sending a signal to it. You can also stop the server manually by executing mysqladmin shutdown. To start and stop MySQL automatically on your server, you need to add start and stop commands to the appropriate places in your /etc/rc* files. If you use the GNU/Linux server rpm package (MySQL-server- VERSION.rpm), the mysql.server script is installed in the /etc/init.d directory with the name mysql. You need not install it manually.

 

Some vendors provide rpm packages that install a startup script under a different name such as mysqld. If you install MySQL from a source distribution or using a binary distribution format that does not install mysql.server automatically, you can install it manually. The script can be found in the support- files directory under the MySQL installation directory or in a MySQL source tree. To install mysql.server manually, copy it to the /etc/init.d directory with the name mysql, and then make it executable. Do this by changing location into the appropriate directory where mysql.server is located and executing these commands: shell> cp mysql.server /etc/init.d/mysql shell> chmod +x /etc/init.d/mysql Older Red Hat systems use the /etc/rc.d/init.d directory rather than /etc/init.d. Adjust the preceding commands accordingly. Alternatively, first create /etc/init.d as a symbolic link that points to /etc/rc.d/init.d: shell> cd /etc shell> ln -s rc.d/init.d After installing the script, the commands needed to activate it to run at system startup depend on your operating system. On

Red Hat based GNU/Linux distributions  

you can use chkconfig: shell> chkconfig --add mysql On some GNU/Linux systems, the following command also seems to be necessary to fully enable the mysql script: shell> chkconfig --level 345 mysql on On FreeBSD, startup scripts generally should go in /usr/local/etc/rc.d/. The rc(8) manual page states that scripts in this directory are executed only if their basename matches the *.sh shell filename pattern. Any other files or directories present within the directory are silently ignored. In other words, on FreeBSD, you should install the mysql.server script as /usr/local/etc/rc.d/mysql.server.sh to enable automatic startup. As an alternative to the preceding setup, some operating systems also use /etc/rc.local or /etc/init.d/boot.local to start additional services on startup. To start up MySQL using this method, you could append a command like the one following to the appropriate startup file: /bin/sh -c 'cd /usr/local/mysql; ./bin/mysqld_safe --user=mysql &' For other systems, consult your operating system documentation to see how to install startup scripts. You can add options for mysql.server in a global /etc/my.cnf file. A typical /etc/my.cnf file might look like this: [mysqld] datadir=/usr/local/mysql/data socket=/tmp/mysql.sock port=3306 user=mysql [mysql.server] basedir=/usr/local/mysql The mysql.server script understands

 

only start and stop as command- line arguments.

In addition  

the mysql.server script understands the options of basedir, datadir, and pid-file if specified

 

in an option file, not on the command line.

Scripted Installation After you have performed a few installations of MySQL server you might wonder if there is a way to automate the process. If you are working on a Unix-based server and have some ingenuity and willingness to do some work up front the answer is yes. The solution is to automate the process using a scripting language such as bash or perl.. Any process that is performed manually can be error prone. Scripting a process properly will increase the reliability of the process. While it would be very difficult, if not impossible, to write a script that would cover every installation platform it is fairly straightforward to write a script for a specific environment. This also allows for a high degree of customization for the installation environment. @@@Keith: Should Perl be initial cap? Ed@@@ As an example, weI wrote the following script for a company that uses  

the Debian GNU/Linux distribution (http://www.debian.org)

as their server operating system. For MySQL installations they use the binary releases that Sun compiles and makes available on their website. In addition they use Nagios (http://www.nagios.org) for server monitoring. After performing the  

installation and initial configuration of the MySQL server weI had the

script install Nagios. It is one less step that would ordinarily be performed manually. #!/bin/bash # assumptions: this script is run from the same directory # where the installation files are located # # # preparation for installation # apt-get update apt-get install -y --force-yes libstdc++5 nscd useradd mysql --home=/var/lib/mysql --shell=/bin/false # # get the tar file, unpack it and soft-link it to /usr/local/mysql # cp mysql 

-5.0.45-linux-x86_64-icc-glibc23.tar.gz

/usr/local gunzip /usr/local/mysql 

-5.0.45-linux-x86_64-icc-glibc23.tar.gz cd /usr/local tar -xvf mysql-5.0.45-linux-x86_64-icc-glibc23.tar

 

ln -s /usr/local/mysql-5.0.45-

linux-x86_64-icc-glibc23  

/usr/local/mysql chown -R mysql:mysql /usr/local/mysql-

 

5.0.45-linux-x86_64-icc- glibc23/

# # set up the my.cnf file in the "debian" way # mkdir /etc/mysql cp /var/backup/mysql_install_files/my.cnf.8gb.master /etc/mysql/my.cnf chown -R mysql:mysql /etc/mysql ln -s /etc/mysql/my.cnf /etc/ # # create the data and logging directories # mkdir /data/mysql cd /var/lib  

ln -s /data/mysql /var/lib/mysql chown mysql:mysql /data/mysql

 

mkdir /var/log/mysql chown mysql:mysql /var/log/mysql

# # miscellaneous setup # mkdir  

/usr/share/mysql mkdir /usr/share/mysql/english cp /usr/local/mysql/share/mysql/english/errmsg.sys /usr/share/mysql/english chown -R mysql:mysql /usr/share/mysql

# # grant file setup #  

cd /usr/local/mysql scripts/mysql_install_db --user=mysql

# # startup file (debian setup) and socket directory #  

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

echo -e '46d\na\nbasedir="/usr/local/mysql"\numask 0000\n.\n281a\n ulimit -n 4096\n.\nwq\n' | ed -s  

/etc/init.d/mysql chmod 755 /etc/init.d/mysql

 

mkdir /var/run/mysqld chmod 775 /var/run/mysqld/ chown mysql:mysql /var/run/mysqld

/etc 

/init.d/mysql start /usr/local/mysql/bin/mysqladmin -u root password

'ndbsRF!' perl -pi.bak -e 's 

#PATH(.*)"$#PATH$1:/usr/local/mysql/bin"#' /etc/profile #This means you can just type mysql to start the client program #instead of typing out the full pathname. # # adding paths to various config files # echo 'export PATH="$PATH:/usr/local/mysql/bin"' >> /etc/

bash.bashrc # # # install mytop # apt-get install mytop # # adding nagios user #  

/usr/local/mysql/bin/mysql -uroot -p'root_password'

-e "GRANT USAGE ON *.* TO 'nagios'@'%';" # # setting up support files # cp /var/backup/mysql_install_files/*.pl /usr/local/bin/ cp /var/backup/mysql_install_files/*.php /usr/local/bin/ cp /var/backup/mysql_install_files/*.sh /usr/local/bin/ cp /var/backup/mysql_install_files/mysqlstats /usr/local/bin/ cp /var/backup/mysql_install_files/mirror-admin /etc/cron.d/ cp /var/backup/mysql_install_files/iostat /usr/local/bin/ cp /var/backup/mysql_install_files/flush_hosts /etc/cron.d/ cp /var/backup/mysql_install_files/mysql-server-50 /etc/cron.daily/ cp /var/backup/mysql_install_files/debian.cnf /etc/mysql/ cp /var/backup/mysql_install_files/debian-log-rotate.cnf /etc/mysql/ While we do not have the space to go through this line by line it is pretty straightforward. Using an nfs-mounted central location (/var/backup/mysql_install_files) weI copied the various files to the appropriate locations and performed any needed modifications such as changing ownership of necessary files to the mysql user. Essentially weI walked through the installation process by hand as weI had been doing in the past and put each step that weI did by hand into the script. While the script is not elegant, and there is significant room for improvement, it does the function required. OurMy installation time went from around thirty minutes to about three minutes. In addition, the reliability of the installation was improved. @@@Keith: Will all readers know what is meant by nfs mounted? Ed@@@ Upgrading As a rule MySQL recommends that when you upgrade  

from one release series to another, you should go to the next series rather than skipping a series. If you wish to upgrade from a release series previous to MySQL 5.0, you should upgrade to each successive release series in turn until you have reached MySQL 5.0, and then proceed with the upgrade to MySQL 5.1. For example, if you currently are running MySQL 4.0 and wish to upgrade to a newer series, upgrade to MySQL 4.1 first before upgrading to 5.0, and so forth.

@@@Keith: are you assuming 5.1 will be the release of choice by the time the book is written? Even if it's GA that might not be true.TE@@@  

If you are cautious about using new versions, you can always rename your old mysqld before installing a newer one. For example, if you are using MySQL 5.0.45 and want to upgrade to 5.1.25, rename your current server from mysqld to mysqld-5.0.45. If your new mysqld then does something unexpected, you can simply shut it down and restart with your old mysqld. If problems occur, such as that the new mysqld server does not start or that you cannot connect without a password, verify that you do not have an old my.cnf file from your previous installation. You can check this with the --print-defaults option (for example, mysqld --print- defaults). If this command displays anything other than the program name, you have an active my.cnf file that affects server or client operation. It is a good idea to rebuild and reinstall the Perl DBD::mysql module whenever you install a new release of MySQL. The same applies to other MySQL interfaces as well, such as the PHP mysql extension and the Python MySQLdb module.

1 Upgrading MySQL fFrom oOne rRelease sSeries to the nNext While Sun makes every effort to protect from data loss during upgrades,  

it is still good practice to back up your data before installing any new version of software. MySQL recommends that you dump and reload your tables

when doing a release series upgrade. This could be  

from version 5.0 to version 5.1 or from version 5.1 to version 6.0

@@@Keith: you are mixing your Sun's and MySQL's TE@@@  

In general you should do the following when upgrading from

one release series to the next: * The  

change history describes significant new features you can use in a new server version that differs from those found in the previous release series. Some of these changes may result in incompatibilities. Note any changes that are marked Known issue or Incompatible change. These incompatibilities with earlier versions of MySQL may require your attention before you upgrade.

* Use a dump and reload procedure to update your data during the upgrade. A dump and reload is simply using the mysqldump tool to dump all your databases and tables before upgrading. After the installation is upgraded you reload the the dump file to to recreate the tables after upgrading *  

After you upgrade to a new version of MySQL you will need to run the mysql_upgrade script. This script will check your tables, and repair them if necessary. It will also update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features.

* On a GUN/Linux distribution that is rpm-based  

if you are upgrading an installation originally produced by installing multiple rpm packages you should upgrade all the packages at the same time. For example, if you have both the server and client rpms installed, do not upgrade just the server rpm. 2 Upgrading MySQL

 

on Windows When upgrading MySQL on Windows you should use the following

steps:  

1. You should always back up your current MySQL installation before performing an upgrade. 2. Download the distribution to which you are upgrading from http://dev.mysql.com/downloads. 3. Before upgrading MySQL, you must stop the server. If the server is installed as a service, stop the service with the following command from the command prompt: C:\> net stop MySQL If you are not running the MySQL server as a service:

 

C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqladmin" -u root shutdown Note If the MySQL root user account has a password, you need to invoke mysqladmin with the -p option and supply the password when prompted. 4. When upgrading to MySQL 5.1 from a version previous to 4.1.5, or when upgrading from a version of MySQL installed from a Zip zip archive to a version of MySQL installed with the MySQL Installation Wizard, you must manually remove the previous installation and MySQL service (if the server is installed as a service). To remove the MySQL service, use the following command: C:\> C:\mysql\bin\mysqld --remove If you do not remove the existing service, the MySQL Installation Wizard may fail to properly install the new MySQL service. 5. When upgrading from MySQL 5.1.23 to MySQL 5.1.24, the change in the default location of the data directory from a directory within the MySQL installation to the AppData folder means that you must manually copy the data files from your old installation to the new location. 6. If you are using the MySQL Installation Wizard, start the wizard as described in Section 2.3.3, "Using the MySQL Installation Wizard". 7. If you are installing MySQL from a Zip archive, extract the archive. You may either overwrite your existing MySQL installation (usually located at C:\mysql), or install it into a different directory, such as C:\mysql5. Overwriting the existing installation is recommended. 8. If you were running MySQL as a Windows service and you had to remove the service earlier in this procedure, reinstall the service.

9. Restart the  

server. If you are running MySQL as a service use the 'net start MySQL' command. Otherwise,

invoke mysqld directly. 1  

Upgrading MySQL with the Installation Wizard The MySQL Installation Wizard can perform server upgrades automatically using the upgrade capabilities of MSI. That means you do not need to remove a previous installation manually before installing a new release. The installer automatically shuts down and removes the previous MySQL service before installing the new version. Automatic upgrades are available only when upgrading between installations that have the same major and minor version numbers. For example, you can upgrade automatically from MySQL 4.1.5 to MySQL 4.1.6, but not from MySQL 5.0 to MySQL 5.1.

 

Copying MySQL Databases to Another Machine You can copy the .frm, .MYI, and .MYD files for MyISAM tables between different architectures that support the same floating-point format. In cases where you need to transfer databases between different architectures, you can use mysqldump to create a file containing SQL statements. You can then transfer the dump file to the second machine and feed it as input to the mysql client.

The easiest  

way to move a database between two machines is to run the following commands on the machine on which the database is located: shell> mysqladmin -h 'other_hostname' create db_name shell> mysqldump --opt db_name | mysql -h 'other_hostname' db_name If you want to copy a database from a remote machine over a slow network, you can use these commands: shell> mysqladmin create db_name shell> mysqldump -h 'other_hostname' --opt --compress db_name | mysql db_name You can also store the dump in a file, transfer the file to the target machine, and then load the file into the database there. For example, you can dump a database to a compressed file on the source machine like this: shell> mysqldump --quick db_name | gzip > db_name.gz Transfer the db_name.gz file to the

 

target machine and run these commands there: shell> mysqladmin create db_name shell> gunzip < db_name.gz | mysql db_name You can also use mysqldump and mysqlimport to transfer the database. For large tables, this is much faster than simply using mysqldump. In the following commands, backup_directory represents the full pathname of the directory you use to store the output from mysqldump. First, create the directory for the output files and dump the database: shell> mkdir backup_directory shell> mysqldump --tab=backup_directory db_name Then transfer the files in the backup_directory directory to the target machine and load the files into MySQL there: shell> mysqladmin create db_name # create database shell> cat backup_directory/*.sql | mysql db_name shell> mysqlimport db_name backup_directory/*.txt Do not forget to copy the mysql database because that is where the grant tables are stored. Until you have

the mysql database in place, you will  

have to run commands as the MySQL root user on the new machine After you import the mysql database on the new machine, execute mysqladmin flush-privileges so that the server reloads the grant table information. Troubleshooting It can be frustrating when you perform an installation of MySQL server and it does not start properly. However, after you work with server installations you begin to see patterns that allow a database administrator to perform guided troubleshooting. These procedures will work on any installation. In order of most to least likely to resolve your problem areproblem is the following guidelines: * Check the error log to see why the server does not start. * Make sure that the server knows where to find the data directory. * Make sure you specify any special options needed by the storage engines

used. *  

Make sure that the server can access the data directory. The ownership and permissions of the data directory and its contents must be set so that the server can read and modify them. * Verify that the network interfaces the server uses are available.

The error log is going to be the first and most important place for troubleshooting any installation. Check it before doing any other troubleshooting as most often it will tell you why the server could not start.  

Some storage engines have options that control their behavior. You can a

 

specify startup options for the storage engines that you plan to use in the mysql configuration file. Storage engines will use default option values if you do not specify any values. Even so, it is recommended that you specify explicit values for those for which the defaults are not appropriate for your installation. When the mysqld server starts it will look for the data directory. If it

finds the designated directory, it will expect the various log files to be stored in the is directory and also the  

process ID (pid) file The data directory location, the location of the various log files and the pid file location can all be given alternate locations either from the command line with starting up myqld or from the my.cnf configuration file. @@@Keith: The "is" directory? Ed@@@ If the data directory is located somewhere on your system besides the

default location and you do not specify a new location in the [mysqld] or [mysqld_safe] sections of the mysql configuration file, the server  

will not be able to find the data directory and will not start up. You can determine what the default path settings are by invoking mysqld with the --verbose and --help options. If the default locations don't match the MySQL installation layout on your system, you can override them by specifying options to mysqld or mysqld_safe on the command line or in an option file. The

--datadir option is used to  

specify the location of the data directory explicitly. You can also specify the

 

location of the base directory under which MySQL is installed

using the --basedir option and mysqld will look for the data directory there.  

If mysqld is currently running, you can find out what path settings it is using by executing this command: shell> mysqladmin variables or: shell> mysqladmin -h host_name variables host_name is the name of the MySQL server host. If you get Errcode 13 (which means Permission denied) when starting mysqld, this means that the privileges of the data directory or its contents do not allow the server access. In this case, you change the permissions for the involved files and directories so that the server has the right to use them. You can also start the server as root, but this raises security issues and should be avoided.

@@@Keith: I would mention perror TE@@@ On Unix, display a listing of the  

data directory and check the ownership of the data directory and its contents to make sure the server has access. If the data directory is /usr/local/mysql/data, use this command: shell> ls -la /usr/local/mysql/data If the data directory or its files or subdirectories are not owned by the account that you use for running the server you need to change ownership to that account. If the account is named mysql, use these commands: shell> chown -R mysql /usr/local/mysql/data shell> chgrp -R mysql /usr/local/mysql/data If the server fails to start up correctly, check the error log. Log files are located in the data directory (typically C:\Program Files\MySQL\MySQL Server 5.1\data on Windows, /usr/local/mysql/data for a Unix binary distribution, and /usr/local/var for a Unix source distribution). Look in the data directory for files with names of the form host_name.err and host_name.log, where host_name is the name of your server host. Then examine the last few lines of these files. On Unix the tail command will display them: shell> tail host_name.err shell> tail host_name.log The error log should contain information that indicates why the server couldn't begin. If you see either of the following errors occurring it means that some other program

 

is using the TCP/IP port or Unix socket file that mysqld is trying to use: Can't start server: Bind on TCP/IP port: Address already in use Can't start server: Bind on unix socket... Use the ps command to determine if you have another MySQL server running. If so, shut down the second server before starting mysqld again. . @@@Keith: should ps be caps? Ed@@@ If no other server is running, try to execute the command telnet your_host_name tcp_ip_port_number. (The default MySQL port number is 3306.) Then press Enter a couple of times. If you don't get an error message like telnet: Unable to connect to remote host: Connection refused, some other program is using the TCP/IP port that mysqld is trying to use. You'll need to track down what program this is and disable it, or else tell mysqld to listen to a different port with the --port option. If you

use an alternate port number be sure  

to specify the port number for client programs when connecting to the server via TCP/IP. Another reason the port might be inaccessible is that you have a firewall running that blocks connections to it. If this is the

case you must  

modify the firewall settings to allow access to the port. If the server starts but you can't connect to it, you should make sure that you have an entry in /etc/hosts simlilar to this - 127.0.0.1 localhost @@@Keith, this

seems like one of the least likely problems preventing connecting to the server. And a non-working thread library is pretty unlikely too. I think it would be helpful to tell people how to tell the difference between various types of access-denied messages, e.g. explain these extremely common errors and how to tell what the difference is between them: baron@kanga:~$ mysql -ufoo ERROR 1045 (28000):  

Access denied for user 'foo'@'localhost' (using password: NO) baron@kanga:~$ mysql -pfoo ERROR 1045 (28000): Access denied for user 'baron'@'localhost' (using password: YES) baron@kanga:~$ mysql

-h 192.168.0.0  

ERROR 2003 (HY000): Can't connect to MySQL server on

'192.168.0.0' (113) baron@kanga:~$ mysql  

-p Enter password: ERROR 1045 (28000): Access denied for user 'baron'@'localhost' (using password: YES)

baron@kanga:~$  

mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

(2) TE@@@  

This problem occurs on systems that do not have a working thread library. In this case MySQL must be configured to use MITpthreads. 1 Troubleshooting a MySQL iInstallation uUnder Windows When installing and running MySQL for the first time, you may encounter certain errors that prevent the MySQL server from starting. The purpose of this section is to help you diagnose and correct some of these errors. Your first resource when troubleshooting server issues is the error log. The MySQL server uses the error log to record information relevant to the error that prevents the server from starting. The error log is located in the data directory specified in your my.ini file. The default data directory location is C:\Program Files\MySQL\MySQL Server 5.1\data.

 

Another source of information regarding possible errors is the console messages displayed when the MySQL service is starting. Use the NET START MySQL command from the command line after installing mysqld as a service to see any error messages regarding the starting of the MySQL server

 

as a service. The following examples show other common error messages you may encounter when installing MySQL and starting the server for the first time: . If the MySQL server cannot find the mysql privileges database or other critical files, you may see these messages: System error 1067 has occurred. Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist These messages often occur when the MySQL base or data directories are installed in different locations than the default locations. With MySQL server

5.1 these are  

C:\Program Files\MySQL\MySQL Server 5.1 and C:\Program Files\MySQL\MySQL Server 5.1\data, respectively. This situation may occur when MySQL is upgraded and installed to a new location, but the configuration file is not updated to reflect the new location. In addition, there may be old and new configuration files that conflict. Be sure to delete or rename any old configuration files when upgrading MySQL. If you have installed MySQL to a directory other than C:\Program Files\MySQL\MySQL Server 5.1, you need to ensure that the MySQL server is aware of this through the use of a configuration (my.ini) file. The my.ini file needs to be located in your Windows directory, typically C:\WINDOWS. You can determine its exact location from the value of the WINDIR environment variable by issuing the following command from the command prompt: C:\> echo %WINDIR% An option file can be created and modified with any text editor, such as Notepad. For example, if MySQL is installed in E:\mysql and the data directory is D:\MySQLdata, you can create the option file and set up a [mysqld] section to specify values for the basedir and datadir parameters: [mysqld] # set basedir to your installation path basedir=E:/mysql # set datadir to the location of your data directory datadir=D:/MySQLdata Note that Windows pathnames are specified in option files using (forward) slashes rather than backslashes. If you do use backslashes, you must double them: [mysqld] # set basedir to your installation path basedir=C:\\Program Files\\MySQL\\MySQL Server 5.1 # set datadir to the location of your data directory datadir=D:\\MySQLdata If you change the datadir value in your MySQL configuration file, you must move the contents of the existing MySQL data directory before restarting the MySQL server. . If you reinstall or upgrade MySQL without first stopping and removing the existing MySQL service and install MySQL using the MySQL Configuration Wizard, you may see this error: Error: Cannot create Windows service for MySql. Error: 0 This occurs when the Configuration Wizard tries to install the service and finds an existing service with the same name. One solution to this problem is to choose a service name other than mysql when using the configuration wizard. This allows the new service to be installed correctly, but leaves the outdated service in place. Although this is harmless, it is best to remove old services that are no longer in use. To permanently remove the old mysql service, execute the following command as a user with administrative privileges, on the command-line: C:\> sc delete mysql [SC] DeleteService SUCCESS If the sc utility is not available for your version of Windows, download the delsrv utility from http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/del srv-o.asp and use the delsrv mysql syntax.

SummaryConclusion Installations of MySQL can be quite challenging. Upgrades can bring their own issues, but these tend to be less noticeable because they are often due to incompatible changes between MySQL server versions. Everything looks fine and the server runs smoothly . Do not underestimate the importance of these changes. As we talked about this is why the change logs that Sun publishes are so important. @@@Keith: A bullet list highlighting what has been learned should conclude the chapter. Ed@@@ Glossary words: Source code Binary file @@@Keith: Do mean these words to be used in the index? The book is not going to have a "glossary." Ed@@@