MySQL 9.0 Reference Manual

Including MySQL NDB Cluster 9.0

Abstract

This is the MySQL Reference Manual. It documents MySQL 9.0 (9.1.0), as well as NDB Cluster 9.0 (9.0.0), 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 9.0 Release Notes.

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

For notes detailing the changes in each release, see the MySQL 9.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: 2024-07-17 (revision: 79060)


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 MySQL Releases: Innovation and LTS
1.4 What Is New in MySQL 9.0
1.5 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 9.0 since 8.4
1.6 How to Report Bugs or Problems
1.7 MySQL Standards Compliance
1.7.1 MySQL Extensions to Standard SQL
1.7.2 MySQL Differences from Standard SQL
1.7.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 Choosing an Installation Package
2.3.2 Configuration: Using MySQL Configurator
2.3.3 Configuration: Manually
2.3.4 Troubleshooting a Microsoft Windows MySQL Server Installation
2.3.5 Windows Postinstallation Procedures
2.3.6 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 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 9.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_tzinfo_to_sql — Load the Time Zone 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 mysqlshow — Display Database, Table, and Column Information
6.5.7 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 perror — Display MySQL Error Message Information
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.5.6 Multilingual Engine Component (MLE)
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 The 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.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
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)
12.9.6 The utf16le Character Set (UTF-16LE Unicode Encoding)
12.9.7 The utf32 Character Set (UTF-32 Unicode Encoding)
12.9.8 Converting Between 3-Byte and 4-Byte Unicode Character Sets
12.10 Supported Character Sets and Collations
12.10.1 Unicode Character Sets
12.10.2 West European Character Sets
12.10.3 Central European Character Sets
12.10.4 South European and Middle East Character Sets
12.10.5 Baltic Character Sets
12.10.6 Cyrillic Character Sets
12.10.7 Asian Character Sets
12.10.8 The Binary Character Set
12.11 Restrictions on Character Sets
12.12 Setting the Error Message Language
12.13 Adding a Character Set
12.13.1 Character Definition Arrays
12.13.2 String Collating Support for Complex Character Sets
12.13.3 Multi-Byte Character Support for Complex Character Sets
12.14 Adding a Collation to a Character Set
12.14.1 Collation Implementation Types
12.14.2 Choosing a Collation ID
12.14.3 Adding a Simple Collation to an 8-Bit Character Set
12.14.4 Adding a UCA Collation to a Unicode Character Set
12.15 Character Set Configuration
12.16 MySQL Server Locale Support
13 Data Types
13.1 Numeric Data Types
13.1.1 Numeric Data Type Syntax
13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC
13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE
13.1.5 Bit-Value Type - BIT
13.1.6 Numeric Type Attributes
13.1.7 Out-of-Range and Overflow Handling
13.2 Date and Time Data Types
13.2.1 Date and Time Data Type Syntax
13.2.2 The DATE, DATETIME, and TIMESTAMP Types
13.2.3 The TIME Type
13.2.4 The YEAR Type
13.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME
13.2.6 Fractional Seconds in Time Values
13.2.7 What Calendar Is Used By MySQL?
13.2.8 Conversion Between Date and Time Types
13.2.9 2-Digit Years in Dates
13.3 String Data Types
13.3.1 String Data Type Syntax
13.3.2 The CHAR and VARCHAR Types
13.3.3 The BINARY and VARBINARY Types
13.3.4 The BLOB and TEXT Types
13.3.5 The VECTOR Type
13.3.6 The ENUM Type
13.3.7 The SET Type
13.4 Spatial Data Types
13.4.1 Spatial Data Types
13.4.2 The OpenGIS Geometry Model
13.4.3 Supported Spatial Data Formats
13.4.4 Geometry Well-Formedness and Validity
13.4.5 Spatial Reference System Support
13.4.6 Creating Spatial Columns
13.4.7 Populating Spatial Columns
13.4.8 Fetching Spatial Data
13.4.9 Optimizing Spatial Analysis
13.4.10 Creating Spatial Indexes
13.4.11 Using Spatial Indexes
13.5 The JSON Data Type
13.6 Data Type Default Values
13.7 Data Type Storage Requirements
13.8 Choosing the Right Type for a Column
13.9 Using Data Types from Other Database Engines
14 Functions and Operators
14.1 Built-In Function and Operator Reference
14.2 Loadable Function Reference
14.3 Type Conversion in Expression Evaluation
14.4 Operators
14.4.1 Operator Precedence
14.4.2 Comparison Functions and Operators
14.4.3 Logical Operators
14.4.4 Assignment Operators
14.5 Flow Control Functions
14.6 Numeric Functions and Operators
14.6.1 Arithmetic Operators
14.6.2 Mathematical Functions
14.7 Date and Time Functions
14.8 String Functions and Operators
14.8.1 String Comparison Functions and Operators
14.8.2 Regular Expressions
14.8.3 Character Set and Collation of Function Results
14.9 Full-Text Search Functions
14.9.1 Natural Language Full-Text Searches
14.9.2 Boolean Full-Text Searches
14.9.3 Full-Text Searches with Query Expansion
14.9.4 Full-Text Stopwords
14.9.5 Full-Text Restrictions
14.9.6 Fine-Tuning MySQL Full-Text Search
14.9.7 Adding a User-Defined Collation for Full-Text Indexing
14.9.8 ngram Full-Text Parser
14.9.9 MeCab Full-Text Parser Plugin
14.10 Cast Functions and Operators
14.11 XML Functions
14.12 Bit Functions and Operators
14.13 Encryption and Compression Functions
14.14 Locking Functions
14.15 Information Functions
14.16 Spatial Analysis Functions
14.16.1 Spatial Function Reference
14.16.2 Argument Handling by Spatial Functions
14.16.3 Functions That Create Geometry Values from WKT Values
14.16.4 Functions That Create Geometry Values from WKB Values
14.16.5 MySQL-Specific Functions That Create Geometry Values
14.16.6 Geometry Format Conversion Functions
14.16.7 Geometry Property Functions
14.16.8 Spatial Operator Functions
14.16.9 Functions That Test Spatial Relations Between Geometry Objects
14.16.10 Spatial Geohash Functions
14.16.11 Spatial GeoJSON Functions
14.16.12 Spatial Aggregate Functions
14.16.13 Spatial Convenience Functions
14.17 JSON Functions
14.17.1 JSON Function Reference
14.17.2 Functions That Create JSON Values
14.17.3 Functions That Search JSON Values
14.17.4 Functions That Modify JSON Values
14.17.5 Functions That Return JSON Value Attributes
14.17.6 JSON Table Functions
14.17.7 JSON Schema Validation Functions
14.17.8 JSON Utility Functions
14.18 Replication Functions
14.18.1 Group Replication Functions
14.18.2 Functions Used with Global Transaction Identifiers (GTIDs)
14.18.3 Asynchronous Replication Channel Failover Functions
14.18.4 Position-Based Synchronization Functions
14.19 Aggregate Functions
14.19.1 Aggregate Function Descriptions
14.19.2 GROUP BY Modifiers
14.19.3 MySQL Handling of GROUP BY
14.19.4 Detection of Functional Dependence
14.20 Window Functions
14.20.1 Window Function Descriptions
14.20.2 Window Function Concepts and Syntax
14.20.3 Window Function Frame Specification
14.20.4 Named Windows
14.20.5 Window Function Restrictions
14.21 Vector Functions
14.22 Performance Schema Functions
14.23 Internal Functions
14.24 Miscellaneous Functions
14.25 Precision Math
14.25.1 Types of Numeric Values
14.25.2 DECIMAL Data Type Characteristics
14.25.3 Expression Handling
14.25.4 Rounding Behavior
14.25.5 Precision Math Examples
15 SQL Statements
15.1 Data Definition Statements
15.1.1 Atomic Data Definition Statement Support
15.1.2 ALTER DATABASE Statement
15.1.3 ALTER EVENT Statement
15.1.4 ALTER FUNCTION Statement
15.1.5 ALTER INSTANCE Statement
15.1.6 ALTER LOGFILE GROUP Statement
15.1.7 ALTER PROCEDURE Statement
15.1.8 ALTER SERVER Statement
15.1.9 ALTER TABLE Statement
15.1.10 ALTER TABLESPACE Statement
15.1.11 ALTER VIEW Statement
15.1.12 CREATE DATABASE Statement
15.1.13 CREATE EVENT Statement
15.1.14 CREATE FUNCTION Statement
15.1.15 CREATE INDEX Statement
15.1.16 CREATE LOGFILE GROUP Statement
15.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements
15.1.18 CREATE SERVER Statement
15.1.19 CREATE SPATIAL REFERENCE SYSTEM Statement
15.1.20 CREATE TABLE Statement
15.1.21 CREATE TABLESPACE Statement
15.1.22 CREATE TRIGGER Statement
15.1.23 CREATE VIEW Statement
15.1.24 DROP DATABASE Statement
15.1.25 DROP EVENT Statement
15.1.26 DROP FUNCTION Statement
15.1.27 DROP INDEX Statement
15.1.28 DROP LOGFILE GROUP Statement
15.1.29 DROP PROCEDURE and DROP FUNCTION Statements
15.1.30 DROP SERVER Statement
15.1.31 DROP SPATIAL REFERENCE SYSTEM Statement
15.1.32 DROP TABLE Statement
15.1.33 DROP TABLESPACE Statement
15.1.34 DROP TRIGGER Statement
15.1.35 DROP VIEW Statement
15.1.36 RENAME TABLE Statement
15.1.37 TRUNCATE TABLE Statement
15.2 Data Manipulation Statements
15.2.1 CALL Statement
15.2.2 DELETE Statement
15.2.3 DO Statement
15.2.4 EXCEPT Clause
15.2.5 HANDLER Statement
15.2.6 IMPORT TABLE Statement
15.2.7 INSERT Statement
15.2.8 INTERSECT Clause
15.2.9 LOAD DATA Statement
15.2.10 LOAD XML Statement
15.2.11 Parenthesized Query Expressions
15.2.12 REPLACE Statement
15.2.13 SELECT Statement
15.2.14 Set Operations with UNION, INTERSECT, and EXCEPT
15.2.15 Subqueries
15.2.16 TABLE Statement
15.2.17 UPDATE Statement
15.2.18 UNION Clause
15.2.19 VALUES Statement
15.2.20 WITH (Common Table Expressions)
15.3 Transactional and Locking Statements
15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements
15.3.2 Statements That Cannot Be Rolled Back
15.3.3 Statements That Cause an Implicit Commit
15.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements
15.3.5 LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements
15.3.6 LOCK TABLES and UNLOCK TABLES Statements
15.3.7 SET TRANSACTION Statement
15.3.8 XA Transactions
15.4 Replication Statements
15.4.1 SQL Statements for Controlling Source Servers
15.4.2 SQL Statements for Controlling Replica Servers
15.4.3 SQL Statements for Controlling Group Replication
15.5 Prepared Statements
15.5.1 PREPARE Statement
15.5.2 EXECUTE Statement
15.5.3 DEALLOCATE PREPARE Statement
15.6 Compound Statement Syntax
15.6.1 BEGIN ... END Compound Statement
15.6.2 Statement Labels
15.6.3 DECLARE Statement
15.6.4 Variables in Stored Programs
15.6.5 Flow Control Statements
15.6.6 Cursors
15.6.7 Condition Handling
15.6.8 Restrictions on Condition Handling
15.7 Database Administration Statements
15.7.1 Account Management Statements
15.7.2 Resource Group Management Statements
15.7.3 Table Maintenance Statements
15.7.4 Component, Plugin, and Loadable Function Statements
15.7.5 CLONE Statement
15.7.6 SET Statements
15.7.7 SHOW Statements
15.7.8 Other Administrative Statements
15.8 Utility Statements
15.8.1 DESCRIBE Statement
15.8.2 EXPLAIN Statement
15.8.3 HELP Statement
15.8.4 USE Statement
16 MySQL Data Dictionary
16.1 Data Dictionary Schema
16.2 Removal of File-based Metadata Storage
16.3 Transactional Storage of Dictionary Data
16.4 Dictionary Object Cache
16.5 INFORMATION_SCHEMA and Data Dictionary Integration
16.6 Serialized Dictionary Information (SDI)
16.7 Data Dictionary Usage Differences
16.8 Data Dictionary Limitations
17 The InnoDB Storage Engine
17.1 Introduction to InnoDB
17.1.1 Benefits of Using InnoDB Tables
17.1.2 Best Practices for InnoDB Tables
17.1.3 Verifying that InnoDB is the Default Storage Engine
17.1.4 Testing and Benchmarking with InnoDB
17.2 InnoDB and the ACID Model
17.3 InnoDB Multi-Versioning
17.4 InnoDB Architecture
17.5 InnoDB In-Memory Structures
17.5.1 Buffer Pool
17.5.2 Change Buffer
17.5.3 Adaptive Hash Index
17.5.4 Log Buffer
17.6 InnoDB On-Disk Structures
17.6.1 Tables
17.6.2 Indexes
17.6.3 Tablespaces
17.6.4 Doublewrite Buffer
17.6.5 Redo Log
17.6.6 Undo Logs
17.7 InnoDB Locking and Transaction Model
17.7.1 InnoDB Locking
17.7.2 InnoDB Transaction Model
17.7.3 Locks Set by Different SQL Statements in InnoDB
17.7.4 Phantom Rows
17.7.5 Deadlocks in InnoDB
17.7.6 Transaction Scheduling
17.8 InnoDB Configuration
17.8.1 InnoDB Startup Configuration
17.8.2 Configuring InnoDB for Read-Only Operation
17.8.3 InnoDB Buffer Pool Configuration
17.8.4 Configuring Thread Concurrency for InnoDB
17.8.5 Configuring the Number of Background InnoDB I/O Threads
17.8.6 Using Asynchronous I/O on Linux
17.8.7 Configuring InnoDB I/O Capacity
17.8.8 Configuring Spin Lock Polling
17.8.9 Purge Configuration
17.8.10 Configuring Optimizer Statistics for InnoDB
17.8.11 Configuring the Merge Threshold for Index Pages
17.8.12 Enabling Automatic Configuration for a Dedicated MySQL Server
17.9 InnoDB Table and Page Compression
17.9.1 InnoDB Table Compression
17.9.2 InnoDB Page Compression
17.10 InnoDB Row Formats
17.11 InnoDB Disk I/O and File Space Management
17.11.1 InnoDB Disk I/O
17.11.2 File Space Management
17.11.3 InnoDB Checkpoints
17.11.4 Defragmenting a Table
17.11.5 Reclaiming Disk Space with TRUNCATE TABLE
17.12 InnoDB and Online DDL
17.12.1 Online DDL Operations
17.12.2 Online DDL Performance and Concurrency
17.12.3 Online DDL Space Requirements
17.12.4 Online DDL Memory Management
17.12.5 Configuring Parallel Threads for Online DDL Operations
17.12.6 Simplifying DDL Statements with Online DDL
17.12.7 Online DDL Failure Conditions
17.12.8 Online DDL Limitations
17.13 InnoDB Data-at-Rest Encryption
17.14 InnoDB Startup Options and System Variables
17.15 InnoDB INFORMATION_SCHEMA Tables
17.15.1 InnoDB INFORMATION_SCHEMA Tables about Compression
17.15.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information
17.15.3 InnoDB INFORMATION_SCHEMA Schema Object Tables
17.15.4 InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables
17.15.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables
17.15.6 InnoDB INFORMATION_SCHEMA Metrics Table
17.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table
17.15.8 Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES
17.16 InnoDB Integration with MySQL Performance Schema
17.16.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema
17.16.2 Monitoring InnoDB Mutex Waits Using Performance Schema
17.17 InnoDB Monitors
17.17.1 InnoDB Monitor Types
17.17.2 Enabling InnoDB Monitors
17.17.3 InnoDB Standard Monitor and Lock Monitor Output
17.18 InnoDB Backup and Recovery
17.18.1 InnoDB Backup
17.18.2 InnoDB Recovery
17.19 InnoDB and MySQL Replication
17.20 InnoDB Troubleshooting
17.20.1 Troubleshooting InnoDB I/O Problems
17.20.2 Troubleshooting Recovery Failures
17.20.3 Forcing InnoDB Recovery
17.20.4 Troubleshooting InnoDB Data Dictionary Operations
17.20.5 InnoDB Error Handling
17.21 InnoDB Limits
17.22 InnoDB Restrictions and Limitations
18 Alternative Storage Engines
18.1 Setting the Storage Engine
18.2 The MyISAM Storage Engine
18.2.1 MyISAM Startup Options
18.2.2 Space Needed for Keys
18.2.3 MyISAM Table Storage Formats
18.2.4 MyISAM Table Problems
18.3 The MEMORY Storage Engine
18.4 The CSV Storage Engine
18.4.1 Repairing and Checking CSV Tables
18.4.2 CSV Limitations
18.5 The ARCHIVE Storage Engine
18.6 The BLACKHOLE Storage Engine
18.7 The MERGE Storage Engine
18.7.1 MERGE Table Advantages and Disadvantages
18.7.2 MERGE Table Problems
18.8 The FEDERATED Storage Engine
18.8.1 FEDERATED Storage Engine Overview
18.8.2 How to Create FEDERATED Tables
18.8.3 FEDERATED Storage Engine Notes and Tips
18.8.4 FEDERATED Storage Engine Resources
18.9 The EXAMPLE Storage Engine
18.10 Other Storage Engines
18.11 Overview of MySQL Storage Engine Architecture
18.11.1 Pluggable Storage Engine Architecture
18.11.2 The Common Database Server Layer
19 Replication
19.1 Configuring Replication
19.1.1 Binary Log File Position Based Replication Configuration Overview
19.1.2 Setting Up Binary Log File Position Based Replication
19.1.3 Replication with Global Transaction Identifiers
19.1.4 Changing GTID Mode on Online Servers
19.1.5 MySQL Multi-Source Replication
19.1.6 Replication and Binary Logging Options and Variables
19.1.7 Common Replication Administration Tasks
19.2 Replication Implementation
19.2.1 Replication Formats
19.2.2 Replication Channels
19.2.3 Replication Threads
19.2.4 Relay Log and Replication Metadata Repositories
19.2.5 How Servers Evaluate Replication Filtering Rules
19.3 Replication Security
19.3.1 Setting Up Replication to Use Encrypted Connections
19.3.2 Encrypting Binary Log Files and Relay Log Files
19.3.3 Replication Privilege Checks
19.4 Replication Solutions
19.4.1 Using Replication for Backups
19.4.2 Handling an Unexpected Halt of a Replica
19.4.3 Monitoring Row-based Replication
19.4.4 Using Replication with Different Source and Replica Storage Engines
19.4.5 Using Replication for Scale-Out
19.4.6 Replicating Different Databases to Different Replicas
19.4.7 Improving Replication Performance
19.4.8 Switching Sources During Failover
19.4.9 Switching Sources and Replicas with Asynchronous Connection Failover
19.4.10 Semisynchronous Replication
19.4.11 Delayed Replication
19.5 Replication Notes and Tips
19.5.1 Replication Features and Issues
19.5.2 Replication Compatibility Between MySQL Versions
19.5.3 Upgrading or Downgrading a Replication Topology
19.5.4 Troubleshooting Replication
19.5.5 How to Report Replication Bugs or Problems
20 Group Replication
20.1 Group Replication Background
20.1.1 Replication Technologies
20.1.2 Group Replication Use Cases
20.1.3 Multi-Primary and Single-Primary Modes
20.1.4 Group Replication Services
20.1.5 Group Replication Plugin Architecture
20.2 Getting Started
20.2.1 Deploying Group Replication in Single-Primary Mode
20.2.2 Deploying Group Replication Locally
20.3 Requirements and Limitations
20.3.1 Group Replication Requirements
20.3.2 Group Replication Limitations
20.4 Monitoring Group Replication
20.4.1 GTIDs and Group Replication
20.4.2 Group Replication Server States
20.4.3 The replication_group_members Table
20.4.4 The replication_group_member_stats Table
20.5 Group Replication Operations
20.5.1 Configuring an Online Group
20.5.2 Restarting a Group
20.5.3 Transaction Consistency Guarantees
20.5.4 Distributed Recovery
20.5.5 Support For IPv6 And For Mixed IPv6 And IPv4 Groups
20.5.6 Using MySQL Enterprise Backup with Group Replication
20.6 Group Replication Security
20.6.1 Communication Stack for Connection Security Management
20.6.2 Securing Group Communication Connections with Secure Socket Layer (SSL)
20.6.3 Securing Distributed Recovery Connections
20.6.4 Group Replication IP Address Permissions
20.7 Group Replication Performance and Troubleshooting
20.7.1 Fine Tuning the Group Communication Thread
20.7.2 Flow Control
20.7.3 Single Consensus Leader
20.7.4 Message Compression
20.7.5 Message Fragmentation
20.7.6 XCom Cache Management
20.7.7 Responses to Failure Detection and Network Partitioning
20.7.8 Handling a Network Partition and Loss of Quorum
20.7.9 Monitoring Group Replication Memory Usage with Performance Schema Memory Instrumentation
20.8 Upgrading Group Replication
20.8.1 Combining Different Member Versions in a Group
20.8.2 Group Replication Offline Upgrade
20.8.3 Group Replication Online Upgrade
20.9 Group Replication Variables
20.9.1 Group Replication System Variables
20.9.2 Group Replication Status Variables
20.10 Frequently Asked Questions
21 MySQL Shell
22 Using MySQL as a Document Store
22.1 Interfaces to a MySQL Document Store
22.2 Document Store Concepts
22.3 JavaScript Quick-Start Guide: MySQL Shell for Document Store
22.3.1 MySQL Shell
22.3.2 Download and Import world_x Database
22.3.3 Documents and Collections
22.3.4 Relational Tables
22.3.5 Documents in Tables
22.4 Python Quick-Start Guide: MySQL Shell for Document Store
22.4.1 MySQL Shell
22.4.2 Download and Import world_x Database
22.4.3 Documents and Collections
22.4.4 Relational Tables
22.4.5 Documents in Tables
22.5 X Plugin
22.5.1 Checking X Plugin Installation
22.5.2 Disabling X Plugin
22.5.3 Using Encrypted Connections with X Plugin
22.5.4 Using X Plugin with the Caching SHA-2 Authentication Plugin
22.5.5 Connection Compression with X Plugin
22.5.6 X Plugin Options and Variables
22.5.7 Monitoring X Plugin
23 InnoDB Cluster
24 InnoDB ReplicaSet
25 MySQL NDB Cluster 9.0
25.1 General Information
25.2 NDB Cluster Overview
25.2.1 NDB Cluster Core Concepts
25.2.2 NDB Cluster Nodes, Node Groups, Fragment Replicas, and Partitions
25.2.3 NDB Cluster Hardware, Software, and Networking Requirements
25.2.4 What is New in MySQL NDB Cluster 9.0
25.2.5 Options, Variables, and Parameters Added, Deprecated or Removed in NDB 9.0
25.2.6 MySQL Server Using InnoDB Compared with NDB Cluster
25.2.7 Known Limitations of NDB Cluster
25.3 NDB Cluster Installation
25.3.1 Installation of NDB Cluster on Linux
25.3.2 Installing NDB Cluster on Windows
25.3.3 Initial Configuration of NDB Cluster
25.3.4 Initial Startup of NDB Cluster
25.3.5 NDB Cluster Example with Tables and Data
25.3.6 Safe Shutdown and Restart of NDB Cluster
25.3.7 Upgrading and Downgrading NDB Cluster
25.4 Configuration of NDB Cluster
25.4.1 Quick Test Setup of NDB Cluster
25.4.2 Overview of NDB Cluster Configuration Parameters, Options, and Variables
25.4.3 NDB Cluster Configuration Files
25.4.4 Using High-Speed Interconnects with NDB Cluster
25.5 NDB Cluster Programs
25.5.1 ndbd — The NDB Cluster Data Node Daemon
25.5.2 ndbinfo_select_all — Select From ndbinfo Tables
25.5.3 ndbmtd — The NDB Cluster Data Node Daemon (Multi-Threaded)
25.5.4 ndb_mgmd — The NDB Cluster Management Server Daemon
25.5.5 ndb_mgm — The NDB Cluster Management Client
25.5.6 ndb_blob_tool — Check and Repair BLOB and TEXT columns of NDB Cluster Tables
25.5.7 ndb_config — Extract NDB Cluster Configuration Information
25.5.8 ndb_delete_all — Delete All Rows from an NDB Table
25.5.9 ndb_desc — Describe NDB Tables
25.5.10 ndb_drop_index — Drop Index from an NDB Table
25.5.11 ndb_drop_table — Drop an NDB Table
25.5.12 ndb_error_reporter — NDB Error-Reporting Utility
25.5.13 ndb_import — Import CSV Data Into NDB
25.5.14 ndb_index_stat — NDB Index Statistics Utility
25.5.15 ndb_move_data — NDB Data Copy Utility
25.5.16 ndb_perror — Obtain NDB Error Message Information
25.5.17 ndb_print_backup_file — Print NDB Backup File Contents
25.5.18 ndb_print_file — Print NDB Disk Data File Contents
25.5.19 ndb_print_frag_file — Print NDB Fragment List File Contents
25.5.20 ndb_print_schema_file — Print NDB Schema File Contents
25.5.21 ndb_print_sys_file — Print NDB System File Contents
25.5.22 ndb_redo_log_reader — Check and Print Content of Cluster Redo Log
25.5.23 ndb_restore — Restore an NDB Cluster Backup
25.5.24 ndb_secretsfile_reader — Obtain Key Information from an Encrypted NDB Data File
25.5.25 ndb_select_all — Print Rows from an NDB Table
25.5.26 ndb_select_count — Print Row Counts for NDB Tables
25.5.27 ndb_show_tables — Display List of NDB Tables
25.5.28 ndb_sign_keys — Create, Sign, and Manage TLS Keys and Certificates for NDB Cluster
25.5.29 ndb_size.pl — NDBCLUSTER Size Requirement Estimator
25.5.30 ndb_top — View CPU usage information for NDB threads
25.5.31 ndb_waiter — Wait for NDB Cluster to Reach a Given Status
25.5.32 ndbxfrm — Compress, Decompress, Encrypt, and Decrypt Files Created by NDB Cluster
25.6 Management of NDB Cluster
25.6.1 Commands in the NDB Cluster Management Client
25.6.2 NDB Cluster Log Messages
25.6.3 Event Reports Generated in NDB Cluster
25.6.4 Summary of NDB Cluster Start Phases
25.6.5 Performing a Rolling Restart of an NDB Cluster
25.6.6 NDB Cluster Single User Mode
25.6.7 Adding NDB Cluster Data Nodes Online
25.6.8 Online Backup of NDB Cluster
25.6.9 Importing Data Into MySQL Cluster
25.6.10 MySQL Server Usage for NDB Cluster
25.6.11 NDB Cluster Disk Data Tables
25.6.12 Online Operations with ALTER TABLE in NDB Cluster
25.6.13 Privilege Synchronization and NDB_STORED_USER
25.6.14 File System Encryption for NDB Cluster
25.6.15 TLS Link Encryption for NDB Cluster
25.6.16 NDB API Statistics Counters and Variables
25.6.17 ndbinfo: The NDB Cluster Information Database
25.6.18 INFORMATION_SCHEMA Tables for NDB Cluster
25.6.19 NDB Cluster and the Performance Schema
25.6.20 Quick Reference: NDB Cluster SQL Statements
25.6.21 NDB Cluster Security Issues
25.7 NDB Cluster Replication
25.7.1 NDB Cluster Replication: Abbreviations and Symbols
25.7.2 General Requirements for NDB Cluster Replication
25.7.3 Known Issues in NDB Cluster Replication
25.7.4 NDB Cluster Replication Schema and Tables
25.7.5 Preparing the NDB Cluster for Replication
25.7.6 Starting NDB Cluster Replication (Single Replication Channel)
25.7.7 Using Two Replication Channels for NDB Cluster Replication
25.7.8 Implementing Failover with NDB Cluster Replication
25.7.9 NDB Cluster Backups With NDB Cluster Replication
25.7.10 NDB Cluster Replication: Bidirectional and Circular Replication
25.7.11 NDB Cluster Replication Using the Multithreaded Applier
25.7.12 NDB Cluster Replication Conflict Resolution
25.8 NDB Cluster Release Notes
26 Partitioning
26.1 Overview of Partitioning in MySQL
26.2 Partitioning Types
26.2.1 RANGE Partitioning
26.2.2 LIST Partitioning
26.2.3 COLUMNS Partitioning
26.2.4 HASH Partitioning
26.2.5 KEY Partitioning
26.2.6 Subpartitioning
26.2.7 How MySQL Partitioning Handles NULL
26.3 Partition Management
26.3.1 Management of RANGE and LIST Partitions
26.3.2 Management of HASH and KEY Partitions
26.3.3 Exchanging Partitions and Subpartitions with Tables
26.3.4 Maintenance of Partitions
26.3.5 Obtaining Information About Partitions
26.4 Partition Pruning
26.5 Partition Selection
26.6 Restrictions and Limitations on Partitioning
26.6.1 Partitioning Keys, Primary Keys, and Unique Keys
26.6.2 Partitioning Limitations Relating to Storage Engines
26.6.3 Partitioning Limitations Relating to Functions
27 Stored Objects
27.1 Defining Stored Programs
27.2 Using Stored Routines
27.2.1 Stored Routine Syntax
27.2.2 Stored Routines and MySQL Privileges
27.2.3 Stored Routine Metadata
27.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()
27.3 JavaScript Stored Programs
27.3.1 JavaScript Stored Program Creation and Management
27.3.2 Obtaining Information About JavaScript Stored Programs
27.3.3 JavaScript Stored Program Language Support
27.3.4 JavaScript Stored Program Data Types and Argument Handling
27.3.5 JavaScript Stored Programs—Session Information and Options
27.3.6 JavaScript SQL API
27.3.7 Using the JavaScript SQL API
27.3.8 JavaScript Stored Program Limitations and Restrictions
27.3.9 JavaScript Stored Program Examples
27.4 Using Triggers
27.4.1 Trigger Syntax and Examples
27.4.2 Trigger Metadata
27.5 Using the Event Scheduler
27.5.1 Event Scheduler Overview
27.5.2 Event Scheduler Configuration
27.5.3 Event Syntax
27.5.4 Event Metadata
27.5.5 Event Scheduler Status
27.5.6 The Event Scheduler and MySQL Privileges
27.6 Using Views
27.6.1 View Syntax
27.6.2 View Processing Algorithms
27.6.3 Updatable and Insertable Views
27.6.4 The View WITH CHECK OPTION Clause
27.6.5 View Metadata
27.7 Stored Object Access Control
27.8 Stored Program Binary Logging
27.9 Restrictions on Stored Programs
27.10 Restrictions on Views
28 INFORMATION_SCHEMA Tables
28.1 Introduction
28.2 INFORMATION_SCHEMA Table Reference
28.3 INFORMATION_SCHEMA General Tables
28.3.1 INFORMATION_SCHEMA General Table Reference
28.3.2 The INFORMATION_SCHEMA ADMINISTRABLE_ROLE_AUTHORIZATIONS Table
28.3.3 The INFORMATION_SCHEMA APPLICABLE_ROLES Table
28.3.4 The INFORMATION_SCHEMA CHARACTER_SETS Table
28.3.5 The INFORMATION_SCHEMA CHECK_CONSTRAINTS Table
28.3.6 The INFORMATION_SCHEMA COLLATIONS Table
28.3.7 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
28.3.8 The INFORMATION_SCHEMA COLUMNS Table
28.3.9 The INFORMATION_SCHEMA COLUMNS_EXTENSIONS Table
28.3.10 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
28.3.11 The INFORMATION_SCHEMA COLUMN_STATISTICS Table
28.3.12 The INFORMATION_SCHEMA ENABLED_ROLES Table
28.3.13 The INFORMATION_SCHEMA ENGINES Table
28.3.14 The INFORMATION_SCHEMA EVENTS Table
28.3.15 The INFORMATION_SCHEMA FILES Table
28.3.16 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
28.3.17 The INFORMATION_SCHEMA KEYWORDS Table
28.3.18 The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table
28.3.19 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table
28.3.20 The INFORMATION_SCHEMA PARAMETERS Table
28.3.21 The INFORMATION_SCHEMA PARTITIONS Table
28.3.22 The INFORMATION_SCHEMA PLUGINS Table
28.3.23 The INFORMATION_SCHEMA PROCESSLIST Table
28.3.24 The INFORMATION_SCHEMA PROFILING Table
28.3.25 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
28.3.26 The INFORMATION_SCHEMA RESOURCE_GROUPS Table
28.3.27 The INFORMATION_SCHEMA ROLE_COLUMN_GRANTS Table
28.3.28 The INFORMATION_SCHEMA ROLE_ROUTINE_GRANTS Table
28.3.29 The INFORMATION_SCHEMA ROLE_TABLE_GRANTS Table
28.3.30 The INFORMATION_SCHEMA ROUTINES Table
28.3.31 The INFORMATION_SCHEMA SCHEMATA Table
28.3.32 The INFORMATION_SCHEMA SCHEMATA_EXTENSIONS Table
28.3.33 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
28.3.34 The INFORMATION_SCHEMA STATISTICS Table
28.3.35 The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table
28.3.36 The INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS Table
28.3.37 The INFORMATION_SCHEMA ST_UNITS_OF_MEASURE Table
28.3.38 The INFORMATION_SCHEMA TABLES Table
28.3.39 The INFORMATION_SCHEMA TABLES_EXTENSIONS Table
28.3.40 The INFORMATION_SCHEMA TABLESPACES_EXTENSIONS Table
28.3.41 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
28.3.42 The INFORMATION_SCHEMA TABLE_CONSTRAINTS_EXTENSIONS Table
28.3.43 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
28.3.44 The INFORMATION_SCHEMA TRIGGERS Table
28.3.45 The INFORMATION_SCHEMA USER_ATTRIBUTES Table
28.3.46 The INFORMATION_SCHEMA USER_PRIVILEGES Table
28.3.47 The INFORMATION_SCHEMA VIEWS Table
28.3.48 The INFORMATION_SCHEMA VIEW_ROUTINE_USAGE Table
28.3.49 The INFORMATION_SCHEMA VIEW_TABLE_USAGE Table
28.4 INFORMATION_SCHEMA InnoDB Tables
28.4.1 INFORMATION_SCHEMA InnoDB Table Reference
28.4.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table
28.4.3 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table
28.4.4 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table
28.4.5 The INFORMATION_SCHEMA INNODB_CACHED_INDEXES Table
28.4.6 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables
28.4.7 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables
28.4.8 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables
28.4.9 The INFORMATION_SCHEMA INNODB_COLUMNS Table
28.4.10 The INFORMATION_SCHEMA INNODB_DATAFILES Table
28.4.11 The INFORMATION_SCHEMA INNODB_FIELDS Table
28.4.12 The INFORMATION_SCHEMA INNODB_FOREIGN Table
28.4.13 The INFORMATION_SCHEMA INNODB_FOREIGN_COLS Table
28.4.14 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table
28.4.15 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table
28.4.16 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table
28.4.17 The INFORMATION_SCHEMA INNODB_FT_DELETED Table
28.4.18 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table
28.4.19 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table
28.4.20 The INFORMATION_SCHEMA INNODB_INDEXES Table
28.4.21 The INFORMATION_SCHEMA INNODB_METRICS Table
28.4.22 The INFORMATION_SCHEMA INNODB_SESSION_TEMP_TABLESPACES Table
28.4.23 The INFORMATION_SCHEMA INNODB_TABLES Table
28.4.24 The INFORMATION_SCHEMA INNODB_TABLESPACES Table
28.4.25 The INFORMATION_SCHEMA INNODB_TABLESPACES_BRIEF Table
28.4.26 The INFORMATION_SCHEMA INNODB_TABLESTATS View
28.4.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table
28.4.28 The INFORMATION_SCHEMA INNODB_TRX Table
28.4.29 The INFORMATION_SCHEMA INNODB_VIRTUAL Table
28.5 INFORMATION_SCHEMA Thread Pool Tables
28.5.1 INFORMATION_SCHEMA Thread Pool Table Reference
28.5.2 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table
28.5.3 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table
28.5.4 The INFORMATION_SCHEMA TP_THREAD_STATE Table
28.6 INFORMATION_SCHEMA Connection-Control Tables
28.6.1 INFORMATION_SCHEMA Connection-Control Table Reference
28.6.2 The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Table
28.7 INFORMATION_SCHEMA MySQL Enterprise Firewall Tables
28.7.1 INFORMATION_SCHEMA Firewall Table Reference
28.7.2 The INFORMATION_SCHEMA MYSQL_FIREWALL_USERS Table
28.7.3 The INFORMATION_SCHEMA MYSQL_FIREWALL_WHITELIST Table
28.8 Extensions to SHOW Statements
29 MySQL Performance Schema
29.1 Performance Schema Quick Start
29.2 Performance Schema Build Configuration
29.3 Performance Schema Startup Configuration
29.4 Performance Schema Runtime Configuration
29.4.1 Performance Schema Event Timing
29.4.2 Performance Schema Event Filtering
29.4.3 Event Pre-Filtering
29.4.4 Pre-Filtering by Instrument
29.4.5 Pre-Filtering by Object
29.4.6 Pre-Filtering by Thread
29.4.7 Pre-Filtering by Consumer
29.4.8 Example Consumer Configurations
29.4.9 Naming Instruments or Consumers for Filtering Operations
29.4.10 Determining What Is Instrumented
29.5 Performance Schema Queries
29.6 Performance Schema Instrument Naming Conventions
29.7 Performance Schema Status Monitoring
29.8 Performance Schema Atom and Molecule Events
29.9 Performance Schema Tables for Current and Historical Events
29.10 Performance Schema Statement Digests and Sampling
29.11 Performance Schema General Table Characteristics
29.12 Performance Schema Table Descriptions
29.12.1 Performance Schema Table Reference
29.12.2 Performance Schema Setup Tables
29.12.3 Performance Schema Instance Tables
29.12.4 Performance Schema Wait Event Tables
29.12.5 Performance Schema Stage Event Tables
29.12.6 Performance Schema Statement Event Tables
29.12.7 Performance Schema Transaction Tables
29.12.8 Performance Schema Connection Tables
29.12.9 Performance Schema Connection Attribute Tables
29.12.10 Performance Schema User-Defined Variable Tables
29.12.11 Performance Schema Replication Tables
29.12.12 Performance Schema NDB Cluster Tables
29.12.13 Performance Schema Lock Tables
29.12.14 Performance Schema System Variable Tables
29.12.15 Performance Schema Status Variable Tables
29.12.16 Performance Schema Thread Pool Tables
29.12.17 Performance Schema Firewall Tables
29.12.18 Performance Schema Keyring Tables
29.12.19 Performance Schema Clone Tables
29.12.20 Performance Schema Summary Tables
29.12.21 Performance Schema Telemetry Tables
29.12.22 Performance Schema Miscellaneous Tables
29.13 Performance Schema Option and Variable Reference
29.14 Performance Schema Command Options
29.15 Performance Schema System Variables
29.16 Performance Schema Status Variables
29.17 The Performance Schema Memory-Allocation Model
29.18 Performance Schema and Plugins
29.19 Using the Performance Schema to Diagnose Problems
29.19.1 Query Profiling Using Performance Schema
29.19.2 Obtaining Parent Event Information
29.20 Restrictions on Performance Schema
30 MySQL sys Schema
30.1 Prerequisites for Using the sys Schema
30.2 Using the sys Schema
30.3 sys Schema Progress Reporting
30.4 sys Schema Object Reference
30.4.1 sys Schema Object Index
30.4.2 sys Schema Tables and Triggers
30.4.3 sys Schema Views
30.4.4 sys Schema Stored Procedures
30.4.5 sys Schema Stored Functions
31 Connectors and APIs
31.1 MySQL Connector/C++
31.2 MySQL Connector/J
31.3 MySQL Connector/NET
31.4 MySQL Connector/ODBC
31.5 MySQL Connector/Python
31.6 MySQL Connector/Node.js
31.7 MySQL C API
31.8 MySQL PHP API
31.9 MySQL Perl API
31.10 MySQL Python API
31.11 MySQL Ruby APIs
31.11.1 The MySQL/Ruby API
31.11.2 The Ruby/MySQL API
31.12 MySQL Tcl API
31.13 MySQL Eiffel Wrapper
32 MySQL Enterprise Edition
32.1 MySQL Enterprise Backup Overview
32.2 MySQL Enterprise Security Overview
32.3 MySQL Enterprise Encryption Overview
32.4 MySQL Enterprise Audit Overview
32.5 MySQL Enterprise Firewall Overview
32.6 MySQL Enterprise Thread Pool Overview
32.7 MySQL Enterprise Data Masking and De-Identification Overview
32.8 MySQL Enterprise Monitor Overview
32.9 MySQL Telemetry
33 MySQL Workbench
34 MySQL on OCI Marketplace
34.1 Prerequisites to Deploying MySQL EE on Oracle Cloud Infrastructure
34.2 Deploying MySQL EE on Oracle Cloud Infrastructure
34.3 Configuring Network Access
34.4 Connecting
34.5 Maintenance
35 Telemetry
35.1 Installing OpenTelemetry Support
35.2 Telemetry Variables
35.3 OpenTelemetry Trace
35.3.1 Configuring Trace Telemetry
35.3.2 Trace Format
35.4 OpenTelemetry Metrics
35.4.1 Configuring Metrics Telemetry
35.4.2 Server Meters
35.4.3 Server Metrics
A MySQL 9.0 Frequently Asked Questions
A.1 MySQL 9.0 FAQ: General
A.2 MySQL 9.0 FAQ: Storage Engines
A.3 MySQL 9.0 FAQ: Server SQL Mode
A.4 MySQL 9.0 FAQ: Stored Procedures and Functions
A.5 MySQL 9.0 FAQ: Triggers
A.6 MySQL 9.0 FAQ: Views
A.7 MySQL 9.0 FAQ: INFORMATION_SCHEMA
A.8 MySQL 9.0 FAQ: Migration
A.9 MySQL 9.0 FAQ: Security
A.10 MySQL 9.0 FAQ: NDB Cluster
A.11 MySQL 9.0 FAQ: MySQL Chinese, Japanese, and Korean Character Sets
A.12 MySQL 9.0 FAQ: Connectors & APIs
A.13 MySQL 9.0 FAQ: C API, libmysql
A.14 MySQL 9.0 FAQ: Replication
A.15 MySQL 9.0 FAQ: MySQL Enterprise Thread Pool
A.16 MySQL 9.0 FAQ: InnoDB Change Buffer
A.17 MySQL 9.0 FAQ: InnoDB Data-at-Rest Encryption
A.18 MySQL 9.0 FAQ: Virtualization Support
B Error Messages and Common Problems
B.1 Error Message Sources and Elements
B.2 Error Information Interfaces
B.3 Problems and Common Errors
B.3.1 How to Determine What Is Causing a Problem
B.3.2 Common Errors When Using MySQL Programs
B.3.3 Administration-Related Issues
B.3.4 Query-Related Issues
B.3.5 Optimizer-Related Issues
B.3.6 Table Definition-Related Issues
B.3.7 Known Issues in MySQL
C Indexes
General Index
C Function Index
Command Index
Function Index
INFORMATION_SCHEMA Index
Join Types Index
Operator Index
Option Index
Privileges Index
SQL Modes Index
Statement/Syntax Index
Status Variable Index
System Variable Index
Transaction Isolation Level Index
MySQL Glossary