MySQL 8.0 Reference Manual

Including MySQL NDB Cluster 8.0

Abstract

This is the MySQL Reference Manual. It documents MySQL 8.0 through 8.0.46, as well as NDB Cluster releases based on version 8.0 of NDB through 8.0.44, 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 8.0 Release Notes.

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

For notes detailing the changes in each release, see the MySQL 8.0 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: 2026-07-08 (revision: 84719)

Table of Contents

Preface and Legal Notices
1 General Information
1.1 About This Manual
1.2 Overview of the MySQL Database Management System
1.2.1 What is MySQL?
1.2.2 The Main Features of MySQL
1.2.3 History of MySQL
1.3 What Is New in MySQL 8.0
1.4 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 8.0
1.5 How to Report Bugs or Problems
1.6 MySQL Standards Compliance
1.6.1 MySQL Extensions to Standard SQL
1.6.2 MySQL Differences from Standard SQL
1.6.3 How MySQL Deals with Constraints
2 Installing MySQL
2.1 General Installation Guidance
2.1.1 Supported Platforms
2.1.2 Which MySQL Version and Distribution to Install
2.1.3 How to Get MySQL
2.1.4 Verifying Package Integrity Using MD5 Checksums or GnuPG
2.1.5 Installation Layouts
2.1.6 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 macOS
2.4.1 General Notes on Installing MySQL on macOS
2.4.2 Installing MySQL on macOS Using Native Packages
2.4.3 Installing and Using the 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 Installing MySQL on Linux Using the MySQL APT Repository
2.5.3 Installing MySQL on Linux Using the MySQL SLES Repository
2.5.4 Installing MySQL on Linux Using RPM Packages from Oracle
2.5.5 Installing MySQL on Linux Using Debian Packages from Oracle
2.5.6 Deploying MySQL on Linux with Docker Containers
2.5.7 Installing MySQL on Linux from the Native Software Repositories
2.5.8 Installing MySQL on Linux with Juju
2.5.9 Managing MySQL Server with systemd
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 from Source
2.8.1 Source Installation Methods
2.8.2 Source Installation Prerequisites
2.8.3 MySQL Layout for Source Installation
2.8.4 Installing MySQL Using a Standard Source Distribution
2.8.5 Installing MySQL Using a Development Source Tree
2.8.6 Configuring SSL Library Support
2.8.7 MySQL Source-Configuration Options
2.8.8 Dealing with Problems Compiling MySQL
2.8.9 MySQL Configuration and Third-Party Tools
2.8.10 Generating MySQL Doxygen Documentation Content
2.9 Postinstallation Setup and Testing
2.9.1 Initializing the Data Directory
2.9.2 Starting the Server
2.9.3 Testing the Server
2.9.4 Securing the Initial MySQL Account
2.9.5 Starting and Stopping MySQL Automatically
2.10 Perl Installation Notes
2.10.1 Installing Perl on Unix
2.10.2 Installing ActiveState Perl on Windows
2.10.3 Problems Using the Perl DBI/DBD Interface
3 Upgrading MySQL
3.1 Before You Begin
3.2 Upgrade Paths
3.3 Upgrade Best Practices
3.4 What the MySQL Upgrade Process Upgrades
3.5 Changes in MySQL 8.0
3.6 Preparing Your Installation for Upgrade
3.7 Upgrading MySQL Binary or Package-based Installations on Unix/Linux
3.8 Upgrading MySQL with the MySQL Yum Repository
3.9 Upgrading MySQL with the MySQL APT Repository
3.10 Upgrading MySQL with the MySQL SLES Repository
3.11 Upgrading MySQL on Windows
3.12 Upgrading a Docker Installation of MySQL
3.13 Upgrade Troubleshooting
3.14 Rebuilding or Repairing Tables or Indexes
3.15 Copying MySQL Databases to Another Machine
4 Downgrading MySQL
5 Tutorial
5.1 Connecting to and Disconnecting from the Server
5.2 Entering Queries
5.3 Creating and Using a Database
5.3.1 Creating and Selecting a Database
5.3.2 Creating a Table
5.3.3 Loading Data into a Table
5.3.4 Retrieving Information from a Table
5.4 Getting Information About Databases and Tables
5.5 Using mysql in Batch Mode
5.6 Examples of Common Queries
5.6.1 The Maximum Value for a Column
5.6.2 The Row Holding the Maximum of a Certain Column
5.6.3 Maximum of Column per Group
5.6.4 The Rows Holding the Group-wise Maximum of a Certain Column
5.6.5 Using User-Defined Variables
5.6.6 Using Foreign Keys
5.6.7 Searching on Two Keys
5.6.8 Calculating Visits Per Day
5.6.9 Using AUTO_INCREMENT
5.7 Using MySQL with Apache
6 MySQL Programs
6.1 Overview of MySQL Programs
6.2 Using MySQL Programs
6.2.1 Invoking MySQL Programs
6.2.2 Specifying Program Options
6.2.3 Command Options for Connecting to the Server
6.2.4 Connecting to the MySQL Server Using Command Options
6.2.5 Connecting to the Server Using URI-Like Strings or Key-Value Pairs
6.2.6 Connecting to the Server Using DNS SRV Records
6.2.7 Connection Transport Protocols
6.2.8 Connection Compression Control
6.2.9 Setting Environment Variables
6.3 Server and Server-Startup Programs
6.3.1 mysqld — The MySQL Server
6.3.2 mysqld_safe — MySQL Server Startup Script
6.3.3 mysql.server — MySQL Server Startup Script
6.3.4 mysqld_multi — Manage Multiple MySQL Servers
6.4 Installation-Related Programs
6.4.1 comp_err — Compile MySQL Error Message File
6.4.2 mysql_secure_installation — Improve MySQL Installation Security
6.4.3 mysql_ssl_rsa_setup — Create SSL/RSA Files
6.4.4 mysql_tzinfo_to_sql — Load the Time Zone Tables
6.4.5 mysql_upgrade — Check and Upgrade MySQL Tables
6.5 Client Programs
6.5.1 mysql — The MySQL Command-Line Client
6.5.2 mysqladmin — A MySQL Server Administration Program
6.5.3 mysqlcheck — A Table Maintenance Program
6.5.4 mysqldump — A Database Backup Program
6.5.5 mysqlimport — A Data Import Program
6.5.6 mysqlpump — A Database Backup Program
6.5.7 mysqlshow — Display Database, Table, and Column Information
6.5.8 mysqlslap — A Load Emulation Client
6.6 Administrative and Utility Programs
6.6.1 ibd2sdi — InnoDB Tablespace SDI Extraction Utility
6.6.2 innochecksum — Offline InnoDB File Checksum Utility
6.6.3 myisam_ftdump — Display Full-Text Index information
6.6.4 myisamchk — MyISAM Table-Maintenance Utility
6.6.5 myisamlog — Display MyISAM Log File Contents
6.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables
6.6.7 mysql_config_editor — MySQL Configuration Utility
6.6.8 mysql_migrate_keyring — Keyring Key Migration Utility
6.6.9 mysqlbinlog — Utility for Processing Binary Log Files
6.6.10 mysqldumpslow — Summarize Slow Query Log Files
6.7 Program Development Utilities
6.7.1 mysql_config — Display Options for Compiling Clients
6.7.2 my_print_defaults — Display Options from Option Files
6.8 Miscellaneous Programs
6.8.1 lz4_decompress — Decompress mysqlpump LZ4-Compressed Output
6.8.2 perror — Display MySQL Error Message Information
6.8.3 zlib_decompress — Decompress mysqlpump ZLIB-Compressed Output
6.9 Environment Variables
6.10 Unix Signal Handling in MySQL
7 MySQL Server Administration
7.1 The MySQL Server
7.1.1 Configuring the Server
7.1.2 Server Configuration Defaults
7.1.3 Server Configuration Validation
7.1.4 Server Option, System Variable, and Status Variable Reference
7.1.5 Server System Variable Reference
7.1.6 Server Status Variable Reference
7.1.7 Server Command Options
7.1.8 Server System Variables
7.1.9 Using System Variables
7.1.10 Server Status Variables
7.1.11 Server SQL Modes
7.1.12 Connection Management
7.1.13 IPv6 Support
7.1.14 Network Namespace Support
7.1.15 MySQL Server Time Zone Support
7.1.16 Resource Groups
7.1.17 Server-Side Help Support
7.1.18 Server Tracking of Client Session State
7.1.19 The Server Shutdown Process
7.2 The MySQL Data Directory
7.3 The mysql System Schema
7.4 MySQL Server Logs
7.4.1 Selecting General Query Log and Slow Query Log Output Destinations
7.4.2 The Error Log
7.4.3 The General Query Log
7.4.4 The Binary Log
7.4.5 The Slow Query Log
7.4.6 Server Log Maintenance
7.5 MySQL Components
7.5.1 Installing and Uninstalling Components
7.5.2 Obtaining Component Information
7.5.3 Error Log Components
7.5.4 Query Attribute Components
7.5.5 Scheduler Component
7.6 MySQL Server Plugins
7.6.1 Installing and Uninstalling Plugins
7.6.2 Obtaining Server Plugin Information
7.6.3 MySQL Enterprise Thread Pool
7.6.4 The Rewriter Query Rewrite Plugin
7.6.5 The ddl_rewriter Plugin
7.6.6 Version Tokens
7.6.7 The Clone Plugin
7.6.8 The Keyring Proxy Bridge Plugin
7.6.9 MySQL Plugin Services
7.7 MySQL Server Loadable Functions
7.7.1 Installing and Uninstalling Loadable Functions
7.7.2 Obtaining Information About Loadable Functions
7.8 Running Multiple MySQL Instances on One Machine
7.8.1 Setting Up Multiple Data Directories
7.8.2 Running Multiple MySQL Instances on Windows
7.8.3 Running Multiple MySQL Instances on Unix
7.8.4 Using Client Programs in a Multiple-Server Environment
7.9 Debugging MySQL
7.9.1 Debugging a MySQL Server
7.9.2 Debugging a MySQL Client
7.9.3 The LOCK_ORDER Tool
7.9.4 The DBUG Package
8 Security
8.1 General Security Issues
8.1.1 Security Guidelines
8.1.2 Keeping Passwords Secure
8.1.3 Making MySQL Secure Against Attackers
8.1.4 Security-Related mysqld Options and Variables
8.1.5 How to Run MySQL as a Normal User
8.1.6 Security Considerations for LOAD DATA LOCAL
8.1.7 Client Programming Security Guidelines
8.2 Access Control and Account Management
8.2.1 Account User Names and Passwords
8.2.2 Privileges Provided by MySQL
8.2.3 Grant Tables
8.2.4 Specifying Account Names
8.2.5 Specifying Role Names
8.2.6 Access Control, Stage 1: Connection Verification
8.2.7 Access Control, Stage 2: Request Verification
8.2.8 Adding Accounts, Assigning Privileges, and Dropping Accounts
8.2.9 Reserved Accounts
8.2.10 Using Roles
8.2.11 Account Categories
8.2.12 Privilege Restriction Using Partial Revokes
8.2.13 When Privilege Changes Take Effect
8.2.14 Assigning Account Passwords
8.2.15 Password Management
8.2.16 Server Handling of Expired Passwords
8.2.17 Pluggable Authentication
8.2.18 Multifactor Authentication
8.2.19 Proxy Users
8.2.20 Account Locking
8.2.21 Setting Account Resource Limits
8.2.22 Troubleshooting Problems Connecting to MySQL
8.2.23 SQL-Based Account Activity Auditing
8.3 Using Encrypted Connections
8.3.1 Configuring MySQL to Use Encrypted Connections
8.3.2 Encrypted Connection TLS Protocols and Ciphers
8.3.3 Creating SSL and RSA Certificates and Keys
8.3.4 Connecting to MySQL Remotely from Windows with SSH
8.3.5 Reusing SSL Sessions
8.4 Security Components and Plugins
8.4.1 Authentication Plugins
8.4.2 Connection Control Plugins
8.4.3 The Password Validation Component
8.4.4 The MySQL Keyring
8.4.5 MySQL Enterprise Audit
8.4.6 The Audit Message Component
8.4.7 MySQL Enterprise Firewall
8.5 MySQL Enterprise Data Masking and De-Identification
8.5.1 Data-Masking Components Versus the Data-Masking Plugin
8.5.2 MySQL Enterprise Data Masking and De-Identification Components
8.5.3 MySQL Enterprise Data Masking and De-Identification Plugin
8.6 MySQL Enterprise Encryption
8.6.1 MySQL Enterprise Encryption Installation and Upgrading
8.6.2 Configuring MySQL Enterprise Encryption
8.6.3 MySQL Enterprise Encryption Usage and Examples
8.6.4 MySQL Enterprise Encryption Function Reference
8.6.5 MySQL Enterprise Encryption Component Function Descriptions
8.6.6 MySQL Enterprise Encryption Legacy Function Descriptions
8.7 SELinux
8.7.1 Check if SELinux is Enabled
8.7.2 Changing the SELinux Mode
8.7.3 MySQL Server SELinux Policies
8.7.4 SELinux File Context
8.7.5 SELinux TCP Port Context
8.7.6 Troubleshooting SELinux
8.8 FIPS Support
9 Backup and Recovery
9.1 Backup and Recovery Types
9.2 Database Backup Methods
9.3 Example Backup and Recovery Strategy
9.3.1 Establishing a Backup Policy
9.3.2 Using Backups for Recovery
9.3.3 Backup Strategy Summary
9.4 Using mysqldump for Backups
9.4.1 Dumping Data in SQL Format with mysqldump
9.4.2 Reloading SQL-Format Backups
9.4.3 Dumping Data in Delimited-Text Format with mysqldump
9.4.4 Reloading Delimited-Text Format Backups
9.4.5 mysqldump Tips
9.5 Point-in-Time (Incremental) Recovery
9.5.1 Point-in-Time Recovery Using Binary Log
9.5.2 Point-in-Time Recovery Using Event Positions
9.6 MyISAM Table Maintenance and Crash Recovery
9.6.1 Using myisamchk for Crash Recovery
9.6.2 How to Check MyISAM Tables for Errors
9.6.3 How to Repair MyISAM Tables
9.6.4 MyISAM Table Optimization
9.6.5 Setting Up a MyISAM Table Maintenance Schedule
10 Optimization
10.1 Optimization Overview
10.2 Optimizing SQL Statements
10.2.1 Optimizing SELECT Statements
10.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions
10.2.3 Optimizing INFORMATION_SCHEMA Queries
10.2.4 Optimizing Performance Schema Queries
10.2.5 Optimizing Data Change Statements
10.2.6 Optimizing Database Privileges
10.2.7 Other Optimization Tips
10.3 Optimization and Indexes
10.3.1 How MySQL Uses Indexes
10.3.2 Primary Key Optimization
10.3.3 SPATIAL Index Optimization
10.3.4 Foreign Key Optimization
10.3.5 Column Indexes
10.3.6 Multiple-Column Indexes
10.3.7 Verifying Index Usage
10.3.8 InnoDB and MyISAM Index Statistics Collection
10.3.9 Comparison of B-Tree and Hash Indexes
10.3.10 Use of Index Extensions
10.3.11 Optimizer Use of Generated Column Indexes
10.3.12 Invisible Indexes
10.3.13 Descending Indexes
10.3.14 Indexed Lookups from TIMESTAMP Columns
10.4 Optimizing Database Structure
10.4.1 Optimizing Data Size
10.4.2 Optimizing MySQL Data Types
10.4.3 Optimizing for Many Tables
10.4.4 Internal Temporary Table Use in MySQL
10.4.5 Limits on Number of Databases and Tables
10.4.6 Limits on Table Size
10.4.7 Limits on Table Column Count and Row Size
10.5 Optimizing for InnoDB Tables
10.5.1 Optimizing Storage Layout for InnoDB Tables
10.5.2 Optimizing InnoDB Transaction Management
10.5.3 Optimizing InnoDB Read-Only Transactions
10.5.4 Optimizing InnoDB Redo Logging
10.5.5 Bulk Data Loading for InnoDB Tables
10.5.6 Optimizing InnoDB Queries
10.5.7 Optimizing InnoDB DDL Operations
10.5.8 Optimizing InnoDB Disk I/O
10.5.9 Optimizing InnoDB Configuration Variables
10.5.10 Optimizing InnoDB for Systems with Many Tables
10.6 Optimizing for MyISAM Tables
10.6.1 Optimizing MyISAM Queries
10.6.2 Bulk Data Loading for MyISAM Tables
10.6.3 Optimizing REPAIR TABLE Statements
10.7 Optimizing for MEMORY Tables
10.8 Understanding the Query Execution Plan
10.8.1 Optimizing Queries with EXPLAIN
10.8.2 EXPLAIN Output Format
10.8.3 Extended EXPLAIN Output Format
10.8.4 Obtaining Execution Plan Information for a Named Connection
10.8.5 Estimating Query Performance
10.9 Controlling the Query Optimizer
10.9.1 Controlling Query Plan Evaluation
10.9.2 Switchable Optimizations
10.9.3 Optimizer Hints
10.9.4 Index Hints
10.9.5 The Optimizer Cost Model
10.9.6 Optimizer Statistics
10.10 Buffering and Caching
10.10.1 InnoDB Buffer Pool Optimization
10.10.2 The MyISAM Key Cache
10.10.3 Caching of Prepared Statements and Stored Programs
10.11 Optimizing Locking Operations
10.11.1 Internal Locking Methods
10.11.2 Table Locking Issues
10.11.3 Concurrent Inserts
10.11.4 Metadata Locking
10.11.5 External Locking
10.12 Optimizing the MySQL Server
10.12.1 Optimizing Disk I/O
10.12.2 Using Symbolic Links
10.12.3 Optimizing Memory Use
10.13 Measuring Performance (Benchmarking)
10.13.1 Measuring the Speed of Expressions and Functions
10.13.2 Using Your Own Benchmarks
10.13.3 Measuring Performance with performance_schema
10.14 Examining Server Thread (Process) Information
10.14.1 Accessing the Process List
10.14.2 Thread Command Values
10.14.3 General Thread States
10.14.4 Replication Source Thread States
10.14.5 Replication I/O (Receiver) Thread States
10.14.6 Replication SQL Thread States
10.14.7 Replication Connection Thread States
10.14.8 NDB Cluster Thread States
10.14.9 Event Scheduler Thread States
10.15 Tracing the Optimizer
10.15.1 Typical Usage
10.15.2 System Variables Controlling Tracing
10.15.3 Traceable Statements
10.15.4 Tuning Trace Purging
10.15.5 Tracing Memory Usage
10.15.6 Privilege Checking
10.15.7 Interaction with the --debug Option
10.15.8 The optimizer_trace System Variable
10.15.9 The end_markers_in_json System Variable
10.15.10 Selecting Optimizer Features to Trace
10.15.11 Trace General Structure
10.15.12 Example
10.15.13 Displaying Traces in Other Applications
10.15.14 Preventing the Use of Optimizer Trace
10.15.15 Testing Optimizer Trace
10.15.16 Optimizer Trace Implementation
11 Language Structure
11.1 Literal Values
11.1.1 String Literals
11.1.2 Numeric Literals
11.1.3 Date and Time Literals
11.1.4 Hexadecimal Literals
11.1.5 Bit-Value Literals
11.1.6 Boolean Literals
11.1.7 NULL Values
11.2 Schema Object Names
11.2.1 Identifier Length Limits
11.2.2 Identifier Qualifiers
11.2.3 Identifier Case Sensitivity
11.2.4 Mapping of Identifiers to File Names
11.2.5 Function Name Parsing and Resolution
11.3 Keywords and Reserved Words
11.4 User-Defined Variables
11.5 Expressions
11.6 Query Attributes
11.7 Comments
12 Character Sets, Collations, Unicode
12.1 Character Sets and Collations in General
12.2 Character Sets and Collations in MySQL
12.2.1 Character Set Repertoire
12.2.2 UTF-8 for Metadata
12.3 Specifying Character Sets and Collations
12.3.1 Collation Naming Conventions
12.3.2 Server Character Set and Collation
12.3.3 Database Character Set and Collation
12.3.4 Table Character Set and Collation
12.3.5 Column Character Set and Collation
12.3.6 Character String Literal Character Set and Collation
12.3.7 The National Character Set
12.3.8 Character Set Introducers
12.3.9 Examples of Character Set and Collation Assignment
12.3.10 Compatibility with Other DBMSs
12.4 Connection Character Sets and Collations
12.5 Configuring Application Character Set and Collation
12.6 Error Message Character Set
12.7 Column Character Set Conversion
12.8 Collation Issues
12.8.1 Using COLLATE in SQL Statements
12.8.2 COLLATE Clause Precedence
12.8.3 Character Set and Collation Compatibility
12.8.4 Collation Coercibility in Expressions
12.8.5 The binary Collation Compared to _bin Collations
12.8.6 Examples of the Effect of Collation
12.8.7 Using Collation in INFORMATION_SCHEMA Searches
12.9 Unicode Support
12.9.1 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)
12.9.2 The utf8mb3 Character Set (3-Byte UTF-8 Unicode Encoding)
12.9.3 The utf8 Character Set (Deprecated alias for utf8mb3)
12.9.4 The ucs2 Character Set (UCS-2 Unicode Encoding)
12.9.5 The utf16 Character Set (UTF-16 Unicode Encoding)