MySQL 5.7 Reference Manual
Including MySQL Cluster NDB 7.5
Abstract
This is the MySQL™ Reference Manual. It documents MySQL 5.7 through 5.7.17, as well as MySQL Cluster
releases based on version 7.5 of NDB through 5.7.16-ndb-7.5.5, respectively.
MySQL 5.7 features. This manual describes features that are not included in every edition of MySQL 5.7; such
features may not be included in the edition of MySQL 5.7 licensed to you. If you have any questions about the
features included in your edition of MySQL 5.7, refer to your MySQL 5.7 license agreement or contact your Oracle
sales representative.
For notes detailing the changes in each release, see the MySQL 5.7 Release Notes.
For legal information, see the Legal Notices.
For 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 and PDF formats, see the MySQL
Documentation Library.
Licensing information—MySQL 5.7. This product may include third-party software, used under license. If you
are using a Commercial release of MySQL 5.7, see this document for licensing information, including licensing
information relating to third-party software that may be included in this Commercial release. If you are using a
Community release of MySQL 5.7, see this document for licensing information, including licensing information
relating to third-party software that may be included in this Community release.
Licensing information—MySQL Cluster. This product may include third-party software, used under license.
If you are using a Community release of MySQL Cluster NDB 7.5, see this document for licensing information,
including licensing information relating to third-party software that may be included in this Community release.
Document generated on: 2016-10-04 (revision: 49305)
iii
Table of Contents
Preface and Legal Notices ......................................................................................................... xxv
1 General Information ................................................................................................................... 1
1.1 About This Manual .......................................................................................................... 2
1.2 Typographical and Syntax Conventions ........................................................................... 2
1.3 Overview of the MySQL Database Management System ................................................... 4
1.3.1 What is MySQL? .................................................................................................. 4
1.3.2 The Main Features of MySQL ............................................................................... 5
1.3.3 History of MySQL ................................................................................................. 8
1.4 What Is New in MySQL 5.7 ............................................................................................. 9
1.5 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL
5.7 ...................................................................................................................................... 20
1.6 MySQL Information Sources .......................................................................................... 28
1.6.1 MySQL Mailing Lists ........................................................................................... 28
1.6.2 MySQL Community Support at the MySQL Forums .............................................. 31
1.6.3 MySQL Community Support on Internet Relay Chat (IRC) .................................... 31
1.6.4 MySQL Enterprise .............................................................................................. 31
1.7 How to Report Bugs or Problems .................................................................................. 31
1.8 MySQL Standards Compliance ...................................................................................... 36
1.8.1 MySQL Extensions to Standard SQL ................................................................... 37
1.8.2 MySQL Differences from Standard SQL .............................................................. 40
1.8.3 How MySQL Deals with Constraints .................................................................... 42
1.9 Credits .......................................................................................................................... 45
1.9.1 Contributors to MySQL ....................................................................................... 45
1.9.2 Documenters and translators .............................................................................. 49
1.9.3 Packages that support MySQL ............................................................................ 51
1.9.4 Tools that were used to create MySQL ............................................................... 51
1.9.5 Supporters of MySQL ......................................................................................... 52
2 Installing and Upgrading MySQL .............................................................................................. 53
2.1 General Installation Guidance ........................................................................................ 55
2.1.1 Which MySQL Version and Distribution to Install .................................................. 55
2.1.2 How to Get MySQL ............................................................................................ 57
2.1.3 Verifying Package Integrity Using MD5 Checksums or GnuPG .............................. 57
2.1.4 Installation Layouts ............................................................................................. 66
2.1.5 Compiler-Specific Build Characteristics ................................................................ 66
2.2 Installing MySQL on Unix/Linux Using Generic Binaries .................................................. 66
2.3 Installing MySQL on Microsoft Windows ......................................................................... 69
2.3.1 MySQL Installation Layout on Microsoft Windows ................................................ 72
2.3.2 Choosing An Installation Package ....................................................................... 72
2.3.3 Installing MySQL on Microsoft Windows Using MySQL Installer ............................ 73
2.3.4 MySQL Notifier ................................................................................................. 102
2.3.5 Installing MySQL on Microsoft Windows Using a noinstall Zip Archive .................. 112
2.3.6 Troubleshooting a Microsoft Windows MySQL Server Installation ........................ 120
2.3.7 Windows Postinstallation Procedures ................................................................ 122
2.3.8 Upgrading MySQL on Windows ........................................................................ 124
2.4 Installing MySQL on OS X ........................................................................................... 125
2.4.1 General Notes on Installing MySQL on OS X ..................................................... 126
2.4.2 Installing MySQL on OS X Using Native Packages ............................................. 127
2.4.3 Installing a MySQL Launch Daemon ................................................................. 132
2.4.4 Installing and Using the MySQL Preference Pane .............................................. 135
2.5 Installing MySQL on Linux ........................................................................................... 140
2.5.1 Installing MySQL on Linux Using the MySQL Yum Repository ............................. 141
2.5.2 Replacing a Third-Party Distribution of MySQL Using the MySQL Yum
Repository ................................................................................................................ 145
2.5.3 Installing MySQL on Linux Using the MySQL APT Repository ............................. 148
2.5.4 Installing MySQL on Linux Using the MySQL SLES Repository ........................... 148
MySQL 5.7 Reference Manual
iv
2.5.5 Installing MySQL on Linux Using RPM Packages from Oracle ............................. 148
2.5.6 Installing MySQL on Linux Using Debian Packages from Oracle .......................... 153
2.5.7 Installing MySQL on Linux from the Native Software Repositories ........................ 154
2.5.8 Installing MySQL on Linux with docker .............................................................. 158
2.5.9 Installing MySQL on Linux with juju ................................................................... 158
2.5.10 Managing MySQL Server with systemd ........................................................... 158
2.6 Installing MySQL Using Unbreakable Linux Network (ULN) ........................................... 162
2.7 Installing MySQL on Solaris and OpenSolaris ............................................................... 162
2.7.1 Installing MySQL on Solaris Using a Solaris PKG .............................................. 163
2.7.2 Installing MySQL on OpenSolaris Using IPS ...................................................... 164
2.8 Installing MySQL on FreeBSD ..................................................................................... 165
2.9 Installing MySQL from Source ..................................................................................... 166
2.9.1 MySQL Layout for Source Installation ................................................................ 168
2.9.2 Installing MySQL Using a Standard Source Distribution ...................................... 168
2.9.3 Installing MySQL Using a Development Source Tree .......................................... 172
2.9.4 MySQL Source-Configuration Options ............................................................... 174
2.9.5 Dealing with Problems Compiling MySQL .......................................................... 193
2.9.6 MySQL Configuration and Third-Party Tools ...................................................... 195
2.10 Postinstallation Setup and Testing ............................................................................. 195
2.10.1 Initializing the Data Directory ........................................................................... 195
2.10.2 Starting the Server ......................................................................................... 203
2.10.3 Testing the Server .......................................................................................... 206
2.10.4 Securing the Initial MySQL Accounts ............................................................... 208
2.10.5 Starting and Stopping MySQL Automatically .................................................... 212
2.11 Upgrading or Downgrading MySQL ............................................................................ 213
2.11.1 Upgrading MySQL .......................................................................................... 213
2.11.2 Downgrading MySQL ...................................................................................... 229
2.11.3 Checking Whether Tables or Indexes Must Be Rebuilt ...................................... 236
2.11.4 Rebuilding or Repairing Tables or Indexes ....................................................... 236
2.11.5 Copying MySQL Databases to Another Machine .............................................. 238
2.12 Environment Variables ............................................................................................... 239
2.13 Perl Installation Notes ................................................................................................ 240
2.13.1 Installing Perl on Unix ..................................................................................... 240
2.13.2 Installing ActiveState Perl on Windows ............................................................ 241
2.13.3 Problems Using the Perl DBI/DBD Interface ..................................................... 242
3 Using MySQL as a Document Store ....................................................................................... 243
3.1 Preproduction Status — Legal Notice ........................................................................... 244
3.2 Key Concepts ............................................................................................................. 244
3.3 Setting Up MySQL as a Document Store ..................................................................... 245
3.3.1 Installing MySQL Shell ...................................................................................... 247
3.3.2 Starting MySQL Shell ....................................................................................... 251
3.4 Quick-Start Guide: MySQL Shell for JavaScript ............................................................ 251
3.4.1 Introduction ...................................................................................................... 252
3.4.2 Import Database Sample .................................................................................. 253
3.4.3 MySQL Shell .................................................................................................... 253
3.4.4 Documents and Collections ............................................................................... 255
3.4.5 Relational Tables .............................................................................................. 265
3.4.6 Documents in Tables ........................................................................................ 270
3.5 Quick-Start Guide: MySQL Shell for Python .................................................................. 271
3.5.1 Introduction ...................................................................................................... 272
3.5.2 Import Database Sample .................................................................................. 273
3.5.3 MySQL Shell .................................................................................................... 273
3.5.4 Documents and Collections ............................................................................... 275
3.5.5 Relational Tables .............................................................................................. 285
3.5.6 Documents in Tables ........................................................................................ 290
3.6 Quick-Start Guide: MySQL for Visual Studio ................................................................. 291
3.7 X Plugin ...................................................................................................................... 293
3.7.1 Using Secure Connections with X Plugin ........................................................... 293
MySQL 5.7 Reference Manual
v
3.7.2 X Plugin Options and Variables ......................................................................... 293
3.7.3 Monitoring X Plugin .......................................................................................... 299
3.8 MySQL Shell User Guide ............................................................................................ 302
3.8.1 MySQL Shell Features ...................................................................................... 302
3.8.2 Getting Started with MySQL Shell ..................................................................... 304
3.8.3 MySQL Shell Code Execution ........................................................................... 311
3.8.4 Configuring MySQL Shell .................................................................................. 316
3.8.5 Stored Sessions ............................................................................................... 318
3.8.6 MySQL Shell Application Log ............................................................................ 321
3.8.7 Customizing MySQL Shell ................................................................................ 322
4 Tutorial .................................................................................................................................. 327
4.1 Connecting to and Disconnecting from the Server ......................................................... 327
4.2 Entering Queries ......................................................................................................... 328
4.3 Creating and Using a Database ................................................................................... 331
4.3.1 Creating and Selecting a Database ................................................................... 332
4.3.2 Creating a Table .............................................................................................. 333
4.3.3 Loading Data into a Table ................................................................................ 334
4.3.4 Retrieving Information from a Table ................................................................... 335
4.4 Getting Information About Databases and Tables ......................................................... 348
4.5 Using mysql in Batch Mode ......................................................................................... 349
4.6 Examples of Common Queries .................................................................................... 351
4.6.1 The Maximum Value for a Column .................................................................... 351
4.6.2 The Row Holding the Maximum of a Certain Column .......................................... 351
4.6.3 Maximum of Column per Group ........................................................................ 352
4.6.4 The Rows Holding the Group-wise Maximum of a Certain Column ....................... 352
4.6.5 Using User-Defined Variables ........................................................................... 353
4.6.6 Using Foreign Keys .......................................................................................... 353
4.6.7 Searching on Two Keys .................................................................................... 355
4.6.8 Calculating Visits Per Day ................................................................................. 355
4.6.9 Using AUTO_INCREMENT ............................................................................... 356
4.7 Using MySQL with Apache .......................................................................................... 358
5 MySQL Programs .................................................................................................................. 359
5.1 Overview of MySQL Programs ..................................................................................... 360
5.2 Using MySQL Programs .............................................................................................. 364
5.2.1 Invoking MySQL Programs ............................................................................... 364
5.2.2 Connecting to the MySQL Server ...................................................................... 365
5.2.3 Specifying Program Options .............................................................................. 368
5.2.4 Using Options on the Command Line ................................................................ 369
5.2.5 Program Option Modifiers ................................................................................. 370
5.2.6 Using Option Files ............................................................................................ 371
5.2.7 Command-Line Options that Affect Option-File Handling ..................................... 376
5.2.8 Using Options to Set Program Variables ............................................................ 377
5.2.9 Option Defaults, Options Expecting Values, and the = Sign ................................. 378
5.2.10 Setting Environment Variables ......................................................................... 381
5.3 MySQL Server and Server-Startup Programs ............................................................... 382
5.3.1 mysqld — The MySQL Server ......................................................................... 382
5.3.2 mysqld_safe — MySQL Server Startup Script ................................................. 383
5.3.3 mysql.server — MySQL Server Startup Script ............................................... 388
5.3.4 mysqld_multi — Manage Multiple MySQL Servers ......................................... 391
5.4 MySQL Installation-Related Programs .......................................................................... 395
5.4.1 comp_err — Compile MySQL Error Message File ............................................ 395
5.4.2 mysql_install_db — Initialize MySQL Data Directory .................................... 396
5.4.3 mysql_plugin — Configure MySQL Server Plugins ......................................... 406
5.4.4 mysql_secure_installation — Improve MySQL Installation Security ........... 408
5.4.5 mysql_ssl_rsa_setup — Create SSL/RSA Files ........................................... 411
5.4.6 mysql_tzinfo_to_sql — Load the Time Zone Tables ................................... 413
5.4.7 mysql_upgrade — Check and Upgrade MySQL Tables ................................... 414
5.5 MySQL Client Programs .............................................................................................. 421
- 1
- 2
- 3
- 4
- 5
前往页