基本信息
源码名称：mysql 8.0官方文档.pdf（共5140页）
源码大小：38.35M
文件格式：.pdf
开发语言：SQL
更新时间：2020-10-27
友情提示：（无需注册或充值，赞助后即可获取资源下载链接）
嘿，亲！知识可是无价之宝呢，但咱这精心整理的资料也耗费了不少心血呀。小小地破费一下，绝对物超所值哦！如有下载和支付问题，请联系我们QQ(微信同号)：813200300
本次赞助数额为： 1 元×
微信扫码支付：1 元
×
请留下您的邮箱，我们将在2小时内将文件发到您的邮箱
源码介绍
是MySQL8权威参考
oracle为mysql8制作的官方参考，mysql8与之前版本有不少变化，在现行的国内相关书中几无涉及。
Table of Contents Preface and Legal Notices ......................................................................................................... xxv 1 General Information ................................................................................................................... 1 1.1 About This Manual .......................................................................................................... 2 1.2 Typographical and Syntax Conventions ........................................................................... 2 1.3 Overview of the MySQL Database Management System ................................................... 4 1.3.1 What is MySQL? .................................................................................................. 4 1.3.2 The Main Features of MySQL ............................................................................... 5 1.3.3 History of MySQL ................................................................................................. 8 1.4 What Is New in MySQL 8.0 ............................................................................................. 9 1.5 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 8.0 ...................................................................................................................................... 27 1.6 MySQL Information Sources .......................................................................................... 35 1.6.1 MySQL Websites ............................................................................................... 35 1.6.2 MySQL Mailing Lists ........................................................................................... 36 1.6.3 MySQL Community Support at the MySQL Forums .............................................. 38 1.6.4 MySQL Community Support on Internet Relay Chat (IRC) .................................... 38 1.6.5 MySQL Enterprise .............................................................................................. 38 1.7 How to Report Bugs or Problems .................................................................................. 39 1.8 MySQL Standards Compliance ...................................................................................... 43 1.8.1 MySQL Extensions to Standard SQL ................................................................... 44 1.8.2 MySQL Differences from Standard SQL .............................................................. 47 1.8.3 How MySQL Deals with Constraints .................................................................... 48 1.9 Credits .......................................................................................................................... 52 1.9.1 Contributors to MySQL ....................................................................................... 52 1.9.2 Documenters and translators .............................................................................. 56 1.9.3 Packages that support MySQL ............................................................................ 57 1.9.4 Tools that were used to create MySQL ............................................................... 58 1.9.5 Supporters of MySQL ......................................................................................... 58 2 Installing and Upgrading MySQL .............................................................................................. 61 2.1 General Installation Guidance ........................................................................................ 63 2.1.1 Which MySQL Version and Distribution to Install .................................................. 63 2.1.2 How to Get MySQL ............................................................................................ 65 2.1.3 Verifying Package Integrity Using MD5 Checksums or GnuPG .............................. 65 2.1.4 Installation Layouts ............................................................................................. 78 2.1.5 Compiler-Specific Build Characteristics ................................................................ 78 2.2 Installing MySQL on Unix/Linux Using Generic Binaries .................................................. 78 2.3 Installing MySQL on Microsoft Windows ......................................................................... 81 2.3.1 MySQL Installation Layout on Microsoft Windows ................................................ 83 2.3.2 Choosing an Installation Package ....................................................................... 84 2.3.3 MySQL Installer for Windows .............................................................................. 85 2.3.4 MySQL Notifier ................................................................................................. 106 2.3.5 Installing MySQL on Microsoft Windows Using a noinstall ZIP Archive ............ 117 2.3.6 Troubleshooting a Microsoft Windows MySQL Server Installation ........................ 125 2.3.7 Windows Postinstallation Procedures ................................................................ 127 2.3.8 Upgrading MySQL on Windows ........................................................................ 129 2.4 Installing MySQL on macOS ........................................................................................ 131 2.4.1 General Notes on Installing MySQL on macOS .................................................. 131 2.4.2 Installing MySQL on macOS Using Native Packages .......................................... 132 2.4.3 Installing and Using the MySQL Launch Daemon ............................................... 136 2.4.4 Installing and Using the MySQL Preference Pane .............................................. 139 2.5 Installing MySQL on Linux ........................................................................................... 143 2.5.1 Installing MySQL on Linux Using the MySQL Yum Repository ............................. 144 2.5.2 Installing MySQL on Linux Using the MySQL APT Repository ............................. 148 2.5.3 Installing MySQL on Linux Using the MySQL SLES Repository ........................... 148 2.5.4 Installing MySQL on Linux Using RPM Packages from Oracle ............................. 148 MySQL 8.0 Reference Manual iv 2.5.5 Installing MySQL on Linux Using Debian Packages from Oracle .......................... 154 2.5.6 Deploying MySQL on Linux with Docker ............................................................ 155 2.5.7 Installing MySQL on Linux from the Native Software Repositories ........................ 164 2.5.8 Installing MySQL on Linux with Juju .................................................................. 167 2.5.9 Managing MySQL Server with systemd ............................................................. 168 2.6 Installing MySQL Using Unbreakable Linux Network (ULN) ........................................... 173 2.7 Installing MySQL on Solaris ......................................................................................... 173 2.7.1 Installing MySQL on Solaris Using a Solaris PKG .............................................. 174 2.8 Installing MySQL on FreeBSD ..................................................................................... 175 2.9 Installing MySQL from Source ..................................................................................... 176 2.9.1 MySQL Layout for Source Installation ................................................................ 178 2.9.2 Installing MySQL Using a Standard Source Distribution ...................................... 178 2.9.3 Installing MySQL Using a Development Source Tree .......................................... 183 2.9.4 MySQL Source-Configuration Options ............................................................... 185 2.9.5 Dealing with Problems Compiling MySQL .......................................................... 206 2.9.6 MySQL Configuration and Third-Party Tools ...................................................... 207 2.9.7 Generating MySQL Doxygen Documentation Content ......................................... 207 2.10 Postinstallation Setup and Testing ............................................................................. 208 2.10.1 Initializing the Data Directory ........................................................................... 209 2.10.2 Starting the Server ......................................................................................... 214 2.10.3 Testing the Server .......................................................................................... 217 2.10.4 Securing the Initial MySQL Account ................................................................. 219 2.10.5 Starting and Stopping MySQL Automatically .................................................... 221 2.11 Upgrading or Downgrading MySQL ............................................................................ 221 2.11.1 Upgrading MySQL .......................................................................................... 222 2.11.2 Downgrading MySQL ...................................................................................... 238 2.11.3 Rebuilding or Repairing Tables or Indexes ....................................................... 238 2.11.4 Copying MySQL Databases to Another Machine .............................................. 240 2.12 Perl Installation Notes ................................................................................................ 241 2.12.1 Installing Perl on Unix ..................................................................................... 241 2.12.2 Installing ActiveState Perl on Windows ............................................................ 242 2.12.3 Problems Using the Perl DBI/DBD Interface ..................................................... 243 3 Tutorial .................................................................................................................................. 245 3.1 Connecting to and Disconnecting from the Server ......................................................... 245 3.2 Entering Queries ......................................................................................................... 246 3.3 Creating and Using a Database ................................................................................... 249 3.3.1 Creating and Selecting a Database ................................................................... 250 3.3.2 Creating a Table .............................................................................................. 251 3.3.3 Loading Data into a Table ................................................................................ 252 3.3.4 Retrieving Information from a Table ................................................................... 254 3.4 Getting Information About Databases and Tables ......................................................... 267 3.5 Using mysql in Batch Mode ......................................................................................... 267 3.6 Examples of Common Queries .................................................................................... 269 3.6.1 The Maximum Value for a Column .................................................................... 269 3.6.2 The Row Holding the Maximum of a Certain Column .......................................... 270 3.6.3 Maximum of Column per Group ........................................................................ 270 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column ....................... 270 3.6.5 Using User-Defined Variables ........................................................................... 271 3.6.6 Using Foreign Keys .......................................................................................... 272 3.6.7 Searching on Two Keys .................................................................................... 273 3.6.8 Calculating Visits Per Day ................................................................................. 273 3.6.9 Using AUTO_INCREMENT ............................................................................... 274 3.7 Using MySQL with Apache .......................................................................................... 276 4 MySQL Programs .................................................................................................................. 279 4.1 Overview of MySQL Programs ..................................................................................... 280 4.2 Using MySQL Programs .............................................................................................. 284 4.2.1 Invoking MySQL Programs ............................................................................... 284 4.2.2 Connecting to the MySQL Server ...................................................................... 284 MySQL 8.0 Reference Manual v 4.2.3 Connecting Using a Path .................................................................................. 288 4.2.4 Specifying Program Options .............................................................................. 292 4.2.5 Using Options on the Command Line ................................................................ 293 4.2.6 Program Option Modifiers ................................................................................. 294 4.2.7 Using Option Files ............................................................................................ 295 4.2.8 Command-Line Options that Affect Option-File Handling ..................................... 300 4.2.9 Using Options to Set Program Variables ............................................................ 301 4.2.10 Option Defaults, Options Expecting Values, and the = Sign ............................... 302 4.2.11 Setting Environment Variables ......................................................................... 306 4.3 MySQL Server and Server-Startup Programs ............................................................... 307 4.3.1 mysqld — The MySQL Server ......................................................................... 307 4.3.2 mysqld_safe — MySQL Server Startup Script ................................................. 307 4.3.3 mysql.server — MySQL Server Startup Script ............................................... 313 4.3.4 mysqld_multi — Manage Multiple MySQL Servers ......................................... 315 4.4 MySQL Installation-Related Programs .......................................................................... 319 4.4.1 comp_err — Compile MySQL Error Message File ............................................ 319 4.4.2 mysql_secure_installation — Improve MySQL Installation Security ........... 320 4.4.3 mysql_ssl_rsa_setup — Create SSL/RSA Files ........................................... 323 4.4.4 mysql_tzinfo_to_sql — Load the Time Zone Tables ................................... 325 4.4.5 mysql_upgrade — Check and Upgrade MySQL Tables ................................... 326 4.5 MySQL Client Programs .............................................................................................. 333 4.5.1 mysql — The MySQL Command-Line Tool ....................................................... 333 4.5.2 mysqladmin — Client for Administering a MySQL Server .................................. 359 4.5.3 mysqlcheck — A Table Maintenance Program ................................................. 368 4.5.4 mysqldump — A Database Backup Program ..................................................... 376 4.5.5 mysqlimport — A Data Import Program ......................................................... 397 4.5.6 mysqlpump — A Database Backup Program ..................................................... 404 4.5.7 mysqlshow — Display Database, Table, and Column Information ...................... 420 4.5.8 mysqlslap — Load Emulation Client ............................................................... 425 4.6 MySQL Administrative and Utility Programs .................................................................. 434 4.6.1 ibd2sdi — InnoDB Tablespace SDI Extraction Utility ....................................... 434 4.6.2 innochecksum — Offline InnoDB File Checksum Utility .................................... 437 4.6.3 myisam_ftdump — Display Full-Text Index information ..................................... 443 4.6.4 myisamchk — MyISAM Table-Maintenance Utility ............................................. 444 4.6.5 myisamlog — Display MyISAM Log File Contents ............................................ 460 4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables .................. 461 4.6.7 mysql_config_editor — MySQL Configuration Utility ................................... 467 4.6.8 mysqlbinlog — Utility for Processing Binary Log Files ..................................... 473 4.6.9 mysqldumpslow — Summarize Slow Query Log Files ...................................... 494 4.7 MySQL Program Development Utilities ......................................................................... 495 4.7.1 mysql_config — Display Options for Compiling Clients ................................... 496 4.7.2 my_print_defaults — Display Options from Option Files .............................. 497 4.7.3 resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols ....... 498 4.8 Miscellaneous Programs .............................................................................................. 499 4.8.1 lz4_decompress — Decompress mysqlpump LZ4-Compressed Output ............ 499 4.8.2 perror — Explain Error Codes ........................................................................ 499 4.8.3 resolveip — Resolve Host name to IP Address or Vice Versa ......................... 500 4.8.4 zlib_decompress — Decompress mysqlpump ZLIB-Compressed Output ......... 500 4.9 MySQL Program Environment Variables ....................................................................... 501 5 MySQL Server Administration ................................................................................................. 505 5.1 The MySQL Server ..................................................................................................... 506 5.1.1 Configuring the Server ...................................................................................... 506 5.1.2 Server Configuration Defaults ........................................................................... 507 5.1.3 Server Option, System Variable, and Status Variable Reference ......................... 508 5.1.4 Server System Variable Reference .................................................................... 549 5.1.5 Server Status Variable Reference ..................................................................... 572 5.1.6 Server Command Options ................................................................................. 586 5.1.7 Server System Variables .................................................................................. 623 MySQL 8.0 Reference Manual vi 5.1.8 Using System Variables .................................................................................... 755 5.1.9 Server Status Variables .................................................................................... 780 5.1.10 Server SQL Modes ......................................................................................... 799 5.1.11 IPv6 Support .................................................................................................. 810 5.1.12 MySQL Server Time Zone Support .................................................................. 814 5.1.13 Server Tracking of Client Session State Changes ............................................. 819 5.1.14 Server-Side Help ............................................................................................ 822 5.1.15 Server Response to Signals ............................................................................ 822 5.1.16 The Server Shutdown Process ........................................................................ 823 5.2 The MySQL Data Directory .......................................................................................... 824 5.3 The mysql System Database ....................................................................................... 825 5.4 MySQL Server Logs .................................................................................................... 830 5.4.1 Selecting General Query and Slow Query Log Output Destinations ...................... 831 5.4.2 The Error Log .................................................................................................. 833 5.4.3 The General Query Log .................................................................................... 847 5.4.4 The Binary Log ................................................................................................ 848 5.4.5 The Slow Query Log ........................................................................................ 860 5.4.6 The DDL Log ................................................................................................... 862 5.4.7 Server Log Maintenance ................................................................................... 862 5.5 MySQL Server Components ........................................................................................ 863 5.5.1 Error Log Components ..................................................................................... 864 5.6 MySQL Server Plugins ................................................................................................ 866 5.6.1 Installing and Uninstalling Plugins ..................................................................... 867 5.6.2 Obtaining Server Plugin Information .................................................................. 871 5.6.3 MySQL Enterprise Thread Pool ......................................................................... 872 5.6.4 The Rewriter Query Rewrite Plugin ................................................................... 879 5.6.5 Version Tokens ................................................................................................ 888 5.7 Running Multiple MySQL Instances on One Machine .................................................... 900 5.7.1 Setting Up Multiple Data Directories .................................................................. 901 5.7.2 Running Multiple MySQL Instances on Windows ................................................ 902 5.7.3 Running Multiple MySQL Instances on Unix ....................................................... 905 5.7.4 Using Client Programs in a Multiple-Server Environment .................................... 906 6 Security ................................................................................................................................. 909 6.1 General Security Issues .............................................................................................. 910 6.1.1 Security Guidelines ........................................................................................... 910 6.1.2 Keeping Passwords Secure .............................................................................. 912 6.1.3 Making MySQL Secure Against Attackers .......................................................... 914 6.1.4 Security-Related mysqld Options and Variables ................................................. 916 6.1.5 How to Run MySQL as a Normal User .............................................................. 917 6.1.6 Security Issues with LOAD DATA LOCAL .......................................................... 917 6.1.7 Client Programming Security Guidelines ............................................................ 919 6.2 The MySQL Access Privilege System .......................................................................... 920 6.2.1 Privileges Provided by MySQL .......................................................................... 921 6.2.2 Static Versus Dynamic Privileges ...................................................................... 931 6.2.3 Grant Tables .................................................................................................... 933 6.2.4 Specifying Account Names ............................................................................... 940 6.2.5 Specifying Role Names .................................................................................... 942 6.2.6 Access Control, Stage 1: Connection Verification ............................................... 943 6.2.7 Access Control, Stage 2: Request Verification ................................................... 946 6.2.8 When Privilege Changes Take Effect ................................................................ 947 6.2.9 Troubleshooting Problems Connecting to MySQL ............................................... 948 6.3 MySQL User Account Management ............................................................................. 952 6.3.1 User Names and Passwords ............................................................................. 953 6.3.2 Adding User Accounts ...................................................................................... 954 6.3.3 Removing User Accounts .................................................................................. 956 6.3.4 Using Roles ..................................................................................................... 956 6.3.5 Reserved User Accounts .................................................................................. 963 6.3.6 Setting Account Resource Limits ....................................................................... 963 MySQL 8.0 Reference Manual vii 6.3.7 Assigning Account Passwords ........................................................................... 965 6.3.8 Password Management .................................................................................... 966 6.3.9 Server Handling of Expired Passwords .............................................................. 973 6.3.10 Pluggable Authentication ................................................................................. 974 6.3.11 Proxy Users ................................................................................................... 978 6.3.12 User Account Locking ..................................................................................... 983 6.3.13 SQL-Based MySQL Account Activity Auditing ................................................... 984 6.4 Using Encrypted Connections ...................................................................................... 985 6.4.1 Configuring MySQL to Use Encrypted Connections ............................................ 986 6.4.2 Command Options for Encrypted Connections ................................................... 990 6.4.3 Creating SSL and RSA Certificates and Keys .................................................... 993 6.4.4 OpenSSL Versus wolfSSL .............................................................................. 1002 6.4.5 Building MySQL with Support for Encrypted Connections .................................. 1003 6.4.6 Encrypted Connection Protocols and Ciphers ................................................... 1004 6.4.7 Connecting to MySQL Remotely from Windows with SSH ................................. 1007 6.5 Security Components and Plugins .............................................................................. 1007 6.5.1 Authentication Plugins ..................................................................................... 1008 6.5.2 The Connection-Control Plugins ...................................................................... 1064 6.5.3 The Password Validation Component .............................................................. 1070 6.5.4 The MySQL Keyring ....................................................................................... 1080 6.5.5 MySQL Enterprise Audit .................................................................................. 1113 6.5.6 MySQL Enterprise Firewall .............................................................................. 1174 6.6 FIPS Support ............................................................................................................ 1186 7 Backup and Recovery .......................................................................................................... 1189 7.1 Backup and Recovery Types ..................................................................................... 1190 7.2 Database Backup Methods ........................................................................................ 1193 7.3 Example Backup and Recovery Strategy .................................................................... 1195 7.3.1 Establishing a Backup Policy .......................................................................... 1196 7.3.2 Using Backups for Recovery ........................................................................... 1197 7.3.3 Backup Strategy Summary .............................................................................. 1198 7.4 Using mysqldump for Backups ................................................................................... 1198 7.4.1 Dumping Data in SQL Format with mysqldump ................................................ 1199 7.4.2 Reloading SQL-Format Backups ..................................................................... 1200 7.4.3 Dumping Data in Delimited-Text Format with mysqldump .................................. 1200 7.4.4 Reloading Delimited-Text Format Backups ....................................................... 1202 7.4.5 mysqldump Tips ............................................................................................. 1202 7.5 Point-in-Time (Incremental) Recovery Using the Binary Log ......................................... 1204 7.5.1 Point-in-Time Recovery Using Event Times ..................................................... 1206 7.5.2 Point-in-Time Recovery Using Event Positions ................................................. 1206 7.6 MyISAM Table Maintenance and Crash Recovery ....................................................... 1207 7.6.1 Using myisamchk for Crash Recovery ............................................................. 1207 7.6.2 How to Check MyISAM Tables for Errors ......................................................... 1208 7.6.3 How to Repair MyISAM Tables ....................................................................... 1209 7.6.4 MyISAM Table Optimization ............................................................................ 1211 7.6.5 Setting Up a MyISAM Table Maintenance Schedule ......................................... 1211 8 Optimization ......................................................................................................................... 1213 8.1 Optimization Overview ............................................................................................... 1214 8.2 Optimizing SQL Statements ....................................................................................... 1216 8.2.1 Optimizing SELECT Statements ...................................................................... 1216 8.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions ............................................................................................................ 1260 8.2.3 Optimizing INFORMATION_SCHEMA Queries ................................................. 1271 8.2.4 Optimizing Performance Schema Queries ........................................................ 1274 8.2.5 Optimizing Data Change Statements ............................................................... 1276 8.2.6 Optimizing Database Privileges ....................................................................... 1277 8.2.7 Other Optimization Tips .................................................................................. 1277 8.3 Optimization and Indexes .......................................................................................... 1278 8.3.1 How MySQL Uses Indexes ............................................................................. 1278 MySQL 8.0 Reference Manual viii 8.3.2 Primary Key Optimization ................................................................................ 1279 8.3.3 SPATIAL Index Optimization ........................................................................... 1279 8.3.4 Foreign Key Optimization ................................................................................ 1280 8.3.5 Column Indexes ............................................................................................. 1280 8.3.6 Multiple-Column Indexes ................................................................................. 1281 8.3.7 Verifying Index Usage ..................................................................................... 1283 8.3.8 InnoDB and MyISAM Index Statistics Collection ............................................... 1283 8.3.9 Comparison of B-Tree and Hash Indexes ........................................................ 1285 8.3.10 Use of Index Extensions ............................................................................... 1286 8.3.11 Optimizer Use of Generated Column Indexes ................................................. 1288 8.3.12 Invisible Indexes ........................................................................................... 1290 8.3.13 Descending Indexes ..................................................................................... 1291 8.4 Optimizing Database Structure ................................................................................... 1292 8.4.1 Optimizing Data Size ...................................................................................... 1292 8.4.2 Optimizing MySQL Data Types ....................................................................... 1294 8.4.3 Optimizing for Many Tables ............................................................................ 1295 8.4.4 Internal Temporary Table Use in MySQL ......................................................... 1297 8.5 Optimizing for InnoDB Tables .................................................................................... 1300 8.5.1 Optimizing Storage Layout for InnoDB Tables .................................................. 1300 8.5.2 Optimizing InnoDB Transaction Management ................................................... 1300 8.5.3 Optimizing InnoDB Read-Only Transactions ..................................................... 1301 8.5.4 Optimizing InnoDB Redo Logging .................................................................... 1302 8.5.5 Bulk Data Loading for InnoDB Tables .............................................................. 1303 8.5.6 Optimizing InnoDB Queries ............................................................................. 1305 8.5.7 Optimizing InnoDB DDL Operations ................................................................. 1305 8.5.8 Optimizing InnoDB Disk I/O ............................................................................ 1305 8.5.9 Optimizing InnoDB Configuration Variables ...................................................... 1308 8.5.10 Optimizing InnoDB for Systems with Many Tables .......................................... 1310 8.6 Optimizing for MyISAM Tables ................................................................................... 1310 8.6.1 Optimizing MyISAM Queries ........................................................................... 1310 8.6.2 Bulk Data Loading for MyISAM Tables ............................................................ 1311 8.6.3 Optimizing REPAIR TABLE Statements ........................................................... 1312 8.7 Optimizing for MEMORY Tables ................................................................................ 1314 8.8 Understanding the Query Execution Plan ................................................................... 1314 8.8.1 Optimizing Queries with EXPLAIN ................................................................... 1314 8.8.2 EXPLAIN Output Format ................................................................................. 1315 8.8.3 Extended EXPLAIN Output Format .................................................................. 1327 8.8.4 Obtaining Execution Plan Information for a Named Connection ......................... 1330 8.8.5 Estimating Query Performance ........................................................................ 1330 8.9 Controlling the Query Optimizer ................................................................................. 1331 8.9.1 Controlling Query Plan Evaluation ................................................................... 1331 8.9.2 Optimizer Hints ............................................................................................... 1331 8.9.3 Switchable Optimizations ................................................................................ 1345 8.9.4 Index Hints ..................................................................................................... 1349 8.9.5 The Optimizer Cost Model .............................................................................. 1351 8.9.6 Optimizer Statistics ......................................................................................... 1354 8.10 Buffering and Caching ............................................................................................. 1357 8.10.1 InnoDB Buffer Pool Optimization ................................................................... 1357 8.10.2 The MyISAM Key Cache ............................................................................... 1358 8.10.3 Caching of Prepared Statements and Stored Programs .................................. 1362 8.11 Optimizing Locking Operations ................................................................................. 1363 8.11.1 Internal Locking Methods .............................................................................. 1363 8.11.2 Table Locking Issues .................................................................................... 1366 8.11.3 Concurrent Inserts ........................................................................................ 1367 8.11.4 Metadata Locking ......................................................................................... 1368 8.11.5 External Locking ........................................................................................... 1369 8.12 Optimizing the MySQL Server .................................................................................. 1370 8.12.1 Optimizing Disk I/O ....................................................................................... 1370 MySQL 8.0 Reference Manual ix 8.12.2 Using Symbolic Links .................................................................................... 1371 8.12.3 Optimizing Memory Use ................................................................................ 1373 8.12.4 Optimizing Network Use ................................................................................ 1380 8.12.5 Resource Groups .......................................................................................... 1382 8.13 Measuring Performance (Benchmarking) .................................................................. 1386 8.13.1 Measuring the Speed of Expressions and Functions ....................................... 1387 8.13.2 Using Your Own Benchmarks ........................................................................ 1387 8.13.3 Measuring Performance with performance_schema ........................................ 1387 8.14 Examining Thread Information .................................................................................. 1387 8.14.1 Thread Command Values ............................................................................. 1388 8.14.2 General Thread States .................................................................................. 1390 8.14.3 Replication Master Thread States .................................................................. 1397 8.14.4 Replication Slave I/O Thread States .............................................................. 1397 8.14.5 Replication Slave SQL Thread States ............................................................ 1398 8.14.6 Replication Slave Connection Thread States .................................................. 1399 8.14.7 Event Scheduler Thread States ..................................................................... 1399 9 Language Structure .............................................................................................................. 1401 9.1 Literal Values ............................................................................................................ 1401 9.1.1 String Literals ................................................................................................. 1401 9.1.2 Numeric Literals ............................................................................................. 1404 9.1.3 Date and Time Literals ................................................................................... 1404 9.1.4 Hexadecimal Literals ....................................................................................... 1406 9.1.5 Bit-Value Literals ............................................................................................ 1408 9.1.6 Boolean Literals .............................................................................................. 1410 9.1.7 NULL Values .................................................................................................. 1410 9.2 Schema Object Names .............................................................................................. 1410 9.2.1 Identifier Qualifiers .......................................................................................... 1413 9.2.2 Identifier Case Sensitivity ................................................................................ 1414 9.2.3 Mapping of Identifiers to File Names ............................................................... 1416 9.2.4 Function Name Parsing and Resolution ........................................................... 1417 9.3 Keywords and Reserved Words ................................................................................. 1421 9.4 User-Defined Variables .............................................................................................. 1447 9.5 Expression Syntax ..................................................................................................... 1449 9.6 Comment Syntax ....................................................................................................... 1451 10 Character Sets, Collations, Unicode .................................................................................... 1453 10.1 Character Sets and Collations in General ................................................................. 1454 10.2 Character Sets and Collations in MySQL .................................................................. 1455 10.2.1 Character Set Repertoire .............................................................................. 1457 10.2.2 UTF-8 for Metadata ...................................................................................... 1459 10.3 Specifying Character Sets and Collations ................................................................. 1460 10.3.1 Collation Naming Conventions ....................................................................... 1460 10.3.2 Server Character Set and Collation ............................................................... 1461 10.3.3 Database Character Set and Collation ........................................................... 1462 10.3.4 Table Character Set and Collation ................................................................. 1463 10.3.5 Column Character Set and Collation .............................................................. 1464 10.3.6 Character String Literal Character Set and Collation ....................................... 1465 10.3.7 The National Character Set ........................................................................... 1467 10.3.8 Character Set Introducers ............................................................................. 1467 10.3.9 Examples of Character Set and Collation Assignment ..................................... 1469 10.3.10 Compatibility with Other DBMSs .................................................................. 1470 10.4 Connection Character Sets and Collations ................................................................ 1470 10.5 Configuring Application Character Set and Collation .................................................. 1477 10.6 Error Message Character Set ................................................................................... 1478 10.7 Column Character Set Conversion ........................................................................... 1479 10.8 Collation Issues ....................................................................................................... 1480 10.8.1 Using COLLATE in SQL Statements .............................................................. 1480 10.8.2 COLLATE Clause Precedence ...................................................................... 1481 10.8.3 Character Set and Collation Compatibility ...................................................... 1481 MySQL 8.0 Reference Manual x 10.8.4 Collation Coercibility in Expressions ............................................................... 1481 10.8.5 The binary Collation Compared to _bin Collations ........................................... 1483 10.8.6 Examples of the Effect of Collation ................................................................ 1485 10.8.7 Using Collation in INFORMATION_SCHEMA Searches .................................. 1486 10.9 Unicode Support ...................................................................................................... 1488 10.9.1 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding) ........................ 1490 10.9.2 The utf8mb3 Character Set (3-Byte UTF-8 Unicode Encoding) ........................ 1490 10.9.3 The utf8 Character Set (Alias for utf8mb3) ..................................................... 1491 10.9.4 The ucs2 Character Set (UCS-2 Unicode Encoding) ....................................... 1491 10.9.5 The utf16 Character Set (UTF-16 Unicode Encoding) ..................................... 1492 10.9.6 The utf16le Character Set (UTF-16LE Unicode Encoding) ............................... 1492 10.9.7 The utf32 Character Set (UTF-32 Unicode Encoding) ..................................... 1492 10.9.8 Converting Between 3-Byte and 4-Byte Unicode Character Sets ...................... 1493 10.10 Supported Character Sets and Collations ................................................................ 1495 10.10.1 Unicode Character Sets .............................................................................. 1496 10.10.2 West European Character Sets ................................................................... 1502 10.10.3 Central European Character Sets ................................................................ 1503 10.10.4 South European and Middle East Character Sets ......................................... 1504 10.10.5 Baltic Character Sets .................................................................................. 1505 10.10.6 Cyrillic Character Sets ................................................................................. 1505 10.10.7 Asian Character Sets .................................................................................. 1505 10.10.8 The Binary Character Set ............................................................................ 1510 10.11 Setting the Error Message Language ...................................................................... 1510 10.12 Adding a Character Set ......................................................................................... 1511 10.12.1 Character Definition Arrays .......................................................................... 1513 10.12.2 String Collating Support for Complex Character Sets .................................... 1514 10.12.3 Multi-Byte Character Support for Complex Character Sets ............................. 1514 10.13 Adding a Collation to a Character Set ..................................................................... 1514 10.13.1 Collation Implementation Types ................................................................... 1515 10.13.2 Choosing a Collation ID .............................................................................. 1518 10.13.3 Adding a Simple Collation to an 8-Bit Character Set ..................................... 1519 10.13.4 Adding a UCA Collation to a Unicode Character Set ..................................... 1520 10.14 Character Set Configuration ................................................................................... 1527 10.15 MySQL Server Locale Support ............................................................................... 1528 11 Data Types ........................................................................................................................ 1531 11.1 Data Type Overview ................................................................................................ 1532 11.1.1 Numeric Type Overview ................................................................................ 1532 11.1.2 Date and Time Type Overview ...................................................................... 1535 11.1.3 String Type Overview .................................................................................... 1537 11.2 Numeric Types ........................................................................................................ 1540 11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT .............................................................................................. 1541 11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC ................................. 1541 11.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE ......................... 1541 11.2.4 Bit-Value Type - BIT ..................................................................................... 1542 11.2.5 Numeric Type Attributes ................................................................................ 1542 11.2.6 Out-of-Range and Overflow Handling ............................................................. 1543 11.3 Date and Time Types .............................................................................................. 1545 11.3.1 The DATE, DATETIME, and TIMESTAMP Types ........................................... 1546 11.3.2 The TIME Type ............................................................................................ 1547 11.3.3 The YEAR Type ........................................................................................... 1548 11.3.4 Migrating YEAR(2) Columns to YEAR(4) ........................................................ 1548 11.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME ............. 1550 11.3.6 Fractional Seconds in Time Values ................................................................ 1553 11.3.7 Conversion Between Date and Time Types .................................................... 1554 11.3.8 Two-Digit Years in Dates .............................................................................. 1555 11.4 String Types ............................................................................................................ 1555 11.4.1 The CHAR and VARCHAR Types ................................................................. 1555 MySQL 8.0 Reference Manual xi 11.4.2 The BINARY and VARBINARY Types ........................................................... 1557 11.4.3 The BLOB and TEXT Types ......................................................................... 1558 11.4.4 The ENUM Type ........................................................................................... 1560 11.4.5 The SET Type .............................................................................................. 1563 11.5 Spatial Data Types .................................................................................................. 1565 11.5.1 Spatial Data Types ....................................................................................... 1567 11.5.2 The OpenGIS Geometry Model ..................................................................... 1568 11.5.3 Supported Spatial Data Formats .................................................................... 1573 11.5.4 Geometry Well-Formedness and Validity ........................................................ 1576 11.5.5 Spatial Reference System Support ................................................................ 1577 11.5.6 Creating Spatial Columns .............................................................................. 1578 11.5.7 Populating Spatial Columns ........................................................................... 1579 11.5.8 Fetching Spatial Data .................................................................................... 1580 11.5.9 Optimizing Spatial Analysis ........................................................................... 1580 11.5.10 Creating Spatial Indexes ............................................................................. 1580 11.5.11 Using Spatial Indexes ................................................................................. 1582 11.6 The JSON Data Type .............................................................................................. 1583 11.7 Data Type Default Values ........................................................................................ 1598 11.8 Data Type Storage Requirements ............................................................................ 1601 11.9 Choosing the Right Type for a Column ..................................................................... 1605 11.10 Using Data Types from Other Database Engines .................................................... 1605 12 Functions and Operators .................................................................................................... 1607 12.1 Function and Operator Reference ............................................................................ 1609 12.2 Type Conversion in Expression Evaluation ............................................................... 1621 12.3 Operators ................................................................................................................ 1623 12.3.1 Operator Precedence .................................................................................... 1624 12.3.2 Comparison Functions and Operators ............................................................ 1625 12.3.3 Logical Operators ......................................................................................... 1632 12.3.4 Assignment Operators ................................................................................... 1633 12.4 Control Flow Functions ............................................................................................ 1635 12.5 String Functions ...................................................................................................... 1637 12.5.1 String Comparison Functions ......................................................................... 1652 12.5.2 Regular Expressions ..................................................................................... 1655 12.5.3 Character Set and Collation of Function Results ............................................. 1664 12.6 Numeric Functions and Operators ............................................................................ 1665 12.6.1 Arithmetic Operators ..................................................................................... 1666 12.6.2 Mathematical Functions ................................................................................. 1668 12.7 Date and Time Functions ......................................................................................... 1677 12.8 What Calendar Is Used By MySQL? ........................................................................ 1698 12.9 Full-Text Search Functions ...................................................................................... 1699 12.9.1 Natural Language Full-Text Searches ............................................................ 1700 12.9.2 Boolean Full-Text Searches .......................................................................... 1703 12.9.3 Full-Text Searches with Query Expansion ...................................................... 1709 12.9.4 Full-Text Stopwords ...................................................................................... 1709 12.9.5 Full-Text Restrictions .................................................................................... 1714 12.9.6 Fine-Tuning MySQL Full-Text Search ............................................................ 1714 12.9.7 Adding a Collation for Full-Text Indexing ........................................................ 1717 12.9.8 ngram Full-Text Parser ................................................................................. 1719 12.9.9 MeCab Full-Text Parser Plugin ...................................................................... 1721 12.10 Cast Functions and Operators ................................................................................ 1725 12.11 XML Functions ...................................................................................................... 1731 12.12 Bit Functions and Operators ................................................................................... 1741 12.13 Encryption and Compression Functions .................................................................. 1753 12.14 Information Functions ............................................................................................. 1759 12.15 Spatial Analysis Functions ..................................................................................... 1769 12.15.1 Spatial Function Reference ......................................................................... 1770 12.15.2 Argument Handling by Spatial Functions ...................................................... 1772 12.15.3 Functions That Create Geometry Values from WKT Values ........................... 1773 MySQL 8.0 Reference Manual xii 12.15.4 Functions That Create Geometry Values from WKB Values ........................... 1775 12.15.5 MySQL-Specific Functions That Create Geometry Values ............................. 1777 12.15.6 Geometry Format Conversion Functions ...................................................... 1778 12.15.7 Geometry Property Functions ...................................................................... 1780 12.15.8 Spatial Operator Functions .......................................................................... 1791 12.15.9 Functions That Test Spatial Relations Between Geometry Objects ................. 1795 12.15.10 Spatial Geohash Functions ........................................................................ 1801 12.15.11 Spatial GeoJSON Functions ...................................................................... 1802 12.15.12 Spatial Convenience Functions .................................................................. 1804 12.16 JSON Functions .................................................................................................... 1808 12.16.1 JSON Function Reference ........................................................................... 1808 12.16.2 Functions That Create JSON Values ........................................................... 1809 12.16.3 Functions That Search JSON Values ........................................................... 1810 12.16.4 Functions That Modify JSON Values ............................................................ 1819 12.16.5 Functions That Return JSON Value Attributes .............................................. 1828 12.16.6 JSON Table Functions ................................................................................ 1830 12.16.7 JSON Utility Functions ................................................................................ 1834 12.16.8 JSON Path Syntax ...................................................................................... 1840 12.17 Functions Used with Global Transaction Identifiers (GTIDs) ..................................... 1841 12.18 MySQL Enterprise Encryption Functions ................................................................. 1843 12.18.1 Enterprise Encryption Installation ................................................................. 1844 12.18.2 Enterprise Encryption Usage and Examples ................................................. 1844 12.18.3 Enterprise Encryption Function Reference .................................................... 1846 12.18.4 Enterprise Encryption Function Descriptions ................................................. 1847 12.19 Aggregate (GROUP BY) Functions ......................................................................... 1851 12.19.1 Aggregate (GROUP BY) Function Descriptions ............................................. 1851 12.19.2 GROUP BY Modifiers .................................................................................. 1859 12.19.3 MySQL Handling of GROUP BY .................................................................. 1865 12.19.4 Detection of Functional Dependence ............................................................ 1868 12.20 Window Functions ................................................................................................. 1871 12.20.1 Window Function Descriptions ..................................................................... 1871 12.20.2 Window Function Concepts and Syntax ....................................................... 1877 12.20.3 Window Function Frame Specification .......................................................... 1880 12.20.4 Named Windows ......................................................................................... 1884 12.20.5 Window Function Restrictions ...................................................................... 1885 12.21 Internal Functions .................................................................................................. 1885 12.22 Miscellaneous Functions ........................................................................................ 1887 12.23 Precision Math ...................................................................................................... 1903 12.23.1 Types of Numeric Values ............................................................................ 1903 12.23.2 DECIMAL Data Type Characteristics ............................................................ 1904 12.23.3 Expression Handling ................................................................................... 1905 12.23.4 Rounding Behavior ..................................................................................... 1906 12.23.5 Precision Math Examples ............................................................................ 1907 13 SQL Statement Syntax ....................................................................................................... 1911 13.1 Data Definition Statements ....................................................................................... 1912 13.1.1 Atomic Data Definition Statement Support ...................................................... 1912 13.1.2 ALTER DATABASE Syntax ........................................................................... 1918 13.1.3 ALTER EVENT Syntax .................................................................................. 1918 13.1.4 ALTER FUNCTION Syntax ........................................................................... 1920 13.1.5 ALTER INSTANCE Syntax ............................................................................ 1920 13.1.6 ALTER PROCEDURE Syntax ....................................................................... 1920 13.1.7 ALTER SERVER Syntax ............................................................................... 1921 13.1.8 ALTER TABLE Syntax .................................................................................. 1921 13.1.9 ALTER TABLESPACE Syntax ....................................................................... 1941 13.1.10 ALTER VIEW Syntax .................................................................................. 1941 13.1.11 CREATE DATABASE Syntax ...................................................................... 1941 13.1.12 CREATE EVENT Syntax ............................................................................. 1942 13.1.13 CREATE FUNCTION Syntax ....................................................................... 1947 MySQL 8.0 Reference Manual xiii 13.1.14 CREATE INDEX Syntax .............................................................................. 1947 13.1.15 CREATE PROCEDURE and CREATE FUNCTION Syntax ............................ 1955 13.1.16 CREATE SERVER Syntax .......................................................................... 1960 13.1.17 CREATE SPATIAL REFERENCE SYSTEM Syntax ...................................... 1961 13.1.18 CREATE TABLE Syntax ............................................................................. 1963 13.1.19 CREATE TABLESPACE Syntax .................................................................. 2000 13.1.20 CREATE TRIGGER Syntax ......................................................................... 2002 13.1.21 CREATE VIEW Syntax ................................................................................ 2005 13.1.22 DROP DATABASE Syntax .......................................................................... 2009 13.1.23 DROP EVENT Syntax ................................................................................. 2010 13.1.24 DROP FUNCTION Syntax ........................................................................... 2010 13.1.25 DROP INDEX Syntax .................................................................................. 2010 13.1.26 DROP PROCEDURE and DROP FUNCTION Syntax .................................... 2010 13.1.27 DROP SERVER Syntax .............................................................................. 2011 13.1.28 DROP SPATIAL REFERENCE SYSTEM Syntax .......................................... 2011 13.1.29 DROP TABLE Syntax ................................................................................. 2012 13.1.30 DROP TABLESPACE Syntax ...................................................................... 2012 13.1.31 DROP TRIGGER Syntax ............................................................................. 2013 13.1.32 DROP VIEW Syntax ................................................................................... 2014 13.1.33 RENAME TABLE Syntax ............................................................................. 2014 13.1.34 TRUNCATE TABLE Syntax ......................................................................... 2015 13.2 Data Manipulation Statements .................................................................................. 2017 13.2.1 CALL Syntax ................................................................................................ 2017 13.2.2 DELETE Syntax ............................................................................................ 2018 13.2.3 DO Syntax ................................................................................................... 2022 13.2.4 HANDLER Syntax ......................................................................................... 2023 13.2.5 IMPORT TABLE Syntax ................................................................................ 2024 13.2.6 INSERT Syntax ............................................................................................ 2027 13.2.7 LOAD DATA INFILE Syntax .......................................................................... 2034 13.2.8 LOAD XML Syntax ....................................................................................... 2044 13.2.9 REPLACE Syntax ......................................................................................... 2051 13.2.10 SELECT Syntax .......................................................................................... 2054 13.2.11 Subquery Syntax ........................................................................................ 2069 13.2.12 UPDATE Syntax ......................................................................................... 2082 13.2.13 WITH Syntax (Common Table Expressions) ................................................. 2085 13.3 Transactional and Locking Statements ..................................................................... 2096 13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax ........................... 2096 13.3.2 Statements That Cannot Be Rolled Back ....................................................... 2099 13.3.3 Statements That Cause an Implicit Commit .................................................... 2099 13.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Syntax .................................................................................................................... 2100 13.3.5 LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Syntax ................ 2101 13.3.6 LOCK TABLES and UNLOCK TABLES Syntax .............................................. 2102 13.3.7 SET TRANSACTION Syntax ......................................................................... 2107 13.3.8 XA Transactions ........................................................................................... 2109 13.4 Replication Statements ............................................................................................ 2112 13.4.1 SQL Statements for Controlling Master Servers .............................................. 2112 13.4.2 SQL Statements for Controlling Slave Servers ............................................... 2115 13.4.3 SQL Statements for Controlling Group Replication .......................................... 2131 13.5 Prepared SQL Statement Syntax ............................................................................. 2132 13.5.1 PREPARE Syntax ......................................................................................... 2135 13.5.2 EXECUTE Syntax ......................................................................................... 2136 13.5.3 DEALLOCATE PREPARE Syntax .................................................................. 2136 13.6 Compound-Statement Syntax ................................................................................... 2137 13.6.1 BEGIN ... END Compound-Statement Syntax ................................................. 2137 13.6.2 Statement Label Syntax ................................................................................ 2137 13.6.3 DECLARE Syntax ......................................................................................... 2138 13.6.4 Variables in Stored Programs ........................................................................ 2138 MySQL 8.0 Reference Manual xiv 13.6.5 Flow Control Statements ............................................................................... 2140 13.6.6 Cursors ........................................................................................................ 2144 13.6.7 Condition Handling ........................................................................................ 2146 13.7 Database Administration Statements ........................................................................ 2173 13.7.1 Account Management Statements ................................................................. 2173 13.7.2 Resource Group Management Statements ..................................................... 2208 13.7.3 Table Maintenance Statements ..................................................................... 2211 13.7.4 Component, Plugin, and User-Defined Function Statements ............................ 2223 13.7.5 SET Syntax .................................................................................................. 2227 13.7.6 SHOW Syntax .............................................................................................. 2232 13.7.7 Other Administrative Statements .................................................................... 2283 13.8 Utility Statements .................................................................................................... 2294 13.8.1 DESCRIBE Syntax ........................................................................................ 2294 13.8.2 EXPLAIN Syntax .......................................................................................... 2294 13.8.3 HELP Syntax ................................................................................................ 2296 13.8.4 USE Syntax .................................................................................................. 2298 14 MySQL Data Dictionary ...................................................................................................... 2299 14.1 Data Dictionary Schema .......................................................................................... 2299 14.2 Removal of File-based Metadata Storage ................................................................. 2300 14.3 Transactional Storage of Dictionary Data .................................................................. 2301 14.4 Dictionary Object Cache .......................................................................................... 2301 14.5 INFORMATION_SCHEMA and Data Dictionary Integration ........................................ 2302 14.6 Serialized Dictionary Information (SDI) ..................................................................... 2304 14.7 Data Dictionary Usage Differences ........................................................................... 2304 14.8 Data Dictionary Limitations ....................................................................................... 2306 15 The InnoDB Storage Engine ............................................................................................... 2307 15.1 Introduction to InnoDB ............................................................................................. 2309 15.1.1 Benefits of Using InnoDB Tables ................................................................... 2310 15.1.2 Best Practices for InnoDB Tables .................................................................. 2311 15.1.3 Verifying that InnoDB is the Default Storage Engine ....................................... 2312 15.1.4 Testing and Benchmarking with InnoDB ......................................................... 2312 15.2 InnoDB and the ACID Model .................................................................................... 2312 15.3 InnoDB Multi-Versioning ........................................................................................... 2314 15.4 InnoDB Architecture ................................................................................................. 2315 15.4.1 Buffer Pool ................................................................................................... 2315 15.4.2 Change Buffer .............................................................................................. 2315 15.4.3 Adaptive Hash Index ..................................................................................... 2317 15.4.4 Redo Log Buffer ........................................................................................... 2318 15.4.5 System Tablespace ...................................................................................... 2318 15.4.6 Doublewrite Buffer ........................................................................................ 2318 15.4.7 Undo Logs ................................................................................................... 2319 15.4.8 File-Per-Table Tablespaces ........................................................................... 2319 15.4.9 General Tablespaces .................................................................................... 2319 15.4.10 Undo Tablespace ........................................................................................ 2319 15.4.11 Temporary Tablespace ................................................................................ 2319 15.4.12 Redo Log ................................................................................................... 2321 15.5 InnoDB Locking and Transaction Model .................................................................... 2322 15.5.1 InnoDB Locking ............................................................................................ 2322 15.5.2 InnoDB Transaction Model ............................................................................ 2326 15.5.3 Locks Set by Different SQL Statements in InnoDB .......................................... 2335 15.5.4 Phantom Rows ............................................................................................. 2338 15.5.5 Deadlocks in InnoDB .................................................................................... 2339 15.6 InnoDB Configuration ............................................................................................... 2342 15.6.1 InnoDB Startup Configuration ........................................................................ 2342 15.6.2 Configuring InnoDB for Read-Only Operation ................................................. 2348 15.6.3 InnoDB Buffer Pool Configuration .................................................................. 2349 15.6.4 Configuring InnoDB Change Buffering ............................................................ 2368 15.6.5 Configuring Thread Concurrency for InnoDB .................................................. 2370 MySQL 8.0 Reference Manual xv 15.6.6 Configuring the Number of Background InnoDB I/O Threads ........................... 2371 15.6.7 Using Asynchronous I/O on Linux .................................................................. 2371 15.6.8 Configuring the InnoDB Master Thread I/O Rate ............................................. 2372 15.6.9 Configuring Spin Lock Polling ........................................................................ 2372 15.6.10 Configuring InnoDB Purge Scheduling ......................................................... 2373 15.6.11 Configuring Optimizer Statistics for InnoDB .................................................. 2373 15.6.12 Configuring the Merge Threshold for Index Pages ........................................ 2384 15.6.13 Enabling Automatic Configuration for a Dedicated MySQL Server .................. 2387 15.7 InnoDB Tablespaces ................................................................................................ 2388 15.7.1 Resizing the InnoDB System Tablespace ....................................................... 2388 15.7.2 Changing the Number or Size of InnoDB Redo Log Files ................................ 2389 15.7.3 Using Raw Disk Partitions for the System Tablespace .................................... 2389 15.7.4 InnoDB File-Per-Table Tablespaces ............................................................... 2391 15.7.5 Creating a Tablespace Outside of the Data Directory ...................................... 2393 15.7.6 Copying File-Per-Table Tablespaces to Another Instance ................................ 2394 15.7.7 Moving Tablespace Files While the Server is Offline ....................................... 2402 15.7.8 Configuring Undo Tablespaces ...................................................................... 2404 15.7.9 Truncating Undo Tablespaces ....................................................................... 2404 15.7.10 InnoDB General Tablespaces ...................................................................... 2406 15.7.11 InnoDB Tablespace Encryption .................................................................... 2411 15.8 InnoDB Tables and Indexes ..................................................................................... 2418 15.8.1 InnoDB Tables .............................................................................................. 2419 15.8.2 InnoDB Indexes ............................................................................................ 2442 15.9 InnoDB Table and Page Compression ...................................................................... 2449 15.9.1 InnoDB Table Compression ........................................................................... 2449 15.9.2 InnoDB Page Compression ........................................................................... 2463 15.10 InnoDB Row Storage and Row Formats ................................................................. 2467 15.10.1 Overview of InnoDB Row Storage ............................................................... 2467 15.10.2 Specifying the Row Format for a Table ........................................................ 2467 15.10.3 DYNAMIC and COMPRESSED Row Formats .............................................. 2469 15.10.4 COMPACT and REDUNDANT Row Formats ................................................ 2470 15.11 InnoDB Disk I/O and File Space Management ........................................................ 2470 15.11.1 InnoDB Disk I/O .......................................................................................... 2471 15.11.2 File Space Management ............................................................................. 2471 15.11.3 InnoDB Checkpoints ................................................................................... 2473 15.11.4 Defragmenting a Table ................................................................................ 2473 15.11.5 Reclaiming Disk Space with TRUNCATE TABLE .......................................... 2474 15.12 InnoDB and Online DDL ........................................................................................ 2474 15.12.1 Online DDL Operations ............................................................................... 2475 15.12.2 Online DDL Performance and Concurrency .................................................. 2489 15.12.3 Online DDL Space Requirements ................................................................ 2492 15.12.4 Simplifying DDL Statements with Online DDL ............................................... 2493 15.12.5 Online DDL Failure Conditions .................................................................... 2493 15.12.6 Online DDL Limitations ................................................................................ 2494 15.13 InnoDB Startup Options and System Variables ........................................................ 2494 15.14 InnoDB INFORMATION_SCHEMA Tables .............................................................. 2582 15.14.1 InnoDB INFORMATION_SCHEMA Tables about Compression ...................... 2583 15.14.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information ........ 2584 15.14.3 InnoDB INFORMATION_SCHEMA Schema Object Tables ............................ 2591 15.14.4 InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables ......................... 2596 15.14.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables .................................. 2599 15.14.6 InnoDB INFORMATION_SCHEMA Metrics Table ......................................... 2604 15.14.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table .................... 2612 15.14.8 Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES ............................................................................. 2613 15.15 InnoDB Integration with MySQL Performance Schema ............................................ 2614 15.15.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema .................................................................................................................. 2616 MySQL 8.0 Reference Manual xvi 15.15.2 Monitoring InnoDB Mutex Waits Using Performance Schema ........................ 2618 15.16 InnoDB Monitors .................................................................................................... 2621 15.16.1 InnoDB Monitor Types ................................................................................ 2622 15.16.2 Enabling InnoDB Monitors ........................................................................... 2622 15.16.3 InnoDB Standard Monitor and Lock Monitor Output ...................................... 2624 15.17 InnoDB Backup and Recovery ................................................................................ 2628 15.17.1 InnoDB Backup ........................................................................................... 2628 15.17.2 InnoDB Recovery ........................................................................................ 2629 15.18 InnoDB and MySQL Replication ............................................................................. 2632 15.19 InnoDB memcached Plugin .................................................................................... 2634 15.19.1 Benefits of the InnoDB memcached Plugin ................................................... 2634 15.19.2 InnoDB memcached Architecture ................................................................. 2635 15.19.3 Setting Up the InnoDB memcached Plugin ................................................... 2639 15.19.4 InnoDB memcached Multiple get and Range Query Support .......................... 2644 15.19.5 Security Considerations for the InnoDB memcached Plugin ........................... 2647 15.19.6 Writing Applications for the InnoDB memcached Plugin ................................. 2648 15.19.7 The InnoDB memcached Plugin and Replication ........................................... 2661 15.19.8 InnoDB memcached Plugin Internals ............................................................ 2664 15.19.9 Troubleshooting the InnoDB memcached Plugin ........................................... 2668 15.20 InnoDB Troubleshooting ......................................................................................... 2670 15.20.1 Troubleshooting InnoDB I/O Problems ......................................................... 2671 15.20.2 Forcing InnoDB Recovery ........................................................................... 2671 15.20.3 Troubleshooting InnoDB Data Dictionary Operations ..................................... 2673 15.20.4 InnoDB Error Handling ................................................................................ 2674 16 Alternative Storage Engines ................................................................................................ 2677 16.1 Setting the Storage Engine ...................................................................................... 2680 16.2 The MyISAM Storage Engine ................................................................................... 2681 16.2.1 MyISAM Startup Options ............................................................................... 2684 16.2.2 Space Needed for Keys ................................................................................ 2686 16.2.3 MyISAM Table Storage Formats .................................................................... 2686 16.2.4 MyISAM Table Problems ............................................................................... 2688 16.3 The MEMORY Storage Engine ................................................................................ 2690 16.4 The CSV Storage Engine ........................................................................................ 2694 16.4.1 Repairing and Checking CSV Tables ............................................................. 2695 16.4.2 CSV Limitations ............................................................................................ 2696 16.5 The ARCHIVE Storage Engine ................................................................................. 2696 16.6 The BLACKHOLE Storage Engine ........................................................................... 2697 16.7 The MERGE Storage Engine ................................................................................... 2700 16.7.1 MERGE Table Advantages and Disadvantages .............................................. 2702 16.7.2 MERGE Table Problems ............................................................................... 2703 16.8 The FEDERATED Storage Engine ........................................................................... 2704 16.8.1 FEDERATED Storage Engine Overview ......................................................... 2705 16.8.2 How to Create FEDERATED Tables .............................................................. 2706 16.8.3 FEDERATED Storage Engine Notes and Tips ................................................ 2708 16.8.4 FEDERATED Storage Engine Resources ....................................................... 2710 16.9 The EXAMPLE Storage Engine ................................................................................ 2710 16.10 Other Storage Engines .......................................................................................... 2710 16.11 Overview of MySQL Storage Engine Architecture .................................................... 2710 16.11.1 Pluggable Storage Engine Architecture ........................................................ 2711 16.11.2 The Common Database Server Layer .......................................................... 2711 17 Replication ......................................................................................................................... 2713 17.1 Configuring Replication ............................................................................................ 2714 17.1.1 Binary Log File Position Based Replication Configuration Overview ................. 2715 17.1.2 Setting Up Binary Log File Position Based Replication .................................... 2715 17.1.3 Replication with Global Transaction Identifiers ................................................ 2725 17.1.4 MySQL Multi-Source Replication ................................................................... 2740 17.1.5 Changing Replication Modes on Online Servers ............................................. 2744 17.1.6 Replication and Binary Logging Options and Variables .................................... 2749 MySQL 8.0 Reference Manual xvii 17.1.7 Common Replication Administration Tasks ..................................................... 2840 17.2 Replication Implementation ...................................................................................... 2843 17.2.1 Replication Formats ...................................................................................... 2843 17.2.2 Replication Implementation Details ................................................................ 2850 17.2.3 Replication Channels .................................................................................... 2852 17.2.4 Replication Relay and Status Logs ................................................................ 2855 17.2.5 How Servers Evaluate Replication Filtering Rules ........................................... 2860 17.3 Replication Solutions ............................................................................................... 2868 17.3.1 Using Replication for Backups ....................................................................... 2868 17.3.2 Handling an Unexpected Halt of a Replication Slave ....................................... 2872 17.3.3 Monitoring Row-based Replication ................................................................. 2874 17.3.4 Using Replication with Different Master and Slave Storage Engines ................. 2874 17.3.5 Using Replication for Scale-Out ..................................................................... 2875 17.3.6 Replicating Different Databases to Different Slaves ........................................ 2877 17.3.7 Improving Replication Performance ................................................................ 2878 17.3.8 Switching Masters During Failover ................................................................. 2879 17.3.9 Setting Up Replication to Use Encrypted Connections .................................... 2881 17.3.10 Semisynchronous Replication ...................................................................... 2883 17.3.11 Delayed Replication .................................................................................... 2888 17.4 Replication Notes and Tips ...................................................................................... 2891 17.4.1 Replication Features and Issues .................................................................... 2891 17.4.2 Replication Compatibility Between MySQL Versions ....................................... 2917 17.4.3 Upgrading a Replication Setup ...................................................................... 2918 17.4.4 Troubleshooting Replication .......................................................................... 2919 17.4.5 How to Report Replication Bugs or Problems ................................................. 2921 18 Group Replication .............................................................................................................. 2923 18.1 Group Replication Background ................................................................................. 2924 18.1.1 Replication Technologies ............................................................................... 2924 18.1.2 Group Replication Use Cases ....................................................................... 2926 18.1.3 Group Replication Details .............................................................................. 2927 18.2 Getting Started ........................................................................................................ 2928 18.2.1 Deploying Group Replication in Single-Primary Mode ...................................... 2928 18.3 Monitoring Group Replication ................................................................................... 2939 18.3.1 Replication_group_member_stats .................................................................. 2939 18.3.2 Replication_group_members ......................................................................... 2940 18.3.3 Replication_connection_status ....................................................................... 2941 18.3.4 Replication_applier_status ............................................................................. 2941 18.3.5 Group Replication Server States .................................................................... 2942 18.4 Group Replication Operations .................................................................................. 2942 18.4.1 Deploying in Multi-Primary or Single-Primary Mode ......................................... 2943 18.4.2 Tuning Recovery .......................................................................................... 2945 18.4.3 Network Partitioning ...................................................................................... 2946 18.4.4 Using MySQL Enterprise Backup with Group Replication ................................ 2950 18.5 Group Replication Security ....................................................................................... 2952 18.5.1 IP Address Whitelisting ................................................................................. 2952 18.5.2 Secure Socket Layer Support (SSL) .............................................................. 2953 18.5.3 Virtual Private Networks (VPN) ...................................................................... 2957 18.6 Group Replication System Variables ......................................................................... 2957 18.7 Requirements and Limitations .................................................................................. 2976 18.7.1 Group Replication Requirements ................................................................... 2976 18.7.2 Group Replication Limitations ........................................................................ 2977 18.8 Frequently Asked Questions .................................................................................... 2978 18.9 Group Replication Technical Details ......................................................................... 2982 18.9.1 Group Replication Plugin Architecture ............................................................ 2982 18.9.2 The Group .................................................................................................... 2983 18.9.3 Data Manipulation Statements ....................................................................... 2983 18.9.4 Data Definition Statements ............................................................................ 2984 18.9.5 Distributed Recovery ..................................................................................... 2984 MySQL 8.0 Reference Manual xviii 18.9.6 Observability ................................................................................................. 2990 18.9.7 Group Replication Performance ..................................................................... 2991 19 MySQL Shell ...................................................................................................................... 2995 20 Using MySQL as a Document Store ................................................................................... 2997 20.1 Key Concepts .......................................................................................................... 2998 20.2 Setting Up MySQL as a Document Store .................................................................. 2999 20.2.1 Installing MySQL Shell .................................................................................. 2999 20.2.2 Starting MySQL Shell .................................................................................... 3001 20.3 Quick-Start Guide: MySQL Shell for JavaScript ......................................................... 3002 20.3.1 Introduction ................................................................................................... 3002 20.3.2 Import Database Sample ............................................................................... 3003 20.3.3 MySQL Shell ................................................................................................ 3004 20.3.4 Documents and Collections ........................................................................... 3005 20.3.5 Relational Tables .......................................................................................... 3015 20.3.6 Documents in Tables .................................................................................... 3021 20.4 Quick-Start Guide: MySQL Shell for Python .............................................................. 3022 20.4.1 Introduction ................................................................................................... 3022 20.4.2 Import Database Sample ............................................................................... 3023 20.4.3 MySQL Shell ................................................................................................ 3024 20.4.4 Documents and Collections ........................................................................... 3025 20.4.5 Relational Tables .......................................................................................... 3035 20.4.6 Documents in Tables .................................................................................... 3041 20.5 X Plugin .................................................................................................................. 3042 20.5.1 Checking X Plugin Installation ....................................................................... 3042 20.5.2 Disabling X Plugin ........................................................................................ 3042 20.5.3 Using Secure Connections with X Plugin ....................................................... 3042 20.5.4 Using X Plugin with the Caching SHA-2 Authentication Plugin ......................... 3043 20.5.5 X Plugin Options and Variables ..................................................................... 3043 20.5.6 Monitoring X Plugin ...................................................................................... 3058 21 InnoDB Cluster ................................................................................................................... 3063 21.1 Introducing InnoDB Cluster ...................................................................................... 3063 21.2 Creating an InnoDB Cluster ..................................................................................... 3065 21.2.1 Deployment Scenarios .................................................................................. 3065 21.2.2 InnoDB Cluster Requirements ....................................................................... 3066 21.2.3 Methods of Installing ..................................................................................... 3066 21.2.4 Production Deployment of InnoDB Cluster ..................................................... 3067 21.2.5 Sandbox Deployment of InnoDB Cluster ........................................................ 3076 21.2.6 Adopting a Group Replication Deployment ..................................................... 3079 21.3 Using MySQL Router with InnoDB Cluster ................................................................ 3079 21.4 Working with InnoDB Cluster ................................................................................... 3083 21.5 Known Limitations ................................................................................................... 3095 22 Partitioning ......................................................................................................................... 3097 22.1 Overview of Partitioning in MySQL ........................................................................... 3098 22.2 Partitioning Types .................................................................................................... 3101 22.2.1 RANGE Partitioning ...................................................................................... 3103 22.2.2 LIST Partitioning ........................................................................................... 3107 22.2.3 COLUMNS Partitioning ................................................................................. 3109 22.2.4 HASH Partitioning ......................................................................................... 3116 22.2.5 KEY Partitioning ........................................................................................... 3119 22.2.6 Subpartitioning .............................................................................................. 3121 22.2.7 How MySQL Partitioning Handles NULL ........................................................ 3122 22.3 Partition Management .............................................................................................. 3127 22.3.1 Management of RANGE and LIST Partitions .................................................. 3128 22.3.2 Management of HASH and KEY Partitions ..................................................... 3134 22.3.3 Exchanging Partitions and Subpartitions with Tables ....................................... 3135 22.3.4 Maintenance of Partitions .............................................................................. 3142 22.3.5 Obtaining Information About Partitions ........................................................... 3144 22.4 Partition Pruning ...................................................................................................... 3146 MySQL 8.0 Reference Manual xix 22.5 Partition Selection .................................................................................................... 3149 22.6 Restrictions and Limitations on Partitioning ............................................................... 3154 22.6.1 Partitioning Keys, Primary Keys, and Unique Keys ......................................... 3159 22.6.2 Partitioning Limitations Relating to Storage Engines ........................................ 3163 22.6.3 Partitioning Limitations Relating to Functions .................................................. 3163 23 Stored Programs and Views ............................................................................................... 3165 23.1 Defining Stored Programs ........................................................................................ 3166 23.2 Using Stored Routines (Procedures and Functions) ................................................... 3167 23.2.1 Stored Routine Syntax .................................................................................. 3167 23.2.2 Stored Routines and MySQL Privileges ......................................................... 3168 23.2.3 Stored Routine Metadata .............................................................................. 3168 23.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID() .................... 3169 23.3 Using Triggers ......................................................................................................... 3169 23.3.1 Trigger Syntax and Examples ........................................................................ 3170 23.3.2 Trigger Metadata .......................................................................................... 3173 23.4 Using the Event Scheduler ...................................................................................... 3174 23.4.1 Event Scheduler Overview ............................................................................ 3174 23.4.2 Event Scheduler Configuration ...................................................................... 3175 23.4.3 Event Syntax ................................................................................................ 3177 23.4.4 Event Metadata ............................................................................................ 3177 23.4.5 Event Scheduler Status ................................................................................. 3178 23.4.6 The Event Scheduler and MySQL Privileges .................................................. 3179 23.5 Using Views ............................................................................................................ 3181 23.5.1 View Syntax ................................................................................................. 3182 23.5.2 View Processing Algorithms .......................................................................... 3182 23.5.3 Updatable and Insertable Views .................................................................... 3183 23.5.4 The View WITH CHECK OPTION Clause ...................................................... 3186 23.5.5 View Metadata .............................................................................................. 3187 23.6 Access Control for Stored Programs and Views ........................................................ 3187 23.7 Binary Logging of Stored Programs .......................................................................... 3188 24 INFORMATION_SCHEMA Tables ....................................................................................... 3195 24.1 Introduction ............................................................................................................. 3196 24.2 The INFORMATION_SCHEMA CHARACTER_SETS Table ....................................... 3199 24.3 The INFORMATION_SCHEMA COLLATIONS Table ................................................. 3200 24.4 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table .............................................................................................................................. 3200 24.5 The INFORMATION_SCHEMA COLUMNS Table ..................................................... 3201 24.6 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table .................................. 3203 24.7 The INFORMATION_SCHEMA COLUMN_STATISTICS Table ................................... 3204 24.8 The INFORMATION_SCHEMA ENGINES Table ....................................................... 3205 24.9 The INFORMATION_SCHEMA EVENTS Table ......................................................... 3206 24.10 The INFORMATION_SCHEMA FILES Table ........................................................... 3209 24.11 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table ................................ 3212 24.12 The INFORMATION_SCHEMA KEYWORDS Table ................................................. 3214 24.13 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table ..................................... 3214 24.14 The INFORMATION_SCHEMA PARAMETERS Table ............................................. 3215 24.15 The INFORMATION_SCHEMA PARTITIONS Table ................................................ 3216 24.16 The INFORMATION_SCHEMA PLUGINS Table ...................................................... 3219 24.17 The INFORMATION_SCHEMA PROCESSLIST Table ............................................. 3220 24.18 The INFORMATION_SCHEMA PROFILING Table .................................................. 3221 24.19 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table .................... 3223 24.20 The INFORMATION_SCHEMA RESOURCE_GROUPS Table ................................. 3223 24.21 The INFORMATION_SCHEMA ROUTINES Table ................................................... 3224 24.22 The INFORMATION_SCHEMA SCHEMATA Table .................................................. 3226 24.23 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table ................................. 3227 24.24 The INFORMATION_SCHEMA STATISTICS Table ................................................. 3228 24.25 The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table ......................... 3230 24.26 The INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS Table ......... 3231 MySQL 8.0 Reference Manual xx 24.27 The INFORMATION_SCHEMA TABLES Table ....................................................... 3232 24.28 The INFORMATION_SCHEMA TABLESPACES Table ............................................ 3236 24.29 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table ................................ 3237 24.30 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table .................................... 3237 24.31 The INFORMATION_SCHEMA TRIGGERS Table ................................................... 3238 24.32 The INFORMATION_SCHEMA USER_PRIVILEGES Table ..................................... 3240 24.33 The INFORMATION_SCHEMA VIEWS Table ......................................................... 3240 24.34 The INFORMATION_SCHEMA VIEW_ROUTINE_USAGE Table .............................. 3242 24.35 The INFORMATION_SCHEMA VIEW_TABLE_USAGE Table .................................. 3242 24.36 INFORMATION_SCHEMA InnoDB Tables .............................................................. 3243 24.36.1 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table ................... 3243 24.36.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table .......... 3247 24.36.3 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table ...... 3250 24.36.4 The INFORMATION_SCHEMA INNODB_CACHED_INDEXES Table ............. 3253 24.36.5 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables ..................................................................................................................... 3254 24.36.6 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables ........................................................................ 3255 24.36.7 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables ........................................................... 3257 24.36.8 The INFORMATION_SCHEMA INNODB_COLUMNS Table .......................... 3258 24.36.9 The INFORMATION_SCHEMA INNODB_DATAFILES Table ......................... 3260 24.36.10 The INFORMATION_SCHEMA INNODB_FIELDS Table ............................. 3260 24.36.11 The INFORMATION_SCHEMA INNODB_FOREIGN Table .......................... 3261 24.36.12 The INFORMATION_SCHEMA INNODB_FOREIGN_COLS Table ............... 3262 24.36.13 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table ........ 3262 24.36.14 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table ...................... 3263 24.36.15 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table 3264 24.36.16 The INFORMATION_SCHEMA INNODB_FT_DELETED Table .................... 3265 24.36.17 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table ............ 3266 24.36.18 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table ............. 3267 24.36.19 The INFORMATION_SCHEMA INNODB_INDEXES Table .......................... 3269 24.36.20 The INFORMATION_SCHEMA INNODB_LOCKS Table .............................. 3270 24.36.21 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table .................... 3271 24.36.22 The INFORMATION_SCHEMA INNODB_METRICS Table .......................... 3271 24.36.23 The INFORMATION_SCHEMA INNODB_SESSION_TEMP_TABLESPACES Table ...................................................................................................................... 3273 24.36.24 The INFORMATION_SCHEMA INNODB_TABLES Table ............................ 3274 24.36.25 The INFORMATION_SCHEMA INNODB_TABLESPACES Table ................. 3275 24.36.26 The INFORMATION_SCHEMA INNODB_TABLESPACES_BRIEF Table ...... 3277 24.36.27 The INFORMATION_SCHEMA INNODB_TABLESTATS View ..................... 3278 24.36.28 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table .......... 3279 24.36.29 The INFORMATION_SCHEMA INNODB_TRX Table .................................. 3280 24.36.30 The INFORMATION_SCHEMA INNODB_VIRTUAL Table ........................... 3283 24.37 INFORMATION_SCHEMA Thread Pool Tables ....................................................... 3284 24.37.1 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table ............ 3285 24.37.2 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table ............ 3285 24.37.3 The INFORMATION_SCHEMA TP_THREAD_STATE Table ......................... 3285 24.38 INFORMATION_SCHEMA Connection-Control Tables ............................................. 3286 24.38.1 The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Table ............................... 3286 24.39 Extensions to SHOW Statements ........................................................................... 3286 25 MySQL Performance Schema ............................................................................................. 3289 25.1 Performance Schema Quick Start ............................................................................ 3291 25.2 Performance Schema Build Configuration ................................................................. 3297 25.3 Performance Schema Startup Configuration .............................................................. 3297 25.4 Performance Schema Runtime Configuration ............................................................ 3299 25.4.1 Performance Schema Event Timing ............................................................... 3300 MySQL 8.0 Reference Manual xxi 25.4.2 Performance Schema Event Filtering ............................................................. 3302 25.4.3 Event Pre-Filtering ........................................................................................ 3304 25.4.4 Pre-Filtering by Instrument ............................................................................ 3304 25.4.5 Pre-Filtering by Object .................................................................................. 3306 25.4.6 Pre-Filtering by Thread ................................................................................. 3308 25.4.7 Pre-Filtering by Consumer ............................................................................. 3310 25.4.8 Example Consumer Configurations ................................................................ 3312 25.4.9 Naming Instruments or Consumers for Filtering Operations ............................. 3317 25.4.10 Determining What Is Instrumented ............................................................... 3318 25.5 Performance Schema Queries ................................................................................. 3318 25.6 Performance Schema Instrument Naming Conventions .............................................. 3319 25.7 Performance Schema Status Monitoring ................................................................... 3322 25.8 Performance Schema Atom and Molecule Events ..................................................... 3325 25.9 Performance Schema Statement Digests and Sampling ............................................ 3326 25.10 Performance Schema General Table Characteristics ............................................... 3330 25.11 Performance Schema Table Descriptions ................................................................ 3331 25.11.1 Performance Schema Table Index ............................................................... 3332 25.11.2 Performance Schema Setup Tables ............................................................. 3335 25.11.3 Performance Schema Instance Tables ......................................................... 3342 25.11.4 Performance Schema Wait Event Tables ..................................................... 3348 25.11.5 Performance Schema Stage Event Tables ................................................... 3353 25.11.6 Performance Schema Statement Event Tables ............................................. 3358 25.11.7 Performance Schema Transaction Tables .................................................... 3368 25.11.8 Performance Schema Connection Tables ..................................................... 3374 25.11.9 Performance Schema Connection Attribute Tables ....................................... 3378 25.11.10 Performance Schema User-Defined Variable Tables ................................... 3381 25.11.11 Performance Schema Replication Tables ................................................... 3381 25.11.12 Performance Schema Lock Tables ............................................................. 3397 25.11.13 Performance Schema System Variable Tables ........................................... 3406 25.11.14 Performance Schema Status Variable Tables ............................................. 3410 25.11.15 Performance Schema Thread Pool Tables ................................................. 3411 25.11.16 Performance Schema Summary Tables ..................................................... 3417 25.11.17 Performance Schema Miscellaneous Tables ............................................... 3444 25.12 Performance Schema Option and Variable Reference ............................................. 3454 25.13 Performance Schema Command Options ............................................................... 3456 25.14 Performance Schema System Variables ................................................................. 3458 25.15 Performance Schema Status Variables ................................................................... 3475 25.16 The Performance Schema Memory-Allocation Model ............................................... 3478 25.17 Performance Schema and Plugins .......................................................................... 3479 25.18 Using the Performance Schema to Diagnose Problems ........................................... 3479 25.18.1 Query Profiling Using Performance Schema ................................................. 3481 25.18.2 Obtaining Parent Event Information .............................................................. 3482 26 MySQL sys Schema ........................................................................................................... 3485 26.1 Prerequisites for Using the sys Schema ................................................................... 3485 26.2 Using the sys Schema ............................................................................................. 3486 26.3 sys Schema Progress Reporting .............................................................................. 3487 26.4 sys Schema Object Reference ................................................................................. 3488 26.4.1 sys Schema Object Index ............................................................................. 3488 26.4.2 sys Schema Tables and Triggers .................................................................. 3492 26.4.3 sys Schema Views ....................................................................................... 3494 26.4.4 sys Schema Stored Procedures .................................................................... 3534 26.4.5 sys Schema Stored Functions ....................................................................... 3552 27 Connectors and APIs ......................................................................................................... 3565 27.1 MySQL Connector/C ................................................................................................ 3568 27.2 MySQL Connector/C ............................................................................................ 3568 27.3 MySQL Connector/J ................................................................................................ 3568 27.4 MySQL Connector/NET ........................................................................................... 3568 27.5 MySQL Connector/ODBC ......................................................................................... 3568 MySQL 8.0 Reference Manual xxii 27.6 MySQL Connector/Python ........................................................................................ 3568 27.7 MySQL C API ......................................................................................................... 3569 27.7.1 MySQL C API Implementations ..................................................................... 3569 27.7.2 Simultaneous MySQL Server and Connector/C Installations ............................ 3570 27.7.3 Example C API Client Programs .................................................................... 3571 27.7.4 Building and Running C API Client Programs ................................................. 3571 27.7.5 C API Data Structures .................................................................................. 3577 27.7.6 C API Function Overview .............................................................................. 3582 27.7.7 C API Function Descriptions .......................................................................... 3587 27.7.8 C API Prepared Statements .......................................................................... 3650 27.7.9 C API Prepared Statement Data Structures .................................................... 3651 27.7.10 C API Prepared Statement Function Overview ............................................. 3657 27.7.11 C API Prepared Statement Function Descriptions ......................................... 3659 27.7.12 C API Threaded Function Descriptions ......................................................... 3682 27.7.13 C API Client Plugin Functions ..................................................................... 3683 27.7.14 C API Binary Log Interface .......................................................................... 3686 27.7.15 C API Binary Log Data Structures ............................................................... 3686 27.7.16 C API Binary Log Function Overview ........................................................... 3688 27.7.17 C API Binary Log Function Descriptions ....................................................... 3688 27.7.18 C API Encrypted Connection Support .......................................................... 3690 27.7.19 C API Multiple Statement Execution Support ................................................ 3692 27.7.20 C API Prepared Statement Handling of Date and Time Values ....................... 3694 27.7.21 C API Prepared CALL Statement Support .................................................... 3695 27.7.22 C API Prepared Statement Problems ........................................................... 3698 27.7.23 C API Optional Result Set Metadata ............................................................ 3699 27.7.24 C API Automatic Reconnection Control ........................................................ 3699 27.7.25 C API Common Issues ................................................................................ 3700 27.8 MySQL PHP API ..................................................................................................... 3702 27.9 MySQL Perl API ...................................................................................................... 3702 27.10 MySQL Python API ................................................................................................ 3703 27.11 MySQL Ruby APIs ................................................................................................ 3703 27.11.1 The MySQL/Ruby API ................................................................................. 3703 27.11.2 The Ruby/MySQL API ................................................................................. 3704 27.12 MySQL Tcl API ..................................................................................................... 3704 27.13 MySQL Eiffel Wrapper ........................................................................................... 3704 28 Extending MySQL .............................................................................................................. 3705 28.1 MySQL Internals ...................................................................................................... 3705 28.1.1 MySQL Threads ........................................................................................... 3705 28.1.2 The MySQL Test Suite ................................................................................. 3706 28.2 The MySQL Plugin API ............................................................................................ 3707 28.2.1 Types of Plugins ........................................................................................... 3707 28.2.2 Plugin API Characteristics ............................................................................. 3712 28.2.3 Plugin API Components ................................................................................ 3713 28.2.4 Writing Plugins ............................................................................................. 3714 28.3 MySQL Services for Plugins .................................................................................... 3768 28.3.1 The Locking Service ..................................................................................... 3770 28.3.2 The Keyring Service ..................................................................................... 3775 28.4 Adding New Functions to MySQL ............................................................................. 3777 28.4.1 Features of the User-Defined Function Interface ............................................. 3778 28.4.2 Adding a New User-Defined Function ............................................................ 3779 28.4.3 Adding a New Native Function ...................................................................... 3788 28.5 Debugging and Porting MySQL ................................................................................ 3790 28.5.1 Debugging a MySQL Server .......................................................................... 3790 28.5.2 Debugging a MySQL Client ........................................................................... 3797 28.5.3 The DBUG Package ..................................................................................... 3797 29 MySQL Enterprise Edition ................................................................................................... 3801 29.1 MySQL Enterprise Monitor Overview ........................................................................ 3801 29.2 MySQL Enterprise Backup Overview ........................................................................ 3802 MySQL 8.0 Reference Manual xxiii 29.3 MySQL Enterprise Security Overview ....................................................................... 3803 29.4 MySQL Enterprise Encryption Overview ................................................................... 3803 29.5 MySQL Enterprise Audit Overview ........................................................................... 3803 29.6 MySQL Enterprise Firewall Overview ........................................................................ 3803 29.7 MySQL Enterprise Thread Pool Overview ................................................................. 3804 30 MySQL Workbench ............................................................................................................ 3805 A MySQL 8.0 Frequently Asked Questions ............................................................................... 3807 A.1 MySQL 8.0 FAQ: General ......................................................................................... 3807 A.2 MySQL 8.0 FAQ: Storage Engines ............................................................................ 3808 A.3 MySQL 8.0 FAQ: Server SQL Mode .......................................................................... 3809 A.4 MySQL 8.0 FAQ: Stored Procedures and Functions ................................................... 3810 A.5 MySQL 8.0 FAQ: Triggers ......................................................................................... 3814 A.6 MySQL 8.0 FAQ: Views ............................................................................................ 3816 A.7 MySQL 8.0 FAQ: INFORMATION_SCHEMA .............................................................. 3817 A.8 MySQL 8.0 FAQ: Migration ....................................................................................... 3817 A.9 MySQL 8.0 FAQ: Security ......................................................................................... 3818 A.10 MySQL 8.0 FAQ: NDB Cluster ................................................................................. 3819 A.11 MySQL 8.0 FAQ: MySQL Chinese, Japanese, and Korean Character Sets ................. 3820 A.12 MySQL 8.0 FAQ: Connectors & APIs ....................................................................... 3830 A.13 MySQL 8.0 FAQ: Replication ................................................................................... 3830 A.14 MySQL 8.0 FAQ: MySQL Enterprise Thread Pool ..................................................... 3834 A.15 MySQL 8.0 FAQ: InnoDB Change Buffer .................................................................. 3835 A.16 MySQL 8.0 FAQ: InnoDB Tablespace Encryption ..................................................... 3837 A.17 MySQL 8.0 FAQ: Virtualization Support .................................................................... 3839 B Errors, Error Codes, and Common Problems ........................................................................ 3841 B.1 Sources of Error Information ...................................................................................... 3841 B.2 Types of Error Values ............................................................................................... 3841 B.3 Server Error Codes and Messages ............................................................................ 3842 B.4 Client Error Codes and Messages ............................................................................. 4268 B.5 Problems and Common Errors .................................................................................. 4273 B.5.1 How to Determine What Is Causing a Problem ................................................ 4273 B.5.2 Common Errors When Using MySQL Programs ............................................... 4274 B.5.3 Administration-Related Issues ......................................................................... 4285 B.5.4 Query-Related Issues ..................................................................................... 4293 B.5.5 Optimizer-Related Issues ................................................................................ 4300 B.5.6 Table Definition-Related Issues ....................................................................... 4300 B.5.7 Known Issues in MySQL ................................................................................ 4301 C Restrictions and Limits ......................................................................................................... 4305 C.1 Restrictions on Stored Programs ............................................................................... 4305 C.2 Restrictions on Condition Handling ............................................................................ 4308 C.3 Restrictions on Server-Side Cursors .......................................................................... 4308 C.4 Restrictions on Subqueries ........................................................................................ 4309 C.5 Restrictions on Views ................................................................................................ 4310 C.6 Restrictions on XA Transactions ................................................................................ 4311 C.7 Restrictions on Character Sets .................................................................................. 4313 C.8 Restrictions on Performance Schema ........................................................................ 4313 C.9 Restrictions on Pluggable Authentication .................................................................... 4313 C.10 Limits in MySQL ..................................................................................................... 4315 C.10.1 Limits on Joins ............................................................................................. 4315 C.10.2 Limits on Number of Databases and Tables .................................................. 4315 C.10.3 Limits on Table Size ..................................................................................... 4315 C.10.4 Limits on Table Column Count and Row Size ................................................ 4316 C.10.5 Windows Platform Limitations ....................................................................... 4319 D Indexes ............................................................................................................................... 4321 MySQL Glossary ..................................................................................................................... 5059