MySQL 6.0 Reference Manual
MySQL 6.0 Reference Manual
Abstract
This is the MySQL Reference Manual. It documents MySQL 6.0 through 6.0.7.
MySQL Cluster is currently not supported in MySQL 6.0. For information about MySQL Cluster, please see MySQL
Cluster.
Document generated on: 2008-10-05 (revision: 11988)
The following table provides direct links into areas of the manual according to different topics. Links marked with »
link to online guides and other manuals for more information.
Table 1. Topic Quick Reference
Getting Star-
ted
Platforms Administrat-
ors
Developers Functionality Connectors HA/Scalability
Tutorial Linux/Unix » MySQL Ver-
sion Reference
» MySQL Ver-
sion Reference
Views Connector/J » HA/
Scalability
Guide
Installing » Mac OS X » Security SQL Syntax Stored Pro-
grams and
Views
Connector/
ODBC
MySQL and
DRBD
Upgrade » Windows » Startup/
Shutdown
Optimization Replication Connector/
NET
Memcached
Server Admin-
istration
» Solaris Partitioning Connectors &
APIs
Spatial Exten-
sions
PHP MySQL Proxy
FAQs » Building
from Source
» Backup and
Recovery
Functions and
Operators
Precision Math C API Replication
Copyright 1997-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the following terms: You may create a printed
copy of this documentation solely for your own personal use. Conversion to other formats is allowed as long as the actual content is not altered or
edited in any way. You shall not publish or distribute this documentation in any form or on any media, except if you distribute the documentation in
a manner similar to how MySQL disseminates it (that is, electronically for download on a Web site with the software) or on a CD-ROM or similar
medium, provided however that the documentation is disseminated together with the software on the same medium. Any other use, such as any dis-
semination of printed copies or use of this documentation, in whole or in part, in another publication, requires the prior written consent from an au-
thorized representative of MySQL AB. MySQL AB reserves any and all rights to this documentation not expressly granted above.
For more information on the terms of this license, for details on how the MySQL documentation is built and produced, or if you are interested in
doing a translation, please contact us at <docs@mysql.com>.
If you want help with using MySQL, please visit either the MySQL Forums or MySQL Mailing Lists where you can discuss your issues with other
MySQL users.
For additional documentation on MySQL products, including translations of the documentation into other languages, and downloadable versions in
variety of formats, including HTML, CHM and PDF formats, see MySQL Documentation Library.
Table of Contents
Preface ......................................................................................................................................xxiii
1. General Information .......................................................................................................................1
1.1. About This Manual ..............................................................................................................1
1.2. Conventions Used in This Manual ............................................................................................2
1.3. Overview of MySQL AB .......................................................................................................3
1.4. Overview of the MySQL Database Management System ..................................................................4
1.4.1. What is MySQL? ......................................................................................................4
1.4.2. History of MySQL ....................................................................................................5
1.4.3. The Main Features of MySQL .......................................................................................5
1.5. MySQL Development Roadmap ...............................................................................................7
1.5.1. What's New in MySQL 6.0 ..........................................................................................8
1.6. MySQL Information Sources ..................................................................................................9
1.6.1. MySQL Mailing Lists .................................................................................................9
1.6.2. MySQL Community Support at the MySQL Forums .......................................................... 11
1.6.3. MySQL Community Support on Internet Relay Chat (IRC) .................................................. 11
1.6.4. MySQL Enterprise .................................................................................................. 12
1.7. How to Report Bugs or Problems ........................................................................................... 12
1.8. MySQL Standards Compliance .............................................................................................. 15
1.8.1. What Standards MySQL Follows ................................................................................. 15
1.8.2. Selecting SQL Modes ............................................................................................... 15
1.8.3. Running MySQL in ANSI Mode .................................................................................. 16
1.8.4. MySQL Extensions to Standard SQL ............................................................................. 16
1.8.5. MySQL Differences from Standard SQL ........................................................................ 18
1.8.6. How MySQL Deals with Constraints ............................................................................. 23
1.9. Credits ........................................................................................................................... 25
1.9.1. Developers at MySQL AB ......................................................................................... 25
1.9.2. Contributors to MySQL ............................................................................................ 29
1.9.3. Documenters and translators ....................................................................................... 33
1.9.4. Libraries used by and included with MySQL .................................................................... 34
1.9.5. Packages that support MySQL ..................................................................................... 35
1.9.6. Tools that were used to create MySQL ........................................................................... 35
1.9.7. Supporters of MySQL .............................................................................................. 36
2. Installing and Upgrading MySQL ..................................................................................................... 37
2.1. General Installation Issues .................................................................................................... 37
2.1.1. Operating Systems Supported by MySQL Community Server ............................................... 38
2.1.2. Choosing Which MySQL Distribution to Install ................................................................ 39
2.1.3. How to Get MySQL ................................................................................................. 46
2.1.4. Verifying Package Integrity Using MD5 Checksums or GnuPG ............................................. 46
2.1.5. Installation Layouts ................................................................................................. 49
2.2. Standard MySQL Installation Using a Binary Distribution .............................................................. 51
2.3. Installing MySQL on Windows .............................................................................................. 51
2.3.1. Choosing An Installation Package ................................................................................ 52
2.3.2. Installing MySQL with the Automated Installer ................................................................ 52
2.3.3. Using the MySQL Installation Wizard ........................................................................... 53
2.3.4. Using the Configuration Wizard ................................................................................... 55
2.3.5. Installing MySQL from a Noinstall Zip Archive ................................................................ 59
2.3.6. Extracting the Install Archive ...................................................................................... 59
2.3.7. Creating an Option File ............................................................................................. 59
2.3.8. Selecting a MySQL Server Type .................................................................................. 60
2.3.9. Starting the Server for the First Time ............................................................................. 61
2.3.10. Starting MySQL from the Windows Command Line ......................................................... 62
2.3.11. Starting MySQL as a Windows Service ........................................................................ 62
2.3.12. Testing The MySQL Installation ................................................................................. 64
2.3.13. Troubleshooting a MySQL Installation Under Windows ..................................................... 64
2.3.14. Upgrading MySQL on Windows ................................................................................ 65
2.3.15. MySQL on Windows Compared to MySQL on Unix ......................................................... 66
2.4. Installing MySQL from RPM Packages on Linux ........................................................................ 68
2.5. Installing MySQL on Mac OS X ............................................................................................ 70
2.6. Installing MySQL on Solaris ................................................................................................. 72
2.7. Installing MySQL on NetWare .............................................................................................. 73
2.8. Installing MySQL from tar.gz Packages on Other Unix-Like Systems ............................................ 74
2.9. MySQL Installation Using a Source Distribution ......................................................................... 77
2.9.1. Source Installation Overview ...................................................................................... 77
2.9.2. Typical configure Options ..................................................................................... 80
iv
2.9.3. Installing from the Development Source Tree ................................................................... 86
2.9.4. Dealing with Problems Compiling MySQL ..................................................................... 88
2.9.5. MIT-pthreads Notes ................................................................................................. 90
2.9.6. Installing MySQL from Source on Windows .................................................................... 91
2.9.7. Compiling MySQL Clients on Windows ......................................................................... 94
2.10. Post-Installation Setup and Testing ........................................................................................ 94
2.10.1. Windows Post-Installation Procedures .......................................................................... 95
2.10.2. Unix Post-Installation Procedures ............................................................................... 96
2.10.3. Securing the Initial MySQL Accounts .........................................................................104
2.11. Upgrading MySQL ..........................................................................................................106
2.11.1. Upgrading from MySQL 5.1 to 6.0 .............................................................................107
2.11.2. Copying MySQL Databases to Another Machine ............................................................110
2.12. Downgrading MySQL ......................................................................................................111
2.12.1. Downgrading to MySQL 5.0 ....................................................................................111
2.13. Operating System-Specific Notes .........................................................................................111
2.13.1. Linux Notes ........................................................................................................111
2.13.2. Mac OS X Notes ..................................................................................................117
2.13.3. Solaris Notes .......................................................................................................117
2.13.4. BSD Notes .........................................................................................................120
2.13.5. Other Unix Notes .................................................................................................123
2.14. Environment Variables .....................................................................................................135
2.15. Perl Installation Notes ......................................................................................................135
2.15.1. Installing Perl on Unix ...........................................................................................136
2.15.2. Installing ActiveState Perl on Windows .......................................................................136
2.15.3. Problems Using the Perl DBI/DBD Interface ..................................................................137
3. Tutorial ...................................................................................................................................139
3.1. Connecting to and Disconnecting from the Server .......................................................................139
3.2. Entering Queries ..............................................................................................................140
3.3. Creating and Using a Database .............................................................................................142
3.3.1. Creating and Selecting a Database ...............................................................................143
3.3.2. Creating a Table ....................................................................................................143
3.3.3. Loading Data into a Table .........................................................................................144
3.3.4. Retrieving Information from a Table ............................................................................145
3.4. Getting Information About Databases and Tables .......................................................................155
3.5. Using mysql in Batch Mode ...............................................................................................156
3.6. Examples of Common Queries .............................................................................................157
3.6.1. The Maximum Value for a Column ..............................................................................157
3.6.2. The Row Holding the Maximum of a Certain Column ........................................................158
3.6.3. Maximum of Column per Group .................................................................................158
3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field ...........................................158
3.6.5. Using User-Defined Variables ....................................................................................159
3.6.6. Using Foreign Keys ................................................................................................159
3.6.7. Searching on Two Keys ...........................................................................................160
3.6.8. Calculating Visits Per Day ........................................................................................160
3.6.9. Using AUTO_INCREMENT .......................................................................................161
3.7. Queries from the Twin Project ..............................................................................................162
3.7.1. Find All Non-distributed Twins ..................................................................................162
3.7.2. Show a Table of Twin Pair Status ................................................................................164
3.8. Using MySQL with Apache .................................................................................................164
4. MySQL Programs .......................................................................................................................165
4.1. Overview of MySQL Programs .............................................................................................165
4.2. Using MySQL Programs .....................................................................................................168
4.2.1. Invoking MySQL Programs .......................................................................................168
4.2.2. Connecting to the MySQL Server ................................................................................169
4.2.3. Specifying Program Options ......................................................................................172
4.2.4. Setting Environment Variables ...................................................................................180
4.3. MySQL Server and Server-Startup Programs .............................................................................181
4.3.1. mysqld — The MySQL Server .................................................................................181
4.3.2. mysqld_safe — MySQL Server Startup Script ............................................................181
4.3.3. mysql.server — MySQL Server Startup Script ..........................................................185
4.3.4. mysqld_multi — Manage Multiple MySQL Servers .....................................................186
4.4. MySQL Installation-Related Programs ....................................................................................189
4.4.1. comp_err — Compile MySQL Error Message File .........................................................189
4.4.2. make_win_bin_dist — Package MySQL Distribution as ZIP Archive ..............................190
4.4.3. mysqlbug — Generate Bug Report ............................................................................191
4.4.4. mysql_fix_privilege_tables — Upgrade MySQL System Tables ..............................191
4.4.5. mysql_install_db — Initialize MySQL Data Directory ...............................................192
4.4.6. mysql_secure_installation — Improve MySQL Installation Security .........................192
4.4.7. mysql_tzinfo_to_sql — Load the Time Zone Tables ................................................193
4.4.8. mysql_upgrade — Check Tables for MySQL Upgrade ..................................................193
MySQL 6.0 Reference Manual
v
