第1页 / 共5962页
第2页 / 共5962页
第3页 / 共5962页
第4页 / 共5962页
第5页 / 共5962页
第6页 / 共5962页
第7页 / 共5962页
第8页 / 共5962页
MySQL 8.1 参考手册.pdf
MySQL 8.1 Reference Manual
Table of Contents
Preface and Legal Notices
Chapter 1 General Information
1.1 About This Manual
1.2 Overview of the MySQL Database Management System
1.2.1 What is MySQL?
1.2.2 The Main Features of MySQL
1.2.3 History of MySQL
1.3 What Is New in MySQL 8.1
1.4 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 8.1
1.5 How to Report Bugs or Problems
1.6 MySQL Standards Compliance
1.6.1 MySQL Extensions to Standard SQL
1.6.2 MySQL Differences from Standard SQL
1.6.2.1 SELECT INTO TABLE Differences
1.6.2.2 UPDATE Differences
1.6.2.3 FOREIGN KEY Constraint Differences
1.6.2.4 '--' as the Start of a Comment
1.6.3 How MySQL Deals with Constraints
1.6.3.1 PRIMARY KEY and UNIQUE Index Constraints
1.6.3.2 FOREIGN KEY Constraints
1.6.3.3 Enforced Constraints on Invalid Data
1.6.3.4 ENUM and SET Constraints
1.7 Credits
1.7.1 Contributors to MySQL
1.7.2 Documenters and translators
1.7.3 Packages that support MySQL
1.7.4 Tools that were used to create MySQL
1.7.5 Supporters of MySQL
Chapter 2 Installing and Upgrading 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.4.1 Verifying the MD5 Checksum
2.1.4.2 Signature Checking Using GnuPG
2.1.4.3 Signature Checking Using Gpg4win for Windows
2.1.4.4 Signature Checking Using RPM
2.1.4.5 GPG Public Build Key for Archived Packages
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.2.1 MySQL Server Configuration with MySQL Configurator
Type and Networking
Accounts and Roles
Windows Service
Server File Permissions
Logging Options
Advanced Options
Sample Databases
Apply Configuration
2.3.3 Configuration: Manually
2.3.3.1 Extracting the Install Archive
2.3.3.2 Creating an Option File
2.3.3.3 Selecting a MySQL Server Type
2.3.3.4 Initializing the Data Directory
2.3.3.5 Starting the Server for the First Time
2.3.3.6 Starting MySQL from the Windows Command Line
2.3.3.7 Customizing the PATH for MySQL Tools
2.3.3.8 Starting MySQL as a Windows Service
2.3.3.9 Testing The MySQL Installation
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 Installing MySQL on Linux Using the MySQL SLES Repository
2.5.4 Installing MySQL on Linux Using RPM Packages from Oracle
2.5.5 Installing MySQL on Linux Using Debian Packages from Oracle
2.5.6 Deploying MySQL on Linux with Docker Containers
2.5.6.1 Basic Steps for MySQL Server Deployment with Docker
2.5.6.2 More Topics on Deploying MySQL Server with Docker
2.5.6.3 Deploying MySQL on Windows and Other Non-Linux Platforms 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 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.2.1 Troubleshooting Problems Starting the MySQL 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 Upgrading MySQL
2.10.1 Before You Begin
2.10.2 Upgrade Paths
2.10.3 What the MySQL Upgrade Process Upgrades
2.10.4 Changes in MySQL 8.1
2.10.5 Preparing Your Installation for Upgrade
2.10.6 Upgrading MySQL Binary or Package-based Installations on Unix/Linux
2.10.7 Upgrading MySQL with the MySQL Yum Repository
2.10.8 Upgrading MySQL with the MySQL APT Repository
2.10.9 Upgrading MySQL with the MySQL SLES Repository
2.10.10 Upgrading MySQL on Windows
2.10.11 Upgrading a Docker Installation of MySQL
2.10.12 Upgrade Troubleshooting
2.10.13 Rebuilding or Repairing Tables or Indexes
2.10.14 Copying MySQL Databases to Another Machine
2.11 Downgrading MySQL
2.12 Perl Installation Notes
2.12.1 Installing Perl on Unix
2.12.2 Installing ActiveState Perl on Windows
2.12.3 Problems Using the Perl DBI/DBD Interface
Chapter 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.3.4.1 Selecting All Data
3.3.4.2 Selecting Particular Rows
3.3.4.3 Selecting Particular Columns
3.3.4.4 Sorting Rows
3.3.4.5 Date Calculations
3.3.4.6 Working with NULL Values
3.3.4.7 Pattern Matching
3.3.4.8 Counting Rows
3.3.4.9 Using More Than one 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
Chapter 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.2.1 Using Options on the Command Line
4.2.2.2 Using Option Files
4.2.2.3 Command-Line Options that Affect Option-File Handling
4.2.2.4 Program Option Modifiers
4.2.2.5 Using Options to Set Program Variables
4.2.2.6 Option Defaults, Options Expecting Values, and the = Sign
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 Connecting to the Server Using DNS SRV Records
4.2.7 Connection Transport Protocols
4.2.8 Connection Compression Control
4.2.9 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 — Deprecated; Performs No Tasks and Exits with Status 0
4.5 Client Programs
4.5.1 mysql — The MySQL Command-Line Client
4.5.1.1 mysql Client Options
4.5.1.2 mysql Client Commands
4.5.1.3 mysql Client Logging
4.5.1.4 mysql Client Server-Side Help
4.5.1.5 Executing SQL Statements from a Text File
4.5.1.6 mysql Client Tips
4.5.2 mysqladmin — A MySQL Server Administration Program
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 — A 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.4.1 myisamchk General Options
4.6.4.2 myisamchk Check Options
4.6.4.3 myisamchk Repair Options
4.6.4.4 Other myisamchk Options
4.6.4.5 Obtaining Table Information with myisamchk
4.6.4.6 myisamchk Memory Usage
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 mysql_migrate_keyring — Keyring Key Migration Utility
4.6.9 mysqlbinlog — Utility for Processing Binary Log Files
4.6.9.1 mysqlbinlog Hex Dump Format
4.6.9.2 mysqlbinlog Row Event Display
4.6.9.3 Using mysqlbinlog to Back Up Binary Log Files
4.6.9.4 Specifying the mysqlbinlog Server ID
4.6.10 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
Chapter 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.9.1 System Variable Privileges
5.1.9.2 Dynamic System Variables
5.1.9.3 Persisted System Variables
5.1.9.4 Nonpersistible and Persist-Restricted System Variables
5.1.9.5 Structured System Variables
5.1.10 Server Status Variables
5.1.11 Server SQL Modes
5.1.12 Connection Management
5.1.12.1 Connection Interfaces
5.1.12.2 Administrative Connection Management
5.1.12.3 DNS Lookups and the Host Cache
5.1.13 IPv6 Support
5.1.13.1 Verifying System Support for IPv6
5.1.13.2 Configuring the MySQL Server to Permit IPv6 Connections
5.1.13.3 Connecting Using the IPv6 Local Host Address
5.1.13.4 Connecting Using IPv6 Nonlocal Host Addresses
5.1.13.5 Obtaining an IPv6 Address from a Broker
5.1.14 Network Namespace Support
5.1.15 MySQL Server Time Zone Support
5.1.16 Resource Groups
5.1.17 Server-Side Help Support
5.1.18 Server Tracking of Client Session State
5.1.19 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.2.1 Error Log Configuration
5.4.2.2 Default Error Log Destination Configuration
5.4.2.3 Error Event Fields
5.4.2.4 Types of Error Log Filtering
5.4.2.5 Priority-Based Error Log Filtering (log_filter_internal)
5.4.2.6 Rule-Based Error Log Filtering (log_filter_dragnet)
5.4.2.7 Error Logging in JSON Format
5.4.2.8 Error Logging to the System Log
5.4.2.9 Error Log Output Format
5.4.2.10 Error Log File Flushing and Renaming
5.4.3 The General Query Log
5.4.4 The Binary Log
5.4.4.1 Binary Logging Formats
5.4.4.2 Setting The Binary Log Format
5.4.4.3 Mixed Binary Logging Format
5.4.4.4 Logging Format for Changes to mysql Database Tables
5.4.4.5 Binary Log Transaction Compression
Behaviors When Binary Log Transaction Compression is Enabled
Combining Compressed and Uncompressed Transaction Payloads
Monitoring Binary Log Transaction Compression
5.4.5 The Slow Query Log
5.4.6 Server Log Maintenance
5.5 MySQL Components
5.5.1 Installing and Uninstalling Components
5.5.2 Obtaining Component Information
5.5.3 Error Log Components
5.5.4 Query Attribute Components
5.5.5 Scheduler Component
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.3.1 Thread Pool Elements
5.6.3.2 Thread Pool Installation
5.6.3.3 Thread Pool Operation
5.6.3.4 Thread Pool Tuning
5.6.4 The Rewriter Query Rewrite Plugin
5.6.4.1 Installing or Uninstalling the Rewriter Query Rewrite Plugin
5.6.4.2 Using the Rewriter Query Rewrite Plugin
5.6.4.3 Rewriter Query Rewrite Plugin Reference
Rewriter Query Rewrite Plugin Rules Table
Rewriter Query Rewrite Plugin Procedures and Functions
Rewriter Query Rewrite Plugin System Variables
Rewriter Query Rewrite Plugin Status Variables
5.6.5 The ddl_rewriter Plugin
5.6.5.1 Installing or Uninstalling ddl_rewriter
5.6.5.2 ddl_rewriter Plugin Options
5.6.6 Version Tokens
5.6.6.1 Version Tokens Elements
5.6.6.2 Installing or Uninstalling Version Tokens
5.6.6.3 Using Version Tokens
5.6.6.4 Version Tokens Reference
5.6.7 The Clone Plugin
5.6.7.1 Installing the Clone Plugin
5.6.7.2 Cloning Data Locally
5.6.7.3 Cloning Remote Data
5.6.7.4 Cloning and Concurrent DDL
5.6.7.5 Cloning Encrypted Data
5.6.7.6 Cloning Compressed Data
5.6.7.7 Cloning for Replication
5.6.7.8 Directories and Files Created During a Cloning Operation
5.6.7.9 Remote Cloning Operation Failure Handling
5.6.7.10 Monitoring Cloning Operations
5.6.7.11 Stopping a Cloning Operation
5.6.7.12 Clone System Variable Reference
5.6.7.13 Clone System Variables
5.6.7.14 Clone Plugin Limitations
5.6.8 The Keyring Proxy Bridge Plugin
5.6.9 MySQL Plugin Services
5.6.9.1 The Locking Service
The Locking Service C Interface
The Locking Service Function Interface
5.6.9.2 The Keyring Service
5.7 MySQL Server Loadable Functions
5.7.1 Installing and Uninstalling Loadable Functions
5.7.2 Obtaining Information About Loadable Functions
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.2.1 Starting Multiple MySQL Instances at the Windows Command Line
5.8.2.2 Starting Multiple MySQL Instances as Windows Services
5.8.3 Running Multiple MySQL Instances on Unix
5.8.4 Using Client Programs in a Multiple-Server Environment
5.9 Debugging MySQL
5.9.1 Debugging a MySQL Server
5.9.1.1 Compiling MySQL for Debugging
5.9.1.2 Creating Trace Files
5.9.1.3 Using WER with PDB to create a Windows crashdump
5.9.1.4 Debugging mysqld under gdb
5.9.1.5 Using a Stack Trace
5.9.1.6 Using Server Logs to Find Causes of Errors in mysqld
5.9.1.7 Making a Test Case If You Experience Table Corruption
5.9.2 Debugging a MySQL Client
5.9.3 The LOCK_ORDER Tool
5.9.4 The DBUG Package
Chapter 6 Security
6.1 General Security Issues
6.1.1 Security Guidelines
6.1.2 Keeping Passwords Secure
6.1.2.1 End-User Guidelines for Password Security
6.1.2.2 Administrator Guidelines for Password Security
6.1.2.3 Passwords and Logging
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 Considerations for 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 Multifactor Authentication
6.2.19 Proxy Users
6.2.20 Account Locking
6.2.21 Setting Account Resource Limits
6.2.22 Troubleshooting Problems Connecting to MySQL
6.2.23 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.3.1 Creating SSL and RSA Certificates and Keys using MySQL
6.3.3.2 Creating SSL Certificates and Keys Using openssl
6.3.3.3 Creating RSA Keys Using openssl
6.3.4 Connecting to MySQL Remotely from Windows with SSH
6.3.5 Reusing SSL Sessions
6.4 Security Components and Plugins
6.4.1 Authentication Plugins
6.4.1.1 Native Pluggable Authentication
6.4.1.2 Caching SHA-2 Pluggable Authentication
6.4.1.3 SHA-256 Pluggable Authentication
6.4.1.4 Client-Side Cleartext Pluggable Authentication
6.4.1.5 PAM Pluggable Authentication
6.4.1.6 Windows Pluggable Authentication
6.4.1.7 LDAP Pluggable Authentication
6.4.1.8 Kerberos Pluggable Authentication
6.4.1.9 No-Login Pluggable Authentication
6.4.1.10 Socket Peer-Credential Pluggable Authentication
6.4.1.11 FIDO Pluggable Authentication
6.4.1.12 Test Pluggable Authentication
6.4.1.13 Pluggable Authentication System Variables
6.4.2 The Connection-Control Plugins
6.4.2.1 Connection-Control Plugin Installation
6.4.2.2 Connection-Control System and Status Variables
6.4.3 The Password Validation Component
6.4.3.1 Password Validation Component Installation and Uninstallation
6.4.3.2 Password Validation Options and Variables
6.4.3.3 Transitioning to the Password Validation Component
6.4.4 The MySQL Keyring
6.4.4.1 Keyring Components Versus Keyring Plugins
6.4.4.2 Keyring Component Installation
6.4.4.3 Keyring Plugin Installation
6.4.4.4 Using the component_keyring_file File-Based Keyring Component
6.4.4.5 Using the component_keyring_encrypted_file Encrypted File-Based Keyring Component
6.4.4.6 Using the keyring_file File-Based Keyring Plugin
6.4.4.7 Using the keyring_encrypted_file Encrypted File-Based Keyring Plugin
6.4.4.8 Using the keyring_okv KMIP Plugin
6.4.4.9 Using the keyring_aws Amazon Web Services Keyring Plugin
6.4.4.10 Using the HashiCorp Vault Keyring Plugin
6.4.4.11 Using the Oracle Cloud Infrastructure Vault Keyring Component
6.4.4.12 Using the Oracle Cloud Infrastructure Vault Keyring Plugin
6.4.4.13 Supported Keyring Key Types and Lengths
6.4.4.14 Migrating Keys Between Keyring Keystores
6.4.4.15 General-Purpose Keyring Key-Management Functions
6.4.4.16 Plugin-Specific Keyring Key-Management Functions
6.4.4.17 Keyring Metadata
6.4.4.18 Keyring Command Options
6.4.4.19 Keyring System Variables
6.4.5 MySQL Enterprise Audit
6.4.5.1 Elements of MySQL Enterprise Audit
6.4.5.2 Installing or Uninstalling MySQL Enterprise Audit
6.4.5.3 MySQL Enterprise Audit Security Considerations
6.4.5.4 Audit Log File Formats
6.4.5.5 Configuring Audit Logging Characteristics
6.4.5.6 Reading Audit Log Files
6.4.5.7 Audit Log Filtering
6.4.5.8 Writing Audit Log Filter Definitions
6.4.5.9 Disabling Audit Logging
6.4.5.10 Legacy Mode Audit Log Filtering
6.4.5.11 Audit Log Reference
6.4.5.12 Audit Log Restrictions
6.4.6 The Audit Message Component
6.4.7 MySQL Enterprise Firewall
6.4.7.1 Elements of MySQL Enterprise Firewall
6.4.7.2 Installing or Uninstalling MySQL Enterprise Firewall
6.4.7.3 Using MySQL Enterprise Firewall
6.4.7.4 MySQL Enterprise Firewall Reference
6.5 MySQL Enterprise Data Masking and De-Identification
6.5.1 Data-Masking Components Versus the Data-Masking Plugin
6.5.2 MySQL Enterprise Data Masking and De-Identification Components
6.5.2.1 MySQL Enterprise Data Masking and De-Identification Component Installation
6.5.2.2 Using MySQL Enterprise Data Masking and De-Identification Components
6.5.2.3 MySQL Enterprise Data Masking and De-Identification Component Function Reference
6.5.2.4 MySQL Enterprise Data Masking and De-Identification Component Function Descriptions
6.5.3 MySQL Enterprise Data Masking and De-Identification Plugin
6.5.3.1 MySQL Enterprise Data Masking and De-Identification Plugin Installation
6.5.3.2 Using the MySQL Enterprise Data Masking and De-Identification Plugin
6.5.3.3 MySQL Enterprise Data Masking and De-Identification Plugin Function Reference
6.5.3.4 MySQL Enterprise Data Masking and De-Identification Plugin Function Descriptions
6.6 MySQL Enterprise Encryption
6.6.1 MySQL Enterprise Encryption Installation and Upgrading
6.6.2 Configuring MySQL Enterprise Encryption
6.6.3 MySQL Enterprise Encryption Usage and Examples
6.6.4 MySQL Enterprise Encryption Function Reference
6.6.5 MySQL Enterprise Encryption Component Function Descriptions
6.6.6 MySQL Enterprise Encryption Legacy Function Descriptions
6.7 SELinux
6.7.1 Check if SELinux is Enabled
6.7.2 Changing the SELinux Mode
6.7.3 MySQL Server SELinux Policies
6.7.4 SELinux File Context
6.7.5 SELinux TCP Port Context
6.7.5.1 Setting the TCP Port Context for mysqld
6.7.5.2 Setting the TCP Port Context for MySQL Features
6.7.6 Troubleshooting SELinux
6.8 FIPS Support
Chapter 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.4.5.1 Making a Copy of a Database
7.4.5.2 Copy a Database from one Server to Another
7.4.5.3 Dumping Stored Programs
7.4.5.4 Dumping Table Definitions and Content Separately
7.4.5.5 Using mysqldump to Test for Upgrade Incompatibilities
7.5 Point-in-Time (Incremental) Recovery
7.5.1 Point-in-Time Recovery Using Binary Log
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
Chapter 8 Optimization
8.1 Optimization Overview
8.2 Optimizing SQL Statements
8.2.1 Optimizing SELECT Statements
8.2.1.1 WHERE Clause Optimization
8.2.1.2 Range Optimization
8.2.1.3 Index Merge Optimization
8.2.1.4 Hash Join Optimization
8.2.1.5 Engine Condition Pushdown Optimization
8.2.1.6 Index Condition Pushdown Optimization
8.2.1.7 Nested-Loop Join Algorithms
8.2.1.8 Nested Join Optimization
8.2.1.9 Outer Join Optimization
8.2.1.10 Outer Join Simplification
8.2.1.11 Multi-Range Read Optimization
8.2.1.12 Block Nested-Loop and Batched Key Access Joins
8.2.1.13 Condition Filtering
8.2.1.14 Constant-Folding Optimization
8.2.1.15 IS NULL Optimization
8.2.1.16 ORDER BY Optimization
8.2.1.17 GROUP BY Optimization
8.2.1.18 DISTINCT Optimization
8.2.1.19 LIMIT Query Optimization
8.2.1.20 Function Call Optimization
8.2.1.21 Window Function Optimization
8.2.1.22 Row Constructor Expression Optimization
8.2.1.23 Avoiding Full Table Scans
8.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions
8.2.2.1 Optimizing IN and EXISTS Subquery Predicates with Semijoin and Antijoin Transformations
8.2.2.2 Optimizing Subqueries with Materialization
8.2.2.3 Optimizing Subqueries with the EXISTS Strategy
8.2.2.4 Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization
8.2.2.5 Derived Condition Pushdown Optimization
8.2.3 Optimizing INFORMATION_SCHEMA Queries
8.2.4 Optimizing Performance Schema Queries
8.2.5 Optimizing Data Change Statements
8.2.5.1 Optimizing INSERT Statements
8.2.5.2 Optimizing UPDATE Statements
8.2.5.3 Optimizing DELETE 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.2.1 Optimizing for Numeric Data
8.4.2.2 Optimizing for Character and String Types
8.4.2.3 Optimizing for BLOB Types
8.4.3 Optimizing for Many Tables
8.4.3.1 How MySQL Opens and Closes Tables
8.4.3.2 Disadvantages of Creating Many Tables in the Same Database
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.2.1 Shared Key Cache Access
8.10.2.2 Multiple Key Caches
8.10.2.3 Midpoint Insertion Strategy
8.10.2.4 Index Preloading
8.10.2.5 Key Cache Block Size
8.10.2.6 Restructuring a 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.2.1 Using Symbolic Links for Databases on Unix
8.12.2.2 Using Symbolic Links for MyISAM Tables on Unix
8.12.2.3 Using Symbolic Links for Databases on Windows
8.12.3 Optimizing Memory Use
8.12.3.1 How MySQL Uses Memory
8.12.3.2 Monitoring MySQL Memory Usage
8.12.3.3 Enabling Large Page Support
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 Server Thread (Process) Information
8.14.1 Accessing the Process List
8.14.2 Thread Command Values
8.14.3 General Thread States
8.14.4 Replication Source Thread States
8.14.5 Replication I/O (Receiver) Thread States
8.14.6 Replication SQL Thread States
8.14.7 Replication Connection Thread States
8.14.8 NDB Cluster Thread States
8.14.9 Event Scheduler Thread States
Chapter 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 Query Attributes
9.7 Comments
Chapter 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.7.1 The cp932 Character Set
10.10.7.2 The gb18030 Character Set
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.14.4.1 Defining a UCA Collation Using LDML Syntax
10.14.4.2 LDML Syntax Supported in MySQL
10.14.4.3 Diagnostics During Index.xml Parsing
10.15 Character Set Configuration
10.16 MySQL Server Locale Support
Chapter 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 What Calendar Is Used By MySQL?
11.2.8 Conversion Between Date and Time Types
11.2.9 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.2.1 The Geometry Class Hierarchy
11.4.2.2 Geometry Class
11.4.2.3 Point Class
11.4.2.4 Curve Class
11.4.2.5 LineString Class
11.4.2.6 Surface Class
11.4.2.7 Polygon Class
11.4.2.8 GeometryCollection Class
11.4.2.9 MultiPoint Class
11.4.2.10 MultiCurve Class
11.4.2.11 MultiLineString Class
11.4.2.12 MultiSurface Class
11.4.2.13 MultiPolygon Class
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
Chapter 12 Functions and Operators
12.1 Built-In Function and Operator Reference
12.2 Loadable Function Reference
12.3 Type Conversion in Expression Evaluation
12.4 Operators
12.4.1 Operator Precedence
12.4.2 Comparison Functions and Operators
12.4.3 Logical Operators
12.4.4 Assignment Operators
12.5 Flow Control Functions
12.6 Numeric Functions and Operators
12.6.1 Arithmetic Operators
12.6.2 Mathematical Functions
12.7 Date and Time Functions
12.8 String Functions and Operators
12.8.1 String Comparison Functions and Operators
12.8.2 Regular Expressions
12.8.3 Character Set and Collation of Function Results
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 User-Defined 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.7.1 General Geometry Property Functions
12.16.7.2 Point Property Functions
12.16.7.3 LineString and MultiLineString Property Functions
12.16.7.4 Polygon and MultiPolygon Property Functions
12.16.7.5 GeometryCollection Property Functions
12.16.8 Spatial Operator Functions
12.16.9 Functions That Test Spatial Relations Between Geometry Objects
12.16.9.1 Spatial Relation Functions That Use Object Shapes
12.16.9.2 Spatial Relation Functions That Use Minimum Bounding Rectangles
12.16.10 Spatial Geohash Functions
12.16.11 Spatial GeoJSON Functions
12.16.12 Spatial Aggregate Functions
12.16.13 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 Replication Functions
12.18.1 Group Replication Functions
12.18.1.1 Function which Configures Group Replication Primary
12.18.1.2 Functions which Configure the Group Replication Mode
12.18.1.3 Functions to Inspect and Configure the Maximum Consensus Instances of a Group
12.18.1.4 Functions to Inspect and Set the Group Replication Communication Protocol Version
12.18.1.5 Functions to Set and Reset Group Replication Member Actions
12.18.2 Functions Used with Global Transaction Identifiers (GTIDs)
12.18.3 Asynchronous Replication Channel Failover Functions
12.18.4 Position-Based Synchronization Functions
12.19 Aggregate Functions
12.19.1 Aggregate Function Descriptions
12.19.2 GROUP BY Modifiers
12.19.3 MySQL Handling of GROUP BY
12.19.4 Detection of Functional Dependence
12.20 Window Functions
12.20.1 Window Function Descriptions
12.20.2 Window Function Concepts and Syntax
12.20.3 Window Function Frame Specification
12.20.4 Named Windows
12.20.5 Window Function Restrictions
12.21 Performance Schema Functions
12.22 Internal Functions
12.23 Miscellaneous Functions
12.24 Precision Math
12.24.1 Types of Numeric Values
12.24.2 DECIMAL Data Type Characteristics
12.24.3 Expression Handling
12.24.4 Rounding Behavior
12.24.5 Precision Math Examples
Chapter 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.9.1 ALTER TABLE Partition Operations
13.1.9.2 ALTER TABLE and Generated Columns
13.1.9.3 ALTER TABLE Examples
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.20.1 Files Created by CREATE TABLE
13.1.20.2 CREATE TEMPORARY TABLE Statement
13.1.20.3 CREATE TABLE ... LIKE Statement
13.1.20.4 CREATE TABLE ... SELECT Statement
13.1.20.5 FOREIGN KEY Constraints
13.1.20.6 CHECK Constraints
13.1.20.7 Silent Column Specification Changes
13.1.20.8 CREATE TABLE and Generated Columns
13.1.20.9 Secondary Indexes and Generated Columns
13.1.20.10 Invisible Columns
13.1.20.11 Generated Invisible Primary Keys
13.1.20.12 Setting NDB Comment Options
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 EXCEPT Clause
13.2.5 HANDLER Statement
13.2.6 IMPORT TABLE Statement
13.2.7 INSERT Statement
13.2.7.1 INSERT ... SELECT Statement
13.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement
13.2.7.3 INSERT DELAYED Statement
13.2.8 INTERSECT Clause
13.2.9 LOAD DATA Statement
13.2.10 LOAD XML Statement
13.2.11 Parenthesized Query Expressions
13.2.12 REPLACE Statement
13.2.13 SELECT Statement
13.2.13.1 SELECT ... INTO Statement
13.2.13.2 JOIN Clause
13.2.14 Set Operations with UNION, INTERSECT, and EXCEPT
13.2.15 Subqueries
13.2.15.1 The Subquery as Scalar Operand
13.2.15.2 Comparisons Using Subqueries
13.2.15.3 Subqueries with ANY, IN, or SOME
13.2.15.4 Subqueries with ALL
13.2.15.5 Row Subqueries
13.2.15.6 Subqueries with EXISTS or NOT EXISTS
13.2.15.7 Correlated Subqueries
13.2.15.8 Derived Tables
13.2.15.9 Lateral Derived Tables
13.2.15.10 Subquery Errors
13.2.15.11 Optimizing Subqueries
13.2.15.12 Restrictions on Subqueries
13.2.16 TABLE Statement
13.2.17 UPDATE Statement
13.2.18 UNION Clause
13.2.19 VALUES Statement
13.2.20 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.3.8.1 XA Transaction SQL Statements
13.3.8.2 XA Transaction States
13.3.8.3 Restrictions on XA Transactions
13.4 Replication Statements
13.4.1 SQL Statements for Controlling Source Servers
13.4.1.1 PURGE BINARY LOGS Statement
13.4.1.2 RESET MASTER Statement
13.4.1.3 SET sql_log_bin Statement
13.4.2 SQL Statements for Controlling Replica Servers
13.4.2.1 CHANGE MASTER TO Statement
13.4.2.2 CHANGE REPLICATION FILTER Statement
13.4.2.3 CHANGE REPLICATION SOURCE TO Statement
13.4.2.4 RESET REPLICA Statement
13.4.2.5 RESET SLAVE Statement
13.4.2.6 START REPLICA Statement
13.4.2.7 START SLAVE Statement
13.4.2.8 STOP REPLICA Statement
13.4.2.9 STOP SLAVE Statement
13.4.3 SQL Statements for Controlling Group Replication
13.4.3.1 START GROUP_REPLICATION Statement
13.4.3.2 STOP GROUP_REPLICATION Statement
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.4.1 Local Variable DECLARE Statement
13.6.4.2 Local Variable Scope and Resolution
13.6.5 Flow Control Statements
13.6.5.1 CASE Statement
13.6.5.2 IF Statement
13.6.5.3 ITERATE Statement
13.6.5.4 LEAVE Statement
13.6.5.5 LOOP Statement
13.6.5.6 REPEAT Statement
13.6.5.7 RETURN Statement
13.6.5.8 WHILE Statement
13.6.6 Cursors
13.6.6.1 Cursor CLOSE Statement
13.6.6.2 Cursor DECLARE Statement
13.6.6.3 Cursor FETCH Statement
13.6.6.4 Cursor OPEN Statement
13.6.6.5 Restrictions on Server-Side Cursors
13.6.7 Condition Handling
13.6.7.1 DECLARE ... CONDITION Statement
13.6.7.2 DECLARE ... HANDLER Statement
13.6.7.3 GET DIAGNOSTICS Statement
13.6.7.4 RESIGNAL Statement
13.6.7.5 SIGNAL Statement
13.6.7.6 Scope Rules for Handlers
13.6.7.7 The MySQL Diagnostics Area
13.6.7.8 Condition Handling and OUT or INOUT Parameters
13.6.8 Restrictions on Condition Handling
13.7 Database Administration Statements
13.7.1 Account Management Statements
13.7.1.1 ALTER USER Statement
13.7.1.2 CREATE ROLE Statement
13.7.1.3 CREATE USER Statement
13.7.1.4 DROP ROLE Statement
13.7.1.5 DROP USER Statement
13.7.1.6 GRANT Statement
13.7.1.7 RENAME USER Statement
13.7.1.8 REVOKE Statement
13.7.1.9 SET DEFAULT ROLE Statement
13.7.1.10 SET PASSWORD Statement
13.7.1.11 SET ROLE Statement
13.7.2 Resource Group Management Statements
13.7.2.1 ALTER RESOURCE GROUP Statement
13.7.2.2 CREATE RESOURCE GROUP Statement
13.7.2.3 DROP RESOURCE GROUP Statement
13.7.2.4 SET RESOURCE GROUP Statement
13.7.3 Table Maintenance Statements
13.7.3.1 ANALYZE TABLE Statement
13.7.3.2 CHECK TABLE Statement
13.7.3.3 CHECKSUM TABLE Statement
13.7.3.4 OPTIMIZE TABLE Statement
13.7.3.5 REPAIR TABLE Statement
13.7.4 Component, Plugin, and Loadable Function Statements
13.7.4.1 CREATE FUNCTION Statement for Loadable Functions
13.7.4.2 DROP FUNCTION Statement for Loadable Functions
13.7.4.3 INSTALL COMPONENT Statement
13.7.4.4 INSTALL PLUGIN Statement
13.7.4.5 UNINSTALL COMPONENT Statement
13.7.4.6 UNINSTALL PLUGIN Statement
13.7.5 CLONE Statement
13.7.6 SET Statements
13.7.6.1 SET Syntax for Variable Assignment
13.7.6.2 SET CHARACTER SET Statement
13.7.6.3 SET NAMES Statement
13.7.7 SHOW Statements
13.7.7.1 SHOW BINARY LOGS Statement
13.7.7.2 SHOW BINLOG EVENTS Statement
13.7.7.3 SHOW CHARACTER SET Statement
13.7.7.4 SHOW COLLATION Statement
13.7.7.5 SHOW COLUMNS Statement
13.7.7.6 SHOW CREATE DATABASE Statement
13.7.7.7 SHOW CREATE EVENT Statement
13.7.7.8 SHOW CREATE FUNCTION Statement
13.7.7.9 SHOW CREATE PROCEDURE Statement
13.7.7.10 SHOW CREATE TABLE Statement
13.7.7.11 SHOW CREATE TRIGGER Statement
13.7.7.12 SHOW CREATE USER Statement
13.7.7.13 SHOW CREATE VIEW Statement
13.7.7.14 SHOW DATABASES Statement
13.7.7.15 SHOW ENGINE Statement
13.7.7.16 SHOW ENGINES Statement
13.7.7.17 SHOW ERRORS Statement
13.7.7.18 SHOW EVENTS Statement
13.7.7.19 SHOW FUNCTION CODE Statement
13.7.7.20 SHOW FUNCTION STATUS Statement
13.7.7.21 SHOW GRANTS Statement
13.7.7.22 SHOW INDEX Statement
13.7.7.23 SHOW MASTER STATUS Statement
13.7.7.24 SHOW OPEN TABLES Statement
13.7.7.25 SHOW PARSE_TREE Statement
13.7.7.26 SHOW PLUGINS Statement
13.7.7.27 SHOW PRIVILEGES Statement
13.7.7.28 SHOW PROCEDURE CODE Statement
13.7.7.29 SHOW PROCEDURE STATUS Statement
13.7.7.30 SHOW PROCESSLIST Statement
13.7.7.31 SHOW PROFILE Statement
13.7.7.32 SHOW PROFILES Statement
13.7.7.33 SHOW RELAYLOG EVENTS Statement
13.7.7.34 SHOW REPLICAS Statement
13.7.7.35 SHOW SLAVE HOSTS Statement
13.7.7.36 SHOW REPLICA STATUS Statement
13.7.7.37 SHOW SLAVE STATUS Statement
13.7.7.38 SHOW STATUS Statement
13.7.7.39 SHOW TABLE STATUS Statement
13.7.7.40 SHOW TABLES Statement
13.7.7.41 SHOW TRIGGERS Statement
13.7.7.42 SHOW VARIABLES Statement
13.7.7.43 SHOW WARNINGS Statement
13.7.8 Other Administrative Statements
13.7.8.1 BINLOG Statement
13.7.8.2 CACHE INDEX Statement
13.7.8.3 FLUSH Statement
13.7.8.4 KILL Statement
13.7.8.5 LOAD INDEX INTO CACHE Statement
13.7.8.6 RESET Statement
13.7.8.7 RESET PERSIST Statement
13.7.8.8 RESTART Statement
13.7.8.9 SHUTDOWN Statement
13.8 Utility Statements
13.8.1 DESCRIBE Statement
13.8.2 EXPLAIN Statement
13.8.3 HELP Statement
13.8.4 USE Statement
Chapter 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
Chapter 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.1.1 Creating InnoDB Tables
15.6.1.2 Creating Tables Externally
15.6.1.3 Importing InnoDB Tables
15.6.1.4 Moving or Copying InnoDB Tables
15.6.1.5 Converting Tables from MyISAM to InnoDB
15.6.1.6 AUTO_INCREMENT Handling in InnoDB
15.6.2 Indexes
15.6.2.1 Clustered and Secondary Indexes
15.6.2.2 The Physical Structure of an InnoDB Index
15.6.2.3 Sorted Index Builds
15.6.2.4 InnoDB Full-Text Indexes
15.6.3 Tablespaces
15.6.3.1 The System Tablespace
15.6.3.2 File-Per-Table Tablespaces
15.6.3.3 General Tablespaces
15.6.3.4 Undo Tablespaces
15.6.3.5 Temporary Tablespaces
15.6.3.6 Moving Tablespace Files While the Server is Offline
15.6.3.7 Disabling Tablespace Path Validation
15.6.3.8 Optimizing Tablespace Space Allocation on Linux
15.6.3.9 Tablespace AUTOEXTEND_SIZE Configuration
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.2.1 Transaction Isolation Levels
15.7.2.2 autocommit, Commit, and Rollback
15.7.2.3 Consistent Nonlocking Reads
15.7.2.4 Locking Reads
15.7.3 Locks Set by Different SQL Statements in InnoDB
15.7.4 Phantom Rows
15.7.5 Deadlocks in InnoDB
15.7.5.1 An InnoDB Deadlock Example
15.7.5.2 Deadlock Detection
15.7.5.3 How to Minimize and Handle Deadlocks
15.7.6 Transaction Scheduling
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.3.1 Configuring InnoDB Buffer Pool Size
15.8.3.2 Configuring Multiple Buffer Pool Instances
15.8.3.3 Making the Buffer Pool Scan Resistant
15.8.3.4 Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)
15.8.3.5 Configuring Buffer Pool Flushing
15.8.3.6 Saving and Restoring the Buffer Pool State
15.8.3.7 Excluding Buffer Pool Pages from Core Files
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.10.1 Configuring Persistent Optimizer Statistics Parameters
Configuring Automatic Statistics Calculation for Persistent Optimizer Statistics
Configuring Optimizer Statistics Parameters for Individual Tables
Configuring the Number of Sampled Pages for InnoDB Optimizer Statistics
Including Delete-marked Records in Persistent Statistics Calculations
InnoDB Persistent Statistics Tables
InnoDB Persistent Statistics Tables Example
Retrieving Index Size Using the innodb_index_stats Table
15.8.10.2 Configuring Non-Persistent Optimizer Statistics Parameters
15.8.10.3 Estimating ANALYZE TABLE Complexity for InnoDB Tables
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.1.1 Overview of Table Compression
15.9.1.2 Creating Compressed Tables
15.9.1.3 Tuning Compression for InnoDB Tables
15.9.1.4 Monitoring InnoDB Table Compression at Runtime
15.9.1.5 How Compression Works for InnoDB Tables
15.9.1.6 Compression for OLTP Workloads
15.9.1.7 SQL Compression Syntax Warnings and Errors
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 Online DDL Memory Management
15.12.5 Configuring Parallel Threads for Online DDL Operations
15.12.6 Simplifying DDL Statements with Online DDL
15.12.7 Online DDL Failure Conditions
15.12.8 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.1.1 INNODB_CMP and INNODB_CMP_RESET
15.15.1.2 INNODB_CMPMEM and INNODB_CMPMEM_RESET
15.15.1.3 Using the Compression Information Schema Tables
15.15.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information
15.15.2.1 Using InnoDB Transaction and Locking Information
15.15.2.2 InnoDB Lock and Lock-Wait Information
15.15.2.3 Persistence and Consistency of InnoDB 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.6.1 Adapting an Existing MySQL Schema for the InnoDB memcached Plugin
15.20.6.2 Adapting a memcached Application for the InnoDB memcached Plugin
15.20.6.3 Tuning InnoDB memcached Plugin Performance
15.20.6.4 Controlling Transactional Behavior of the InnoDB memcached Plugin
15.20.6.5 Adapting DML Statements to memcached Operations
15.20.6.6 Performing DML and DDL Statements on the Underlying InnoDB Table
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 Troubleshooting Recovery Failures
15.21.3 Forcing InnoDB Recovery
15.21.4 Troubleshooting InnoDB Data Dictionary Operations
15.21.5 InnoDB Error Handling
15.22 InnoDB Limits
15.23 InnoDB Restrictions and Limitations
Chapter 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.3.1 Static (Fixed-Length) Table Characteristics
16.2.3.2 Dynamic Table Characteristics
16.2.3.3 Compressed Table Characteristics
16.2.4 MyISAM Table Problems
16.2.4.1 Corrupted MyISAM Tables
16.2.4.2 Problems from Tables Not Being Closed Properly
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.2.1 Creating a FEDERATED Table Using CONNECTION
16.8.2.2 Creating a FEDERATED Table Using CREATE SERVER
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
Chapter 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.2.1 Setting the Replication Source Configuration
17.1.2.2 Setting the Replica Configuration
17.1.2.3 Creating a User for Replication
17.1.2.4 Obtaining the Replication Source Binary Log Coordinates
17.1.2.5 Choosing a Method for Data Snapshots
Creating a Data Snapshot Using mysqldump
Creating a Data Snapshot Using Raw Data Files
17.1.2.6 Setting Up Replicas
Setting Up Replication with New Source and Replicas
Setting Up Replication with Existing Data
17.1.2.7 Setting the Source Configuration on the Replica
17.1.2.8 Adding Replicas to a Replication Environment
17.1.3 Replication with Global Transaction Identifiers
17.1.3.1 GTID Format and Storage
17.1.3.2 GTID Life Cycle
17.1.3.3 GTID Auto-Positioning
17.1.3.4 Setting Up Replication Using GTIDs
17.1.3.5 Using GTIDs for Failover and Scaleout
17.1.3.6 Replication From a Source Without GTIDs to a Replica With GTIDs
17.1.3.7 Restrictions on Replication with GTIDs
17.1.3.8 Stored Function Examples to Manipulate GTIDs
17.1.4 Changing GTID Mode on Online Servers
17.1.4.1 Replication Mode Concepts
17.1.4.2 Enabling GTID Transactions Online
17.1.4.3 Disabling GTID Transactions Online
17.1.4.4 Verifying Replication of Anonymous Transactions
17.1.5 MySQL Multi-Source Replication
17.1.5.1 Configuring Multi-Source Replication
17.1.5.2 Provisioning a Multi-Source Replica for GTID-Based Replication
17.1.5.3 Adding GTID-Based Sources to a Multi-Source Replica
17.1.5.4 Adding Binary Log Based Replication Sources to a Multi-Source Replica
17.1.5.5 Starting Multi-Source Replicas
17.1.5.6 Stopping Multi-Source Replicas
17.1.5.7 Resetting Multi-Source Replicas
17.1.5.8 Monitoring Multi-Source Replication
Monitoring Channels Using Performance Schema Tables
17.1.6 Replication and Binary Logging Options and Variables
17.1.6.1 Replication and Binary Logging Option and Variable Reference
17.1.6.2 Replication Source Options and Variables
17.1.6.3 Replica Server Options and Variables
17.1.6.4 Binary Logging Options and Variables
17.1.6.5 Global Transaction ID System Variables
17.1.7 Common Replication Administration Tasks
17.1.7.1 Checking Replication Status
17.1.7.2 Pausing Replication on the Replica
17.1.7.3 Skipping Transactions
Skipping Transactions With GTIDs
Skipping Transactions Without GTIDs
Skipping Transactions With SET GLOBAL sql_replica_skip_counter
Skipping Transactions With CHANGE REPLICATION SOURCE TO
17.2 Replication Implementation
17.2.1 Replication Formats
17.2.1.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication
17.2.1.2 Usage of Row-Based Logging and Replication
17.2.1.3 Determination of Safe and Unsafe Statements in Binary Logging
17.2.2 Replication Channels
17.2.2.1 Commands for Operations on a Single Channel
17.2.2.2 Compatibility with Previous Replication Statements
17.2.2.3 Startup Options and Replication Channels
17.2.2.4 Replication Channel Naming Conventions
17.2.3 Replication Threads
17.2.3.1 Monitoring Replication Main Threads
17.2.3.2 Monitoring Replication Applier Worker Threads
17.2.4 Relay Log and Replication Metadata Repositories
17.2.4.1 The Relay Log
17.2.4.2 Replication Metadata Repositories
17.2.5 How Servers Evaluate Replication Filtering Rules
17.2.5.1 Evaluation of Database-Level Replication and Binary Logging Options
17.2.5.2 Evaluation of Table-Level Replication Options
17.2.5.3 Interactions Between Replication Filtering Options
17.2.5.4 Replication Channel Based Filters
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.2.1 Scope of Binary Log Encryption
17.3.2.2 Binary Log Encryption Keys
17.3.2.3 Binary Log Master Key Rotation
17.3.3 Replication Privilege Checks
17.3.3.1 Privileges For The Replication PRIVILEGE_CHECKS_USER Account
17.3.3.2 Privilege Checks For Group Replication Channels
17.3.3.3 Recovering From Failed Replication Privilege Checks
17.4 Replication Solutions
17.4.1 Using Replication for Backups
17.4.1.1 Backing Up a Replica Using mysqldump
17.4.1.2 Backing Up Raw Data from a Replica
17.4.1.3 Backing Up a Source or Replica by Making It Read Only
17.4.2 Handling an Unexpected Halt of a Replica
17.4.3 Monitoring Row-based Replication
17.4.4 Using Replication with Different Source and Replica Storage Engines
17.4.5 Using Replication for Scale-Out
17.4.6 Replicating Different Databases to Different Replicas
17.4.7 Improving Replication Performance
17.4.8 Switching Sources During Failover
17.4.9 Switching Sources and Replicas with Asynchronous Connection Failover
17.4.9.1 Asynchronous Connection Failover for Sources
17.4.9.2 Asynchronous Connection Failover for Replicas
17.4.10 Semisynchronous Replication
17.4.10.1 Installing Semisynchronous Replication
17.4.10.2 Configuring Semisynchronous Replication
17.4.10.3 Semisynchronous Replication Monitoring
17.4.11 Delayed Replication
17.5 Replication Notes and Tips
17.5.1 Replication Features and Issues
17.5.1.1 Replication and AUTO_INCREMENT
17.5.1.2 Replication and BLACKHOLE Tables
17.5.1.3 Replication and Character Sets
17.5.1.4 Replication and CHECKSUM TABLE
17.5.1.5 Replication of CREATE SERVER, ALTER SERVER, and DROP SERVER
17.5.1.6 Replication of CREATE ... IF NOT EXISTS Statements
17.5.1.7 Replication of CREATE TABLE ... SELECT Statements
17.5.1.8 Replication of CURRENT_USER()
17.5.1.9 Replication with Differing Table Definitions on Source and Replica
Replication with More Columns on Source or Replica
Replication of Columns Having Different Data Types
17.5.1.10 Replication and DIRECTORY Table Options
17.5.1.11 Replication of DROP ... IF EXISTS Statements
17.5.1.12 Replication and Floating-Point Values
17.5.1.13 Replication and FLUSH
17.5.1.14 Replication and System Functions
17.5.1.15 Replication and Fractional Seconds Support
17.5.1.16 Replication of Invoked Features
17.5.1.17 Replication of JSON Documents
17.5.1.18 Replication and LIMIT
17.5.1.19 Replication and LOAD DATA
17.5.1.20 Replication and max_allowed_packet
17.5.1.21 Replication and MEMORY Tables
17.5.1.22 Replication of the mysql System Schema
17.5.1.23 Replication and the Query Optimizer
17.5.1.24 Replication and Partitioning
17.5.1.25 Replication and REPAIR TABLE
17.5.1.26 Replication and Reserved Words
17.5.1.27 Replication and Row Searches
17.5.1.28 Replication and Source or Replica Shutdowns
17.5.1.29 Replica Errors During Replication
17.5.1.30 Replication and Server SQL Mode
17.5.1.31 Replication and Temporary Tables
17.5.1.32 Replication Retries and Timeouts
17.5.1.33 Replication and Time Zones
17.5.1.34 Replication and Transaction Inconsistencies
17.5.1.35 Replication and Transactions
17.5.1.36 Replication and Triggers
17.5.1.37 Replication and TRUNCATE TABLE
17.5.1.38 Replication and User Name Length
17.5.1.39 Replication and Variables
17.5.1.40 Replication and Views
17.5.2 Replication Compatibility Between MySQL Versions
17.5.3 Upgrading a Replication Topology
17.5.4 Troubleshooting Replication
17.5.5 How to Report Replication Bugs or Problems
Chapter 18 Group Replication
18.1 Group Replication Background
18.1.1 Replication Technologies
18.1.1.1 Source to Replica Replication
18.1.1.2 Group Replication
18.1.2 Group Replication Use Cases
18.1.3 Multi-Primary and Single-Primary Modes
18.1.3.1 Single-Primary Mode
Primary Election Algorithm
Finding the Primary
18.1.3.2 Multi-Primary Mode
Transaction Checks
Data Definition Statements
Version Compatibility
18.1.4 Group Replication Services
18.1.4.1 Group Membership
18.1.4.2 Failure Detection
18.1.4.3 Fault-tolerance
18.1.4.4 Observability
18.1.5 Group Replication Plugin Architecture
18.2 Getting Started
18.2.1 Deploying Group Replication in Single-Primary Mode
18.2.1.1 Deploying Instances for Group Replication
18.2.1.2 Configuring an Instance for Group Replication
18.2.1.3 User Credentials For Distributed Recovery
18.2.1.4 Launching Group Replication
18.2.1.5 Bootstrapping the Group
18.2.1.6 Adding Instances to the Group
Adding a Second Instance
Adding Additional Instances
18.2.2 Deploying Group Replication Locally
18.3 Requirements and Limitations
18.3.1 Group Replication Requirements
18.3.2 Group Replication Limitations
18.4 Monitoring Group Replication
18.4.1 GTIDs and Group Replication
18.4.2 Group Replication Server States
18.4.3 The replication_group_members Table
18.4.4 The replication_group_member_stats Table
18.5 Group Replication Operations
18.5.1 Configuring an Online Group
18.5.1.1 Changing the Primary
18.5.1.2 Changing the Group Mode
18.5.1.3 Using Group Replication Group Write Consensus
18.5.1.4 Setting a Group's Communication Protocol Version
18.5.1.5 Configuring Member Actions
18.5.2 Restarting a Group
18.5.3 Transaction Consistency Guarantees
18.5.3.1 Understanding Transaction Consistency Guarantees
18.5.3.2 Configuring Transaction Consistency Guarantees
18.5.4 Distributed Recovery
18.5.4.1 Connections for Distributed Recovery
Selecting addresses for distributed recovery endpoints
Compression for Distributed Recovery
Replication User for Distributed Recovery
SSL and Authentication for Distributed Recovery
18.5.4.2 Cloning for Distributed Recovery
Prerequisites for Cloning
Threshold for Cloning
Cloning Operations
Cloning for Other Purposes
18.5.4.3 Configuring Distributed Recovery
18.5.4.4 Fault Tolerance for Distributed Recovery
18.5.4.5 How Distributed Recovery Works
18.5.5 Support For IPv6 And For Mixed IPv6 And IPv4 Groups
18.5.6 Using MySQL Enterprise Backup with Group Replication
18.6 Group Replication Security
18.6.1 Communication Stack for Connection Security Management
18.6.2 Securing Group Communication Connections with Secure Socket Layer (SSL)
18.6.3 Securing Distributed Recovery Connections
18.6.3.1 Secure User Credentials for Distributed Recovery
Replication User With The Caching SHA-2 Authentication Plugin
Replication User With SSL
Providing Replication User Credentials Securely
18.6.3.2 Secure Socket Layer (SSL) Connections for Distributed Recovery
18.6.4 Group Replication IP Address Permissions
18.7 Group Replication Performance and Troubleshooting
18.7.1 Fine Tuning the Group Communication Thread
18.7.2 Flow Control
18.7.2.1 Probes and Statistics
18.7.2.2 Group Replication Throttling
18.7.3 Single Consensus Leader
18.7.4 Message Compression
18.7.5 Message Fragmentation
18.7.6 XCom Cache Management
18.7.6.1 Increasing the cache size
18.7.6.2 Reducing the cache size
18.7.7 Responses to Failure Detection and Network Partitioning
18.7.7.1 Expel Timeout
18.7.7.2 Unreachable Majority Timeout
18.7.7.3 Auto-Rejoin
18.7.7.4 Exit Action
18.7.8 Handling a Network Partition and Loss of Quorum
18.7.9 Monitoring Group Replication Memory Usage with Performance Schema Memory Instrumentation
18.7.9.1 Enabling or Disabling Group Replication Instrumentation
18.7.9.2 Example Queries
18.8 Upgrading Group Replication
18.8.1 Combining Different Member Versions in a Group
18.8.1.1 Member Versions During Upgrades
18.8.1.2 Group Replication Communication Protocol Version
18.8.2 Group Replication Offline Upgrade
18.8.3 Group Replication Online Upgrade
18.8.3.1 Online Upgrade Considerations
18.8.3.2 Upgrading a Group Replication Member
18.8.3.3 Group Replication Online Upgrade Methods
18.8.3.4 Group Replication Upgrade with mysqlbackup
18.9 Group Replication Variables
18.9.1 Group Replication System Variables
18.9.2 Group Replication Status Variables
18.10 Frequently Asked Questions
Chapter 19 MySQL Shell
Chapter 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.3.1 Create, List, and Drop Collections
20.3.3.2 Working with Collections
20.3.3.3 Find Documents
20.3.3.4 Modify Documents
20.3.3.5 Remove Documents
20.3.3.6 Create and Drop Indexes
20.3.4 Relational Tables
20.3.4.1 Insert Records into Tables
20.3.4.2 Select Tables
20.3.4.3 Update Tables
20.3.4.4 Delete 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.3.1 Create, List, and Drop Collections
20.4.3.2 Working with Collections
20.4.3.3 Find Documents
20.4.3.4 Modify Documents
20.4.3.5 Remove Documents
20.4.3.6 Create and Drop Indexes
20.4.4 Relational Tables
20.4.4.1 Insert Records into Tables
20.4.4.2 Select Tables
20.4.4.3 Update Tables
20.4.4.4 Delete 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 Encrypted 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.6.1 X Plugin Option and Variable Reference
20.5.6.2 X Plugin Options and System Variables
20.5.6.3 X Plugin Status Variables
20.5.7 Monitoring X Plugin
Chapter 21 InnoDB Cluster
Chapter 22 InnoDB ReplicaSet
Chapter 23 MySQL NDB Cluster 8.1
23.1 General Information
23.2 NDB Cluster Overview
23.2.1 NDB Cluster Core Concepts
23.2.2 NDB Cluster Nodes, Node Groups, Fragment Replicas, and Partitions
23.2.3 NDB Cluster Hardware, Software, and Networking Requirements
23.2.4 What is New in MySQL NDB Cluster 8.1
23.2.5 Options, Variables, and Parameters Added, Deprecated or Removed in NDB 8.1
23.2.6 MySQL Server Using InnoDB Compared with NDB Cluster
23.2.6.1 Differences Between the NDB and InnoDB Storage Engines
23.2.6.2 NDB and InnoDB Workloads
23.2.6.3 NDB and InnoDB Feature Usage Summary
23.2.7 Known Limitations of NDB Cluster
23.2.7.1 Noncompliance with SQL Syntax in NDB Cluster
23.2.7.2 Limits and Differences of NDB Cluster from Standard MySQL Limits
23.2.7.3 Limits Relating to Transaction Handling in NDB Cluster
23.2.7.4 NDB Cluster Error Handling
23.2.7.5 Limits Associated with Database Objects in NDB Cluster
23.2.7.6 Unsupported or Missing Features in NDB Cluster
23.2.7.7 Limitations Relating to Performance in NDB Cluster
23.2.7.8 Issues Exclusive to NDB Cluster
23.2.7.9 Limitations Relating to NDB Cluster Disk Data Storage
23.2.7.10 Limitations Relating to Multiple NDB Cluster Nodes
23.2.7.11 Previous NDB Cluster Issues Resolved in NDB Cluster 8.0
23.3 NDB Cluster Installation
23.3.1 Installation of NDB Cluster on Linux
23.3.1.1 Installing an NDB Cluster Binary Release on Linux
23.3.1.2 Installing NDB Cluster from RPM
23.3.1.3 Installing NDB Cluster Using .deb Files
23.3.1.4 Building NDB Cluster from Source on Linux
23.3.2 Installing NDB Cluster on Windows
23.3.2.1 Installing NDB Cluster on Windows from a Binary Release
23.3.2.2 Compiling and Installing NDB Cluster from Source on Windows
23.3.2.3 Initial Startup of NDB Cluster on Windows
23.3.2.4 Installing NDB Cluster Processes as Windows Services
23.3.3 Initial Configuration of NDB Cluster
23.3.4 Initial Startup of NDB Cluster
23.3.5 NDB Cluster Example with Tables and Data
23.3.6 Safe Shutdown and Restart of NDB Cluster
23.3.7 Upgrading and Downgrading NDB Cluster
23.4 Configuration of NDB Cluster
23.4.1 Quick Test Setup of NDB Cluster
23.4.2 Overview of NDB Cluster Configuration Parameters, Options, and Variables
23.4.2.1 NDB Cluster Data Node Configuration Parameters
23.4.2.2 NDB Cluster Management Node Configuration Parameters
23.4.2.3 NDB Cluster SQL Node and API Node Configuration Parameters
23.4.2.4 Other NDB Cluster Configuration Parameters
23.4.2.5 NDB Cluster mysqld Option and Variable Reference
23.4.3 NDB Cluster Configuration Files
23.4.3.1 NDB Cluster Configuration: Basic Example
23.4.3.2 Recommended Starting Configuration for NDB Cluster
23.4.3.3 NDB Cluster Connection Strings
23.4.3.4 Defining Computers in an NDB Cluster
23.4.3.5 Defining an NDB Cluster Management Server
23.4.3.6 Defining NDB Cluster Data Nodes
23.4.3.7 Defining SQL and Other API Nodes in an NDB Cluster
23.4.3.8 Defining the System
23.4.3.9 MySQL Server Options and Variables for NDB Cluster
MySQL Server Options for NDB Cluster
NDB Cluster System Variables
NDB Cluster Status Variables
23.4.3.10 NDB Cluster TCP/IP Connections
23.4.3.11 NDB Cluster TCP/IP Connections Using Direct Connections
23.4.3.12 NDB Cluster Shared-Memory Connections
23.4.3.13 Data Node Memory Management
23.4.3.14 Configuring NDB Cluster Send Buffer Parameters
23.4.4 Using High-Speed Interconnects with NDB Cluster
23.5 NDB Cluster Programs
23.5.1 ndbd — The NDB Cluster Data Node Daemon
23.5.2 ndbinfo_select_all — Select From ndbinfo Tables
23.5.3 ndbmtd — The NDB Cluster Data Node Daemon (Multi-Threaded)
23.5.4 ndb_mgmd — The NDB Cluster Management Server Daemon
23.5.5 ndb_mgm — The NDB Cluster Management Client
23.5.6 ndb_blob_tool — Check and Repair BLOB and TEXT columns of NDB Cluster Tables
23.5.7 ndb_config — Extract NDB Cluster Configuration Information
23.5.8 ndb_delete_all — Delete All Rows from an NDB Table
23.5.9 ndb_desc — Describe NDB Tables
23.5.10 ndb_drop_index — Drop Index from an NDB Table
23.5.11 ndb_drop_table — Drop an NDB Table
23.5.12 ndb_error_reporter — NDB Error-Reporting Utility
23.5.13 ndb_import — Import CSV Data Into NDB
23.5.14 ndb_index_stat — NDB Index Statistics Utility
23.5.15 ndb_move_data — NDB Data Copy Utility
23.5.16 ndb_perror — Obtain NDB Error Message Information
23.5.17 ndb_print_backup_file — Print NDB Backup File Contents
23.5.18 ndb_print_file — Print NDB Disk Data File Contents
23.5.19 ndb_print_frag_file — Print NDB Fragment List File Contents
23.5.20 ndb_print_schema_file — Print NDB Schema File Contents
23.5.21 ndb_print_sys_file — Print NDB System File Contents
23.5.22 ndb_redo_log_reader — Check and Print Content of Cluster Redo Log
23.5.23 ndb_restore — Restore an NDB Cluster Backup
23.5.24 ndb_secretsfile_reader — Obtain Key Information from an Encrypted NDB Data File
23.5.25 ndb_select_all — Print Rows from an NDB Table
23.5.26 ndb_select_count — Print Row Counts for NDB Tables
23.5.27 ndb_show_tables — Display List of NDB Tables
23.5.28 ndb_size.pl — NDBCLUSTER Size Requirement Estimator
23.5.29 ndb_top — View CPU usage information for NDB threads
23.5.30 ndb_waiter — Wait for NDB Cluster to Reach a Given Status
23.5.31 ndbxfrm — Compress, Decompress, Encrypt, and Decrypt Files Created by NDB Cluster
23.6 Management of NDB Cluster
23.6.1 Commands in the NDB Cluster Management Client
23.6.2 NDB Cluster Log Messages
23.6.2.1 NDB Cluster: Messages in the Cluster Log
23.6.2.2 NDB Cluster Log Startup Messages
23.6.2.3 Event Buffer Reporting in the Cluster Log
23.6.2.4 NDB Cluster: NDB Transporter Errors
23.6.3 Event Reports Generated in NDB Cluster
23.6.3.1 NDB Cluster Logging Management Commands
23.6.3.2 NDB Cluster Log Events
23.6.3.3 Using CLUSTERLOG STATISTICS in the NDB Cluster Management Client
23.6.4 Summary of NDB Cluster Start Phases
23.6.5 Performing a Rolling Restart of an NDB Cluster
23.6.6 NDB Cluster Single User Mode
23.6.7 Adding NDB Cluster Data Nodes Online
23.6.7.1 Adding NDB Cluster Data Nodes Online: General Issues
23.6.7.2 Adding NDB Cluster Data Nodes Online: Basic procedure
23.6.7.3 Adding NDB Cluster Data Nodes Online: Detailed Example
23.6.8 Online Backup of NDB Cluster
23.6.8.1 NDB Cluster Backup Concepts
23.6.8.2 Using The NDB Cluster Management Client to Create a Backup
23.6.8.3 Configuration for NDB Cluster Backups
23.6.8.4 NDB Cluster Backup Troubleshooting
23.6.8.5 Taking an NDB Backup with Parallel Data Nodes
23.6.9 Importing Data Into MySQL Cluster
23.6.10 MySQL Server Usage for NDB Cluster
23.6.11 NDB Cluster Disk Data Tables
23.6.11.1 NDB Cluster Disk Data Objects
23.6.11.2 NDB Cluster Disk Data Storage Requirements
23.6.12 Online Operations with ALTER TABLE in NDB Cluster
23.6.13 Privilege Synchronization and NDB_STORED_USER
23.6.14 File System Encryption for NDB Cluster
23.6.14.1 NDB File System Encryption Setup and Usage
23.6.14.2 NDB File System Encryption Implementation
23.6.14.3 NDB File System Encryption Limitations
23.6.15 NDB API Statistics Counters and Variables
23.6.16 ndbinfo: The NDB Cluster Information Database
23.6.16.1 The ndbinfo arbitrator_validity_detail Table
23.6.16.2 The ndbinfo arbitrator_validity_summary Table
23.6.16.3 The ndbinfo backup_id Table
23.6.16.4 The ndbinfo blobs Table
23.6.16.5 The ndbinfo blocks Table
23.6.16.6 The ndbinfo cluster_locks Table
23.6.16.7 The ndbinfo cluster_operations Table
23.6.16.8 The ndbinfo cluster_transactions Table
23.6.16.9 The ndbinfo config_nodes Table
23.6.16.10 The ndbinfo config_params Table
23.6.16.11 The ndbinfo config_values Table
23.6.16.12 The ndbinfo counters Table
23.6.16.13 The ndbinfo cpudata Table
23.6.16.14 The ndbinfo cpudata_1sec Table
23.6.16.15 The ndbinfo cpudata_20sec Table
23.6.16.16 The ndbinfo cpudata_50ms Table
23.6.16.17 The ndbinfo cpuinfo Table
23.6.16.18 The ndbinfo cpustat Table
23.6.16.19 The ndbinfo cpustat_50ms Table
23.6.16.20 The ndbinfo cpustat_1sec Table
23.6.16.21 The ndbinfo cpustat_20sec Table
23.6.16.22 The ndbinfo dictionary_columns Table
23.6.16.23 The ndbinfo dictionary_tables Table
23.6.16.24 The ndbinfo dict_obj_info Table
23.6.16.25 The ndbinfo dict_obj_tree Table
23.6.16.26 The ndbinfo dict_obj_types Table
23.6.16.27 The ndbinfo disk_write_speed_base Table
23.6.16.28 The ndbinfo disk_write_speed_aggregate Table
23.6.16.29 The ndbinfo disk_write_speed_aggregate_node Table
23.6.16.30 The ndbinfo diskpagebuffer Table
23.6.16.31 The ndbinfo diskstat Table
23.6.16.32 The ndbinfo diskstats_1sec Table
23.6.16.33 The ndbinfo error_messages Table
23.6.16.34 The ndbinfo events Table
23.6.16.35 The ndbinfo files Table
23.6.16.36 The ndbinfo foreign_keys Table
23.6.16.37 The ndbinfo hash_maps Table
23.6.16.38 The ndbinfo hwinfo Table
23.6.16.39 The ndbinfo index_columns Table
23.6.16.40 The ndbinfo index_stats Table
23.6.16.41 The ndbinfo locks_per_fragment Table
23.6.16.42 The ndbinfo logbuffers Table
23.6.16.43 The ndbinfo logspaces Table
23.6.16.44 The ndbinfo membership Table
23.6.16.45 The ndbinfo memoryusage Table
23.6.16.46 The ndbinfo memory_per_fragment Table
23.6.16.47 The ndbinfo nodes Table
23.6.16.48 The ndbinfo operations_per_fragment Table
23.6.16.49 The ndbinfo pgman_time_track_stats Table
23.6.16.50 The ndbinfo processes Table
23.6.16.51 The ndbinfo resources Table
23.6.16.52 The ndbinfo restart_info Table
23.6.16.53 The ndbinfo server_locks Table
23.6.16.54 The ndbinfo server_operations Table
23.6.16.55 The ndbinfo server_transactions Table
23.6.16.56 The ndbinfo table_distribution_status Table
23.6.16.57 The ndbinfo table_fragments Table
23.6.16.58 The ndbinfo table_info Table
23.6.16.59 The ndbinfo table_replicas Table
23.6.16.60 The ndbinfo tc_time_track_stats Table
23.6.16.61 The ndbinfo threadblocks Table
23.6.16.62 The ndbinfo threads Table
23.6.16.63 The ndbinfo threadstat Table
23.6.16.64 The ndbinfo transporters Table
23.6.17 INFORMATION_SCHEMA Tables for NDB Cluster
23.6.18 NDB Cluster and the Performance Schema
23.6.19 Quick Reference: NDB Cluster SQL Statements
23.6.20 NDB Cluster Security Issues
23.6.20.1 NDB Cluster Security and Networking Issues
23.6.20.2 NDB Cluster and MySQL Privileges
23.6.20.3 NDB Cluster and MySQL Security Procedures
23.7 NDB Cluster Replication
23.7.1 NDB Cluster Replication: Abbreviations and Symbols
23.7.2 General Requirements for NDB Cluster Replication
23.7.3 Known Issues in NDB Cluster Replication
23.7.4 NDB Cluster Replication Schema and Tables
23.7.5 Preparing the NDB Cluster for Replication
23.7.6 Starting NDB Cluster Replication (Single Replication Channel)
23.7.7 Using Two Replication Channels for NDB Cluster Replication
23.7.8 Implementing Failover with NDB Cluster Replication
23.7.9 NDB Cluster Backups With NDB Cluster Replication
23.7.9.1 NDB Cluster Replication: Automating Synchronization of the Replica to the Source Binary Log
23.7.9.2 Point-In-Time Recovery Using NDB Cluster Replication
23.7.10 NDB Cluster Replication: Bidirectional and Circular Replication
23.7.11 NDB Cluster Replication Using the Multithreaded Applier
23.7.12 NDB Cluster Replication Conflict Resolution
23.8 NDB Cluster Release Notes
Chapter 24 Partitioning
24.1 Overview of Partitioning in MySQL
24.2 Partitioning Types
24.2.1 RANGE Partitioning
24.2.2 LIST Partitioning
24.2.3 COLUMNS Partitioning
24.2.3.1 RANGE COLUMNS partitioning
24.2.3.2 LIST COLUMNS partitioning
24.2.4 HASH Partitioning
24.2.4.1 LINEAR HASH Partitioning
24.2.5 KEY Partitioning
24.2.6 Subpartitioning
24.2.7 How MySQL Partitioning Handles NULL
24.3 Partition Management
24.3.1 Management of RANGE and LIST Partitions
24.3.2 Management of HASH and KEY Partitions
24.3.3 Exchanging Partitions and Subpartitions with Tables
24.3.4 Maintenance of Partitions
24.3.5 Obtaining Information About Partitions
24.4 Partition Pruning
24.5 Partition Selection
24.6 Restrictions and Limitations on Partitioning
24.6.1 Partitioning Keys, Primary Keys, and Unique Keys
24.6.2 Partitioning Limitations Relating to Storage Engines
24.6.3 Partitioning Limitations Relating to Functions
Chapter 25 Stored Objects
25.1 Defining Stored Programs
25.2 Using Stored Routines
25.2.1 Stored Routine Syntax
25.2.2 Stored Routines and MySQL Privileges
25.2.3 Stored Routine Metadata
25.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()
25.3 Using Triggers
25.3.1 Trigger Syntax and Examples
25.3.2 Trigger Metadata
25.4 Using the Event Scheduler
25.4.1 Event Scheduler Overview
25.4.2 Event Scheduler Configuration
25.4.3 Event Syntax
25.4.4 Event Metadata
25.4.5 Event Scheduler Status
25.4.6 The Event Scheduler and MySQL Privileges
25.5 Using Views
25.5.1 View Syntax
25.5.2 View Processing Algorithms
25.5.3 Updatable and Insertable Views
25.5.4 The View WITH CHECK OPTION Clause
25.5.5 View Metadata
25.6 Stored Object Access Control
25.7 Stored Program Binary Logging
25.8 Restrictions on Stored Programs
25.9 Restrictions on Views
Chapter 26 INFORMATION_SCHEMA Tables
26.1 Introduction
26.2 INFORMATION_SCHEMA Table Reference
26.3 INFORMATION_SCHEMA General Tables
26.3.1 INFORMATION_SCHEMA General Table Reference
26.3.2 The INFORMATION_SCHEMA ADMINISTRABLE_ROLE_AUTHORIZATIONS Table
26.3.3 The INFORMATION_SCHEMA APPLICABLE_ROLES Table
26.3.4 The INFORMATION_SCHEMA CHARACTER_SETS Table
26.3.5 The INFORMATION_SCHEMA CHECK_CONSTRAINTS Table
26.3.6 The INFORMATION_SCHEMA COLLATIONS Table
26.3.7 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
26.3.8 The INFORMATION_SCHEMA COLUMNS Table
26.3.9 The INFORMATION_SCHEMA COLUMNS_EXTENSIONS Table
26.3.10 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
26.3.11 The INFORMATION_SCHEMA COLUMN_STATISTICS Table
26.3.12 The INFORMATION_SCHEMA ENABLED_ROLES Table
26.3.13 The INFORMATION_SCHEMA ENGINES Table
26.3.14 The INFORMATION_SCHEMA EVENTS Table
26.3.15 The INFORMATION_SCHEMA FILES Table
26.3.16 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
26.3.17 The INFORMATION_SCHEMA KEYWORDS Table
26.3.18 The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table
26.3.19 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table
26.3.20 The INFORMATION_SCHEMA PARAMETERS Table
26.3.21 The INFORMATION_SCHEMA PARTITIONS Table
26.3.22 The INFORMATION_SCHEMA PLUGINS Table
26.3.23 The INFORMATION_SCHEMA PROCESSLIST Table
26.3.24 The INFORMATION_SCHEMA PROFILING Table
26.3.25 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
26.3.26 The INFORMATION_SCHEMA RESOURCE_GROUPS Table
26.3.27 The INFORMATION_SCHEMA ROLE_COLUMN_GRANTS Table
26.3.28 The INFORMATION_SCHEMA ROLE_ROUTINE_GRANTS Table
26.3.29 The INFORMATION_SCHEMA ROLE_TABLE_GRANTS Table
26.3.30 The INFORMATION_SCHEMA ROUTINES Table
26.3.31 The INFORMATION_SCHEMA SCHEMATA Table
26.3.32 The INFORMATION_SCHEMA SCHEMATA_EXTENSIONS Table
26.3.33 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
26.3.34 The INFORMATION_SCHEMA STATISTICS Table
26.3.35 The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table
26.3.36 The INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS Table
26.3.37 The INFORMATION_SCHEMA ST_UNITS_OF_MEASURE Table
26.3.38 The INFORMATION_SCHEMA TABLES Table
26.3.39 The INFORMATION_SCHEMA TABLES_EXTENSIONS Table
26.3.40 The INFORMATION_SCHEMA TABLESPACES Table
26.3.41 The INFORMATION_SCHEMA TABLESPACES_EXTENSIONS Table
26.3.42 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
26.3.43 The INFORMATION_SCHEMA TABLE_CONSTRAINTS_EXTENSIONS Table
26.3.44 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
26.3.45 The INFORMATION_SCHEMA TRIGGERS Table
26.3.46 The INFORMATION_SCHEMA USER_ATTRIBUTES Table
26.3.47 The INFORMATION_SCHEMA USER_PRIVILEGES Table
26.3.48 The INFORMATION_SCHEMA VIEWS Table
26.3.49 The INFORMATION_SCHEMA VIEW_ROUTINE_USAGE Table
26.3.50 The INFORMATION_SCHEMA VIEW_TABLE_USAGE Table
26.4 INFORMATION_SCHEMA InnoDB Tables
26.4.1 INFORMATION_SCHEMA InnoDB Table Reference
26.4.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table
26.4.3 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table
26.4.4 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table
26.4.5 The INFORMATION_SCHEMA INNODB_CACHED_INDEXES Table
26.4.6 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables
26.4.7 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables
26.4.8 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables
26.4.9 The INFORMATION_SCHEMA INNODB_COLUMNS Table
26.4.10 The INFORMATION_SCHEMA INNODB_DATAFILES Table
26.4.11 The INFORMATION_SCHEMA INNODB_FIELDS Table
26.4.12 The INFORMATION_SCHEMA INNODB_FOREIGN Table
26.4.13 The INFORMATION_SCHEMA INNODB_FOREIGN_COLS Table
26.4.14 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table
26.4.15 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table
26.4.16 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table
26.4.17 The INFORMATION_SCHEMA INNODB_FT_DELETED Table
26.4.18 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table
26.4.19 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table
26.4.20 The INFORMATION_SCHEMA INNODB_INDEXES Table
26.4.21 The INFORMATION_SCHEMA INNODB_METRICS Table
26.4.22 The INFORMATION_SCHEMA INNODB_SESSION_TEMP_TABLESPACES Table
26.4.23 The INFORMATION_SCHEMA INNODB_TABLES Table
26.4.24 The INFORMATION_SCHEMA INNODB_TABLESPACES Table
26.4.25 The INFORMATION_SCHEMA INNODB_TABLESPACES_BRIEF Table
26.4.26 The INFORMATION_SCHEMA INNODB_TABLESTATS View
26.4.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table
26.4.28 The INFORMATION_SCHEMA INNODB_TRX Table
26.4.29 The INFORMATION_SCHEMA INNODB_VIRTUAL Table
26.5 INFORMATION_SCHEMA Thread Pool Tables
26.5.1 INFORMATION_SCHEMA Thread Pool Table Reference
26.5.2 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table
26.5.3 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table
26.5.4 The INFORMATION_SCHEMA TP_THREAD_STATE Table
26.6 INFORMATION_SCHEMA Connection-Control Tables
26.6.1 INFORMATION_SCHEMA Connection-Control Table Reference
26.6.2 The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Table
26.7 INFORMATION_SCHEMA MySQL Enterprise Firewall Tables
26.7.1 INFORMATION_SCHEMA Firewall Table Reference
26.7.2 The INFORMATION_SCHEMA MYSQL_FIREWALL_USERS Table
26.7.3 The INFORMATION_SCHEMA MYSQL_FIREWALL_WHITELIST Table
26.8 Extensions to SHOW Statements
Chapter 27 MySQL Performance Schema
27.1 Performance Schema Quick Start
27.2 Performance Schema Build Configuration
27.3 Performance Schema Startup Configuration
27.4 Performance Schema Runtime Configuration
27.4.1 Performance Schema Event Timing
27.4.2 Performance Schema Event Filtering
27.4.3 Event Pre-Filtering
27.4.4 Pre-Filtering by Instrument
27.4.5 Pre-Filtering by Object
27.4.6 Pre-Filtering by Thread
27.4.7 Pre-Filtering by Consumer
27.4.8 Example Consumer Configurations
27.4.9 Naming Instruments or Consumers for Filtering Operations
27.4.10 Determining What Is Instrumented
27.5 Performance Schema Queries
27.6 Performance Schema Instrument Naming Conventions
27.7 Performance Schema Status Monitoring
27.8 Performance Schema Atom and Molecule Events
27.9 Performance Schema Tables for Current and Historical Events
27.10 Performance Schema Statement Digests and Sampling
27.11 Performance Schema General Table Characteristics
27.12 Performance Schema Table Descriptions
27.12.1 Performance Schema Table Reference
27.12.2 Performance Schema Setup Tables
27.12.2.1 The setup_actors Table
27.12.2.2 The setup_consumers Table
27.12.2.3 The setup_instruments Table
27.12.2.4 The setup_objects Table
27.12.2.5 The setup_threads Table
27.12.3 Performance Schema Instance Tables
27.12.3.1 The cond_instances Table
27.12.3.2 The file_instances Table
27.12.3.3 The mutex_instances Table
27.12.3.4 The rwlock_instances Table
27.12.3.5 The socket_instances Table
27.12.4 Performance Schema Wait Event Tables
27.12.4.1 The events_waits_current Table
27.12.4.2 The events_waits_history Table
27.12.4.3 The events_waits_history_long Table
27.12.5 Performance Schema Stage Event Tables
27.12.5.1 The events_stages_current Table
27.12.5.2 The events_stages_history Table
27.12.5.3 The events_stages_history_long Table
27.12.6 Performance Schema Statement Event Tables
27.12.6.1 The events_statements_current Table
27.12.6.2 The events_statements_history Table
27.12.6.3 The events_statements_history_long Table
27.12.6.4 The prepared_statements_instances Table
27.12.7 Performance Schema Transaction Tables
27.12.7.1 The events_transactions_current Table
27.12.7.2 The events_transactions_history Table
27.12.7.3 The events_transactions_history_long Table
27.12.8 Performance Schema Connection Tables
27.12.8.1 The accounts Table
27.12.8.2 The hosts Table
27.12.8.3 The users Table
27.12.9 Performance Schema Connection Attribute Tables
27.12.9.1 The session_account_connect_attrs Table
27.12.9.2 The session_connect_attrs Table
27.12.10 Performance Schema User-Defined Variable Tables
27.12.11 Performance Schema Replication Tables
27.12.11.1 The replication_connection_configuration Table
27.12.11.2 The replication_connection_status Table
27.12.11.3 The replication_asynchronous_connection_failover Table
27.12.11.4 The replication_asynchronous_connection_failover_managed Table
27.12.11.5 The replication_applier_configuration Table
27.12.11.6 The replication_applier_status Table
27.12.11.7 The replication_applier_status_by_coordinator Table
27.12.11.8 The replication_applier_status_by_worker Table
27.12.11.9 The replication_applier_global_filters Table
27.12.11.10 The replication_applier_filters Table
27.12.11.11 The replication_group_members Table
27.12.11.12 The replication_group_member_stats Table
27.12.11.13 The replication_group_member_actions Table
27.12.11.14 The replication_group_configuration_version Table
27.12.11.15 The replication_group_communication_information Table
27.12.11.16 The binary_log_transaction_compression_stats Table
27.12.12 Performance Schema NDB Cluster Tables
27.12.12.1 The ndb_sync_pending_objects Table
27.12.12.2 The ndb_sync_excluded_objects Table
27.12.13 Performance Schema Lock Tables
27.12.13.1 The data_locks Table
27.12.13.2 The data_lock_waits Table
27.12.13.3 The metadata_locks Table
27.12.13.4 The table_handles Table
27.12.14 Performance Schema System Variable Tables
27.12.14.1 Performance Schema persisted_variables Table
27.12.14.2 Performance Schema variables_info Table
27.12.15 Performance Schema Status Variable Tables
27.12.16 Performance Schema Thread Pool Tables
27.12.16.1 The tp_thread_group_state Table
27.12.16.2 The tp_thread_group_stats Table
27.12.16.3 The tp_thread_state Table
27.12.17 Performance Schema Firewall Tables
27.12.17.1 The firewall_groups Table
27.12.17.2 The firewall_group_allowlist Table
27.12.17.3 The firewall_membership Table
27.12.18 Performance Schema Keyring Tables
27.12.18.1 The keyring_component_status Table
27.12.18.2 The keyring_keys table
27.12.19 Performance Schema Clone Tables
27.12.19.1 The clone_status Table
27.12.19.2 The clone_progress Table
27.12.20 Performance Schema Summary Tables
27.12.20.1 Wait Event Summary Tables
27.12.20.2 Stage Summary Tables
27.12.20.3 Statement Summary Tables
27.12.20.4 Statement Histogram Summary Tables
27.12.20.5 Transaction Summary Tables
27.12.20.6 Object Wait Summary Table
27.12.20.7 File I/O Summary Tables
27.12.20.8 Table I/O and Lock Wait Summary Tables
The table_io_waits_summary_by_table Table
The table_io_waits_summary_by_index_usage Table
The table_lock_waits_summary_by_table Table
27.12.20.9 Socket Summary Tables
27.12.20.10 Memory Summary Tables
27.12.20.11 Error Summary Tables
27.12.20.12 Status Variable Summary Tables
27.12.21 Performance Schema Miscellaneous Tables
27.12.21.1 The component_scheduler_tasks Table
27.12.21.2 The error_log Table
27.12.21.3 The host_cache Table
27.12.21.4 The innodb_redo_log_files Table
27.12.21.5 The log_status Table
27.12.21.6 The performance_timers Table
27.12.21.7 The processlist Table
27.12.21.8 The threads Table
27.12.21.9 The tls_channel_status Table
27.12.21.10 The user_defined_functions Table
27.13 Performance Schema Option and Variable Reference
27.14 Performance Schema Command Options
27.15 Performance Schema System Variables
27.16 Performance Schema Status Variables
27.17 The Performance Schema Memory-Allocation Model
27.18 Performance Schema and Plugins
27.19 Using the Performance Schema to Diagnose Problems
27.19.1 Query Profiling Using Performance Schema
27.19.2 Obtaining Parent Event Information
27.20 Restrictions on Performance Schema
Chapter 28 MySQL sys Schema
28.1 Prerequisites for Using the sys Schema
28.2 Using the sys Schema
28.3 sys Schema Progress Reporting
28.4 sys Schema Object Reference
28.4.1 sys Schema Object Index
28.4.2 sys Schema Tables and Triggers
28.4.2.1 The sys_config Table
28.4.2.2 The sys_config_insert_set_user Trigger
28.4.2.3 The sys_config_update_set_user Trigger
28.4.3 sys Schema Views
28.4.3.1 The host_summary and x$host_summary Views
28.4.3.2 The host_summary_by_file_io and x$host_summary_by_file_io Views
28.4.3.3 The host_summary_by_file_io_type and x$host_summary_by_file_io_type Views
28.4.3.4 The host_summary_by_stages and x$host_summary_by_stages Views
28.4.3.5 The host_summary_by_statement_latency and x$host_summary_by_statement_latency Views
28.4.3.6 The host_summary_by_statement_type and x$host_summary_by_statement_type Views
28.4.3.7 The innodb_buffer_stats_by_schema and x$innodb_buffer_stats_by_schema Views
28.4.3.8 The innodb_buffer_stats_by_table and x$innodb_buffer_stats_by_table Views
28.4.3.9 The innodb_lock_waits and x$innodb_lock_waits Views
28.4.3.10 The io_by_thread_by_latency and x$io_by_thread_by_latency Views
28.4.3.11 The io_global_by_file_by_bytes and x$io_global_by_file_by_bytes Views
28.4.3.12 The io_global_by_file_by_latency and x$io_global_by_file_by_latency Views
28.4.3.13 The io_global_by_wait_by_bytes and x$io_global_by_wait_by_bytes Views
28.4.3.14 The io_global_by_wait_by_latency and x$io_global_by_wait_by_latency Views
28.4.3.15 The latest_file_io and x$latest_file_io Views
28.4.3.16 The memory_by_host_by_current_bytes and x$memory_by_host_by_current_bytes Views
28.4.3.17 The memory_by_thread_by_current_bytes and x$memory_by_thread_by_current_bytes Views
28.4.3.18 The memory_by_user_by_current_bytes and x$memory_by_user_by_current_bytes Views
28.4.3.19 The memory_global_by_current_bytes and x$memory_global_by_current_bytes Views
28.4.3.20 The memory_global_total and x$memory_global_total Views
28.4.3.21 The metrics View
28.4.3.22 The processlist and x$processlist Views
28.4.3.23 The ps_check_lost_instrumentation View
28.4.3.24 The schema_auto_increment_columns View
28.4.3.25 The schema_index_statistics and x$schema_index_statistics Views
28.4.3.26 The schema_object_overview View
28.4.3.27 The schema_redundant_indexes and x$schema_flattened_keys Views
28.4.3.28 The schema_table_lock_waits and x$schema_table_lock_waits Views
28.4.3.29 The schema_table_statistics and x$schema_table_statistics Views
28.4.3.30 The schema_table_statistics_with_buffer and x$schema_table_statistics_with_buffer Views
28.4.3.31 The schema_tables_with_full_table_scans and x$schema_tables_with_full_table_scans Views
28.4.3.32 The schema_unused_indexes View
28.4.3.33 The session and x$session Views
28.4.3.34 The session_ssl_status View
28.4.3.35 The statement_analysis and x$statement_analysis Views
28.4.3.36 The statements_with_errors_or_warnings and x$statements_with_errors_or_warnings Views
28.4.3.37 The statements_with_full_table_scans and x$statements_with_full_table_scans Views
28.4.3.38 The statements_with_runtimes_in_95th_percentile and x$statements_with_runtimes_in_95th_percentile Views
28.4.3.39 The statements_with_sorting and x$statements_with_sorting Views
28.4.3.40 The statements_with_temp_tables and x$statements_with_temp_tables Views
28.4.3.41 The user_summary and x$user_summary Views
28.4.3.42 The user_summary_by_file_io and x$user_summary_by_file_io Views
28.4.3.43 The user_summary_by_file_io_type and x$user_summary_by_file_io_type Views
28.4.3.44 The user_summary_by_stages and x$user_summary_by_stages Views
28.4.3.45 The user_summary_by_statement_latency and x$user_summary_by_statement_latency Views
28.4.3.46 The user_summary_by_statement_type and x$user_summary_by_statement_type Views
28.4.3.47 The version View
28.4.3.48 The wait_classes_global_by_avg_latency and x$wait_classes_global_by_avg_latency Views
28.4.3.49 The wait_classes_global_by_latency and x$wait_classes_global_by_latency Views
28.4.3.50 The waits_by_host_by_latency and x$waits_by_host_by_latency Views
28.4.3.51 The waits_by_user_by_latency and x$waits_by_user_by_latency Views
28.4.3.52 The waits_global_by_latency and x$waits_global_by_latency Views
28.4.4 sys Schema Stored Procedures
28.4.4.1 The create_synonym_db() Procedure
28.4.4.2 The diagnostics() Procedure
28.4.4.3 The execute_prepared_stmt() Procedure
28.4.4.4 The ps_setup_disable_background_threads() Procedure
28.4.4.5 The ps_setup_disable_consumer() Procedure
28.4.4.6 The ps_setup_disable_instrument() Procedure
28.4.4.7 The ps_setup_disable_thread() Procedure
28.4.4.8 The ps_setup_enable_background_threads() Procedure
28.4.4.9 The ps_setup_enable_consumer() Procedure
28.4.4.10 The ps_setup_enable_instrument() Procedure
28.4.4.11 The ps_setup_enable_thread() Procedure
28.4.4.12 The ps_setup_reload_saved() Procedure
28.4.4.13 The ps_setup_reset_to_default() Procedure
28.4.4.14 The ps_setup_save() Procedure
28.4.4.15 The ps_setup_show_disabled() Procedure
28.4.4.16 The ps_setup_show_disabled_consumers() Procedure
28.4.4.17 The ps_setup_show_disabled_instruments() Procedure
28.4.4.18 The ps_setup_show_enabled() Procedure
28.4.4.19 The ps_setup_show_enabled_consumers() Procedure
28.4.4.20 The ps_setup_show_enabled_instruments() Procedure
28.4.4.21 The ps_statement_avg_latency_histogram() Procedure
28.4.4.22 The ps_trace_statement_digest() Procedure
28.4.4.23 The ps_trace_thread() Procedure
28.4.4.24 The ps_truncate_all_tables() Procedure
28.4.4.25 The statement_performance_analyzer() Procedure
28.4.4.26 The table_exists() Procedure
28.4.5 sys Schema Stored Functions
28.4.5.1 The extract_schema_from_file_name() Function
28.4.5.2 The extract_table_from_file_name() Function
28.4.5.3 The format_bytes() Function
28.4.5.4 The format_path() Function
28.4.5.5 The format_statement() Function
28.4.5.6 The format_time() Function
28.4.5.7 The list_add() Function
28.4.5.8 The list_drop() Function
28.4.5.9 The ps_is_account_enabled() Function
28.4.5.10 The ps_is_consumer_enabled() Function
28.4.5.11 The ps_is_instrument_default_enabled() Function
28.4.5.12 The ps_is_instrument_default_timed() Function
28.4.5.13 The ps_is_thread_instrumented() Function
28.4.5.14 The ps_thread_account() Function
28.4.5.15 The ps_thread_id() Function
28.4.5.16 The ps_thread_stack() Function
28.4.5.17 The ps_thread_trx_info() Function
28.4.5.18 The quote_identifier() Function
28.4.5.19 The sys_get_config() Function
28.4.5.20 The version_major() Function
28.4.5.21 The version_minor() Function
28.4.5.22 The version_patch() Function
Chapter 29 Connectors and APIs
29.1 MySQL Connector/C++
29.2 MySQL Connector/J
29.3 MySQL Connector/NET
29.4 MySQL Connector/ODBC
29.5 MySQL Connector/Python
29.6 MySQL Connector/Node.js
29.7 MySQL C API
29.8 MySQL PHP API
29.9 MySQL Perl API
29.10 MySQL Python API
29.11 MySQL Ruby APIs
29.11.1 The MySQL/Ruby API
29.11.2 The Ruby/MySQL API
29.12 MySQL Tcl API
29.13 MySQL Eiffel Wrapper
Chapter 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
Chapter 31 MySQL Workbench
Chapter 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
Chapter 33 Telemetry
33.1 OpenTelemetry TRACE
33.1.1 Installing TRACE Support
33.1.2 Configuring TRACE Telemetry
33.1.3 TRACE Format
Appendix A MySQL 8.1 Frequently Asked Questions
A.1 MySQL 8.1 FAQ: General
A.2 MySQL 8.1 FAQ: Storage Engines
A.3 MySQL 8.1 FAQ: Server SQL Mode
A.4 MySQL 8.1 FAQ: Stored Procedures and Functions
A.5 MySQL 8.1 FAQ: Triggers
A.6 MySQL 8.1 FAQ: Views
A.7 MySQL 8.1 FAQ: INFORMATION_SCHEMA
A.8 MySQL 8.1 FAQ: Migration
A.9 MySQL 8.1 FAQ: Security
A.10 MySQL 8.1 FAQ: NDB Cluster
A.11 MySQL 8.1 FAQ: MySQL Chinese, Japanese, and Korean Character Sets
A.12 MySQL 8.1 FAQ: Connectors & APIs
A.13 MySQL 8.1 FAQ: C API, libmysql
A.14 MySQL 8.1 FAQ: Replication
A.15 MySQL 8.1 FAQ: MySQL Enterprise Thread Pool
A.16 MySQL 8.1 FAQ: InnoDB Change Buffer
A.17 MySQL 8.1 FAQ: InnoDB Data-at-Rest Encryption
A.18 MySQL 8.1 FAQ: Virtualization Support
Appendix 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.2.1 Access denied
B.3.2.2 Can't connect to [local] MySQL server
Connection to MySQL Server Failing on Windows
B.3.2.3 Lost connection to MySQL server
B.3.2.4 Password Fails When Entered Interactively
B.3.2.5 Too many connections
B.3.2.6 Out of memory
B.3.2.7 MySQL server has gone away
B.3.2.8 Packet Too Large
B.3.2.9 Communication Errors and Aborted Connections
B.3.2.10 The table is full
B.3.2.11 Can't create/write to file
B.3.2.12 Commands out of sync
B.3.2.13 Ignoring user
B.3.2.14 Table 'tbl_name' doesn't exist
B.3.2.15 Can't initialize character set
B.3.2.16 File Not Found and Similar Errors
B.3.2.17 Table-Corruption Issues
B.3.3 Administration-Related Issues
B.3.3.1 Problems with File Permissions
B.3.3.2 How to Reset the Root Password
Resetting the Root Password: Windows Systems
Resetting the Root Password: Unix and Unix-Like Systems
Resetting the Root Password: Generic Instructions
B.3.3.3 What to Do If MySQL Keeps Crashing
B.3.3.4 How MySQL Handles a Full Disk
B.3.3.5 Where MySQL Stores Temporary Files
B.3.3.6 How to Protect or Change the MySQL Unix Socket File
B.3.3.7 Time Zone Problems
B.3.4 Query-Related Issues
B.3.4.1 Case Sensitivity in String Searches
B.3.4.2 Problems Using DATE Columns
B.3.4.3 Problems with NULL Values
B.3.4.4 Problems with Column Aliases
B.3.4.5 Rollback Failure for Nontransactional Tables
B.3.4.6 Deleting Rows from Related Tables
B.3.4.7 Solving Problems with No Matching Rows
B.3.4.8 Problems with Floating-Point Values
B.3.5 Optimizer-Related Issues
B.3.6 Table Definition-Related Issues
B.3.6.1 Problems with ALTER TABLE
B.3.6.2 TEMPORARY Table Problems
B.3.7 Known Issues in MySQL
Appendix 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
MySQL 8.1 Reference Manual
Including MySQL NDB Cluster 8.1
Abstract
This is the MySQL Reference Manual. It documents MySQL 8.1 (8.1.0), as well as NDB Cluster 8.1 (8.1.0-
ndb-8.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 8.1 Release Notes.
MySQL 8.1 features.
features may not be included in the edition of MySQL 8.1 licensed to you. If you have any questions about the
features included in your edition of MySQL 8.1, refer to your MySQL 8.1 license agreement or contact your Oracle
sales representative.
This manual describes features that are not included in every edition of MySQL 8.1; such
For notes detailing the changes in each release, see the MySQL 8.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: 2023-09-26 (revision: 76746)
Table of Contents
Preface and Legal Notices ....................................................................................................... xxvii
1 General Information ................................................................................................................... 1
1.1 About This Manual .......................................................................................................... 2
1.2 Overview of the MySQL Database Management System ................................................... 4
1.2.1 What is MySQL? .................................................................................................. 4
1.2.2 The Main Features of MySQL ............................................................................... 5
1.2.3 History of MySQL ................................................................................................. 8
1.3 What Is New in MySQL 8.1 ............................................................................................. 8
1.4 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL
8.1 ...................................................................................................................................... 12
1.5 How to Report Bugs or Problems .................................................................................. 15
1.6 MySQL Standards Compliance ...................................................................................... 19
1.6.1 MySQL Extensions to Standard SQL ................................................................... 20
1.6.2 MySQL Differences from Standard SQL .............................................................. 23
1.6.3 How MySQL Deals with Constraints .................................................................... 26
1.7 Credits .......................................................................................................................... 28
1.7.1 Contributors to MySQL ....................................................................................... 28
1.7.2 Documenters and translators .............................................................................. 32
1.7.3 Packages that support MySQL ............................................................................ 34
1.7.4 Tools that were used to create MySQL ............................................................... 34
1.7.5 Supporters of MySQL ......................................................................................... 35
2 Installing and Upgrading MySQL .............................................................................................. 37
2.1 General Installation Guidance ........................................................................................ 40
2.1.1 Supported Platforms ........................................................................................... 40
2.1.2 Which MySQL Version and Distribution to Install .................................................. 40
2.1.3 How to Get MySQL ............................................................................................ 41
2.1.4 Verifying Package Integrity Using MD5 Checksums or GnuPG .............................. 41
2.1.5 Installation Layouts ............................................................................................. 55
2.1.6 Compiler-Specific Build Characteristics ................................................................ 55
2.2 Installing MySQL on Unix/Linux Using Generic Binaries .................................................. 56
2.3 Installing MySQL on Microsoft Windows ......................................................................... 59
2.3.1 Choosing an Installation Package ....................................................................... 61
2.3.2 Configuration: Using MySQL Configurator ............................................................ 62
2.3.3 Configuration: Manually ...................................................................................... 67
2.3.4 Troubleshooting a Microsoft Windows MySQL Server Installation .......................... 75
2.3.5 Windows Postinstallation Procedures .................................................................. 76
2.3.6 Windows Platform Restrictions ............................................................................ 78
2.4 Installing MySQL on macOS .......................................................................................... 80
2.4.1 General Notes on Installing MySQL on macOS .................................................... 80
2.4.2 Installing MySQL on macOS Using Native Packages ............................................ 81
2.4.3 Installing and Using the MySQL Launch Daemon ................................................. 84
2.4.4 Installing and Using the MySQL Preference Pane ................................................ 87
2.5 Installing MySQL on Linux ............................................................................................. 92
2.5.1 Installing MySQL on Linux Using the MySQL Yum Repository ............................... 93
2.5.2 Installing MySQL on Linux Using the MySQL APT Repository ............................... 98
2.5.3 Installing MySQL on Linux Using the MySQL SLES Repository ............................. 98
2.5.4 Installing MySQL on Linux Using RPM Packages from Oracle ............................... 98
2.5.5 Installing MySQL on Linux Using Debian Packages from Oracle .......................... 102
2.5.6 Deploying MySQL on Linux with Docker Containers ........................................... 104
2.5.7 Installing MySQL on Linux from the Native Software Repositories ........................ 116
2.5.8 Installing MySQL on Linux with Juju .................................................................. 118
2.5.9 Managing MySQL Server with systemd ............................................................. 118
2.6 Installing MySQL Using Unbreakable Linux Network (ULN) ........................................... 123
2.7 Installing MySQL on Solaris ......................................................................................... 124
2.7.1 Installing MySQL on Solaris Using a Solaris PKG .............................................. 124
iii
MySQL 8.1 Reference Manual
2.8 Installing MySQL from Source ..................................................................................... 125
2.8.1 Source Installation Methods .............................................................................. 126
2.8.2 Source Installation Prerequisites ....................................................................... 126
2.8.3 MySQL Layout for Source Installation ................................................................ 128
2.8.4 Installing MySQL Using a Standard Source Distribution ...................................... 128
2.8.5 Installing MySQL Using a Development Source Tree .......................................... 132
2.8.6 Configuring SSL Library Support ....................................................................... 133
2.8.7 MySQL Source-Configuration Options ............................................................... 134
2.8.8 Dealing with Problems Compiling MySQL .......................................................... 160
2.8.9 MySQL Configuration and Third-Party Tools ...................................................... 161
2.8.10 Generating MySQL Doxygen Documentation Content ....................................... 161
2.9 Postinstallation Setup and Testing ............................................................................... 162
2.9.1 Initializing the Data Directory ............................................................................ 163
2.9.2 Starting the Server ........................................................................................... 168
2.9.3 Testing the Server ............................................................................................ 171
2.9.4 Securing the Initial MySQL Account .................................................................. 173
2.9.5 Starting and Stopping MySQL Automatically ...................................................... 174
2.10 Upgrading MySQL ..................................................................................................... 175
2.10.1 Before You Begin ........................................................................................... 176
2.10.2 Upgrade Paths ............................................................................................... 177
2.10.3 What the MySQL Upgrade Process Upgrades .................................................. 177
2.10.4 Changes in MySQL 8.1 ................................................................................... 179
2.10.5 Preparing Your Installation for Upgrade ........................................................... 179
2.10.6 Upgrading MySQL Binary or Package-based Installations on Unix/Linux ............ 182
2.10.7 Upgrading MySQL with the MySQL Yum Repository ......................................... 186
2.10.8 Upgrading MySQL with the MySQL APT Repository ......................................... 188
2.10.9 Upgrading MySQL with the MySQL SLES Repository ....................................... 188
2.10.10 Upgrading MySQL on Windows ..................................................................... 188
2.10.11 Upgrading a Docker Installation of MySQL ..................................................... 189
2.10.12 Upgrade Troubleshooting .............................................................................. 189
2.10.13 Rebuilding or Repairing Tables or Indexes ..................................................... 190
2.10.14 Copying MySQL Databases to Another Machine ............................................ 191
2.11 Downgrading MySQL ................................................................................................. 192
2.12 Perl Installation Notes ................................................................................................ 192
2.12.1 Installing Perl on Unix ..................................................................................... 192
2.12.2 Installing ActiveState Perl on Windows ............................................................ 193
2.12.3 Problems Using the Perl DBI/DBD Interface ..................................................... 194
3 Tutorial .................................................................................................................................. 195
3.1 Connecting to and Disconnecting from the Server ......................................................... 195
3.2 Entering Queries ......................................................................................................... 196
3.3 Creating and Using a Database ................................................................................... 199
3.3.1 Creating and Selecting a Database ................................................................... 200
3.3.2 Creating a Table .............................................................................................. 201
3.3.3 Loading Data into a Table ................................................................................ 202
3.3.4 Retrieving Information from a Table ................................................................... 203
3.4 Getting Information About Databases and Tables ......................................................... 216
3.5 Using mysql in Batch Mode ......................................................................................... 217
3.6 Examples of Common Queries .................................................................................... 218
3.6.1 The Maximum Value for a Column .................................................................... 219
3.6.2 The Row Holding the Maximum of a Certain Column .......................................... 219
3.6.3 Maximum of Column per Group ........................................................................ 219
3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column ....................... 220
3.6.5 Using User-Defined Variables ........................................................................... 221
3.6.6 Using Foreign Keys .......................................................................................... 221
3.6.7 Searching on Two Keys .................................................................................... 223
3.6.8 Calculating Visits Per Day ................................................................................. 223
3.6.9 Using AUTO_INCREMENT ............................................................................... 224
3.7 Using MySQL with Apache .......................................................................................... 226
iv
MySQL 8.1 Reference Manual
4 MySQL Programs .................................................................................................................. 229
4.1 Overview of MySQL Programs ..................................................................................... 230
4.2 Using MySQL Programs .............................................................................................. 233
4.2.1 Invoking MySQL Programs ............................................................................... 233
4.2.2 Specifying Program Options .............................................................................. 234
4.2.3 Command Options for Connecting to the Server ................................................ 247
4.2.4 Connecting to the MySQL Server Using Command Options ................................ 256
4.2.5 Connecting to the Server Using URI-Like Strings or Key-Value Pairs ................... 259
4.2.6 Connecting to the Server Using DNS SRV Records ........................................... 266
4.2.7 Connection Transport Protocols ........................................................................ 267
4.2.8 Connection Compression Control ...................................................................... 269
4.2.9 Setting Environment Variables .......................................................................... 272
4.3 Server and Server-Startup Programs ........................................................................... 273
4.3.1 mysqld — The MySQL Server .......................................................................... 273
4.3.2 mysqld_safe — MySQL Server Startup Script .................................................... 274
4.3.3 mysql.server — MySQL Server Startup Script .................................................... 280
4.3.4 mysqld_multi — Manage Multiple MySQL Servers .............................................. 282
4.4 Installation-Related Programs ...................................................................................... 285
4.4.1 comp_err — Compile MySQL Error Message File .............................................. 285
4.4.2 mysql_secure_installation — Improve MySQL Installation Security ...................... 287
4.4.3 mysql_ssl_rsa_setup — Create SSL/RSA Files .................................................. 290
4.4.4 mysql_tzinfo_to_sql — Load the Time Zone Tables ............................................ 293
4.4.5 mysql_upgrade — Deprecated; Performs No Tasks and Exits with Status 0 ......... 294
4.5 Client Programs .......................................................................................................... 294
4.5.1 mysql — The MySQL Command-Line Client ...................................................... 294
4.5.2 mysqladmin — A MySQL Server Administration Program ................................... 328
4.5.3 mysqlcheck — A Table Maintenance Program ................................................... 339
4.5.4 mysqldump — A Database Backup Program ..................................................... 349
4.5.5 mysqlimport — A Data Import Program ............................................................. 376
4.5.6 mysqlpump — A Database Backup Program ..................................................... 385
4.5.7 mysqlshow — Display Database, Table, and Column Information ........................ 403
4.5.8 mysqlslap — A Load Emulation Client ............................................................... 411
4.6 Administrative and Utility Programs .............................................................................. 421
4.6.1 ibd2sdi — InnoDB Tablespace SDI Extraction Utility ........................................... 421
4.6.2 innochecksum — Offline InnoDB File Checksum Utility ....................................... 425
4.6.3 myisam_ftdump — Display Full-Text Index information ....................................... 430
4.6.4 myisamchk — MyISAM Table-Maintenance Utility .............................................. 431
4.6.5 myisamlog — Display MyISAM Log File Contents .............................................. 447
4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables .................... 448
4.6.7 mysql_config_editor — MySQL Configuration Utility ........................................... 454
4.6.8 mysql_migrate_keyring — Keyring Key Migration Utility ...................................... 460
4.6.9 mysqlbinlog — Utility for Processing Binary Log Files ......................................... 466
4.6.10 mysqldumpslow — Summarize Slow Query Log Files ....................................... 490
4.7 Program Development Utilities ..................................................................................... 492
4.7.1 mysql_config — Display Options for Compiling Clients ....................................... 492
4.7.2 my_print_defaults — Display Options from Option Files ...................................... 494
4.8 Miscellaneous Programs .............................................................................................. 495
4.8.1 lz4_decompress — Decompress mysqlpump LZ4-Compressed Output ................ 495
4.8.2 perror — Display MySQL Error Message Information .......................................... 495
4.8.3 zlib_decompress — Decompress mysqlpump ZLIB-Compressed Output .............. 496
4.9 Environment Variables ................................................................................................. 497
4.10 Unix Signal Handling in MySQL ................................................................................. 499
5 MySQL Server Administration ................................................................................................. 501
5.1 The MySQL Server ..................................................................................................... 502
5.1.1 Configuring the Server ...................................................................................... 503
5.1.2 Server Configuration Defaults ........................................................................... 504
5.1.3 Server Configuration Validation ......................................................................... 504
5.1.4 Server Option, System Variable, and Status Variable Reference ......................... 505
v
MySQL 8.1 Reference Manual
5.1.5 Server System Variable Reference .................................................................... 553
5.1.6 Server Status Variable Reference ..................................................................... 578
5.1.7 Server Command Options ................................................................................. 596
5.1.8 Server System Variables .................................................................................. 623
5.1.9 Using System Variables .................................................................................... 780
5.1.10 Server Status Variables .................................................................................. 811
5.1.11 Server SQL Modes ......................................................................................... 836
5.1.12 Connection Management ................................................................................ 847
5.1.13 IPv6 Support .................................................................................................. 855
5.1.14 Network Namespace Support .......................................................................... 859
5.1.15 MySQL Server Time Zone Support .................................................................. 864
5.1.16 Resource Groups ........................................................................................... 869
5.1.17 Server-Side Help Support ............................................................................... 873
5.1.18 Server Tracking of Client Session State ........................................................... 874
5.1.19 The Server Shutdown Process ........................................................................ 877
5.2 The MySQL Data Directory .......................................................................................... 878
5.3 The mysql System Schema ......................................................................................... 879
5.4 MySQL Server Logs .................................................................................................... 884
5.4.1 Selecting General Query Log and Slow Query Log Output Destinations ............... 885
5.4.2 The Error Log .................................................................................................. 887
5.4.3 The General Query Log .................................................................................... 908
5.4.4 The Binary Log ................................................................................................ 910
5.4.5 The Slow Query Log ........................................................................................ 926
5.4.6 Server Log Maintenance ................................................................................... 929
5.5 MySQL Components ................................................................................................... 931
5.5.1 Installing and Uninstalling Components .............................................................. 932
5.5.2 Obtaining Component Information ..................................................................... 932
5.5.3 Error Log Components ..................................................................................... 933
5.5.4 Query Attribute Components ............................................................................. 935
5.5.5 Scheduler Component ...................................................................................... 935
5.6 MySQL Server Plugins ................................................................................................ 936
5.6.1 Installing and Uninstalling Plugins ..................................................................... 937
5.6.2 Obtaining Server Plugin Information .................................................................. 941
5.6.3 MySQL Enterprise Thread Pool ......................................................................... 942
5.6.4 The Rewriter Query Rewrite Plugin ................................................................... 949
5.6.5 The ddl_rewriter Plugin ..................................................................................... 957
5.6.6 Version Tokens ................................................................................................ 960
5.6.7 The Clone Plugin ............................................................................................. 971
5.6.8 The Keyring Proxy Bridge Plugin ....................................................................... 995
5.6.9 MySQL Plugin Services .................................................................................... 995
5.7 MySQL Server Loadable Functions ............................................................................ 1003
5.7.1 Installing and Uninstalling Loadable Functions ................................................. 1004
5.7.2 Obtaining Information About Loadable Functions .............................................. 1005
5.8 Running Multiple MySQL Instances on One Machine .................................................. 1005
5.8.1 Setting Up Multiple Data Directories ................................................................ 1006
5.8.2 Running Multiple MySQL Instances on Windows .............................................. 1007
5.8.3 Running Multiple MySQL Instances on Unix ..................................................... 1010
5.8.4 Using Client Programs in a Multiple-Server Environment ................................... 1011
5.9 Debugging MySQL .................................................................................................... 1012
5.9.1 Debugging a MySQL Server ........................................................................... 1012
5.9.2 Debugging a MySQL Client ............................................................................. 1017
5.9.3 The LOCK_ORDER Tool ................................................................................ 1018
5.9.4 The DBUG Package ....................................................................................... 1023
6 Security ............................................................................................................................... 1027
6.1 General Security Issues ............................................................................................. 1028
6.1.1 Security Guidelines ......................................................................................... 1028
6.1.2 Keeping Passwords Secure ............................................................................ 1030
6.1.3 Making MySQL Secure Against Attackers ........................................................ 1033
vi
MySQL 8.1 Reference Manual
6.1.4 Security-Related mysqld Options and Variables ............................................... 1034
6.1.5 How to Run MySQL as a Normal User ............................................................ 1035
6.1.6 Security Considerations for LOAD DATA LOCAL ............................................. 1036
6.1.7 Client Programming Security Guidelines .......................................................... 1039
6.2 Access Control and Account Management .................................................................. 1041
6.2.1 Account User Names and Passwords .............................................................. 1042
6.2.2 Privileges Provided by MySQL ........................................................................ 1043
6.2.3 Grant Tables .................................................................................................. 1062
6.2.4 Specifying Account Names ............................................................................. 1072
6.2.5 Specifying Role Names ................................................................................... 1074
6.2.6 Access Control, Stage 1: Connection Verification ............................................. 1074
6.2.7 Access Control, Stage 2: Request Verification .................................................. 1078
6.2.8 Adding Accounts, Assigning Privileges, and Dropping Accounts ........................ 1079
6.2.9 Reserved Accounts ......................................................................................... 1083
6.2.10 Using Roles .................................................................................................. 1083
6.2.11 Account Categories ....................................................................................... 1090
6.2.12 Privilege Restriction Using Partial Revokes .................................................... 1093
6.2.13 When Privilege Changes Take Effect ............................................................. 1099
6.2.14 Assigning Account Passwords ....................................................................... 1100
6.2.15 Password Management ................................................................................. 1101
6.2.16 Server Handling of Expired Passwords .......................................................... 1112
6.2.17 Pluggable Authentication ............................................................................... 1114
6.2.18 Multifactor Authentication .............................................................................. 1119
6.2.19 Proxy Users ................................................................................................. 1123
6.2.20 Account Locking ........................................................................................... 1130
6.2.21 Setting Account Resource Limits ................................................................... 1131
6.2.22 Troubleshooting Problems Connecting to MySQL ........................................... 1133
6.2.23 SQL-Based Account Activity Auditing ............................................................. 1137
6.3 Using Encrypted Connections .................................................................................... 1139
6.3.1 Configuring MySQL to Use Encrypted Connections .......................................... 1140
6.3.2 Encrypted Connection TLS Protocols and Ciphers ............................................ 1148
6.3.3 Creating SSL and RSA Certificates and Keys .................................................. 1156
6.3.4 Connecting to MySQL Remotely from Windows with SSH ................................. 1165
6.3.5 Reusing SSL Sessions ................................................................................... 1166
6.4 Security Components and Plugins .............................................................................. 1168
6.4.1 Authentication Plugins ..................................................................................... 1169
6.4.2 The Connection-Control Plugins ...................................................................... 1260
6.4.3 The Password Validation Component .............................................................. 1266
6.4.4 The MySQL Keyring ....................................................................................... 1278
6.4.5 MySQL Enterprise Audit .................................................................................. 1348
6.4.6 The Audit Message Component ...................................................................... 1434
6.4.7 MySQL Enterprise Firewall .............................................................................. 1436
6.5 MySQL Enterprise Data Masking and De-Identification ................................................ 1462
6.5.1 Data-Masking Components Versus the Data-Masking Plugin ............................. 1464
6.5.2 MySQL Enterprise Data Masking and De-Identification Components .................. 1465
6.5.3 MySQL Enterprise Data Masking and De-Identification Plugin ........................... 1488
6.6 MySQL Enterprise Encryption .................................................................................... 1504
6.6.1 MySQL Enterprise Encryption Installation and Upgrading .................................. 1505
6.6.2 Configuring MySQL Enterprise Encryption ....................................................... 1508
6.6.3 MySQL Enterprise Encryption Usage and Examples ......................................... 1509
6.6.4 MySQL Enterprise Encryption Function Reference ........................................... 1511
6.6.5 MySQL Enterprise Encryption Component Function Descriptions ....................... 1511
6.6.6 MySQL Enterprise Encryption Legacy Function Descriptions ............................. 1515
6.7 SELinux .................................................................................................................... 1520
6.7.1 Check if SELinux is Enabled ........................................................................... 1520
6.7.2 Changing the SELinux Mode ........................................................................... 1521
6.7.3 MySQL Server SELinux Policies ...................................................................... 1521
6.7.4 SELinux File Context ...................................................................................... 1521
vii
MySQL 8.1 Reference Manual
6.7.5 SELinux TCP Port Context .............................................................................. 1523
6.7.6 Troubleshooting SELinux ................................................................................ 1524
6.8 FIPS Support ............................................................................................................ 1525
7 Backup and Recovery .......................................................................................................... 1529
7.1 Backup and Recovery Types ..................................................................................... 1530
7.2 Database Backup Methods ........................................................................................ 1533
7.3 Example Backup and Recovery Strategy .................................................................... 1535
7.3.1 Establishing a Backup Policy .......................................................................... 1535
7.3.2 Using Backups for Recovery ........................................................................... 1537
7.3.3 Backup Strategy Summary .............................................................................. 1538
7.4 Using mysqldump for Backups ................................................................................... 1538
7.4.1 Dumping Data in SQL Format with mysqldump ................................................ 1539
7.4.2 Reloading SQL-Format Backups ..................................................................... 1540
7.4.3 Dumping Data in Delimited-Text Format with mysqldump .................................. 1540
7.4.4 Reloading Delimited-Text Format Backups ....................................................... 1541
7.4.5 mysqldump Tips ............................................................................................. 1542
7.5 Point-in-Time (Incremental) Recovery ......................................................................... 1544
7.5.1 Point-in-Time Recovery Using Binary Log ........................................................ 1544
7.5.2 Point-in-Time Recovery Using Event Positions ................................................. 1545
7.6 MyISAM Table Maintenance and Crash Recovery ....................................................... 1547
7.6.1 Using myisamchk for Crash Recovery ............................................................. 1547
7.6.2 How to Check MyISAM Tables for Errors ......................................................... 1548
7.6.3 How to Repair MyISAM Tables ....................................................................... 1549
7.6.4 MyISAM Table Optimization ............................................................................ 1551
7.6.5 Setting Up a MyISAM Table Maintenance Schedule ......................................... 1551
8 Optimization ......................................................................................................................... 1553
8.1 Optimization Overview ............................................................................................... 1554
8.2 Optimizing SQL Statements ....................................................................................... 1556
8.2.1 Optimizing SELECT Statements ...................................................................... 1556
8.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table
Expressions ............................................................................................................ 1607
8.2.3 Optimizing INFORMATION_SCHEMA Queries ................................................. 1620
8.2.4 Optimizing Performance Schema Queries ........................................................ 1623
8.2.5 Optimizing Data Change Statements ............................................................... 1625
8.2.6 Optimizing Database Privileges ....................................................................... 1626
8.2.7 Other Optimization Tips .................................................................................. 1626
8.3 Optimization and Indexes .......................................................................................... 1627
8.3.1 How MySQL Uses Indexes ............................................................................. 1627
8.3.2 Primary Key Optimization ................................................................................ 1628
8.3.3 SPATIAL Index Optimization ........................................................................... 1628
8.3.4 Foreign Key Optimization ................................................................................ 1629
8.3.5 Column Indexes ............................................................................................. 1629
8.3.6 Multiple-Column Indexes ................................................................................. 1630
8.3.7 Verifying Index Usage ..................................................................................... 1632
8.3.8 InnoDB and MyISAM Index Statistics Collection ............................................... 1632
8.3.9 Comparison of B-Tree and Hash Indexes ........................................................ 1633
8.3.10 Use of Index Extensions ............................................................................... 1635
8.3.11 Optimizer Use of Generated Column Indexes ................................................. 1637
8.3.12 Invisible Indexes ........................................................................................... 1638
8.3.13 Descending Indexes ..................................................................................... 1640
8.3.14 Indexed Lookups from TIMESTAMP Columns ................................................ 1642
8.4 Optimizing Database Structure ................................................................................... 1644
8.4.1 Optimizing Data Size ...................................................................................... 1644
8.4.2 Optimizing MySQL Data Types ....................................................................... 1646
8.4.3 Optimizing for Many Tables ............................................................................ 1647
8.4.4 Internal Temporary Table Use in MySQL ......................................................... 1648
8.4.5 Limits on Number of Databases and Tables ..................................................... 1652
8.4.6 Limits on Table Size ....................................................................................... 1652
viii
相关推荐
FX系列微型可编程控制器用户手册（通信篇）.pdf
FX2N系列PLC培训教程.pdf
Modbus协议资料.pdf
WM8978中文资料(芯片资料).doc
5-1.【Codelab】HarmonyOS基于图像模块实现图库图片的四种常见操作.pdf
GX Developer 编程软件使用说明.pdf
uCOS-III的任务调度算法研究.pdf
STM32F407ZGT6(芯片资料).pdf
ModBus通讯规约-0701中文版.pdf
ATK-NEO-6M GPS模块常见问题汇总_201400721.pdf
STM32F103x8B_DS_CH_V10(7STM32中文数据手册).pdf
MR-E伺服与FX3G接线图.pdf
