MySQL 5.6 Reference Manual
   Next

MySQL 5.6 Reference Manual

Including MySQL NDB Cluster 7.3-7.4 Reference Guide

Abstract

This is the MySQL™ Reference Manual. It documents MySQL 5.6 through 5.6.48, as well as NDB Cluster releases based on versions 7.3 and 7.4 of NDB through 5.6.47-ndb-7.3.28 and 5.6.47-ndb-7.4.27, respectively. It may include documentation of features of MySQL versions that have not yet been released. For information about which versions have been released, see the MySQL 5.6 Release Notes.

MySQL 5.6 features.  This manual describes features that are not included in every edition of MySQL 5.6; such features may not be included in the edition of MySQL 5.6 licensed to you. If you have any questions about the features included in your edition of MySQL 5.6, refer to your MySQL 5.6 license agreement or contact your Oracle sales representative.

For notes detailing the changes in each release, see the MySQL 5.6 Release Notes.

For legal information, including licensing information, see the Preface and Legal Notices.

For help with using MySQL, please visit the MySQL Forums, where you can discuss your issues with other MySQL users.

Document generated on: 2020-03-05 (revision: 65236)

Table of Contents

Preface and Legal Notices
1 General Information
1.1 About This Manual
1.2 Typographical and Syntax Conventions
1.3 Overview of the MySQL Database Management System
1.3.1 What is MySQL?
1.3.2 The Main Features of MySQL
1.3.3 History of MySQL
1.4 What Is New in MySQL 5.6
1.5 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 5.6
1.6 MySQL Information Sources
1.6.1 MySQL Websites
1.6.2 MySQL Community Support at the MySQL Forums
1.6.3 MySQL Enterprise
1.7 How to Report Bugs or Problems
1.8 MySQL Standards Compliance
1.8.1 MySQL Extensions to Standard SQL
1.8.2 MySQL Differences from Standard SQL
1.8.3 How MySQL Deals with Constraints
1.9 Credits
1.9.1 Contributors to MySQL
1.9.2 Documenters and translators
1.9.3 Packages that support MySQL
1.9.4 Tools that were used to create MySQL
1.9.5 Supporters of MySQL
2 Installing and Upgrading MySQL
2.1 General Installation Guidance
2.1.1 Which MySQL Version and Distribution to Install
2.1.2 How to Get MySQL
2.1.3 Verifying Package Integrity Using MD5 Checksums or GnuPG
2.1.4 Installation Layouts
2.1.5 Compiler-Specific Build Characteristics
2.2 Installing MySQL on Unix/Linux Using Generic Binaries
2.3 Installing MySQL on Microsoft Windows
2.3.1 MySQL Installation Layout on Microsoft Windows
2.3.2 Choosing an Installation Package
2.3.3 MySQL Installer for Windows
2.3.4 Installing MySQL on Microsoft Windows Using a noinstall ZIP Archive
2.3.5 Troubleshooting a Microsoft Windows MySQL Server Installation
2.3.6 Windows Postinstallation Procedures
2.3.7 Windows Platform Restrictions
2.4 Installing MySQL on OS X
2.4.1 General Notes on Installing MySQL on OS X
2.4.2 Installing MySQL on OS X Using Native Packages
2.4.3 Installing a MySQL Launch Daemon
2.4.4 Installing and Using the MySQL Preference Pane
2.5 Installing MySQL on Linux
2.5.1 Installing MySQL on Linux Using the MySQL Yum Repository
2.5.2 Replacing a Third-Party Distribution of MySQL Using the MySQL Yum Repository
2.5.3 Installing MySQL on Linux Using the MySQL APT Repository
2.5.4 Installing MySQL on Linux Using the MySQL SLES Repository
2.5.5 Installing MySQL on Linux Using RPM Packages from Oracle
2.5.6 Installing MySQL on Linux Using Debian Packages from Oracle
2.5.7 Installing MySQL on Linux from the Native Software Repositories
2.5.8 Deploying MySQL on Linux with Docker
2.5.9 Installing MySQL on Linux with Juju
2.6 Installing MySQL Using Unbreakable Linux Network (ULN)
2.7 Installing MySQL on Solaris
2.7.1 Installing MySQL on Solaris Using a Solaris PKG
2.8 Installing MySQL on FreeBSD
2.9 Installing MySQL from Source
2.9.1 Source Installation Methods
2.9.2 Source Installation Prerequisites
2.9.3 MySQL Layout for Source Installation
2.9.4 Installing MySQL Using a Standard Source Distribution
2.9.5 Installing MySQL Using a Development Source Tree
2.9.6 Configuring SSL Library Support
2.9.7 MySQL Source-Configuration Options
2.9.8 Dealing with Problems Compiling MySQL
2.9.9 MySQL Configuration and Third-Party Tools
2.10 Postinstallation Setup and Testing
2.10.1 Initializing the Data Directory
2.10.2 Starting the Server
2.10.3 Testing the Server
2.10.4 Securing the Initial MySQL Accounts
2.10.5 Starting and Stopping MySQL Automatically
2.11 Upgrading MySQL
2.11.1 Before You Begin
2.11.2 Upgrade Paths
2.11.3 Changes in MySQL 5.6
2.11.4 Upgrading MySQL Binary or Package-based Installations on Unix/Linux
2.11.5 Upgrading MySQL with the MySQL Yum Repository
2.11.6 Upgrading MySQL with the MySQL APT Repository
2.11.7 Upgrading MySQL with the MySQL SLES Repository
2.11.8 Upgrading MySQL on Windows
2.11.9 Upgrade Troubleshooting
2.11.10 Rebuilding or Repairing Tables or Indexes
2.11.11 Copying MySQL Databases to Another Machine
2.12 Downgrading MySQL
2.12.1 Before You Begin
2.12.2 Downgrade Paths
2.12.3 Downgrade Notes
2.12.4 Downgrading Binary and Package-based Installations on Unix/Linux
2.12.5 Downgrade Troubleshooting
2.13 Perl Installation Notes
2.13.1 Installing Perl on Unix
2.13.2 Installing ActiveState Perl on Windows
2.13.3 Problems Using the Perl DBI/DBD Interface
3 Tutorial
3.1 Connecting to and Disconnecting from the Server
3.2 Entering Queries
3.3 Creating and Using a Database
3.3.1 Creating and Selecting a Database
3.3.2 Creating a Table
3.3.3 Loading Data into a Table
3.3.4 Retrieving Information from a Table
3.4 Getting Information About Databases and Tables
3.5 Using mysql in Batch Mode
3.6 Examples of Common Queries
3.6.1 The Maximum Value for a Column
3.6.2 The Row Holding the Maximum of a Certain Column
3.6.3 Maximum of Column per Group
3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column
3.6.5 Using User-Defined Variables
3.6.6 Using Foreign Keys
3.6.7 Searching on Two Keys
3.6.8 Calculating Visits Per Day
3.6.9 Using AUTO_INCREMENT
3.7 Using MySQL with Apache
4 MySQL Programs
4.1 Overview of MySQL Programs
4.2 Using MySQL Programs
4.2.1 Invoking MySQL Programs
4.2.2 Specifying Program Options
4.2.3 Command Options for Connecting to the Server
4.2.4 Connecting to the MySQL Server Using Command Options
4.2.5 Connection Compression Control
4.2.6 Setting Environment Variables
4.3 Server and Server-Startup Programs
4.3.1 mysqld — The MySQL Server
4.3.2 mysqld_safe — MySQL Server Startup Script
4.3.3 mysql.server — MySQL Server Startup Script
4.3.4 mysqld_multi — Manage Multiple MySQL Servers
4.4 Installation-Related Programs
4.4.1 comp_err — Compile MySQL Error Message File
4.4.2 mysqlbug — Generate Bug Report
4.4.3 mysql_install_db — Initialize MySQL Data Directory
4.4.4 mysql_plugin — Configure MySQL Server Plugins
4.4.5 mysql_secure_installation — Improve MySQL Installation Security
4.4.6 mysql_tzinfo_to_sql — Load the Time Zone Tables
4.4.7 mysql_upgrade — Check and Upgrade MySQL Tables
4.5 Client Programs
4.5.1 mysql — The MySQL Command-Line Client
4.5.2 mysqladmin — Client for Administering a MySQL Server
4.5.3 mysqlcheck — A Table Maintenance Program
4.5.4 mysqldump — A Database Backup Program
4.5.5 mysqlimport — A Data Import Program
4.5.6 mysqlshow — Display Database, Table, and Column Information
4.5.7 mysqlslap — Load Emulation Client
4.6 Administrative and Utility Programs
4.6.1 innochecksum — Offline InnoDB File Checksum Utility
4.6.2 myisam_ftdump — Display Full-Text Index information
4.6.3 myisamchk — MyISAM Table-Maintenance Utility
4.6.4 myisamlog — Display MyISAM Log File Contents
4.6.5 myisampack — Generate Compressed, Read-Only MyISAM Tables
4.6.6 mysql_config_editor — MySQL Configuration Utility
4.6.7 mysqlaccess — Client for Checking Access Privileges
4.6.8 mysqlbinlog — Utility for Processing Binary Log Files
4.6.9 mysqldumpslow — Summarize Slow Query Log Files
4.6.10 mysqlhotcopy — A Database Backup Program
4.6.11 mysql_convert_table_format — Convert Tables to Use a Given Storage Engine
4.6.12 mysql_find_rows — Extract SQL Statements from Files
4.6.13 mysql_fix_extensions — Normalize Table File Name Extensions
4.6.14 mysql_setpermission — Interactively Set Permissions in Grant Tables
4.6.15 mysql_waitpid — Kill Process and Wait for Its Termination
4.6.16 mysql_zap — Kill Processes That Match a Pattern
4.7 Program Development Utilities
4.7.1 msql2mysql — Convert mSQL Programs for Use with MySQL
4.7.2 mysql_config — Display Options for Compiling Clients
4.7.3 my_print_defaults — Display Options from Option Files
4.7.4 resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols
4.8 Miscellaneous Programs
4.8.1 perror — Display MySQL Error Message Information
4.8.2 replace — A String-Replacement Utility
4.8.3 resolveip — Resolve Host name to IP Address or Vice Versa
4.9 Environment Variables
4.10 Unix Signal Handling in MySQL
5 MySQL Server Administration
5.1 The MySQL Server
5.1.1 Configuring the Server
5.1.2 Server Configuration Defaults
5.1.3 Server Option, System Variable, and Status Variable Reference
5.1.4 Server System Variable Reference
5.1.5 Server Status Variable Reference
5.1.6 Server Command Options
5.1.7 Server System Variables
5.1.8 Using System Variables
5.1.9 Server Status Variables
5.1.10 Server SQL Modes
5.1.11 IPv6 Support
5.1.12 MySQL Server Time Zone Support
5.1.13 Server-Side Help Support
5.1.14 The Server Shutdown Process
5.2 The MySQL Data Directory
5.3 The mysql System Database
5.4 MySQL Server Logs
5.4.1 Selecting General Query Log and Slow Query Log Output Destinations
5.4.2 The Error Log
5.4.3 The General Query Log
5.4.4 The Binary Log
5.4.5 The Slow Query Log
5.4.6 The DDL Log
5.4.7 Server Log Maintenance
5.5 MySQL Server Plugins
5.5.1 Installing and Uninstalling Plugins
5.5.2 Obtaining Server Plugin Information
5.5.3 MySQL Enterprise Thread Pool
5.6 MySQL Server User-Defined Functions
5.6.1 Installing and Uninstalling User-Defined Functions
5.6.2 Obtaining User-Defined Function Information
5.7 Running Multiple MySQL Instances on One Machine
5.7.1 Setting Up Multiple Data Directories
5.7.2 Running Multiple MySQL Instances on Windows
5.7.3 Running Multiple MySQL Instances on Unix
5.7.4 Using Client Programs in a Multiple-Server Environment
5.8 Tracing mysqld Using DTrace
5.8.1 mysqld DTrace Probe Reference
6 Security
6.1 General Security Issues
6.1.1 Security Guidelines
6.1.2 Keeping Passwords Secure
6.1.3 Making MySQL Secure Against Attackers
6.1.4 Security-Related mysqld Options and Variables
6.1.5 How to Run MySQL as a Normal User
6.1.6 Security Issues with LOAD DATA LOCAL
6.1.7 Client Programming Security Guidelines
6.2 Access Control and Account Management
6.2.1 Account User Names and Passwords
6.2.2 Privileges Provided by MySQL
6.2.3 Grant Tables
6.2.4 Specifying Account Names
6.2.5 Access Control, Stage 1: Connection Verification
6.2.6 Access Control, Stage 2: Request Verification
6.2.7 Adding Accounts, Assigning Privileges, and Dropping Accounts
6.2.8 When Privilege Changes Take Effect
6.2.9 Assigning Account Passwords
6.2.10 Server Handling of Expired Passwords
6.2.11 Pluggable Authentication
6.2.12 Proxy Users
6.2.13 Setting Account Resource Limits
6.2.14 Troubleshooting Problems Connecting to MySQL
6.2.15 SQL-Based Account Activity Auditing
6.3 Using Encrypted Connections
6.3.1 Configuring MySQL to Use Encrypted Connections
6.3.2 Encrypted Connection TLS Protocols and Ciphers
6.3.3 Creating SSL and RSA Certificates and Keys
6.3.4 SSL Library-Dependent Capabilities
6.3.5 Connecting to MySQL Remotely from Windows with SSH
6.4 Security Plugins
6.4.1 Authentication Plugins
6.4.2 The Connection-Control Plugins
6.4.3 The Password Validation Plugin
6.4.4 MySQL Enterprise Audit
6.4.5 MySQL Enterprise Firewall
7 Backup and Recovery
7.1 Backup and Recovery Types
7.2 Database Backup Methods
7.3 Example Backup and Recovery Strategy
7.3.1 Establishing a Backup Policy
7.3.2 Using Backups for Recovery
7.3.3 Backup Strategy Summary
7.4 Using mysqldump for Backups
7.4.1 Dumping Data in SQL Format with mysqldump
7.4.2 Reloading SQL-Format Backups
7.4.3 Dumping Data in Delimited-Text Format with mysqldump
7.4.4 Reloading Delimited-Text Format Backups
7.4.5 mysqldump Tips
7.5 Point-in-Time (Incremental) Recovery Using the Binary Log
7.5.1 Point-in-Time Recovery Using Event Times
7.5.2 Point-in-Time Recovery Using Event Positions
7.6 MyISAM Table Maintenance and Crash Recovery
7.6.1 Using myisamchk for Crash Recovery
7.6.2 How to Check MyISAM Tables for Errors
7.6.3 How to Repair MyISAM Tables
7.6.4 MyISAM Table Optimization
7.6.5 Setting Up a MyISAM Table Maintenance Schedule
8 Optimization
8.1 Optimization Overview
8.2 Optimizing SQL Statements
8.2.1 Optimizing SELECT Statements
8.2.2 Optimizing Subqueries and Derived Tables
8.2.3 Optimizing INFORMATION_SCHEMA Queries
8.2.4 Optimizing Data Change Statements
8.2.5 Optimizing Database Privileges
8.2.6 Other Optimization Tips
8.3 Optimization and Indexes
8.3.1 How MySQL Uses Indexes
8.3.2 Primary Key Optimization
8.3.3 Foreign Key Optimization