MySQL 8.0 Reference Manual
Table of Contents
- 1 General Information
- 1.1 About This Manual
- 1.2 Typographical and Syntax Conventions
- 1.3 Overview of the MySQL Database Management System
- 1.3.1 What is MySQL?
- 1.3.2 The Main Features of MySQL
- 1.3.3 History of MySQL
- 1.4 What Is New in MySQL 8.0
- 1.5 Server and Status Variables and Options Added, Deprecated, or Removed in
MySQL 8.0
- 1.6 MySQL Information Sources
- 1.6.1 MySQL Websites
- 1.6.2 MySQL Community Support at the MySQL Forums
- 1.6.3 MySQL Enterprise
- 1.7 How to Report Bugs or Problems
- 1.8 MySQL Standards Compliance
- 1.8.1 MySQL Extensions to Standard SQL
- 1.8.2 MySQL Differences from Standard SQL
- 1.8.3 How MySQL Deals with Constraints
- 1.9 Credits
- 1.9.1 Contributors to MySQL
- 1.9.2 Documenters and translators
- 1.9.3 Packages that support MySQL
- 1.9.4 Tools that were used to create MySQL
- 1.9.5 Supporters of MySQL
- 2 Installing and Upgrading MySQL
- 2.1 General Installation Guidance
- 2.1.1 Which MySQL Version and Distribution to Install
- 2.1.2 How to Get MySQL
- 2.1.3 Verifying Package Integrity Using MD5 Checksums or GnuPG
- 2.1.4 Installation Layouts
- 2.1.5 Compiler-Specific Build Characteristics
- 2.2 Installing MySQL on Unix/Linux Using Generic Binaries
- 2.3 Installing MySQL on Microsoft Windows
- 2.3.1 MySQL Installation Layout on Microsoft Windows
- 2.3.2 Choosing an Installation Package
- 2.3.3 MySQL Installer for Windows
- 2.3.4 Installing MySQL on Microsoft Windows Using a
noinstall
ZIP Archive - 2.3.5 Troubleshooting a Microsoft Windows MySQL Server Installation
- 2.3.6 Windows Postinstallation Procedures
- 2.3.7 Windows Platform Restrictions
- 2.4 Installing MySQL on macOS
- 2.4.1 General Notes on Installing MySQL on macOS
- 2.4.2 Installing MySQL on macOS Using Native Packages
- 2.4.3 Installing and Using the MySQL Launch Daemon
- 2.4.4 Installing and Using the MySQL Preference Pane
- 2.5 Installing MySQL on Linux
- 2.5.1 Installing MySQL on Linux Using the MySQL Yum Repository
- 2.5.2 Installing MySQL on Linux Using the MySQL APT Repository
- 2.5.3 Installing MySQL on Linux Using the MySQL SLES Repository
- 2.5.4 Installing MySQL on Linux Using RPM Packages from Oracle
- 2.5.5 Installing MySQL on Linux Using Debian Packages from Oracle
- 2.5.6 Deploying MySQL on Linux with Docker
- 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 on FreeBSD
- 2.9 Installing MySQL from Source
- 2.9.1 Source Installation Methods
- 2.9.2 Source Installation Prerequisites
- 2.9.3 MySQL Layout for Source Installation
- 2.9.4 Installing MySQL Using a Standard Source Distribution
- 2.9.5 Installing MySQL Using a Development Source Tree
- 2.9.6 Configuring SSL Library Support
- 2.9.7 MySQL Source-Configuration Options
- 2.9.8 Dealing with Problems Compiling MySQL
- 2.9.9 MySQL Configuration and Third-Party Tools
- 2.9.10 Generating MySQL Doxygen Documentation Content
- 2.10 Postinstallation Setup and Testing
- 2.10.1 Initializing the Data Directory
- 2.10.2 Starting the Server
- 2.10.3 Testing the Server
- 2.10.4 Securing the Initial MySQL Account
- 2.10.5 Starting and Stopping MySQL Automatically
- 2.11 Upgrading MySQL
- 2.11.1 Before You Begin
- 2.11.2 Upgrade Paths
- 2.11.3 What the MySQL Upgrade Process Upgrades
- 2.11.4 Changes in MySQL 8.0
- 2.11.5 Preparing Your Installation for Upgrade
- 2.11.6 Upgrading MySQL Binary or Package-based Installations on Unix/Linux
- 2.11.7 Upgrading MySQL with the MySQL Yum Repository
- 2.11.8 Upgrading MySQL with the MySQL APT Repository
- 2.11.9 Upgrading MySQL with the MySQL SLES Repository
- 2.11.10 Upgrading MySQL on Windows
- 2.11.11 Upgrading a Docker Installation of MySQL
- 2.11.12 Upgrade Troubleshooting
- 2.11.13 Rebuilding or Repairing Tables or Indexes
- 2.11.14 Copying MySQL Databases to Another Machine
- 2.12 Downgrading MySQL
- 2.13 Perl Installation Notes
- 2.13.1 Installing Perl on Unix
- 2.13.2 Installing ActiveState Perl on Windows
- 2.13.3 Problems Using the Perl DBI/DBD Interface
- 3 Tutorial
- 3.1 Connecting to and Disconnecting from the Server
- 3.2 Entering Queries
- 3.3 Creating and Using a Database
- 3.3.1 Creating and Selecting a Database
- 3.3.2 Creating a Table
- 3.3.3 Loading Data into a Table
- 3.3.4 Retrieving Information from a Table
- 3.4 Getting Information About Databases and Tables
- 3.5 Using mysql in Batch Mode
- 3.6 Examples of Common Queries
- 3.6.1 The Maximum Value for a Column
- 3.6.2 The Row Holding the Maximum of a Certain Column
- 3.6.3 Maximum of Column per Group
- 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column
- 3.6.5 Using User-Defined Variables
- 3.6.6 Using Foreign Keys
- 3.6.7 Searching on Two Keys
- 3.6.8 Calculating Visits Per Day
- 3.6.9 Using AUTO_INCREMENT
- 3.7 Using MySQL with Apache
- 4 MySQL Programs
- 4.1 Overview of MySQL Programs
- 4.2 Using MySQL Programs
- 4.2.1 Invoking MySQL Programs
- 4.2.2 Specifying Program Options
- 4.2.3 Command Options for Connecting to the Server
- 4.2.4 Connecting to the MySQL Server Using Command Options
- 4.2.5 Connecting to the Server Using URI-Like Strings or Key-Value Pairs
- 4.2.6 Connection Compression Control
- 4.2.7 Setting Environment Variables
- 4.3 Server and Server-Startup Programs
- 4.3.1 mysqld — The MySQL Server
- 4.3.2 mysqld_safe — MySQL Server Startup Script
- 4.3.3 mysql.server — MySQL Server Startup Script
- 4.3.4 mysqld_multi — Manage Multiple MySQL Servers
- 4.4 Installation-Related Programs
- 4.4.1 comp_err — Compile MySQL Error Message File
- 4.4.2 mysql_secure_installation — Improve MySQL Installation Security
- 4.4.3 mysql_ssl_rsa_setup — Create SSL/RSA Files
- 4.4.4 mysql_tzinfo_to_sql — Load the Time Zone Tables
- 4.4.5 mysql_upgrade — Check and Upgrade MySQL Tables
- 4.5 Client Programs
- 4.5.1 mysql — The MySQL Command-Line Client
- 4.5.2 mysqladmin — Client for Administering a MySQL Server
- 4.5.3 mysqlcheck — A Table Maintenance Program
- 4.5.4 mysqldump — A Database Backup Program
- 4.5.5 mysqlimport — A Data Import Program
- 4.5.6 mysqlpump — A Database Backup Program
- 4.5.7 mysqlshow — Display Database, Table, and Column Information
- 4.5.8 mysqlslap — Load Emulation Client
- 4.6 Administrative and Utility Programs
- 4.6.1 ibd2sdi — InnoDB Tablespace SDI Extraction Utility
- 4.6.2 innochecksum — Offline InnoDB File Checksum Utility
- 4.6.3 myisam_ftdump — Display Full-Text Index information
- 4.6.4 myisamchk — MyISAM Table-Maintenance Utility
- 4.6.5 myisamlog — Display MyISAM Log File Contents
- 4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables
- 4.6.7 mysql_config_editor — MySQL Configuration Utility
- 4.6.8 mysqlbinlog — Utility for Processing Binary Log Files
- 4.6.9 mysqldumpslow — Summarize Slow Query Log Files
- 4.7 Program Development Utilities
- 4.7.1 mysql_config — Display Options for Compiling Clients
- 4.7.2 my_print_defaults — Display Options from Option Files
- 4.8 Miscellaneous Programs
- 4.8.1 lz4_decompress — Decompress mysqlpump LZ4-Compressed Output
- 4.8.2 perror — Display MySQL Error Message Information
- 4.8.3 zlib_decompress — Decompress mysqlpump ZLIB-Compressed Output
- 4.9 Environment Variables
- 4.10 Unix Signal Handling in MySQL
- 5 MySQL Server Administration
- 5.1 The MySQL Server
- 5.1.1 Configuring the Server
- 5.1.2 Server Configuration Defaults
- 5.1.3 Server Configuration Validation
- 5.1.4 Server Option, System Variable, and Status Variable Reference
- 5.1.5 Server System Variable Reference
- 5.1.6 Server Status Variable Reference
- 5.1.7 Server Command Options
- 5.1.8 Server System Variables
- 5.1.9 Using System Variables
- 5.1.10 Server Status Variables
- 5.1.11 Server SQL Modes
- 5.1.12 IPv6 Support
- 5.1.13 MySQL Server Time Zone Support
- 5.1.14 Server-Side Help Support
- 5.1.15 Server Tracking of Client Session State Changes
- 5.1.16 The Server Shutdown Process
- 5.2 The MySQL Data Directory
- 5.3 The mysql System Schema
- 5.4 MySQL Server Logs
- 5.4.1 Selecting General Query Log and Slow Query Log Output Destinations
- 5.4.2 The Error Log
- 5.4.3 The General Query Log
- 5.4.4 The Binary Log
- 5.4.5 The Slow Query Log
- 5.4.6 The DDL Log
- 5.4.7 Server Log Maintenance
- 5.5 MySQL Server Components
- 5.5.1 Installing and Uninstalling Components
- 5.5.2 Obtaining Server Component Information
- 5.5.3 Error Log Components
- 5.6 MySQL Server Plugins
- 5.6.1 Installing and Uninstalling Plugins
- 5.6.2 Obtaining Server Plugin Information
- 5.6.3 MySQL Enterprise Thread Pool
- 5.6.4 The Rewriter Query Rewrite Plugin
- 5.6.5 The ddl_rewriter Plugin
- 5.6.6 Version Tokens
- 5.6.7 The Clone Plugin
- 5.7 MySQL Server User-Defined Functions
- 5.7.1 Installing and Uninstalling User-Defined Functions
- 5.7.2 Obtaining User-Defined Function Information
- 5.8 Running Multiple MySQL Instances on One Machine
- 5.8.1 Setting Up Multiple Data Directories
- 5.8.2 Running Multiple MySQL Instances on Windows
- 5.8.3 Running Multiple MySQL Instances on Unix
- 5.8.4 Using Client Programs in a Multiple-Server Environment
- 6 Security
- 6.1 General Security Issues
- 6.1.1 Security Guidelines
- 6.1.2 Keeping Passwords Secure
- 6.1.3 Making MySQL Secure Against Attackers
- 6.1.4 Security-Related mysqld Options and Variables
- 6.1.5 How to Run MySQL as a Normal User
- 6.1.6 Security Issues with LOAD DATA LOCAL
- 6.1.7 Client Programming Security Guidelines
- 6.2 Access Control and Account Management
- 6.2.1 Account User Names and Passwords
- 6.2.2 Privileges Provided by MySQL
- 6.2.3 Grant Tables
- 6.2.4 Specifying Account Names
- 6.2.5 Specifying Role Names
- 6.2.6 Access Control, Stage 1: Connection Verification
- 6.2.7 Access Control, Stage 2: Request Verification
- 6.2.8 Adding Accounts, Assigning Privileges, and Dropping Accounts
- 6.2.9 Reserved Accounts
- 6.2.10 Using Roles
- 6.2.11 Account Categories
- 6.2.12 Privilege Restriction Using Partial Revokes
- 6.2.13 When Privilege Changes Take Effect
- 6.2.14 Assigning Account Passwords
- 6.2.15 Password Management
- 6.2.16 Server Handling of Expired Passwords
- 6.2.17 Pluggable Authentication
- 6.2.18 Proxy Users
- 6.2.19 Account Locking
- 6.2.20 Setting Account Resource Limits
- 6.2.21 Troubleshooting Problems Connecting to MySQL
- 6.2.22 SQL-Based Account Activity Auditing
- 6.3 Using Encrypted Connections
- 6.3.1 Configuring MySQL to Use Encrypted Connections
- 6.3.2 Encrypted Connection TLS Protocols and Ciphers
- 6.3.3 Creating SSL and RSA Certificates and Keys
- 6.3.4 Connecting to MySQL Remotely from Windows with SSH
- 6.4 Security Components and Plugins
- 6.4.1 Authentication Plugins
- 6.4.2 The Connection-Control Plugins
- 6.4.3 The Password Validation Component
- 6.4.4 The MySQL Keyring
- 6.4.5 MySQL Enterprise Audit
- 6.4.6 The Audit Message Component
- 6.4.7 MySQL Enterprise Firewall
- 6.4.8 MySQL Enterprise Data Masking and De-Identification
- 6.5 FIPS Support
- 7 Backup and Recovery
- 7.1 Backup and Recovery Types
- 7.2 Database Backup Methods
- 7.3 Example Backup and Recovery Strategy
- 7.3.1 Establishing a Backup Policy
- 7.3.2 Using Backups for Recovery
- 7.3.3 Backup Strategy Summary
- 7.4 Using mysqldump for Backups
- 7.4.1 Dumping Data in SQL Format with mysqldump
- 7.4.2 Reloading SQL-Format Backups
- 7.4.3 Dumping Data in Delimited-Text Format with mysqldump
- 7.4.4 Reloading Delimited-Text Format Backups
- 7.4.5 mysqldump Tips
- 7.5 Point-in-Time (Incremental) Recovery Using the Binary Log
- 7.5.1 Point-in-Time Recovery Using Event Times
- 7.5.2 Point-in-Time Recovery Using Event Positions
- 7.6 MyISAM Table Maintenance and Crash Recovery
- 7.6.1 Using myisamchk for Crash Recovery
- 7.6.2 How to Check MyISAM Tables for Errors
- 7.6.3 How to Repair MyISAM Tables
- 7.6.4 MyISAM Table Optimization
- 7.6.5 Setting Up a MyISAM Table Maintenance Schedule
- 8 Optimization
- 8.1 Optimization Overview
- 8.2 Optimizing SQL Statements
- 8.2.1 Optimizing SELECT Statements
- 8.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table
Expressions
- 8.2.3 Optimizing INFORMATION_SCHEMA Queries
- 8.2.4 Optimizing Performance Schema Queries
- 8.2.5 Optimizing Data Change Statements
- 8.2.6 Optimizing Database Privileges
- 8.2.7 Other Optimization Tips
- 8.3 Optimization and Indexes
- 8.3.1 How MySQL Uses Indexes
- 8.3.2 Primary Key Optimization
- 8.3.3 SPATIAL Index Optimization
- 8.3.4 Foreign Key Optimization
- 8.3.5 Column Indexes
- 8.3.6 Multiple-Column Indexes
- 8.3.7 Verifying Index Usage
- 8.3.8 InnoDB and MyISAM Index Statistics Collection
- 8.3.9 Comparison of B-Tree and Hash Indexes
- 8.3.10 Use of Index Extensions
- 8.3.11 Optimizer Use of Generated Column Indexes
- 8.3.12 Invisible Indexes
- 8.3.13 Descending Indexes
- 8.3.14 Indexed Lookups from TIMESTAMP Columns
- 8.4 Optimizing Database Structure
- 8.4.1 Optimizing Data Size
- 8.4.2 Optimizing MySQL Data Types
- 8.4.3 Optimizing for Many Tables
- 8.4.4 Internal Temporary Table Use in MySQL
- 8.4.5 Limits on Number of Databases and Tables
- 8.4.6 Limits on Table Size
- 8.4.7 Limits on Table Column Count and Row Size
- 8.5 Optimizing for InnoDB Tables
- 8.5.1 Optimizing Storage Layout for InnoDB Tables
- 8.5.2 Optimizing InnoDB Transaction Management
- 8.5.3 Optimizing InnoDB Read-Only Transactions
- 8.5.4 Optimizing InnoDB Redo Logging
- 8.5.5 Bulk Data Loading for InnoDB Tables
- 8.5.6 Optimizing InnoDB Queries
- 8.5.7 Optimizing InnoDB DDL Operations
- 8.5.8 Optimizing InnoDB Disk I/O
- 8.5.9 Optimizing InnoDB Configuration Variables
- 8.5.10 Optimizing InnoDB for Systems with Many Tables
- 8.6 Optimizing for MyISAM Tables
- 8.6.1 Optimizing MyISAM Queries
- 8.6.2 Bulk Data Loading for MyISAM Tables
- 8.6.3 Optimizing REPAIR TABLE Statements
- 8.7 Optimizing for MEMORY Tables
- 8.8 Understanding the Query Execution Plan
- 8.8.1 Optimizing Queries with EXPLAIN
- 8.8.2 EXPLAIN Output Format
- 8.8.3 Extended EXPLAIN Output Format
- 8.8.4 Obtaining Execution Plan Information for a Named Connection
- 8.8.5 Estimating Query Performance
- 8.9 Controlling the Query Optimizer
- 8.9.1 Controlling Query Plan Evaluation
- 8.9.2 Switchable Optimizations
- 8.9.3 Optimizer Hints
- 8.9.4 Index Hints
- 8.9.5 The Optimizer Cost Model
- 8.9.6 Optimizer Statistics
- 8.10 Buffering and Caching
- 8.10.1 InnoDB Buffer Pool Optimization
- 8.10.2 The MyISAM Key Cache
- 8.10.3 Caching of Prepared Statements and Stored Programs
- 8.11 Optimizing Locking Operations
- 8.11.1 Internal Locking Methods
- 8.11.2 Table Locking Issues
- 8.11.3 Concurrent Inserts
- 8.11.4 Metadata Locking
- 8.11.5 External Locking
- 8.12 Optimizing the MySQL Server
- 8.12.1 Optimizing Disk I/O
- 8.12.2 Using Symbolic Links
- 8.12.3 Optimizing Memory Use
- 8.12.4 Optimizing Network Use
- 8.12.5 Resource Groups
- 8.13 Measuring Performance (Benchmarking)
- 8.13.1 Measuring the Speed of Expressions and Functions
- 8.13.2 Using Your Own Benchmarks
- 8.13.3 Measuring Performance with performance_schema
- 8.14 Examining Thread Information
- 8.14.1 Thread Command Values
- 8.14.2 General Thread States
- 8.14.3 Replication Master Thread States
- 8.14.4 Replication Slave I/O Thread States
- 8.14.5 Replication Slave SQL Thread States
- 8.14.6 Replication Slave Connection Thread States
- 8.14.7 NDB Cluster Thread States
- 8.14.8 Event Scheduler Thread States
- 9 Language Structure
- 9.1 Literal Values
- 9.1.1 String Literals
- 9.1.2 Numeric Literals
- 9.1.3 Date and Time Literals
- 9.1.4 Hexadecimal Literals
- 9.1.5 Bit-Value Literals
- 9.1.6 Boolean Literals
- 9.1.7 NULL Values
- 9.2 Schema Object Names
- 9.2.1 Identifier Length Limits
- 9.2.2 Identifier Qualifiers
- 9.2.3 Identifier Case Sensitivity
- 9.2.4 Mapping of Identifiers to File Names
- 9.2.5 Function Name Parsing and Resolution
- 9.3 Keywords and Reserved Words
- 9.4 User-Defined Variables
- 9.5 Expressions
- 9.6 Comment Syntax
- 10 Character Sets, Collations, Unicode
- 10.1 Character Sets and Collations in General
- 10.2 Character Sets and Collations in MySQL
- 10.2.1 Character Set Repertoire
- 10.2.2 UTF-8 for Metadata
- 10.3 Specifying Character Sets and Collations
- 10.3.1 Collation Naming Conventions
- 10.3.2 Server Character Set and Collation
- 10.3.3 Database Character Set and Collation
- 10.3.4 Table Character Set and Collation
- 10.3.5 Column Character Set and Collation
- 10.3.6 Character String Literal Character Set and Collation
- 10.3.7 The National Character Set
- 10.3.8 Character Set Introducers
- 10.3.9 Examples of Character Set and Collation Assignment
- 10.3.10 Compatibility with Other DBMSs
- 10.4 Connection Character Sets and Collations
- 10.5 Configuring Application Character Set and Collation
- 10.6 Error Message Character Set
- 10.7 Column Character Set Conversion
- 10.8 Collation Issues
- 10.8.1 Using COLLATE in SQL Statements
- 10.8.2 COLLATE Clause Precedence
- 10.8.3 Character Set and Collation Compatibility
- 10.8.4 Collation Coercibility in Expressions
- 10.8.5 The binary Collation Compared to _bin Collations
- 10.8.6 Examples of the Effect of Collation
- 10.8.7 Using Collation in INFORMATION_SCHEMA Searches
- 10.9 Unicode Support
- 10.9.1 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)
- 10.9.2 The utf8mb3 Character Set (3-Byte UTF-8 Unicode Encoding)
- 10.9.3 The utf8 Character Set (Alias for utf8mb3)
- 10.9.4 The ucs2 Character Set (UCS-2 Unicode Encoding)
- 10.9.5 The utf16 Character Set (UTF-16 Unicode Encoding)
- 10.9.6 The utf16le Character Set (UTF-16LE Unicode Encoding)
- 10.9.7 The utf32 Character Set (UTF-32 Unicode Encoding)
- 10.9.8 Converting Between 3-Byte and 4-Byte Unicode Character Sets
- 10.10 Supported Character Sets and Collations
- 10.10.1 Unicode Character Sets
- 10.10.2 West European Character Sets
- 10.10.3 Central European Character Sets
- 10.10.4 South European and Middle East Character Sets
- 10.10.5 Baltic Character Sets
- 10.10.6 Cyrillic Character Sets
- 10.10.7 Asian Character Sets
- 10.10.8 The Binary Character Set
- 10.11 Restrictions on Character Sets
- 10.12 Setting the Error Message Language
- 10.13 Adding a Character Set
- 10.13.1 Character Definition Arrays
- 10.13.2 String Collating Support for Complex Character Sets
- 10.13.3 Multi-Byte Character Support for Complex Character Sets
- 10.14 Adding a Collation to a Character Set
- 10.14.1 Collation Implementation Types
- 10.14.2 Choosing a Collation ID
- 10.14.3 Adding a Simple Collation to an 8-Bit Character Set
- 10.14.4 Adding a UCA Collation to a Unicode Character Set
- 10.15 Character Set Configuration
- 10.16 MySQL Server Locale Support
- 11 Data Types
- 11.1 Numeric Data Types
- 11.1.1 Numeric Data Type Syntax
- 11.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT,
MEDIUMINT, BIGINT
- 11.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC
- 11.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE
- 11.1.5 Bit-Value Type - BIT
- 11.1.6 Numeric Type Attributes
- 11.1.7 Out-of-Range and Overflow Handling
- 11.2 Date and Time Data Types
- 11.2.1 Date and Time Data Type Syntax
- 11.2.2 The DATE, DATETIME, and TIMESTAMP Types
- 11.2.3 The TIME Type
- 11.2.4 The YEAR Type
- 11.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME
- 11.2.6 Fractional Seconds in Time Values
- 11.2.7 Conversion Between Date and Time Types
- 11.2.8 2-Digit Years in Dates
- 11.3 String Data Types
- 11.3.1 String Data Type Syntax
- 11.3.2 The CHAR and VARCHAR Types
- 11.3.3 The BINARY and VARBINARY Types
- 11.3.4 The BLOB and TEXT Types
- 11.3.5 The ENUM Type
- 11.3.6 The SET Type
- 11.4 Spatial Data Types
- 11.4.1 Spatial Data Types
- 11.4.2 The OpenGIS Geometry Model
- 11.4.3 Supported Spatial Data Formats
- 11.4.4 Geometry Well-Formedness and Validity
- 11.4.5 Spatial Reference System Support
- 11.4.6 Creating Spatial Columns
- 11.4.7 Populating Spatial Columns
- 11.4.8 Fetching Spatial Data
- 11.4.9 Optimizing Spatial Analysis
- 11.4.10 Creating Spatial Indexes
- 11.4.11 Using Spatial Indexes
- 11.5 The JSON Data Type
- 11.6 Data Type Default Values
- 11.7 Data Type Storage Requirements
- 11.8 Choosing the Right Type for a Column
- 11.9 Using Data Types from Other Database Engines
- 12 Functions and Operators
- 12.1 Function and Operator Reference
- 12.2 Type Conversion in Expression Evaluation
- 12.3 Operators
- 12.3.1 Operator Precedence
- 12.3.2 Comparison Functions and Operators
- 12.3.3 Logical Operators
- 12.3.4 Assignment Operators
- 12.4 Control Flow Functions
- 12.5 Numeric Functions and Operators
- 12.5.1 Arithmetic Operators
- 12.5.2 Mathematical Functions
- 12.6 Date and Time Functions
- 12.7 String Functions and Operators
- 12.7.1 String Comparison Functions and Operators
- 12.7.2 Regular Expressions
- 12.7.3 Character Set and Collation of Function Results
- 12.8 What Calendar Is Used By MySQL?
- 12.9 Full-Text Search Functions
- 12.9.1 Natural Language Full-Text Searches
- 12.9.2 Boolean Full-Text Searches
- 12.9.3 Full-Text Searches with Query Expansion
- 12.9.4 Full-Text Stopwords
- 12.9.5 Full-Text Restrictions
- 12.9.6 Fine-Tuning MySQL Full-Text Search
- 12.9.7 Adding a Collation for Full-Text Indexing
- 12.9.8 ngram Full-Text Parser
- 12.9.9 MeCab Full-Text Parser Plugin
- 12.10 Cast Functions and Operators
- 12.11 XML Functions
- 12.12 Bit Functions and Operators
- 12.13 Encryption and Compression Functions
- 12.14 Locking Functions
- 12.15 Information Functions
- 12.16 Spatial Analysis Functions
- 12.16.1 Spatial Function Reference
- 12.16.2 Argument Handling by Spatial Functions
- 12.16.3 Functions That Create Geometry Values from WKT Values
- 12.16.4 Functions That Create Geometry Values from WKB Values
- 12.16.5 MySQL-Specific Functions That Create Geometry Values
- 12.16.6 Geometry Format Conversion Functions
- 12.16.7 Geometry Property Functions
- 12.16.8 Spatial Operator Functions
- 12.16.9 Functions That Test Spatial Relations Between Geometry Objects
- 12.16.10 Spatial Geohash Functions
- 12.16.11 Spatial GeoJSON Functions
- 12.16.12 Spatial Convenience Functions
- 12.17 JSON Functions
- 12.17.1 JSON Function Reference
- 12.17.2 Functions That Create JSON Values
- 12.17.3 Functions That Search JSON Values
- 12.17.4 Functions That Modify JSON Values
- 12.17.5 Functions That Return JSON Value Attributes
- 12.17.6 JSON Table Functions
- 12.17.7 JSON Schema Validation Functions
- 12.17.8 JSON Utility Functions
- 12.18 Functions Used with Global Transaction Identifiers (GTIDs)
- 12.19 MySQL Enterprise Encryption Functions
- 12.19.1 MySQL Enterprise Encryption Installation
- 12.19.2 MySQL Enterprise Encryption Usage and Examples
- 12.19.3 MySQL Enterprise Encryption Function Reference
- 12.19.4 MySQL Enterprise Encryption Function Descriptions
- 12.20 Aggregate (GROUP BY) Functions
- 12.20.1 Aggregate (GROUP BY) Function Descriptions
- 12.20.2 GROUP BY Modifiers
- 12.20.3 MySQL Handling of GROUP BY
- 12.20.4 Detection of Functional Dependence
- 12.21 Window Functions
- 12.21.1 Window Function Descriptions
- 12.21.2 Window Function Concepts and Syntax
- 12.21.3 Window Function Frame Specification
- 12.21.4 Named Windows
- 12.21.5 Window Function Restrictions
- 12.22 Performance Schema Functions
- 12.23 Internal Functions
- 12.24 Miscellaneous Functions
- 12.25 Precision Math
- 12.25.1 Types of Numeric Values
- 12.25.2 DECIMAL Data Type Characteristics
- 12.25.3 Expression Handling
- 12.25.4 Rounding Behavior
- 12.25.5 Precision Math Examples
- 13 SQL Statements
- 13.1 Data Definition Statements
- 13.1.1 Atomic Data Definition Statement Support
- 13.1.2 ALTER DATABASE Statement
- 13.1.3 ALTER EVENT Statement
- 13.1.4 ALTER FUNCTION Statement
- 13.1.5 ALTER INSTANCE Statement
- 13.1.6 ALTER LOGFILE GROUP Statement
- 13.1.7 ALTER PROCEDURE Statement
- 13.1.8 ALTER SERVER Statement
- 13.1.9 ALTER TABLE Statement
- 13.1.10 ALTER TABLESPACE Statement
- 13.1.11 ALTER VIEW Statement
- 13.1.12 CREATE DATABASE Statement
- 13.1.13 CREATE EVENT Statement
- 13.1.14 CREATE FUNCTION Statement
- 13.1.15 CREATE INDEX Statement
- 13.1.16 CREATE LOGFILE GROUP Statement
- 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements
- 13.1.18 CREATE SERVER Statement
- 13.1.19 CREATE SPATIAL REFERENCE SYSTEM Statement
- 13.1.20 CREATE TABLE Statement
- 13.1.21 CREATE TABLESPACE Statement
- 13.1.22 CREATE TRIGGER Statement
- 13.1.23 CREATE VIEW Statement
- 13.1.24 DROP DATABASE Statement
- 13.1.25 DROP EVENT Statement
- 13.1.26 DROP FUNCTION Statement
- 13.1.27 DROP INDEX Statement
- 13.1.28 DROP LOGFILE GROUP Statement
- 13.1.29 DROP PROCEDURE and DROP FUNCTION Statements
- 13.1.30 DROP SERVER Statement
- 13.1.31 DROP SPATIAL REFERENCE SYSTEM Statement
- 13.1.32 DROP TABLE Statement
- 13.1.33 DROP TABLESPACE Statement
- 13.1.34 DROP TRIGGER Statement
- 13.1.35 DROP VIEW Statement
- 13.1.36 RENAME TABLE Statement
- 13.1.37 TRUNCATE TABLE Statement
- 13.2 Data Manipulation Statements
- 13.2.1 CALL Statement
- 13.2.2 DELETE Statement
- 13.2.3 DO Statement
- 13.2.4 HANDLER Statement
- 13.2.5 IMPORT TABLE Statement
- 13.2.6 INSERT Statement
- 13.2.7 LOAD DATA Statement
- 13.2.8 LOAD XML Statement
- 13.2.9 REPLACE Statement
- 13.2.10 SELECT Statement
- 13.2.11 Subqueries
- 13.2.12 TABLE Statement
- 13.2.13 UPDATE Statement
- 13.2.14 VALUES Statement
- 13.2.15 WITH (Common Table Expressions)
- 13.3 Transactional and Locking Statements
- 13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements
- 13.3.2 Statements That Cannot Be Rolled Back
- 13.3.3 Statements That Cause an Implicit Commit
- 13.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements
- 13.3.5 LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements
- 13.3.6 LOCK TABLES and UNLOCK TABLES Statements
- 13.3.7 SET TRANSACTION Statement
- 13.3.8 XA Transactions
- 13.4 Replication Statements
- 13.4.1 SQL Statements for Controlling Master Servers
- 13.4.2 SQL Statements for Controlling Slave Servers
- 13.4.3 SQL Statements for Controlling Group Replication
- 13.5 Prepared Statements
- 13.5.1 PREPARE Statement
- 13.5.2 EXECUTE Statement
- 13.5.3 DEALLOCATE PREPARE Statement
- 13.6 Compound Statement Syntax
- 13.6.1 BEGIN ... END Compound Statement
- 13.6.2 Statement Labels
- 13.6.3 DECLARE Statement
- 13.6.4 Variables in Stored Programs
- 13.6.5 Flow Control Statements
- 13.6.6 Cursors
- 13.6.7 Condition Handling
- 13.6.8 Restrictions on Condition Handling
- 13.7 Database Administration Statements
- 13.7.1 Account Management Statements
- 13.7.2 Resource Group Management Statements
- 13.7.3 Table Maintenance Statements
- 13.7.4 Component, Plugin, and User-Defined Function Statements
- 13.7.5 CLONE Statement
- 13.7.6 SET Statements
- 13.7.7 SHOW Statements
- 13.7.8 Other Administrative Statements
- 13.8 Utility Statements
- 13.8.1 DESCRIBE Statement
- 13.8.2 EXPLAIN Statement
- 13.8.3 HELP Statement
- 13.8.4 USE Statement
- 14 MySQL Data Dictionary
- 14.1 Data Dictionary Schema
- 14.2 Removal of File-based Metadata Storage
- 14.3 Transactional Storage of Dictionary Data
- 14.4 Dictionary Object Cache
- 14.5 INFORMATION_SCHEMA and Data Dictionary Integration
- 14.6 Serialized Dictionary Information (SDI)
- 14.7 Data Dictionary Usage Differences
- 14.8 Data Dictionary Limitations
- 15 The InnoDB Storage Engine
- 15.1 Introduction to InnoDB
- 15.1.1 Benefits of Using InnoDB Tables
- 15.1.2 Best Practices for InnoDB Tables
- 15.1.3 Verifying that InnoDB is the Default Storage Engine
- 15.1.4 Testing and Benchmarking with InnoDB
- 15.2 InnoDB and the ACID Model
- 15.3 InnoDB Multi-Versioning
- 15.4 InnoDB Architecture
- 15.5 InnoDB In-Memory Structures
- 15.5.1 Buffer Pool
- 15.5.2 Change Buffer
- 15.5.3 Adaptive Hash Index
- 15.5.4 Log Buffer
- 15.6 InnoDB On-Disk Structures
- 15.6.1 Tables
- 15.6.2 Indexes
- 15.6.3 Tablespaces
- 15.6.4 Doublewrite Buffer
- 15.6.5 Redo Log
- 15.6.6 Undo Logs
- 15.7 InnoDB Locking and Transaction Model
- 15.7.1 InnoDB Locking
- 15.7.2 InnoDB Transaction Model
- 15.7.3 Locks Set by Different SQL Statements in InnoDB
- 15.7.4 Phantom Rows
- 15.7.5 Deadlocks in InnoDB
- 15.8 InnoDB Configuration
- 15.8.1 InnoDB Startup Configuration
- 15.8.2 Configuring InnoDB for Read-Only Operation
- 15.8.3 InnoDB Buffer Pool Configuration
- 15.8.4 Configuring Thread Concurrency for InnoDB
- 15.8.5 Configuring the Number of Background InnoDB I/O Threads
- 15.8.6 Using Asynchronous I/O on Linux
- 15.8.7 Configuring InnoDB I/O Capacity
- 15.8.8 Configuring Spin Lock Polling
- 15.8.9 Purge Configuration
- 15.8.10 Configuring Optimizer Statistics for InnoDB
- 15.8.11 Configuring the Merge Threshold for Index Pages
- 15.8.12 Enabling Automatic Configuration for a Dedicated MySQL Server
- 15.9 InnoDB Table and Page Compression
- 15.9.1 InnoDB Table Compression
- 15.9.2 InnoDB Page Compression
- 15.10 InnoDB Row Formats
- 15.11 InnoDB Disk I/O and File Space Management
- 15.11.1 InnoDB Disk I/O
- 15.11.2 File Space Management
- 15.11.3 InnoDB Checkpoints
- 15.11.4 Defragmenting a Table
- 15.11.5 Reclaiming Disk Space with TRUNCATE TABLE
- 15.12 InnoDB and Online DDL
- 15.12.1 Online DDL Operations
- 15.12.2 Online DDL Performance and Concurrency
- 15.12.3 Online DDL Space Requirements
- 15.12.4 Simplifying DDL Statements with Online DDL
- 15.12.5 Online DDL Failure Conditions
- 15.12.6 Online DDL Limitations
- 15.13 InnoDB Data-at-Rest Encryption
- 15.14 InnoDB Startup Options and System Variables
- 15.15 InnoDB INFORMATION_SCHEMA Tables
- 15.15.1 InnoDB INFORMATION_SCHEMA Tables about Compression
- 15.15.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information
- 15.15.3 InnoDB INFORMATION_SCHEMA Schema Object Tables
- 15.15.4 InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables
- 15.15.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables
- 15.15.6 InnoDB INFORMATION_SCHEMA Metrics Table
- 15.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table
- 15.15.8 Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES
- 15.16 InnoDB Integration with MySQL Performance Schema
- 15.16.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance
Schema
- 15.16.2 Monitoring InnoDB Mutex Waits Using Performance Schema
- 15.17 InnoDB Monitors
- 15.17.1 InnoDB Monitor Types
- 15.17.2 Enabling InnoDB Monitors
- 15.17.3 InnoDB Standard Monitor and Lock Monitor Output
- 15.18 InnoDB Backup and Recovery
- 15.18.1 InnoDB Backup
- 15.18.2 InnoDB Recovery
- 15.19 InnoDB and MySQL Replication
- 15.20 InnoDB memcached Plugin
- 15.20.1 Benefits of the InnoDB memcached Plugin
- 15.20.2 InnoDB memcached Architecture
- 15.20.3 Setting Up the InnoDB memcached Plugin
- 15.20.4 InnoDB memcached Multiple get and Range Query Support
- 15.20.5 Security Considerations for the InnoDB memcached Plugin
- 15.20.6 Writing Applications for the InnoDB memcached Plugin
- 15.20.7 The InnoDB memcached Plugin and Replication
- 15.20.8 InnoDB memcached Plugin Internals
- 15.20.9 Troubleshooting the InnoDB memcached Plugin
- 15.21 InnoDB Troubleshooting
- 15.21.1 Troubleshooting InnoDB I/O Problems
- 15.21.2 Forcing InnoDB Recovery
- 15.21.3 Troubleshooting InnoDB Data Dictionary Operations
- 15.21.4 InnoDB Error Handling
- 15.22 InnoDB Limits
- 15.23 InnoDB Restrictions and Limitations
- 16 Alternative Storage Engines
- 16.1 Setting the Storage Engine
- 16.2 The MyISAM Storage Engine
- 16.2.1 MyISAM Startup Options
- 16.2.2 Space Needed for Keys
- 16.2.3 MyISAM Table Storage Formats
- 16.2.4 MyISAM Table Problems
- 16.3 The MEMORY Storage Engine
- 16.4 The CSV Storage Engine
- 16.4.1 Repairing and Checking CSV Tables
- 16.4.2 CSV Limitations
- 16.5 The ARCHIVE Storage Engine
- 16.6 The BLACKHOLE Storage Engine
- 16.7 The MERGE Storage Engine
- 16.7.1 MERGE Table Advantages and Disadvantages
- 16.7.2 MERGE Table Problems
- 16.8 The FEDERATED Storage Engine
- 16.8.1 FEDERATED Storage Engine Overview
- 16.8.2 How to Create FEDERATED Tables
- 16.8.3 FEDERATED Storage Engine Notes and Tips
- 16.8.4 FEDERATED Storage Engine Resources
- 16.9 The EXAMPLE Storage Engine
- 16.10 Other Storage Engines
- 16.11 Overview of MySQL Storage Engine Architecture
- 16.11.1 Pluggable Storage Engine Architecture
- 16.11.2 The Common Database Server Layer
- 17 Replication
- 17.1 Configuring Replication
- 17.1.1 Binary Log File Position Based Replication Configuration Overview
- 17.1.2 Setting Up Binary Log File Position Based Replication
- 17.1.3 Replication with Global Transaction Identifiers
- 17.1.4 MySQL Multi-Source Replication
- 17.1.5 Changing Replication Modes on Online Servers
- 17.1.6 Replication and Binary Logging Options and Variables
- 17.1.7 Common Replication Administration Tasks
- 17.2 Replication Implementation
- 17.2.1 Replication Formats
- 17.2.2 Replication Implementation Details
- 17.2.3 Replication Channels
- 17.2.4 Replication Relay and Status Logs
- 17.2.5 How Servers Evaluate Replication Filtering Rules
- 17.3 Replication Security
- 17.3.1 Setting Up Replication to Use Encrypted Connections
- 17.3.2 Encrypting Binary Log Files and Relay Log Files
- 17.3.3 Replication Privilege Checks
- 17.4 Replication Solutions
- 17.4.1 Using Replication for Backups
- 17.4.2 Handling an Unexpected Halt of a Replication Slave
- 17.4.3 Monitoring Row-based Replication
- 17.4.4 Using Replication with Different Master and Slave Storage Engines
- 17.4.5 Using Replication for Scale-Out
- 17.4.6 Replicating Different Databases to Different Slaves
- 17.4.7 Improving Replication Performance
- 17.4.8 Switching Masters During Failover
- 17.4.9 Semisynchronous Replication
- 17.4.10 Delayed Replication
- 17.5 Replication Notes and Tips
- 17.5.1 Replication Features and Issues
- 17.5.2 Replication Compatibility Between MySQL Versions
- 17.5.3 Upgrading a Replication Setup
- 17.5.4 Troubleshooting Replication
- 17.5.5 How to Report Replication Bugs or Problems
- 18 Group Replication
- 18.1 Group Replication Background
- 18.1.1 Replication Technologies
- 18.1.2 Group Replication Use Cases
- 18.1.3 Multi-Primary and Single-Primary Modes
- 18.1.4 Group Replication Services
- 18.1.5 Group Replication Plugin Architecture
- 18.2 Getting Started
- 18.2.1 Deploying Group Replication in Single-Primary Mode
- 18.2.2 Deploying Group Replication Locally
- 18.3 Monitoring Group Replication
- 18.3.1 Group Replication Server States
- 18.3.2 The replication_group_members Table
- 18.3.3 The replication_group_member_stats Table
- 18.4 Group Replication Operations
- 18.4.1 Configuring an Online Group
- 18.4.2 Transaction Consistency Guarantees
- 18.4.3 Distributed Recovery
- 18.4.4 Network Partitioning
- 18.4.5 Support For IPv6 And For Mixed IPv6 And IPv4 Groups
- 18.4.6 Using MySQL Enterprise Backup with Group Replication
- 18.5 Group Replication Security
- 18.5.1 Group Replication IP Address Whitelisting
- 18.5.2 Group Replication Secure Socket Layer (SSL) Support
- 18.6 Group Replication Performance
- 18.6.1 Fine Tuning the Group Communication Thread
- 18.6.2 Flow Control
- 18.6.3 Message Compression
- 18.6.4 Message Fragmentation
- 18.6.5 XCom Cache Management
- 18.6.6 Responses to Failure Detection and Network Partitioning
- 18.7 Upgrading Group Replication
- 18.7.1 Combining Different Member Versions in a Group
- 18.7.2 Group Replication Offline Upgrade
- 18.7.3 Group Replication Online Upgrade
- 18.8 Group Replication System Variables
- 18.9 Requirements and Limitations
- 18.9.1 Group Replication Requirements
- 18.9.2 Group Replication Limitations
- 18.10 Frequently Asked Questions
- 19 MySQL Shell
- 20 Using MySQL as a Document Store
- 20.1 Interfaces to a MySQL Document Store
- 20.2 Document Store Concepts
- 20.3 JavaScript Quick-Start Guide: MySQL Shell for Document Store
- 20.3.1 MySQL Shell
- 20.3.2 Download and Import world_x Database
- 20.3.3 Documents and Collections
- 20.3.4 Relational Tables
- 20.3.5 Documents in Tables
- 20.4 Python Quick-Start Guide: MySQL Shell for Document Store
- 20.4.1 MySQL Shell
- 20.4.2 Download and Import world_x Database
- 20.4.3 Documents and Collections
- 20.4.4 Relational Tables
- 20.4.5 Documents in Tables
- 20.5 X Plugin
- 20.5.1 Checking X Plugin Installation
- 20.5.2 Disabling X Plugin
- 20.5.3 Using Secure Connections with X Plugin
- 20.5.4 Using X Plugin with the Caching SHA-2 Authentication Plugin
- 20.5.5 Connection Compression with X Plugin
- 20.5.6 X Plugin Options and Variables
- 20.5.7 Monitoring X Plugin
- 21 InnoDB Cluster
- 21.1 Introducing InnoDB Cluster
- 21.2 Creating an InnoDB Cluster
- 21.2.1 Deployment Scenarios
- 21.2.2 InnoDB Cluster Requirements
- 21.2.3 Methods of Installing
- 21.2.4 Production Deployment of InnoDB Cluster
- 21.2.5 Using MySQL Clone with InnoDB cluster
- 21.2.6 Sandbox Deployment of InnoDB Cluster
- 21.2.7 Adopting a Group Replication Deployment
- 21.3 Upgrading an InnoDB cluster
- 21.3.1 Rolling Upgrades
- 21.3.2 Upgrading InnoDB cluster Metadata
- 21.3.3 Troubleshooting InnoDB cluster Upgrades
- 21.4 Using MySQL Router with InnoDB Cluster
- 21.5 Working with InnoDB Cluster
- 21.6 InnoDB ReplicaSet
- 21.6.1 InnoDB ReplicaSet Introduction
- 21.6.2 Deploying InnoDB ReplicaSet
- 21.6.3 Adding Instances to a Replica Set
- 21.6.4 Adopting an Existing Replication Set Up
- 21.6.5 Working with InnoDB ReplicaSet
- 21.6.6 Using Replica Sets with MySQL Router
- 21.7 Known Limitations
- 22 MySQL NDB Cluster 8.0
- 22.1 NDB Cluster Overview
- 22.1.1 NDB Cluster Core Concepts
- 22.1.2 NDB Cluster Nodes, Node Groups, Replicas, and Partitions
- 22.1.3 NDB Cluster Hardware, Software, and Networking Requirements
- 22.1.4 What is New in NDB Cluster
- 22.1.5 Options, Variables, and Parameters Added, Deprecated or Removed in NDB
8.0
- 22.1.6 MySQL Server Using InnoDB Compared with NDB Cluster
- 22.1.7 Known Limitations of NDB Cluster
- 22.2 NDB Cluster Installation
- 22.2.1 The NDB Cluster Auto-Installer
- 22.2.2 Installation of NDB Cluster on Linux
- 22.2.3 Installing NDB Cluster on Windows
- 22.2.4 Initial Configuration of NDB Cluster
- 22.2.5 Initial Startup of NDB Cluster
- 22.2.6 NDB Cluster Example with Tables and Data
- 22.2.7 Safe Shutdown and Restart of NDB Cluster
- 22.2.8 Upgrading and Downgrading NDB Cluster
- 22.3 Configuration of NDB Cluster
- 22.3.1 Quick Test Setup of NDB Cluster
- 22.3.2 Overview of NDB Cluster Configuration Parameters, Options, and Variables
- 22.3.3 NDB Cluster Configuration Files
- 22.3.4 Using High-Speed Interconnects with NDB Cluster
- 22.4 NDB Cluster Programs
- 22.4.1 ndbd — The NDB Cluster Data Node Daemon
- 22.4.2 ndbinfo_select_all — Select From ndbinfo Tables
- 22.4.3 ndbmtd — The NDB Cluster Data Node Daemon (Multi-Threaded)
- 22.4.4 ndb_mgmd — The NDB Cluster Management Server Daemon
- 22.4.5 ndb_mgm — The NDB Cluster Management Client
- 22.4.6 ndb_blob_tool — Check and Repair BLOB and TEXT columns of NDB Cluster
Tables
- 22.4.7 ndb_config — Extract NDB Cluster Configuration Information
- 22.4.8 ndb_delete_all — Delete All Rows from an NDB Table
- 22.4.9 ndb_desc — Describe NDB Tables
- 22.4.10 ndb_drop_index — Drop Index from an NDB Table
- 22.4.11 ndb_drop_table — Drop an NDB Table
- 22.4.12 ndb_error_reporter — NDB Error-Reporting Utility
- 22.4.13 ndb_import — Import CSV Data Into NDB
- 22.4.14 ndb_index_stat — NDB Index Statistics Utility
- 22.4.15 ndb_move_data — NDB Data Copy Utility
- 22.4.16 ndb_perror — Obtain NDB Error Message Information
- 22.4.17 ndb_print_backup_file — Print NDB Backup File Contents
- 22.4.18 ndb_print_file — Print NDB Disk Data File Contents
- 22.4.19 ndb_print_frag_file — Print NDB Fragment List File Contents
- 22.4.20 ndb_print_schema_file — Print NDB Schema File Contents
- 22.4.21 ndb_print_sys_file — Print NDB System File Contents
- 22.4.22 ndb_redo_log_reader — Check and Print Content of Cluster Redo Log
- 22.4.23 ndb_restore — Restore an NDB Cluster Backup
- 22.4.24 ndb_select_all — Print Rows from an NDB Table
- 22.4.25 ndb_select_count — Print Row Counts for NDB Tables
- 22.4.26 ndb_setup.py — Start browser-based Auto-Installer for
NDB Cluster
- 22.4.27 ndb_show_tables — Display List of NDB Tables
- 22.4.28 ndb_size.pl — NDBCLUSTER Size Requirement Estimator
- 22.4.29 ndb_top — View CPU usage information for NDB threads
- 22.4.30 ndb_waiter — Wait for NDB Cluster to Reach a Given Status
- 22.4.31 Options Common to NDB Cluster Programs — Options Common to NDB Cluster Programs
- 22.5 Management of NDB Cluster
- 22.5.1 Summary of NDB Cluster Start Phases
- 22.5.2 Commands in the NDB Cluster Management Client
- 22.5.3 Online Backup of NDB Cluster
- 22.5.4 MySQL Server Usage for NDB Cluster
- 22.5.5 Performing a Rolling Restart of an NDB Cluster
- 22.5.6 Event Reports Generated in NDB Cluster
- 22.5.7 NDB Cluster Log Messages
- 22.5.8 NDB Cluster Single User Mode
- 22.5.9 Quick Reference: NDB Cluster SQL Statements
- 22.5.10 ndbinfo: The NDB Cluster Information Database
- 22.5.11 INFORMATION_SCHEMA Tables for NDB Cluster
- 22.5.12 NDB Cluster Security Issues
- 22.5.13 NDB Cluster Disk Data Tables
- 22.5.14 Online Operations with ALTER TABLE in NDB Cluster
- 22.5.15 Adding NDB Cluster Data Nodes Online
- 22.5.16 Distributed MySQL Privileges with NDB_STORED_USER
- 22.5.17 NDB API Statistics Counters and Variables
- 22.6 NDB Cluster Replication
- 22.6.1 NDB Cluster Replication: Abbreviations and Symbols
- 22.6.2 General Requirements for NDB Cluster Replication
- 22.6.3 Known Issues in NDB Cluster Replication
- 22.6.4 NDB Cluster Replication Schema and Tables
- 22.6.5 Preparing the NDB Cluster for Replication
- 22.6.6 Starting NDB Cluster Replication (Single Replication Channel)
- 22.6.7 Using Two Replication Channels for NDB Cluster Replication
- 22.6.8 Implementing Failover with NDB Cluster Replication
- 22.6.9 NDB Cluster Backups With NDB Cluster Replication
- 22.6.10 NDB Cluster Replication: Multi-Master and Circular Replication
- 22.6.11 NDB Cluster Replication Conflict Resolution
- 22.7 NDB Cluster Release Notes
- 23 Partitioning
- 23.1 Overview of Partitioning in MySQL
- 23.2 Partitioning Types
- 23.2.1 RANGE Partitioning
- 23.2.2 LIST Partitioning
- 23.2.3 COLUMNS Partitioning
- 23.2.4 HASH Partitioning
- 23.2.5 KEY Partitioning
- 23.2.6 Subpartitioning
- 23.2.7 How MySQL Partitioning Handles NULL
- 23.3 Partition Management
- 23.3.1 Management of RANGE and LIST Partitions
- 23.3.2 Management of HASH and KEY Partitions
- 23.3.3 Exchanging Partitions and Subpartitions with Tables
- 23.3.4 Maintenance of Partitions
- 23.3.5 Obtaining Information About Partitions
- 23.4 Partition Pruning
- 23.5 Partition Selection
- 23.6 Restrictions and Limitations on Partitioning
- 23.6.1 Partitioning Keys, Primary Keys, and Unique Keys
- 23.6.2 Partitioning Limitations Relating to Storage Engines
- 23.6.3 Partitioning Limitations Relating to Functions
- 24 Stored Objects
- 24.1 Defining Stored Programs
- 24.2 Using Stored Routines
- 24.2.1 Stored Routine Syntax
- 24.2.2 Stored Routines and MySQL Privileges
- 24.2.3 Stored Routine Metadata
- 24.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()
- 24.3 Using Triggers
- 24.3.1 Trigger Syntax and Examples
- 24.3.2 Trigger Metadata
- 24.4 Using the Event Scheduler
- 24.4.1 Event Scheduler Overview
- 24.4.2 Event Scheduler Configuration
- 24.4.3 Event Syntax
- 24.4.4 Event Metadata
- 24.4.5 Event Scheduler Status
- 24.4.6 The Event Scheduler and MySQL Privileges
- 24.5 Using Views
- 24.5.1 View Syntax
- 24.5.2 View Processing Algorithms
- 24.5.3 Updatable and Insertable Views
- 24.5.4 The View WITH CHECK OPTION Clause
- 24.5.5 View Metadata
- 24.6 Stored Object Access Control
- 24.7 Stored Program Binary Logging
- 24.8 Restrictions on Stored Programs
- 24.9 Restrictions on Views
- 25 INFORMATION_SCHEMA Tables
- 25.1 Introduction
- 25.2 The INFORMATION_SCHEMA ADMINISTRABLE_ROLE_AUTHORIZATIONS Table
- 25.3 The INFORMATION_SCHEMA APPLICABLE_ROLES Table
- 25.4 The INFORMATION_SCHEMA CHARACTER_SETS Table
- 25.5 The INFORMATION_SCHEMA CHECK_CONSTRAINTS Table
- 25.6 The INFORMATION_SCHEMA COLLATIONS Table
- 25.7 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
- 25.8 The INFORMATION_SCHEMA COLUMNS Table
- 25.9 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
- 25.10 The INFORMATION_SCHEMA COLUMN_STATISTICS Table
- 25.11 The INFORMATION_SCHEMA ENABLED_ROLES Table
- 25.12 The INFORMATION_SCHEMA ENGINES Table
- 25.13 The INFORMATION_SCHEMA EVENTS Table
- 25.14 The INFORMATION_SCHEMA FILES Table
- 25.15 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
- 25.16 The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table
- 25.17 The INFORMATION_SCHEMA KEYWORDS Table
- 25.18 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table
- 25.19 The INFORMATION_SCHEMA PARAMETERS Table
- 25.20 The INFORMATION_SCHEMA PARTITIONS Table
- 25.21 The INFORMATION_SCHEMA PLUGINS Table
- 25.22 The INFORMATION_SCHEMA PROCESSLIST Table
- 25.23 The INFORMATION_SCHEMA PROFILING Table
- 25.24 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
- 25.25 The INFORMATION_SCHEMA RESOURCE_GROUPS Table
- 25.26 The INFORMATION_SCHEMA ROLE_COLUMN_GRANTS Table
- 25.27 The INFORMATION_SCHEMA ROLE_ROUTINE_GRANTS Table
- 25.28 The INFORMATION_SCHEMA ROLE_TABLE_GRANTS Table
- 25.29 The INFORMATION_SCHEMA ROUTINES Table
- 25.30 The INFORMATION_SCHEMA SCHEMATA Table
- 25.31 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
- 25.32 The INFORMATION_SCHEMA STATISTICS Table
- 25.33 The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table
- 25.34 The INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS Table
- 25.35 The INFORMATION_SCHEMA ST_UNITS_OF_MEASURE Table
- 25.36 The INFORMATION_SCHEMA TABLES Table
- 25.37 The INFORMATION_SCHEMA TABLESPACES Table
- 25.38 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
- 25.39 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
- 25.40 The INFORMATION_SCHEMA TRIGGERS Table
- 25.41 The INFORMATION_SCHEMA USER_PRIVILEGES Table
- 25.42 The INFORMATION_SCHEMA VIEWS Table
- 25.43 The INFORMATION_SCHEMA VIEW_ROUTINE_USAGE Table
- 25.44 The INFORMATION_SCHEMA VIEW_TABLE_USAGE Table
- 25.45 INFORMATION_SCHEMA InnoDB Tables
- 25.45.1 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table
- 25.45.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table
- 25.45.3 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table
- 25.45.4 The INFORMATION_SCHEMA INNODB_CACHED_INDEXES Table
- 25.45.5 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables
- 25.45.6 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables
- 25.45.7 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and
INNODB_CMP_PER_INDEX_RESET Tables
- 25.45.8 The INFORMATION_SCHEMA INNODB_COLUMNS Table
- 25.45.9 The INFORMATION_SCHEMA INNODB_DATAFILES Table
- 25.45.10 The INFORMATION_SCHEMA INNODB_FIELDS Table
- 25.45.11 The INFORMATION_SCHEMA INNODB_FOREIGN Table
- 25.45.12 The INFORMATION_SCHEMA INNODB_FOREIGN_COLS Table
- 25.45.13 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table
- 25.45.14 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table
- 25.45.15 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table
- 25.45.16 The INFORMATION_SCHEMA INNODB_FT_DELETED Table
- 25.45.17 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table
- 25.45.18 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table
- 25.45.19 The INFORMATION_SCHEMA INNODB_INDEXES Table
- 25.45.20 The INFORMATION_SCHEMA INNODB_LOCKS Table
- 25.45.21 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
- 25.45.22 The INFORMATION_SCHEMA INNODB_METRICS Table
- 25.45.23 The INFORMATION_SCHEMA INNODB_SESSION_TEMP_TABLESPACES Table
- 25.45.24 The INFORMATION_SCHEMA INNODB_TABLES Table
- 25.45.25 The INFORMATION_SCHEMA INNODB_TABLESPACES Table
- 25.45.26 The INFORMATION_SCHEMA INNODB_TABLESPACES_BRIEF Table
- 25.45.27 The INFORMATION_SCHEMA INNODB_TABLESTATS View
- 25.45.28 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table
- 25.45.29 The INFORMATION_SCHEMA INNODB_TRX Table
- 25.45.30 The INFORMATION_SCHEMA INNODB_VIRTUAL Table
- 25.46 INFORMATION_SCHEMA Thread Pool Tables
- 25.46.1 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table
- 25.46.2 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table
- 25.46.3 The INFORMATION_SCHEMA TP_THREAD_STATE Table
- 25.47 INFORMATION_SCHEMA Connection-Control Tables
- 25.47.1 The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Table
- 25.48 Extensions to SHOW Statements
- 26 MySQL Performance Schema
- 26.1 Performance Schema Quick Start
- 26.2 Performance Schema Build Configuration
- 26.3 Performance Schema Startup Configuration
- 26.4 Performance Schema Runtime Configuration
- 26.4.1 Performance Schema Event Timing
- 26.4.2 Performance Schema Event Filtering
- 26.4.3 Event Pre-Filtering
- 26.4.4 Pre-Filtering by Instrument
- 26.4.5 Pre-Filtering by Object
- 26.4.6 Pre-Filtering by Thread
- 26.4.7 Pre-Filtering by Consumer
- 26.4.8 Example Consumer Configurations
- 26.4.9 Naming Instruments or Consumers for Filtering Operations
- 26.4.10 Determining What Is Instrumented
- 26.5 Performance Schema Queries
- 26.6 Performance Schema Instrument Naming Conventions
- 26.7 Performance Schema Status Monitoring
- 26.8 Performance Schema Atom and Molecule Events
- 26.9 Performance Schema Tables for Current and Historical Events
- 26.10 Performance Schema Statement Digests and Sampling
- 26.11 Performance Schema General Table Characteristics
- 26.12 Performance Schema Table Descriptions
- 26.12.1 Performance Schema Table Index
- 26.12.2 Performance Schema Setup Tables
- 26.12.3 Performance Schema Instance Tables
- 26.12.4 Performance Schema Wait Event Tables
- 26.12.5 Performance Schema Stage Event Tables
- 26.12.6 Performance Schema Statement Event Tables
- 26.12.7 Performance Schema Transaction Tables
- 26.12.8 Performance Schema Connection Tables
- 26.12.9 Performance Schema Connection Attribute Tables
- 26.12.10 Performance Schema User-Defined Variable Tables
- 26.12.11 Performance Schema Replication Tables
- 26.12.12 Performance Schema Lock Tables
- 26.12.13 Performance Schema System Variable Tables
- 26.12.14 Performance Schema Status Variable Tables
- 26.12.15 Performance Schema Thread Pool Tables
- 26.12.16 Performance Schema Clone Tables
- 26.12.17 Performance Schema Summary Tables
- 26.12.18 Performance Schema Miscellaneous Tables
- 26.13 Performance Schema Option and Variable Reference
- 26.14 Performance Schema Command Options
- 26.15 Performance Schema System Variables
- 26.16 Performance Schema Status Variables
- 26.17 The Performance Schema Memory-Allocation Model
- 26.18 Performance Schema and Plugins
- 26.19 Using the Performance Schema to Diagnose Problems
- 26.19.1 Query Profiling Using Performance Schema
- 26.19.2 Obtaining Parent Event Information
- 26.20 Restrictions on Performance Schema
- 27 MySQL sys Schema
- 27.1 Prerequisites for Using the sys Schema
- 27.2 Using the sys Schema
- 27.3 sys Schema Progress Reporting
- 27.4 sys Schema Object Reference
- 27.4.1 sys Schema Object Index
- 27.4.2 sys Schema Tables and Triggers
- 27.4.3 sys Schema Views
- 27.4.4 sys Schema Stored Procedures
- 27.4.5 sys Schema Stored Functions
- 28 Connectors and APIs
- 28.1 MySQL Connector/C++
- 28.2 MySQL Connector/J
- 28.3 MySQL Connector/NET
- 28.4 MySQL Connector/ODBC
- 28.5 MySQL Connector/Python
- 28.6 MySQL C API
- 28.6.1 MySQL C API Implementations
- 28.6.2 Example C API Client Programs
- 28.6.3 Building and Running C API Client Programs
- 28.6.4 C API Data Structures
- 28.6.5 C API Function Overview
- 28.6.6 C API Function Descriptions
- 28.6.7 C API Prepared Statements
- 28.6.8 C API Prepared Statement Data Structures
- 28.6.9 C API Prepared Statement Function Overview
- 28.6.10 C API Prepared Statement Function Descriptions
- 28.6.11 C API Asynchronous Interface
- 28.6.12 C API Asynchronous Interface Data Structures
- 28.6.13 C API Asynchronous Function Overview
- 28.6.14 C API Asynchronous Function Descriptions
- 28.6.15 C API Threaded Function Descriptions
- 28.6.16 C API Client Plugin Functions
- 28.6.17 C API Binary Log Interface
- 28.6.18 C API Binary Log Data Structures
- 28.6.19 C API Binary Log Function Overview
- 28.6.20 C API Binary Log Function Descriptions
- 28.6.21 C API Encrypted Connection Support
- 28.6.22 C API Multiple Statement Execution Support
- 28.6.23 C API Prepared Statement Handling of Date and Time Values
- 28.6.24 C API Prepared CALL Statement Support
- 28.6.25 C API Prepared Statement Problems
- 28.6.26 C API Optional Result Set Metadata
- 28.6.27 C API Automatic Reconnection Control
- 28.6.28 C API Common Issues
- 28.7 MySQL PHP API
- 28.8 MySQL Perl API
- 28.9 MySQL Python API
- 28.10 MySQL Ruby APIs
- 28.10.1 The MySQL/Ruby API
- 28.10.2 The Ruby/MySQL API
- 28.11 MySQL Tcl API
- 28.12 MySQL Eiffel Wrapper
- 29 Extending MySQL
- 29.1 MySQL Internals
- 29.1.1 MySQL Threads
- 29.1.2 The MySQL Test Suite
- 29.2 The MySQL Plugin API
- 29.2.1 Types of Plugins
- 29.2.2 Plugin API Characteristics
- 29.2.3 Plugin API Components
- 29.2.4 Writing Plugins
- 29.3 MySQL Services for Plugins
- 29.3.1 The Locking Service
- 29.3.2 The Keyring Service
- 29.4 Adding Functions to MySQL
- 29.4.1 Features of the User-Defined Function Interface
- 29.4.2 Adding a User-Defined Function
- 29.4.3 Adding a Native Function
- 29.5 Debugging and Porting MySQL
- 29.5.1 Debugging a MySQL Server
- 29.5.2 Debugging a MySQL Client
- 29.5.3 The LOCK_ORDER Tool
- 29.5.4 The DBUG Package
- 30 MySQL Enterprise Edition
- 30.1 MySQL Enterprise Monitor Overview
- 30.2 MySQL Enterprise Backup Overview
- 30.3 MySQL Enterprise Security Overview
- 30.4 MySQL Enterprise Encryption Overview
- 30.5 MySQL Enterprise Audit Overview
- 30.6 MySQL Enterprise Firewall Overview
- 30.7 MySQL Enterprise Thread Pool Overview
- 30.8 MySQL Enterprise Data Masking and De-Identification Overview
- 31 MySQL Workbench
- 32 MySQL on the OCI Marketplace
- 32.1 Prerequisites to Deploying MySQL EE on Oracle Cloud Infrastructure
- 32.2 Deploying MySQL EE on Oracle Cloud Infrastructure
- 32.3 Configuring Network Access
- 32.4 Connecting
- 32.5 Maintenance
- A MySQL 8.0 Frequently Asked Questions
- A.1 MySQL 8.0 FAQ: General
- A.2 MySQL 8.0 FAQ: Storage Engines
- A.3 MySQL 8.0 FAQ: Server SQL Mode
- A.4 MySQL 8.0 FAQ: Stored Procedures and Functions
- A.5 MySQL 8.0 FAQ: Triggers
- A.6 MySQL 8.0 FAQ: Views
- A.7 MySQL 8.0 FAQ: INFORMATION_SCHEMA
- A.8 MySQL 8.0 FAQ: Migration
- A.9 MySQL 8.0 FAQ: Security
- A.10 MySQL 8.0 FAQ: NDB Cluster
- A.11 MySQL 8.0 FAQ: MySQL Chinese, Japanese, and Korean
Character Sets
- A.12 MySQL 8.0 FAQ: Connectors & APIs
- A.13 MySQL 8.0 FAQ: C API, libmysql
- A.14 MySQL 8.0 FAQ: Replication
- A.15 MySQL 8.0 FAQ: MySQL Enterprise Thread Pool
- A.16 MySQL 8.0 FAQ: InnoDB Change Buffer
- A.17 MySQL 8.0 FAQ: InnoDB Data-at-Rest Encryption
- A.18 MySQL 8.0 FAQ: Virtualization Support
- B Errors, Error Codes, and Common Problems
- B.1 Error Message Sources and Components
- B.2 Error Information Interfaces
- B.3 Error Message Reference
- B.3.1 Server Error Message Reference
- B.3.2 Client Error Message Reference
- B.3.3 Global Error Message Reference
- B.4 Problems and Common Errors
- B.4.1 How to Determine What Is Causing a Problem
- B.4.2 Common Errors When Using MySQL Programs
- B.4.3 Administration-Related Issues
- B.4.4 Query-Related Issues
- B.4.5 Optimizer-Related Issues
- B.4.6 Table Definition-Related Issues
- B.4.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