MySQL 9.1 Reference Manual

Including MySQL NDB Cluster 9.1

Abstract

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

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

For notes detailing the changes in each release, see the MySQL 9.1 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-11-20 (revision: 80271)

---

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.1

1.5 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 9.1

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.1

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 Replication Applier Metrics Component

7.5.7 Group Replication Flow Control Statistics Component

7.5.8 Multilingual Engine Component (MLE)

7.5.9 Option Tracker 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 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

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)

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 InnoDB 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.1

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.1

25.2.5 Options, Variables, and Parameters Added, Deprecated or Removed in NDB 9.1

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 GenAI API

27.3.9 JavaScript Stored Program Limitations and Restrictions

27.3.10 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

35.5 OpenTelemetry Logging

35.5.1 Configuring Log Telemetry

A MySQL 9.1 Frequently Asked Questions

A.1 MySQL 9.1 FAQ: General

A.2 MySQL 9.1 FAQ: Storage Engines

A.3 MySQL 9.1 FAQ: Server SQL Mode

A.4 MySQL 9.1 FAQ: Stored Procedures and Functions

A.5 MySQL 9.1 FAQ: Triggers

A.6 MySQL 9.1 FAQ: Views

A.7 MySQL 9.1 FAQ: INFORMATION_SCHEMA

A.8 MySQL 9.1 FAQ: Migration

A.9 MySQL 9.1 FAQ: Security

A.10 MySQL 9.1 FAQ: NDB Cluster

A.11 MySQL 9.1 FAQ: MySQL Chinese, Japanese, and Korean Character Sets

A.12 MySQL 9.1 FAQ: Connectors & APIs

A.13 MySQL 9.1 FAQ: C API, libmysql

A.14 MySQL 9.1 FAQ: Replication

A.15 MySQL 9.1 FAQ: MySQL Enterprise Thread Pool

A.16 MySQL 9.1 FAQ: InnoDB Change Buffer

A.17 MySQL 9.1 FAQ: InnoDB Data-at-Rest Encryption

A.18 MySQL 9.1 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