MySQL 8.4 Reference Manual
Including MySQL NDB Cluster 8.4
Abstract
This is the MySQL Reference Manual. It documents MySQL 8.4 (8.4.0), as well as NDB Cluster 8.4 (8.4.0),
respectively. It may include documentation of features of MySQL versions that have not yet been released. For
information about which versions have been released, see the MySQL 8.4 Release Notes.
MySQL 8.4 features. This manual describes features that are not included in every edition of MySQL 8.4; such
features may not be included in the edition of MySQL 8.4 licensed to you. If you have any questions about the
features included in your edition of MySQL 8.4, refer to your MySQL 8.4 license agreement or contact your Oracle
sales representative.
For notes detailing the changes in each release, see the MySQL 8.4 Release Notes.
For legal information, including licensing information, see the Preface and Legal Notices.
For help with using MySQL, please visit the MySQL Forums, where you can discuss your issues with other
MySQL users.
Document generated on: 2024-05-18 (revision: 78646)
Table of Contents
Preface and Legal Notices ....................................................................................................... xxvii
1 General Information ................................................................................................................... 1
1.1 About This Manual .......................................................................................................... 2
1.2 Overview of the MySQL Database Management System ................................................... 4
1.2.1 What is MySQL? .................................................................................................. 4
1.2.2 The Main Features of MySQL ............................................................................... 5
1.2.3 History of MySQL ................................................................................................. 8
1.3 MySQL Releases: Innovation and LTS ............................................................................. 9
1.4 What Is New in MySQL 8.4 since MySQL 8.0 ................................................................ 10
1.5 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL
8.4 since 8.0 ...................................................................................................................... 32
1.6 How to Report Bugs or Problems .................................................................................. 39
1.7 MySQL Standards Compliance ...................................................................................... 43
1.7.1 MySQL Extensions to Standard SQL ................................................................... 45
1.7.2 MySQL Differences from Standard SQL .............................................................. 47
1.7.3 How MySQL Deals with Constraints .................................................................... 51
2 Installing MySQL ..................................................................................................................... 55
2.1 General Installation Guidance ........................................................................................ 57
2.1.1 Supported Platforms ........................................................................................... 57
2.1.2 Which MySQL Version and Distribution to Install .................................................. 57
2.1.3 How to Get MySQL ............................................................................................ 58
2.1.4 Verifying Package Integrity Using MD5 Checksums or GnuPG .............................. 59
2.1.5 Installation Layouts ............................................................................................. 74
2.1.6 Compiler-Specific Build Characteristics ................................................................ 74
2.2 Installing MySQL on Unix/Linux Using Generic Binaries .................................................. 74
2.3 Installing MySQL on Microsoft Windows ......................................................................... 77
2.3.1 Choosing an Installation Package ....................................................................... 79
2.3.2 Configuration: Using MySQL Configurator ............................................................ 80
2.3.3 Configuration: Manually ...................................................................................... 86
2.3.4 Troubleshooting a Microsoft Windows MySQL Server Installation .......................... 94
2.3.5 Windows Postinstallation Procedures .................................................................. 95
2.3.6 Windows Platform Restrictions ............................................................................ 97
2.4 Installing MySQL on macOS .......................................................................................... 99
2.4.1 General Notes on Installing MySQL on macOS .................................................... 99
2.4.2 Installing MySQL on macOS Using Native Packages .......................................... 100
2.4.3 Installing and Using the MySQL Launch Daemon ............................................... 102
2.4.4 Installing and Using the MySQL Preference Pane .............................................. 105
2.5 Installing MySQL on Linux ........................................................................................... 109
2.5.1 Installing MySQL on Linux Using the MySQL Yum Repository ............................. 110
2.5.2 Installing MySQL on Linux Using the MySQL APT Repository ............................. 115
2.5.3 Using the MySQL SLES Repository .................................................................. 124
2.5.4 Installing MySQL on Linux Using RPM Packages from Oracle ............................. 129
2.5.5 Installing MySQL on Linux Using Debian Packages from Oracle .......................... 134
2.5.6 Deploying MySQL on Linux with Docker Containers ........................................... 135
2.5.7 Installing MySQL on Linux from the Native Software Repositories ........................ 147
2.5.8 Installing MySQL on Linux with Juju .................................................................. 149
2.5.9 Managing MySQL Server with systemd ............................................................. 149
2.6 Installing MySQL Using Unbreakable Linux Network (ULN) ........................................... 154
2.7 Installing MySQL on Solaris ......................................................................................... 155
2.7.1 Installing MySQL on Solaris Using a Solaris PKG .............................................. 155
2.8 Installing MySQL from Source ..................................................................................... 156
2.8.1 Source Installation Methods .............................................................................. 157
2.8.2 Source Installation Prerequisites ....................................................................... 157
2.8.3 MySQL Layout for Source Installation ................................................................ 158
2.8.4 Installing MySQL Using a Standard Source Distribution ...................................... 159
iii
MySQL 8.4 Reference Manual
2.8.5 Installing MySQL Using a Development Source Tree .......................................... 163
2.8.6 Configuring SSL Library Support ....................................................................... 164
2.8.7 MySQL Source-Configuration Options ............................................................... 165
2.8.8 Dealing with Problems Compiling MySQL .......................................................... 189
2.8.9 MySQL Configuration and Third-Party Tools ...................................................... 190
2.8.10 Generating MySQL Doxygen Documentation Content ....................................... 191
2.9 Postinstallation Setup and Testing ............................................................................... 191
2.9.1 Initializing the Data Directory ............................................................................ 192
2.9.2 Starting the Server ........................................................................................... 197
2.9.3 Testing the Server ............................................................................................ 200
2.9.4 Securing the Initial MySQL Account .................................................................. 202
2.9.5 Starting and Stopping MySQL Automatically ...................................................... 203
2.10 Perl Installation Notes ................................................................................................ 204
2.10.1 Installing Perl on Unix ..................................................................................... 205
2.10.2 Installing ActiveState Perl on Windows ............................................................ 206
2.10.3 Problems Using the Perl DBI/DBD Interface ..................................................... 206
3 Upgrading MySQL ................................................................................................................. 209
3.1 Before You Begin ........................................................................................................ 209
3.2 Upgrade Paths ............................................................................................................ 210
3.3 Upgrade Best Practices ............................................................................................... 211
3.4 What the MySQL Upgrade Process Upgrades .............................................................. 213
3.5 Changes in MySQL 8.4 ............................................................................................... 216
3.6 Preparing Your Installation for Upgrade ........................................................................ 216
3.7 Upgrading MySQL Binary or Package-based Installations on Unix/Linux ......................... 218
3.8 Upgrading MySQL with the MySQL Yum Repository ..................................................... 222
3.9 Upgrading MySQL with the MySQL APT Repository ..................................................... 224
3.10 Upgrading MySQL with the MySQL SLES Repository .................................................. 224
3.11 Upgrading MySQL on Windows ................................................................................. 224
3.12 Upgrading a Docker Installation of MySQL ................................................................. 225
3.13 Upgrade Troubleshooting ........................................................................................... 225
3.14 Rebuilding or Repairing Tables or Indexes ................................................................. 226
3.15 Copying MySQL Databases to Another Machine ......................................................... 227
4 Downgrading MySQL ............................................................................................................. 229
5 Tutorial .................................................................................................................................. 231
5.1 Connecting to and Disconnecting from the Server ......................................................... 231
5.2 Entering Queries ......................................................................................................... 232
5.3 Creating and Using a Database ................................................................................... 235
5.3.1 Creating and Selecting a Database ................................................................... 236
5.3.2 Creating a Table .............................................................................................. 237
5.3.3 Loading Data into a Table ................................................................................ 238
5.3.4 Retrieving Information from a Table ................................................................... 239
5.4 Getting Information About Databases and Tables ......................................................... 252
5.5 Using mysql in Batch Mode ......................................................................................... 253
5.6 Examples of Common Queries .................................................................................... 254
5.6.1 The Maximum Value for a Column .................................................................... 255
5.6.2 The Row Holding the Maximum of a Certain Column .......................................... 255
5.6.3 Maximum of Column per Group ........................................................................ 255
5.6.4 The Rows Holding the Group-wise Maximum of a Certain Column ....................... 256
5.6.5 Using User-Defined Variables ........................................................................... 257
5.6.6 Using Foreign Keys .......................................................................................... 257
5.6.7 Searching on Two Keys .................................................................................... 259
5.6.8 Calculating Visits Per Day ................................................................................. 259
5.6.9 Using AUTO_INCREMENT ............................................................................... 260
5.7 Using MySQL with Apache .......................................................................................... 262
6 MySQL Programs .................................................................................................................. 265
6.1 Overview of MySQL Programs ..................................................................................... 266
6.2 Using MySQL Programs .............................................................................................. 269
6.2.1 Invoking MySQL Programs ............................................................................... 269
iv
MySQL 8.4 Reference Manual
6.2.2 Specifying Program Options .............................................................................. 270
6.2.3 Command Options for Connecting to the Server ................................................ 284
6.2.4 Connecting to the MySQL Server Using Command Options ................................ 296
6.2.5 Connecting to the Server Using URI-Like Strings or Key-Value Pairs ................... 299
6.2.6 Connecting to the Server Using DNS SRV Records ........................................... 306
6.2.7 Connection Transport Protocols ........................................................................ 307
6.2.8 Connection Compression Control ...................................................................... 309
6.2.9 Setting Environment Variables .......................................................................... 312
6.3 Server and Server-Startup Programs ........................................................................... 313
6.3.1 mysqld — The MySQL Server .......................................................................... 313
6.3.2 mysqld_safe — MySQL Server Startup Script .................................................... 314
6.3.3 mysql.server — MySQL Server Startup Script .................................................... 322
6.3.4 mysqld_multi — Manage Multiple MySQL Servers .............................................. 324
6.4 Installation-Related Programs ...................................................................................... 329
6.4.1 comp_err — Compile MySQL Error Message File .............................................. 329
6.4.2 mysql_secure_installation — Improve MySQL Installation Security ...................... 332
6.4.3 mysql_tzinfo_to_sql — Load the Time Zone Tables ............................................ 337
6.5 Client Programs .......................................................................................................... 338
6.5.1 mysql — The MySQL Command-Line Client ...................................................... 338
6.5.2 mysqladmin — A MySQL Server Administration Program ................................... 381
6.5.3 mysqlcheck — A Table Maintenance Program ................................................... 396
6.5.4 mysqldump — A Database Backup Program ..................................................... 410
6.5.5 mysqlimport — A Data Import Program ............................................................. 448
6.5.6 mysqlshow — Display Database, Table, and Column Information ........................ 461
6.5.7 mysqlslap — A Load Emulation Client ............................................................... 473
6.6 Administrative and Utility Programs .............................................................................. 490
6.6.1 ibd2sdi — InnoDB Tablespace SDI Extraction Utility ........................................... 490
6.6.2 innochecksum — Offline InnoDB File Checksum Utility ....................................... 495
6.6.3 myisam_ftdump — Display Full-Text Index information ....................................... 502
6.6.4 myisamchk — MyISAM Table-Maintenance Utility .............................................. 503
6.6.5 myisamlog — Display MyISAM Log File Contents .............................................. 522
6.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables .................... 523
6.6.7 mysql_config_editor — MySQL Configuration Utility ........................................... 529
6.6.8 mysql_migrate_keyring — Keyring Key Migration Utility ...................................... 536
6.6.9 mysqlbinlog — Utility for Processing Binary Log Files ......................................... 545
6.6.10 mysqldumpslow — Summarize Slow Query Log Files ....................................... 575
6.7 Program Development Utilities ..................................................................................... 578
6.7.1 mysql_config — Display Options for Compiling Clients ....................................... 578
6.7.2 my_print_defaults — Display Options from Option Files ...................................... 579
6.8 Miscellaneous Programs .............................................................................................. 581
6.8.1 perror — Display MySQL Error Message Information .......................................... 581
6.9 Environment Variables ................................................................................................. 581
6.10 Unix Signal Handling in MySQL ................................................................................. 584
7 MySQL Server Administration ................................................................................................. 585
7.1 The MySQL Server ..................................................................................................... 586
7.1.1 Configuring the Server ...................................................................................... 587
7.1.2 Server Configuration Defaults ........................................................................... 588
7.1.3 Server Configuration Validation ......................................................................... 588
7.1.4 Server Option, System Variable, and Status Variable Reference ......................... 589
7.1.5 Server System Variable Reference .................................................................... 637
7.1.6 Server Status Variable Reference ..................................................................... 662
7.1.7 Server Command Options ................................................................................. 680
7.1.8 Server System Variables .................................................................................. 704
7.1.9 Using System Variables .................................................................................... 860
7.1.10 Server Status Variables .................................................................................. 891
7.1.11 Server SQL Modes ......................................................................................... 916
7.1.12 Connection Management ................................................................................ 928
7.1.13 IPv6 Support .................................................................................................. 935
v
