How do I stop mysql80 service?

When using MySQL, there are times when it’s important to know how to start, stop, or restart your MySQL server. Luckily, there are multiple, easy ways to do this. Which methods are available to you however, will depend on the operating system your running. 

Read on to learn how to start, stop, and restart MySQL server in both Linux and Windows.

How to Start, Stop, and Restart MySQL Server in Linux

If you need to stop or restart your MySQL server on a Linux system, there are three different commands that can be used:

1. Depending on your Linux distribution, you can change the state of MySQL using the service command.
    
   • To start MySQL server: 

     sudo service mysqld start
      

   • To stop MySQL server: 

     sudo service mysqld stop

     
      
   • To restart MySQL server: 

     sudo service mysqld restart

     
      
2. If you don’t have the service command available or would prefer to make changes to MySQL using a different method, you can also use the init.d command to start/stop your MySQL server.
    
   • To start MySQL server: 

     sudo /etc/init.d/mysqld start
      

   • To stop MySQL server: 

     sudo /etc/init.d/mysqld stop

     
      
   • To restart MySQL server: 

     sudo /etc/init.d/mysqld restart

     
      
3. Lastly, you can also use the systemctl command to start, stop, and restart applications on Linux, including MySQL.
    
   • To start MySQL server: 

     sudo systemctl start mysqld

     
      
   • To stop MySQL server: 

     sudo systemctl stop mysqld

     
      
   • To restart MySQL server: 

     sudo systemctl restart mysqld

     
      

How to Start, Stop, and Restart MySQL Server in Windows

If you’re trying to start, stop, or restart your MySQL server on a Windows-based system, you can do so easily from the command line. Just follow these 3 steps:

1. To start, you’ll first need to open a terminal window. If you don’t have this somewhere easily accessible, you can find it quickly using the Windows’ Run dialog. To open the Run dialog, just press the Windows Key + R.
    
2. Next, type in “cmd” and press the Enter key. This will open a new terminal window.
    
3. Once you’ve opened a terminal window, just type the following commands to start or stop MySQL server:
    
   • To start MySQL server:  

     mysqld start

     
      
   • To stop MySQL server: 

     mysqld stop

     
      

*Note: depending on which version of Windows you are running, you may need the specific name of the MySQL version number you are running in order to start or stop the service. To find this, go to the start menu and search for Services. Locate the version of MySQL you are using and try the following commands, replacing “##” with your version number:

net start MySQL##

net stop MySQL##

For instance, if you’re running MySQL 8.0, replace “MySQL##” with “MySQL80”.

And there you have it! You now have several different methods for starting, stopping, and restarting MySQL server as needed. 

The MySQL service on Local Computer started and then stopped

Posted by: Mike Alhayek
Date: July 07, 2019 03:17PM

I have MySQL 8.0.16 installed on Windows Server 2016. It was running with no problem. Then I had a need to use LOAD DATA LOCAL INFILE command which required me to add this to my.ini file secure-file-priv='' I tried to restart the server but I am getting the following error image description here "The MySQL service on Local Computer started and then stopped" I removed the secure-file-priv='' line from `my.ini` file, but I am still getting the same problem when trying to start it. I restarted the whole machine but still the same problem. I looked a the .err file but there is nothing there that tells me why it is not working. What can I do to get it started again? I tried to start mysql from the command line like so net start mysql --verbose then got the following The MySQL service is starting. The MySQL service could not be started. The service did not report an error. More help is available by typing NET HELPMSG 3534. typing NET HELPMSG 3534 return The service did not report an error. Additionally, NETWORK SERVICE has full control permission on the Data folder I also tried mysql --initialize but it is just hanging. Nothing is happening on the screen

How can I get my production server back up?

Navigate: Previous Message• Next Message

Options: Reply• Quote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.

2.3.4.8 Starting MySQL as a Windows Service

On Windows, the recommended way to run MySQL is to install it as a Windows service, so that MySQL starts and stops automatically when Windows starts and stops. A MySQL server installed as a service can also be controlled from the command line using NET commands, or with the graphical Services utility. Generally, to install MySQL as a Windows service you should be logged in using an account that has administrator rights.

The Services utility [the Windows Service Control Manager] can be found in the Windows Control Panel. To avoid conflicts, it is advisable to close the Services utility while performing server installation or removal operations from the command line.

Installing the service

Before installing MySQL as a Windows service, you should first stop the current server if it is running by using the following command:

C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqladmin" -u root shutdown

Note

If the MySQL root user account has a password, you need to invoke mysqladmin with the -p option and supply the password when prompted.

This command invokes the MySQL administrative utility mysqladmin to connect to the server and tell it to shut down. The command connects as the MySQL root user, which is the default administrative account in the MySQL grant system.

Note

Users in the MySQL grant system are wholly independent from any operating system users under Windows.

Install the server as a service using this command:

C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld" --install

The service-installation command does not start the server. Instructions for that are given later in this section.

To make it easier to invoke MySQL programs, you can add the path name of the MySQL bin directory to your Windows system PATH environment variable:

• On the Windows desktop, right-click the My Computer icon, and select .

• Next select the tab from the menu that appears, and click the Environment Variables button.

• Under System Variables, select , and then click the Edit button. The dialogue should appear.

• Place your cursor at the end of the text appearing in the space marked Variable Value. [Use the End key to ensure that your cursor is positioned at the very end of the text in this space.] Then enter the complete path name of your MySQL bin directory [for example, C:\Program Files\MySQL\MySQL Server 8.0\bin], and there should be a semicolon separating this path from any values present in this field. Dismiss this dialogue, and each dialogue in turn, by clicking OK until all of the dialogues that were opened have been dismissed. You should now be able to invoke any MySQL executable program by typing its name at the DOS prompt from any directory on the system, without having to supply the path. This includes the servers, the mysql client, and all MySQL command-line utilities such as mysqladmin and mysqldump.

  You should not add the MySQL bin directory to your Windows PATH if you are running multiple MySQL servers on the same machine.

Warning

You must exercise great care when editing your system PATH by hand; accidental deletion or modification of any portion of the existing PATH value can leave you with a malfunctioning or even unusable system.

The following additional arguments can be used when installing the service:

• You can specify a service name immediately following the --install option. The default service name is MySQL.

• If a service name is given, it can be followed by a single option. By convention, this should be --defaults-file=file_name to specify the name of an option file from which the server should read options when it starts.

  The use of a single option other than --defaults-file is possible but discouraged. --defaults-file is more flexible because it enables you to specify multiple startup options for the server by placing them in the named option file.

• You can also specify a --local-service option following the service name. This causes the server to run using the LocalService Windows account that has limited system privileges. If both --defaults-file and --local-service are given following the service name, they can be in any order.

For a MySQL server that is installed as a Windows service, the following rules determine the service name and option files that the server uses:

• If the service-installation command specifies no service name or the default service name [MySQL] following the --install option, the server uses the service name of MySQL and reads options from the [mysqld] group in the standard option files.

• If the service-installation command specifies a service name other than MySQL following the --install option, the server uses that service name. It reads options from the [mysqld] group and the group that has the same name as the service in the standard option files. This enables you to use the [mysqld] group for options that should be used by all MySQL services, and an option group with the service name for use by the server installed with that service name.

• If the service-installation command specifies a --defaults-file option after the service name, the server reads options the same way as described in the previous item, except that it reads options only from the named file and ignores the standard option files.

As a more complex example, consider the following command:

C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld" --install MySQL --defaults-file=C:\my-opts.cnf

Here, the default service name [MySQL] is given after the --install option. If no --defaults-file option had been given, this command would have the effect of causing the server to read the [mysqld] group from the standard option files. However, because the --defaults-file option is present, the server reads options from the [mysqld] option group, and only from the named file.

Note

On Windows, if the server is started with the --defaults-file and --install options, --install must be first. Otherwise, mysqld.exe attempts to start the MySQL server.

You can also specify options as Start parameters in the Windows Services utility before you start the MySQL service.

Finally, before trying to start the MySQL service, make sure the user variables %TEMP% and %TMP% [and also %TMPDIR%, if it has ever been set] for the operating system user who is to run the service are pointing to a folder to which the user has write access. The default user for running the MySQL service is LocalSystem, and the default value for its %TEMP% and %TMP% is C:\Windows\Temp, a directory LocalSystem has write access to by default. However, if there are any changes to that default setup [for example, changes to the user who runs the service or to the mentioned user variables, or the --tmpdir option has been used to put the temporary directory somewhere else], the MySQL service might fail to run because write access to the temporary directory has not been granted to the proper user.

Starting the service

After a MySQL server instance has been installed as a service, Windows starts the service automatically whenever Windows starts. The service also can be started immediately from the Services utility, or by using an sc start mysqld_service_name or NET START mysqld_service_name command. SC and NET commands are not case-sensitive.

When run as a service, mysqld has no access to a console window, so no messages can be seen there. If mysqld does not start, check the error log to see whether the server wrote any messages there to indicate the cause of the problem. The error log is located in the MySQL data directory [for example, C:\Program Files\MySQL\MySQL Server 8.0\data]. It is the file with a suffix of .err.

When a MySQL server has been installed as a service, and the service is running, Windows stops the service automatically when Windows shuts down. The server also can be stopped manually using the Services utility, the sc stop mysqld_service_name command, the NET STOP mysqld_service_name command, or the mysqladmin shutdown command.

You also have the choice of installing the server as a manual service if you do not wish for the service to be started automatically during the boot process. To do this, use the --install-manual option rather than the --install option:

C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld" --install-manual Removing the service

To remove a server that is installed as a service, first stop it if it is running by executing SC STOP mysqld_service_name or NET STOP mysqld_service_name. Then use SC DELETE mysqld_service_name to remove it:

C:\> SC DELETE mysql

Alternatively, use the mysqld --remove option to remove the service.

C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld" --remove

If mysqld is not running as a service, you can start it from the command line. For instructions, see Section 2.3.4.6, “Starting MySQL from the Windows Command Line”.

If you encounter difficulties during installation, see Section 2.3.5, “Troubleshooting a Microsoft Windows MySQL Server Installation”.

For more information about stopping or removing a Windows service, see Section 5.8.2.2, “Starting Multiple MySQL Instances as Windows Services”.

Page 2

2.3.4.9 Testing The MySQL Installation

You can test whether the MySQL server is working by executing any of the following commands:

C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqlshow" C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqlshow" -u root mysql C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqladmin" version status proc C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql" test

If mysqld is slow to respond to TCP/IP connections from client programs, there is probably a problem with your DNS. In this case, start mysqld with the skip_name_resolve system variable enabled and use only localhost and IP addresses in the Host column of the MySQL grant tables. [Be sure that an account exists that specifies an IP address or you may not be able to connect.]

You can force a MySQL client to use a named-pipe connection rather than TCP/IP by specifying the --pipe or --protocol=PIPE option, or by specifying . [period] as the host name. Use the --socket option to specify the name of the pipe if you do not want to use the default pipe name.

If you have set a password for the root account, deleted the anonymous account, or created a new user account, then to connect to the MySQL server you must use the appropriate -u and -p options with the commands shown previously. See Section 4.2.4, “Connecting to the MySQL Server Using Command Options”.

For more information about mysqlshow, see Section 4.5.7, “mysqlshow — Display Database, Table, and Column Information”.

Page 3

2.3.5 Troubleshooting a Microsoft Windows MySQL Server Installation

When installing and running MySQL for the first time, you may encounter certain errors that prevent the MySQL server from starting. This section helps you diagnose and correct some of these errors.

Your first resource when troubleshooting server issues is the error log. The MySQL server uses the error log to record information relevant to the error that prevents the server from starting. The error log is located in the data directory specified in your my.ini file. The default data directory location is C:\Program Files\MySQL\MySQL Server 8.0\data, or C:\ProgramData\Mysql on Windows 7 and Windows Server 2008. The C:\ProgramData directory is hidden by default. You need to change your folder options to see the directory and contents. For more information on the error log and understanding the content, see Section 5.4.2, “The Error Log”.

For information regarding possible errors, also consult the console messages displayed when the MySQL service is starting. Use the SC START mysqld_service_name or NET START mysqld_service_name command from the command line after installing mysqld as a service to see any error messages regarding the starting of the MySQL server as a service. See Section 2.3.4.8, “Starting MySQL as a Windows Service”.

The following examples show other common error messages you might encounter when installing MySQL and starting the server for the first time:

• If the MySQL server cannot find the mysql privileges database or other critical files, it displays these messages:

  System error 1067 has occurred. Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

  These messages often occur when the MySQL base or data directories are installed in different locations than the default locations [C:\Program Files\MySQL\MySQL Server 8.0 and C:\Program Files\MySQL\MySQL Server 8.0\data, respectively].

  This situation can occur when MySQL is upgraded and installed to a new location, but the configuration file is not updated to reflect the new location. In addition, old and new configuration files might conflict. Be sure to delete or rename any old configuration files when upgrading MySQL.

  If you have installed MySQL to a directory other than C:\Program Files\MySQL\MySQL Server 8.0, ensure that the MySQL server is aware of this through the use of a configuration [my.ini] file. Put the my.ini file in your Windows directory, typically C:\WINDOWS. To determine its exact location from the value of the WINDIR environment variable, issue the following command from the command prompt:

  C:\> echo %WINDIR%

  You can create or modify an option file with any text editor, such as Notepad. For example, if MySQL is installed in E:\mysql and the data directory is D:\MySQLdata, you can create the option file and set up a [mysqld] section to specify values for the basedir and datadir options:

  [mysqld] # set basedir to your installation path basedir=E:/mysql # set datadir to the location of your data directory datadir=D:/MySQLdata

  Microsoft Windows path names are specified in option files using [forward] slashes rather than backslashes. If you do use backslashes, double them:

  [mysqld] # set basedir to your installation path basedir=C:\\Program Files\\MySQL\\MySQL Server 8.0 # set datadir to the location of your data directory datadir=D:\\MySQLdata

  The rules for use of backslash in option file values are given in Section 4.2.2.2, “Using Option Files”.

  If you change the datadir value in your MySQL configuration file, you must move the contents of the existing MySQL data directory before restarting the MySQL server.

  See Section 2.3.4.2, “Creating an Option File”.

• If you reinstall or upgrade MySQL without first stopping and removing the existing MySQL service and install MySQL using the MySQL Installer, you might see this error:

  Error: Cannot create Windows service for MySql. Error: 0

  This occurs when the Configuration Wizard tries to install the service and finds an existing service with the same name.

  One solution to this problem is to choose a service name other than mysql when using the configuration wizard. This enables the new service to be installed correctly, but leaves the outdated service in place. Although this is harmless, it is best to remove old services that are no longer in use.

  To permanently remove the old mysql service, execute the following command as a user with administrative privileges, on the command line:

  C:\> SC DELETE mysql [SC] DeleteService SUCCESS

  If the SC utility is not available for your version of Windows, download the delsrv utility from //www.microsoft.com/windows2000/techinfo/reskit/tools/existing/delsrv-o.asp and use the delsrv mysql syntax.

Page 4

2.3.6 Windows Postinstallation Procedures

GUI tools exist that perform most of the tasks described in this section, including:

If necessary, initialize the data directory and create the MySQL grant tables. Windows installation operations performed by MySQL Installer initialize the data directory automatically. For installation from a ZIP Archive package, initialize the data directory as described at Section 2.10.1, “Initializing the Data Directory”.

Regarding passwords, if you installed MySQL using the MySQL Installer, you may have already assigned a password to the initial root account. [See Section 2.3.3, “MySQL Installer for Windows”.] Otherwise, use the password-assignment procedure given in Section 2.10.4, “Securing the Initial MySQL Account”.

Before assigning a password, you might want to try running some client programs to make sure that you can connect to the server and that it is operating properly. Make sure that the server is running [see Section 2.3.4.5, “Starting the Server for the First Time”]. You can also set up a MySQL service that runs automatically when Windows starts [see Section 2.3.4.8, “Starting MySQL as a Windows Service”].

These instructions assume that your current location is the MySQL installation directory and that it has a bin subdirectory containing the MySQL programs used here. If that is not true, adjust the command path names accordingly.

If you installed MySQL using MySQL Installer [see Section 2.3.3, “MySQL Installer for Windows”], the default installation directory is C:\Program Files\MySQL\MySQL Server 8.0:

C:\> cd "C:\Program Files\MySQL\MySQL Server 8.0"

A common installation location for installation from a ZIP archive is C:\mysql:

C:\> cd C:\mysql

Alternatively, add the bin directory to your PATH environment variable setting. That enables your command interpreter to find MySQL programs properly, so that you can run a program by typing only its name, not its path name. See Section 2.3.4.7, “Customizing the PATH for MySQL Tools”.

With the server running, issue the following commands to verify that you can retrieve information from the server. The output should be similar to that shown here.

Use mysqlshow to see what databases exist:

C:\> bin\mysqlshow +--------------------+ | Databases | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+

The list of installed databases may vary, but always includes at least mysql and information_schema.

The preceding command [and commands for other MySQL programs such as mysql] may not work if the correct MySQL account does not exist. For example, the program may fail with an error, or you may not be able to view all databases. If you install MySQL using MySQL Installer, the root user is created automatically with the password you supplied. In this case, you should use the -u root and -p options. [You must use those options if you have already secured the initial MySQL accounts.] With -p, the client program prompts for the root password. For example:

C:\> bin\mysqlshow -u root -p Enter password: [enter root password here] +--------------------+ | Databases | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+

If you specify a database name, mysqlshow displays a list of the tables within the database:

C:\> bin\mysqlshow mysql Database: mysql +---------------------------+ | Tables | +---------------------------+ | columns_priv | | component | | db | | default_roles | | engine_cost | | func | | general_log | | global_grants | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | password_history | | plugin | | procs_priv | | proxies_priv | | role_edges | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+

Use the mysql program to select information from a table in the mysql database:

C:\> bin\mysql -e "SELECT User, Host, plugin FROM mysql.user" mysql +------+-----------+-----------------------+ | User | Host | plugin | +------+-----------+-----------------------+ | root | localhost | caching_sha2_password | +------+-----------+-----------------------+

For more information about mysql and mysqlshow, see Section 4.5.1, “mysql — The MySQL Command-Line Client”, and Section 4.5.7, “mysqlshow — Display Database, Table, and Column Information”.

Page 5

• Process memory

  On Windows 32-bit platforms, it is not possible by default to use more than 2GB of RAM within a single process, including MySQL. This is because the physical address limit on Windows 32-bit is 4GB and the default setting within Windows is to split the virtual address space between kernel [2GB] and user/applications [2GB].

  Some versions of Windows have a boot time setting to enable larger applications by reducing the kernel application. Alternatively, to use more than 2GB, use a 64-bit version of Windows.

• File system aliases

  When using MyISAM tables, you cannot use aliases within Windows link to the data files on another volume and then link back to the main MySQL datadir location.

  This facility is often used to move the data and index files to a RAID or other fast solution.

• Limited number of ports

  Windows systems have about 4,000 ports available for client connections, and after a connection on a port closes, it takes two to four minutes before the port can be reused. In situations where clients connect to and disconnect from the server at a high rate, it is possible for all available ports to be used up before closed ports become available again. If this happens, the MySQL server appears to be unresponsive even though it is running. Ports may be used by other applications running on the machine as well, in which case the number of ports available to MySQL is lower.

  For more information about this problem, see //support.microsoft.com/kb/196271.

• DATA DIRECTORY and INDEX DIRECTORY

  The DATA DIRECTORY clause of the CREATE TABLE statement is supported on Windows for InnoDB tables only, as described in Section 15.6.1.2, “Creating Tables Externally”. For MyISAM and other storage engines, the DATA DIRECTORY and INDEX DIRECTORY clauses for CREATE TABLE are ignored on Windows and any other platforms with a nonfunctional realpath[] call.

• DROP DATABASE

  You cannot drop a database that is in use by another session.

• Case-insensitive names

  File names are not case-sensitive on Windows, so MySQL database and table names are also not case-sensitive on Windows. The only restriction is that database and table names must be specified using the same case throughout a given statement. See Section 9.2.3, “Identifier Case Sensitivity”.

• Directory and file names

  On Windows, MySQL Server supports only directory and file names that are compatible with the current ANSI code pages. For example, the following Japanese directory name does not work in the Western locale [code page 1252]:

  datadir="C:/私たちのプロジェクトのデータ"

  The same limitation applies to directory and file names referred to in SQL statements, such as the data file path name in LOAD DATA.

• The \ path name separator character

  Path name components in Windows are separated by the \ character, which is also the escape character in MySQL. If you are using LOAD DATA or SELECT ... INTO OUTFILE, use Unix-style file names with / characters:

  mysql> LOAD DATA INFILE 'C:/tmp/skr.txt' INTO TABLE skr; mysql> SELECT * INTO OUTFILE 'C:/tmp/skr.txt' FROM skr;

  Alternatively, you must double the \ character:

  mysql> LOAD DATA INFILE 'C:\\tmp\\skr.txt' INTO TABLE skr; mysql> SELECT * INTO OUTFILE 'C:\\tmp\\skr.txt' FROM skr;

• Problems with pipes

  Pipes do not work reliably from the Windows command-line prompt. If the pipe includes the character ^Z / CHAR[24], Windows thinks that it has encountered end-of-file and aborts the program.

  This is mainly a problem when you try to apply a binary log as follows:

  C:\> mysqlbinlog binary_log_file | mysql --user=root

  If you have a problem applying the log and suspect that it is because of a ^Z / CHAR[24] character, you can use the following workaround:

  C:\> mysqlbinlog binary_log_file --result-file=/tmp/bin.sql C:\> mysql --user=root --execute "source /tmp/bin.sql"

  The latter command also can be used to reliably read any SQL file that may contain binary data.

Page 6

2.4 Installing MySQL on macOS

For a list of macOS versions that the MySQL server supports, see //www.mysql.com/support/supportedplatforms/database.html.

MySQL for macOS is available in a number of different forms:

• Native Package Installer, which uses the native macOS installer [DMG] to walk you through the installation of MySQL. For more information, see Section 2.4.2, “Installing MySQL on macOS Using Native Packages”. You can use the package installer with macOS. The user you use to perform the installation must have administrator privileges.

• Compressed TAR archive, which uses a file packaged using the Unix tar and gzip commands. To use this method, you need to open a Terminal window. You do not need administrator privileges using this method; you can install the MySQL server anywhere using this method. For more information on using this method, you can use the generic instructions for using a tarball, Section 2.2, “Installing MySQL on Unix/Linux Using Generic Binaries”.

  In addition to the core installation, the Package Installer also includes Section 2.4.3, “Installing and Using the MySQL Launch Daemon” and Section 2.4.4, “Installing and Using the MySQL Preference Pane” to simplify the management of your installation.

For additional information on using MySQL on macOS, see Section 2.4.1, “General Notes on Installing MySQL on macOS”.

Page 7

Abstract

This is the MySQL Reference Manual. It documents MySQL 8.0 through 8.0.30, as well as NDB Cluster releases based on version 8.0 of NDB through 8.0.29-ndb-8.0.29, 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.0 Release Notes.

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

For notes detailing the changes in each release, see the MySQL 8.0 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: 2022-03-08 [revision: 72198]

Table of Contents

Preface and Legal Notices1 General Information1.1 About This Manual1.2 Overview of the MySQL Database Management System1.2.1 What is MySQL?1.2.2 The Main Features of MySQL1.2.3 History of MySQL1.3 What Is New in MySQL 8.01.4 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 8.01.5 MySQL Information Sources1.6 How to Report Bugs or Problems1.7 MySQL Standards Compliance1.7.1 MySQL Extensions to Standard SQL1.7.2 MySQL Differences from Standard SQL1.7.3 How MySQL Deals with Constraints1.8 Credits1.8.1 Contributors to MySQL1.8.2 Documenters and translators1.8.3 Packages that support MySQL1.8.4 Tools that were used to create MySQL1.8.5 Supporters of MySQL2 Installing and Upgrading MySQL2.1 General Installation Guidance2.1.1 Supported Platforms2.1.2 Which MySQL Version and Distribution to Install2.1.3 How to Get MySQL2.1.4 Verifying Package Integrity Using MD5 Checksums or GnuPG2.1.5 Installation Layouts2.1.6 Compiler-Specific Build Characteristics2.2 Installing MySQL on Unix/Linux Using Generic Binaries2.3 Installing MySQL on Microsoft Windows2.3.1 MySQL Installation Layout on Microsoft Windows2.3.2 Choosing an Installation Package2.3.3 MySQL Installer for Windows2.3.4 Installing MySQL on Microsoft Windows Using a noinstall ZIP Archive2.3.5 Troubleshooting a Microsoft Windows MySQL Server Installation2.3.6 Windows Postinstallation Procedures2.3.7 Windows Platform Restrictions2.4 Installing MySQL on macOS2.4.1 General Notes on Installing MySQL on macOS2.4.2 Installing MySQL on macOS Using Native Packages2.4.3 Installing and Using the MySQL Launch Daemon2.4.4 Installing and Using the MySQL Preference Pane2.5 Installing MySQL on Linux2.5.1 Installing MySQL on Linux Using the MySQL Yum Repository2.5.2 Installing MySQL on Linux Using the MySQL APT Repository2.5.3 Installing MySQL on Linux Using the MySQL SLES Repository2.5.4 Installing MySQL on Linux Using RPM Packages from Oracle2.5.5 Installing MySQL on Linux Using Debian Packages from Oracle2.5.6 Deploying MySQL on Linux with Docker2.5.7 Installing MySQL on Linux from the Native Software Repositories2.5.8 Installing MySQL on Linux with Juju2.5.9 Managing MySQL Server with systemd2.6 Installing MySQL Using Unbreakable Linux Network [ULN]2.7 Installing MySQL on Solaris2.7.1 Installing MySQL on Solaris Using a Solaris PKG2.8 Installing MySQL on FreeBSD2.9 Installing MySQL from Source2.9.1 Source Installation Methods2.9.2 Source Installation Prerequisites2.9.3 MySQL Layout for Source Installation2.9.4 Installing MySQL Using a Standard Source Distribution2.9.5 Installing MySQL Using a Development Source Tree2.9.6 Configuring SSL Library Support2.9.7 MySQL Source-Configuration Options2.9.8 Dealing with Problems Compiling MySQL2.9.9 MySQL Configuration and Third-Party Tools2.9.10 Generating MySQL Doxygen Documentation Content2.10 Postinstallation Setup and Testing2.10.1 Initializing the Data Directory2.10.2 Starting the Server2.10.3 Testing the Server2.10.4 Securing the Initial MySQL Account2.10.5 Starting and Stopping MySQL Automatically2.11 Upgrading MySQL2.11.1 Before You Begin2.11.2 Upgrade Paths2.11.3 What the MySQL Upgrade Process Upgrades2.11.4 Changes in MySQL 8.02.11.5 Preparing Your Installation for Upgrade2.11.6 Upgrading MySQL Binary or Package-based Installations on Unix/Linux2.11.7 Upgrading MySQL with the MySQL Yum Repository2.11.8 Upgrading MySQL with the MySQL APT Repository2.11.9 Upgrading MySQL with the MySQL SLES Repository2.11.10 Upgrading MySQL on Windows2.11.11 Upgrading a Docker Installation of MySQL2.11.12 Upgrade Troubleshooting2.11.13 Rebuilding or Repairing Tables or Indexes2.11.14 Copying MySQL Databases to Another Machine2.12 Downgrading MySQL2.13 Perl Installation Notes2.13.1 Installing Perl on Unix2.13.2 Installing ActiveState Perl on Windows2.13.3 Problems Using the Perl DBI/DBD Interface3 Tutorial3.1 Connecting to and Disconnecting from the Server3.2 Entering Queries3.3 Creating and Using a Database3.3.1 Creating and Selecting a Database3.3.2 Creating a Table3.3.3 Loading Data into a Table3.3.4 Retrieving Information from a Table3.4 Getting Information About Databases and Tables3.5 Using mysql in Batch Mode3.6 Examples of Common Queries3.6.1 The Maximum Value for a Column3.6.2 The Row Holding the Maximum of a Certain Column3.6.3 Maximum of Column per Group3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column3.6.5 Using User-Defined Variables3.6.6 Using Foreign Keys3.6.7 Searching on Two Keys3.6.8 Calculating Visits Per Day3.6.9 Using AUTO_INCREMENT3.7 Using MySQL with Apache4 MySQL Programs4.1 Overview of MySQL Programs4.2 Using MySQL Programs4.2.1 Invoking MySQL Programs4.2.2 Specifying Program Options4.2.3 Command Options for Connecting to the Server4.2.4 Connecting to the MySQL Server Using Command Options4.2.5 Connecting to the Server Using URI-Like Strings or Key-Value Pairs4.2.6 Connecting to the Server Using DNS SRV Records4.2.7 Connection Transport Protocols4.2.8 Connection Compression Control4.2.9 Setting Environment Variables4.3 Server and Server-Startup Programs4.3.1 mysqld — The MySQL Server4.3.2 mysqld_safe — MySQL Server Startup Script4.3.3 mysql.server — MySQL Server Startup Script4.3.4 mysqld_multi — Manage Multiple MySQL Servers4.4 Installation-Related Programs4.4.1 comp_err — Compile MySQL Error Message File4.4.2 mysql_secure_installation — Improve MySQL Installation Security4.4.3 mysql_ssl_rsa_setup — Create SSL/RSA Files4.4.4 mysql_tzinfo_to_sql — Load the Time Zone Tables4.4.5 mysql_upgrade — Check and Upgrade MySQL Tables4.5 Client Programs4.5.1 mysql — The MySQL Command-Line Client4.5.2 mysqladmin — A MySQL Server Administration Program4.5.3 mysqlcheck — A Table Maintenance Program4.5.4 mysqldump — A Database Backup Program4.5.5 mysqlimport — A Data Import Program4.5.6 mysqlpump — A Database Backup Program4.5.7 mysqlshow — Display Database, Table, and Column Information4.5.8 mysqlslap — A Load Emulation Client4.6 Administrative and Utility Programs4.6.1 ibd2sdi — InnoDB Tablespace SDI Extraction Utility4.6.2 innochecksum — Offline InnoDB File Checksum Utility4.6.3 myisam_ftdump — Display Full-Text Index information4.6.4 myisamchk — MyISAM Table-Maintenance Utility4.6.5 myisamlog — Display MyISAM Log File Contents4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables4.6.7 mysql_config_editor — MySQL Configuration Utility4.6.8 mysql_migrate_keyring — Keyring Key Migration Utility4.6.9 mysqlbinlog — Utility for Processing Binary Log Files4.6.10 mysqldumpslow — Summarize Slow Query Log Files4.7 Program Development Utilities4.7.1 mysql_config — Display Options for Compiling Clients4.7.2 my_print_defaults — Display Options from Option Files4.8 Miscellaneous Programs4.8.1 lz4_decompress — Decompress mysqlpump LZ4-Compressed Output4.8.2 perror — Display MySQL Error Message Information4.8.3 zlib_decompress — Decompress mysqlpump ZLIB-Compressed Output4.9 Environment Variables4.10 Unix Signal Handling in MySQL5 MySQL Server Administration5.1 The MySQL Server5.1.1 Configuring the Server5.1.2 Server Configuration Defaults5.1.3 Server Configuration Validation5.1.4 Server Option, System Variable, and Status Variable Reference5.1.5 Server System Variable Reference5.1.6 Server Status Variable Reference5.1.7 Server Command Options5.1.8 Server System Variables5.1.9 Using System Variables5.1.10 Server Status Variables5.1.11 Server SQL Modes5.1.12 Connection Management5.1.13 IPv6 Support5.1.14 Network Namespace Support5.1.15 MySQL Server Time Zone Support5.1.16 Resource Groups5.1.17 Server-Side Help Support5.1.18 Server Tracking of Client Session State5.1.19 The Server Shutdown Process5.2 The MySQL Data Directory5.3 The mysql System Schema5.4 MySQL Server Logs5.4.1 Selecting General Query Log and Slow Query Log Output Destinations5.4.2 The Error Log5.4.3 The General Query Log5.4.4 The Binary Log5.4.5 The Slow Query Log5.4.6 Server Log Maintenance5.5 MySQL Components5.5.1 Installing and Uninstalling Components5.5.2 Obtaining Component Information5.5.3 Error Log Components5.5.4 Query Attribute Components5.6 MySQL Server Plugins5.6.1 Installing and Uninstalling Plugins5.6.2 Obtaining Server Plugin Information5.6.3 MySQL Enterprise Thread Pool5.6.4 The Rewriter Query Rewrite Plugin5.6.5 The ddl_rewriter Plugin5.6.6 Version Tokens5.6.7 The Clone Plugin5.6.8 The Keyring Proxy Bridge Plugin5.6.9 MySQL Plugin Services5.7 MySQL Server Loadable Functions5.7.1 Installing and Uninstalling Loadable Functions5.7.2 Obtaining Information About Loadable Functions5.8 Running Multiple MySQL Instances on One Machine5.8.1 Setting Up Multiple Data Directories5.8.2 Running Multiple MySQL Instances on Windows5.8.3 Running Multiple MySQL Instances on Unix5.8.4 Using Client Programs in a Multiple-Server Environment5.9 Debugging MySQL5.9.1 Debugging a MySQL Server5.9.2 Debugging a MySQL Client5.9.3 The LOCK_ORDER Tool5.9.4 The DBUG Package6 Security6.1 General Security Issues6.1.1 Security Guidelines6.1.2 Keeping Passwords Secure6.1.3 Making MySQL Secure Against Attackers6.1.4 Security-Related mysqld Options and Variables6.1.5 How to Run MySQL as a Normal User6.1.6 Security Considerations for LOAD DATA LOCAL6.1.7 Client Programming Security Guidelines6.2 Access Control and Account Management6.2.1 Account User Names and Passwords6.2.2 Privileges Provided by MySQL6.2.3 Grant Tables6.2.4 Specifying Account Names6.2.5 Specifying Role Names6.2.6 Access Control, Stage 1: Connection Verification6.2.7 Access Control, Stage 2: Request Verification6.2.8 Adding Accounts, Assigning Privileges, and Dropping Accounts6.2.9 Reserved Accounts6.2.10 Using Roles6.2.11 Account Categories6.2.12 Privilege Restriction Using Partial Revokes6.2.13 When Privilege Changes Take Effect6.2.14 Assigning Account Passwords6.2.15 Password Management6.2.16 Server Handling of Expired Passwords6.2.17 Pluggable Authentication6.2.18 Multifactor Authentication6.2.19 Proxy Users6.2.20 Account Locking6.2.21 Setting Account Resource Limits6.2.22 Troubleshooting Problems Connecting to MySQL6.2.23 SQL-Based Account Activity Auditing6.3 Using Encrypted Connections6.3.1 Configuring MySQL to Use Encrypted Connections6.3.2 Encrypted Connection TLS Protocols and Ciphers6.3.3 Creating SSL and RSA Certificates and Keys6.3.4 Connecting to MySQL Remotely from Windows with SSH6.4 Security Components and Plugins6.4.1 Authentication Plugins6.4.2 The Connection-Control Plugins6.4.3 The Password Validation Component6.4.4 The MySQL Keyring6.4.5 MySQL Enterprise Audit6.4.6 The Audit Message Component6.4.7 MySQL Enterprise Firewall6.5 MySQL Enterprise Data Masking and De-Identification6.5.1 MySQL Enterprise Data Masking and De-Identification Elements6.5.2 Installing or Uninstalling MySQL Enterprise Data Masking and De-Identification6.5.3 Using MySQL Enterprise Data Masking and De-Identification6.5.4 MySQL Enterprise Data Masking and De-Identification Function Reference6.5.5 MySQL Enterprise Data Masking and De-Identification Function Descriptions6.6 MySQL Enterprise Encryption6.6.1 MySQL Enterprise Encryption Installation6.6.2 MySQL Enterprise Encryption Usage and Examples6.6.3 MySQL Enterprise Encryption Function Reference6.6.4 MySQL Enterprise Encryption Function Descriptions6.7 SELinux6.7.1 Check if SELinux is Enabled6.7.2 Changing the SELinux Mode6.7.3 MySQL Server SELinux Policies6.7.4 SELinux File Context6.7.5 SELinux TCP Port Context6.7.6 Troubleshooting SELinux6.8 FIPS Support7 Backup and Recovery7.1 Backup and Recovery Types7.2 Database Backup Methods7.3 Example Backup and Recovery Strategy7.3.1 Establishing a Backup Policy7.3.2 Using Backups for Recovery7.3.3 Backup Strategy Summary7.4 Using mysqldump for Backups7.4.1 Dumping Data in SQL Format with mysqldump7.4.2 Reloading SQL-Format Backups7.4.3 Dumping Data in Delimited-Text Format with mysqldump7.4.4 Reloading Delimited-Text Format Backups7.4.5 mysqldump Tips7.5 Point-in-Time [Incremental] Recovery7.5.1 Point-in-Time Recovery Using Binary Log7.5.2 Point-in-Time Recovery Using Event Positions7.6 MyISAM Table Maintenance and Crash Recovery7.6.1 Using myisamchk for Crash Recovery7.6.2 How to Check MyISAM Tables for Errors7.6.3 How to Repair MyISAM Tables7.6.4 MyISAM Table Optimization7.6.5 Setting Up a MyISAM Table Maintenance Schedule8 Optimization8.1 Optimization Overview8.2 Optimizing SQL Statements8.2.1 Optimizing SELECT Statements8.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions8.2.3 Optimizing INFORMATION_SCHEMA Queries8.2.4 Optimizing Performance Schema Queries8.2.5 Optimizing Data Change Statements8.2.6 Optimizing Database Privileges8.2.7 Other Optimization Tips8.3 Optimization and Indexes8.3.1 How MySQL Uses Indexes8.3.2 Primary Key Optimization8.3.3 SPATIAL Index Optimization8.3.4 Foreign Key Optimization8.3.5 Column Indexes8.3.6 Multiple-Column Indexes8.3.7 Verifying Index Usage8.3.8 InnoDB and MyISAM Index Statistics Collection8.3.9 Comparison of B-Tree and Hash Indexes8.3.10 Use of Index Extensions8.3.11 Optimizer Use of Generated Column Indexes8.3.12 Invisible Indexes8.3.13 Descending Indexes8.3.14 Indexed Lookups from TIMESTAMP Columns8.4 Optimizing Database Structure8.4.1 Optimizing Data Size8.4.2 Optimizing MySQL Data Types8.4.3 Optimizing for Many Tables8.4.4 Internal Temporary Table Use in MySQL8.4.5 Limits on Number of Databases and Tables8.4.6 Limits on Table Size8.4.7 Limits on Table Column Count and Row Size8.5 Optimizing for InnoDB Tables8.5.1 Optimizing Storage Layout for InnoDB Tables8.5.2 Optimizing InnoDB Transaction Management8.5.3 Optimizing InnoDB Read-Only Transactions8.5.4 Optimizing InnoDB Redo Logging8.5.5 Bulk Data Loading for InnoDB Tables8.5.6 Optimizing InnoDB Queries8.5.7 Optimizing InnoDB DDL Operations8.5.8 Optimizing InnoDB Disk I/O8.5.9 Optimizing InnoDB Configuration Variables8.5.10 Optimizing InnoDB for Systems with Many Tables8.6 Optimizing for MyISAM Tables8.6.1 Optimizing MyISAM Queries8.6.2 Bulk Data Loading for MyISAM Tables8.6.3 Optimizing REPAIR TABLE Statements8.7 Optimizing for MEMORY Tables8.8 Understanding the Query Execution Plan8.8.1 Optimizing Queries with EXPLAIN8.8.2 EXPLAIN Output Format8.8.3 Extended EXPLAIN Output Format8.8.4 Obtaining Execution Plan Information for a Named Connection8.8.5 Estimating Query Performance8.9 Controlling the Query Optimizer8.9.1 Controlling Query Plan Evaluation8.9.2 Switchable Optimizations8.9.3 Optimizer Hints8.9.4 Index Hints8.9.5 The Optimizer Cost Model8.9.6 Optimizer Statistics8.10 Buffering and Caching8.10.1 InnoDB Buffer Pool Optimization8.10.2 The MyISAM Key Cache8.10.3 Caching of Prepared Statements and Stored Programs8.11 Optimizing Locking Operations8.11.1 Internal Locking Methods8.11.2 Table Locking Issues8.11.3 Concurrent Inserts8.11.4 Metadata Locking8.11.5 External Locking8.12 Optimizing the MySQL Server8.12.1 Optimizing Disk I/O8.12.2 Using Symbolic Links8.12.3 Optimizing Memory Use8.13 Measuring Performance [Benchmarking]8.13.1 Measuring the Speed of Expressions and Functions8.13.2 Using Your Own Benchmarks8.13.3 Measuring Performance with performance_schema8.14 Examining Server Thread [Process] Information8.14.1 Accessing the Process List8.14.2 Thread Command Values8.14.3 General Thread States8.14.4 Replication Source Thread States8.14.5 Replication I/O [Receiver] Thread States8.14.6 Replication SQL Thread States8.14.7 Replication Connection Thread States8.14.8 NDB Cluster Thread States8.14.9 Event Scheduler Thread States9 Language Structure9.1 Literal Values9.1.1 String Literals9.1.2 Numeric Literals9.1.3 Date and Time Literals9.1.4 Hexadecimal Literals9.1.5 Bit-Value Literals9.1.6 Boolean Literals9.1.7 NULL Values9.2 Schema Object Names9.2.1 Identifier Length Limits9.2.2 Identifier Qualifiers9.2.3 Identifier Case Sensitivity9.2.4 Mapping of Identifiers to File Names9.2.5 Function Name Parsing and Resolution9.3 Keywords and Reserved Words9.4 User-Defined Variables9.5 Expressions9.6 Query Attributes9.7 Comments10 Character Sets, Collations, Unicode10.1 Character Sets and Collations in General10.2 Character Sets and Collations in MySQL10.2.1 Character Set Repertoire10.2.2 UTF-8 for Metadata10.3 Specifying Character Sets and Collations10.3.1 Collation Naming Conventions10.3.2 Server Character Set and Collation10.3.3 Database Character Set and Collation10.3.4 Table Character Set and Collation10.3.5 Column Character Set and Collation10.3.6 Character String Literal Character Set and Collation10.3.7 The National Character Set10.3.8 Character Set Introducers10.3.9 Examples of Character Set and Collation Assignment10.3.10 Compatibility with Other DBMSs10.4 Connection Character Sets and Collations10.5 Configuring Application Character Set and Collation10.6 Error Message Character Set10.7 Column Character Set Conversion10.8 Collation Issues10.8.1 Using COLLATE in SQL Statements10.8.2 COLLATE Clause Precedence10.8.3 Character Set and Collation Compatibility10.8.4 Collation Coercibility in Expressions10.8.5 The binary Collation Compared to _bin Collations10.8.6 Examples of the Effect of Collation10.8.7 Using Collation in INFORMATION_SCHEMA Searches10.9 Unicode Support10.9.1 The utf8mb4 Character Set [4-Byte UTF-8 Unicode Encoding]10.9.2 The utf8mb3 Character Set [3-Byte UTF-8 Unicode Encoding]10.9.3 The utf8 Character Set [Alias for utf8mb3]10.9.4 The ucs2 Character Set [UCS-2 Unicode Encoding]10.9.5 The utf16 Character Set [UTF-16 Unicode Encoding]10.9.6 The utf16le Character Set [UTF-16LE Unicode Encoding]10.9.7 The utf32 Character Set [UTF-32 Unicode Encoding]10.9.8 Converting Between 3-Byte and 4-Byte Unicode Character Sets10.10 Supported Character Sets and Collations10.10.1 Unicode Character Sets10.10.2 West European Character Sets10.10.3 Central European Character Sets10.10.4 South European and Middle East Character Sets10.10.5 Baltic Character Sets10.10.6 Cyrillic Character Sets10.10.7 Asian Character Sets10.10.8 The Binary Character Set10.11 Restrictions on Character Sets10.12 Setting the Error Message Language10.13 Adding a Character Set10.13.1 Character Definition Arrays10.13.2 String Collating Support for Complex Character Sets10.13.3 Multi-Byte Character Support for Complex Character Sets10.14 Adding a Collation to a Character Set10.14.1 Collation Implementation Types10.14.2 Choosing a Collation ID10.14.3 Adding a Simple Collation to an 8-Bit Character Set10.14.4 Adding a UCA Collation to a Unicode Character Set10.15 Character Set Configuration10.16 MySQL Server Locale Support11 Data Types11.1 Numeric Data Types11.1.1 Numeric Data Type Syntax11.1.2 Integer Types [Exact Value] - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT11.1.3 Fixed-Point Types [Exact Value] - DECIMAL, NUMERIC11.1.4 Floating-Point Types [Approximate Value] - FLOAT, DOUBLE11.1.5 Bit-Value Type - BIT11.1.6 Numeric Type Attributes11.1.7 Out-of-Range and Overflow Handling11.2 Date and Time Data Types11.2.1 Date and Time Data Type Syntax11.2.2 The DATE, DATETIME, and TIMESTAMP Types11.2.3 The TIME Type11.2.4 The YEAR Type11.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME11.2.6 Fractional Seconds in Time Values11.2.7 Conversion Between Date and Time Types11.2.8 2-Digit Years in Dates11.3 String Data Types11.3.1 String Data Type Syntax11.3.2 The CHAR and VARCHAR Types11.3.3 The BINARY and VARBINARY Types11.3.4 The BLOB and TEXT Types11.3.5 The ENUM Type11.3.6 The SET Type11.4 Spatial Data Types11.4.1 Spatial Data Types11.4.2 The OpenGIS Geometry Model11.4.3 Supported Spatial Data Formats11.4.4 Geometry Well-Formedness and Validity11.4.5 Spatial Reference System Support11.4.6 Creating Spatial Columns11.4.7 Populating Spatial Columns11.4.8 Fetching Spatial Data11.4.9 Optimizing Spatial Analysis11.4.10 Creating Spatial Indexes11.4.11 Using Spatial Indexes11.5 The JSON Data Type11.6 Data Type Default Values11.7 Data Type Storage Requirements11.8 Choosing the Right Type for a Column11.9 Using Data Types from Other Database Engines12 Functions and Operators12.1 Built-In Function and Operator Reference12.2 Loadable Function Reference12.3 Type Conversion in Expression Evaluation12.4 Operators12.4.1 Operator Precedence12.4.2 Comparison Functions and Operators12.4.3 Logical Operators12.4.4 Assignment Operators12.5 Flow Control Functions12.6 Numeric Functions and Operators12.6.1 Arithmetic Operators12.6.2 Mathematical Functions12.7 Date and Time Functions12.8 String Functions and Operators12.8.1 String Comparison Functions and Operators12.8.2 Regular Expressions12.8.3 Character Set and Collation of Function Results12.9 What Calendar Is Used By MySQL?12.10 Full-Text Search Functions12.10.1 Natural Language Full-Text Searches12.10.2 Boolean Full-Text Searches12.10.3 Full-Text Searches with Query Expansion12.10.4 Full-Text Stopwords12.10.5 Full-Text Restrictions12.10.6 Fine-Tuning MySQL Full-Text Search12.10.7 Adding a User-Defined Collation for Full-Text Indexing12.10.8 ngram Full-Text Parser12.10.9 MeCab Full-Text Parser Plugin12.11 Cast Functions and Operators12.12 XML Functions12.13 Bit Functions and Operators12.14 Encryption and Compression Functions12.15 Locking Functions12.16 Information Functions12.17 Spatial Analysis Functions12.17.1 Spatial Function Reference12.17.2 Argument Handling by Spatial Functions12.17.3 Functions That Create Geometry Values from WKT Values12.17.4 Functions That Create Geometry Values from WKB Values12.17.5 MySQL-Specific Functions That Create Geometry Values12.17.6 Geometry Format Conversion Functions12.17.7 Geometry Property Functions12.17.8 Spatial Operator Functions12.17.9 Functions That Test Spatial Relations Between Geometry Objects12.17.10 Spatial Geohash Functions12.17.11 Spatial GeoJSON Functions12.17.12 Spatial Aggregate Functions12.17.13 Spatial Convenience Functions12.18 JSON Functions12.18.1 JSON Function Reference12.18.2 Functions That Create JSON Values12.18.3 Functions That Search JSON Values12.18.4 Functions That Modify JSON Values12.18.5 Functions That Return JSON Value Attributes12.18.6 JSON Table Functions12.18.7 JSON Schema Validation Functions12.18.8 JSON Utility Functions12.19 Functions Used with Global Transaction Identifiers [GTIDs]12.20 Aggregate Functions12.20.1 Aggregate Function Descriptions12.20.2 GROUP BY Modifiers12.20.3 MySQL Handling of GROUP BY12.20.4 Detection of Functional Dependence12.21 Window Functions12.21.1 Window Function Descriptions12.21.2 Window Function Concepts and Syntax12.21.3 Window Function Frame Specification12.21.4 Named Windows12.21.5 Window Function Restrictions12.22 Performance Schema Functions12.23 Internal Functions12.24 Miscellaneous Functions12.25 Precision Math12.25.1 Types of Numeric Values12.25.2 DECIMAL Data Type Characteristics12.25.3 Expression Handling12.25.4 Rounding Behavior12.25.5 Precision Math Examples13 SQL Statements13.1 Data Definition Statements13.1.1 Atomic Data Definition Statement Support13.1.2 ALTER DATABASE Statement13.1.3 ALTER EVENT Statement13.1.4 ALTER FUNCTION Statement13.1.5 ALTER INSTANCE Statement13.1.6 ALTER LOGFILE GROUP Statement13.1.7 ALTER PROCEDURE Statement13.1.8 ALTER SERVER Statement13.1.9 ALTER TABLE Statement13.1.10 ALTER TABLESPACE Statement13.1.11 ALTER VIEW Statement13.1.12 CREATE DATABASE Statement13.1.13 CREATE EVENT Statement13.1.14 CREATE FUNCTION Statement13.1.15 CREATE INDEX Statement13.1.16 CREATE LOGFILE GROUP Statement13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements13.1.18 CREATE SERVER Statement13.1.19 CREATE SPATIAL REFERENCE SYSTEM Statement13.1.20 CREATE TABLE Statement13.1.21 CREATE TABLESPACE Statement13.1.22 CREATE TRIGGER Statement13.1.23 CREATE VIEW Statement13.1.24 DROP DATABASE Statement13.1.25 DROP EVENT Statement13.1.26 DROP FUNCTION Statement13.1.27 DROP INDEX Statement13.1.28 DROP LOGFILE GROUP Statement13.1.29 DROP PROCEDURE and DROP FUNCTION Statements13.1.30 DROP SERVER Statement13.1.31 DROP SPATIAL REFERENCE SYSTEM Statement13.1.32 DROP TABLE Statement13.1.33 DROP TABLESPACE Statement13.1.34 DROP TRIGGER Statement13.1.35 DROP VIEW Statement13.1.36 RENAME TABLE Statement13.1.37 TRUNCATE TABLE Statement13.2 Data Manipulation Statements13.2.1 CALL Statement13.2.2 DELETE Statement13.2.3 DO Statement13.2.4 HANDLER Statement13.2.5 IMPORT TABLE Statement13.2.6 INSERT Statement13.2.7 LOAD DATA Statement13.2.8 LOAD XML Statement13.2.9 REPLACE Statement13.2.10 SELECT Statement13.2.11 Subqueries13.2.12 TABLE Statement13.2.13 UPDATE Statement13.2.14 VALUES Statement13.2.15 WITH [Common Table Expressions]13.3 Transactional and Locking Statements13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements13.3.2 Statements That Cannot Be Rolled Back13.3.3 Statements That Cause an Implicit Commit13.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements13.3.5 LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements13.3.6 LOCK TABLES and UNLOCK TABLES Statements13.3.7 SET TRANSACTION Statement13.3.8 XA Transactions13.4 Replication Statements13.4.1 SQL Statements for Controlling Source Servers13.4.2 SQL Statements for Controlling Replica Servers13.4.3 SQL Statements for Controlling Group Replication13.5 Prepared Statements13.5.1 PREPARE Statement13.5.2 EXECUTE Statement13.5.3 DEALLOCATE PREPARE Statement13.6 Compound Statement Syntax13.6.1 BEGIN ... END Compound Statement13.6.2 Statement Labels13.6.3 DECLARE Statement13.6.4 Variables in Stored Programs13.6.5 Flow Control Statements13.6.6 Cursors13.6.7 Condition Handling13.6.8 Restrictions on Condition Handling13.7 Database Administration Statements13.7.1 Account Management Statements13.7.2 Resource Group Management Statements13.7.3 Table Maintenance Statements13.7.4 Component, Plugin, and Loadable Function Statements13.7.5 CLONE Statement13.7.6 SET Statements13.7.7 SHOW Statements13.7.8 Other Administrative Statements13.8 Utility Statements13.8.1 DESCRIBE Statement13.8.2 EXPLAIN Statement13.8.3 HELP Statement13.8.4 USE Statement14 MySQL Data Dictionary14.1 Data Dictionary Schema14.2 Removal of File-based Metadata Storage14.3 Transactional Storage of Dictionary Data14.4 Dictionary Object Cache14.5 INFORMATION_SCHEMA and Data Dictionary Integration14.6 Serialized Dictionary Information [SDI]14.7 Data Dictionary Usage Differences14.8 Data Dictionary Limitations15 The InnoDB Storage Engine15.1 Introduction to InnoDB15.1.1 Benefits of Using InnoDB Tables15.1.2 Best Practices for InnoDB Tables15.1.3 Verifying that InnoDB is the Default Storage Engine15.1.4 Testing and Benchmarking with InnoDB15.2 InnoDB and the ACID Model15.3 InnoDB Multi-Versioning15.4 InnoDB Architecture15.5 InnoDB In-Memory Structures15.5.1 Buffer Pool15.5.2 Change Buffer15.5.3 Adaptive Hash Index15.5.4 Log Buffer15.6 InnoDB On-Disk Structures15.6.1 Tables15.6.2 Indexes15.6.3 Tablespaces15.6.4 Doublewrite Buffer15.6.5 Redo Log15.6.6 Undo Logs15.7 InnoDB Locking and Transaction Model15.7.1 InnoDB Locking15.7.2 InnoDB Transaction Model15.7.3 Locks Set by Different SQL Statements in InnoDB15.7.4 Phantom Rows15.7.5 Deadlocks in InnoDB15.7.6 Transaction Scheduling15.8 InnoDB Configuration15.8.1 InnoDB Startup Configuration15.8.2 Configuring InnoDB for Read-Only Operation15.8.3 InnoDB Buffer Pool Configuration15.8.4 Configuring Thread Concurrency for InnoDB15.8.5 Configuring the Number of Background InnoDB I/O Threads15.8.6 Using Asynchronous I/O on Linux15.8.7 Configuring InnoDB I/O Capacity15.8.8 Configuring Spin Lock Polling15.8.9 Purge Configuration15.8.10 Configuring Optimizer Statistics for InnoDB15.8.11 Configuring the Merge Threshold for Index Pages15.8.12 Enabling Automatic Configuration for a Dedicated MySQL Server15.9 InnoDB Table and Page Compression15.9.1 InnoDB Table Compression15.9.2 InnoDB Page Compression15.10 InnoDB Row Formats15.11 InnoDB Disk I/O and File Space Management15.11.1 InnoDB Disk I/O15.11.2 File Space Management15.11.3 InnoDB Checkpoints15.11.4 Defragmenting a Table15.11.5 Reclaiming Disk Space with TRUNCATE TABLE15.12 InnoDB and Online DDL15.12.1 Online DDL Operations15.12.2 Online DDL Performance and Concurrency15.12.3 Online DDL Space Requirements15.12.4 Online DDL Memory Management15.12.5 Configuring Parallel Threads for Online DDL Operations15.12.6 Simplifying DDL Statements with Online DDL15.12.7 Online DDL Failure Conditions15.12.8 Online DDL Limitations15.13 InnoDB Data-at-Rest Encryption15.14 InnoDB Startup Options and System Variables15.15 InnoDB INFORMATION_SCHEMA Tables15.15.1 InnoDB INFORMATION_SCHEMA Tables about Compression15.15.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information15.15.3 InnoDB INFORMATION_SCHEMA Schema Object Tables15.15.4 InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables15.15.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables15.15.6 InnoDB INFORMATION_SCHEMA Metrics Table15.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table15.15.8 Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES15.16 InnoDB Integration with MySQL Performance Schema15.16.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema15.16.2 Monitoring InnoDB Mutex Waits Using Performance Schema15.17 InnoDB Monitors15.17.1 InnoDB Monitor Types15.17.2 Enabling InnoDB Monitors15.17.3 InnoDB Standard Monitor and Lock Monitor Output15.18 InnoDB Backup and Recovery15.18.1 InnoDB Backup15.18.2 InnoDB Recovery15.19 InnoDB and MySQL Replication15.20 InnoDB memcached Plugin15.20.1 Benefits of the InnoDB memcached Plugin15.20.2 InnoDB memcached Architecture15.20.3 Setting Up the InnoDB memcached Plugin15.20.4 InnoDB memcached Multiple get and Range Query Support15.20.5 Security Considerations for the InnoDB memcached Plugin15.20.6 Writing Applications for the InnoDB memcached Plugin15.20.7 The InnoDB memcached Plugin and Replication15.20.8 InnoDB memcached Plugin Internals15.20.9 Troubleshooting the InnoDB memcached Plugin15.21 InnoDB Troubleshooting15.21.1 Troubleshooting InnoDB I/O Problems15.21.2 Troubleshooting Recovery Failures15.21.3 Forcing InnoDB Recovery15.21.4 Troubleshooting InnoDB Data Dictionary Operations15.21.5 InnoDB Error Handling15.22 InnoDB Limits15.23 InnoDB Restrictions and Limitations16 Alternative Storage Engines16.1 Setting the Storage Engine16.2 The MyISAM Storage Engine16.2.1 MyISAM Startup Options16.2.2 Space Needed for Keys16.2.3 MyISAM Table Storage Formats16.2.4 MyISAM Table Problems16.3 The MEMORY Storage Engine16.4 The CSV Storage Engine16.4.1 Repairing and Checking CSV Tables16.4.2 CSV Limitations16.5 The ARCHIVE Storage Engine16.6 The BLACKHOLE Storage Engine16.7 The MERGE Storage Engine16.7.1 MERGE Table Advantages and Disadvantages16.7.2 MERGE Table Problems16.8 The FEDERATED Storage Engine16.8.1 FEDERATED Storage Engine Overview16.8.2 How to Create FEDERATED Tables16.8.3 FEDERATED Storage Engine Notes and Tips16.8.4 FEDERATED Storage Engine Resources16.9 The EXAMPLE Storage Engine16.10 Other Storage Engines16.11 Overview of MySQL Storage Engine Architecture16.11.1 Pluggable Storage Engine Architecture16.11.2 The Common Database Server Layer17 Replication17.1 Configuring Replication17.1.1 Binary Log File Position Based Replication Configuration Overview17.1.2 Setting Up Binary Log File Position Based Replication17.1.3 Replication with Global Transaction Identifiers17.1.4 Changing GTID Mode on Online Servers17.1.5 MySQL Multi-Source Replication17.1.6 Replication and Binary Logging Options and Variables17.1.7 Common Replication Administration Tasks17.2 Replication Implementation17.2.1 Replication Formats17.2.2 Replication Channels17.2.3 Replication Threads17.2.4 Relay Log and Replication Metadata Repositories17.2.5 How Servers Evaluate Replication Filtering Rules17.3 Replication Security17.3.1 Setting Up Replication to Use Encrypted Connections17.3.2 Encrypting Binary Log Files and Relay Log Files17.3.3 Replication Privilege Checks17.4 Replication Solutions17.4.1 Using Replication for Backups17.4.2 Handling an Unexpected Halt of a Replica17.4.3 Monitoring Row-based Replication17.4.4 Using Replication with Different Source and Replica Storage Engines17.4.5 Using Replication for Scale-Out17.4.6 Replicating Different Databases to Different Replicas17.4.7 Improving Replication Performance17.4.8 Switching Sources During Failover17.4.9 Switching Sources and Replicas with Asynchronous Connection Failover17.4.10 Semisynchronous Replication17.4.11 Delayed Replication17.5 Replication Notes and Tips17.5.1 Replication Features and Issues17.5.2 Replication Compatibility Between MySQL Versions17.5.3 Upgrading a Replication Topology17.5.4 Troubleshooting Replication17.5.5 How to Report Replication Bugs or Problems18 Group Replication18.1 Group Replication Background18.1.1 Replication Technologies18.1.2 Group Replication Use Cases18.1.3 Multi-Primary and Single-Primary Modes18.1.4 Group Replication Services18.1.5 Group Replication Plugin Architecture18.2 Getting Started18.2.1 Deploying Group Replication in Single-Primary Mode18.2.2 Deploying Group Replication Locally18.3 Requirements and Limitations18.3.1 Group Replication Requirements18.3.2 Group Replication Limitations18.4 Monitoring Group Replication18.4.1 GTIDs and Group Replication18.4.2 Group Replication Server States18.4.3 The replication_group_members Table18.4.4 The replication_group_member_stats Table18.5 Group Replication Operations18.5.1 Configuring an Online Group18.5.2 Restarting a Group18.5.3 Transaction Consistency Guarantees18.5.4 Distributed Recovery18.5.5 Support For IPv6 And For Mixed IPv6 And IPv4 Groups18.5.6 Using MySQL Enterprise Backup with Group Replication18.6 Group Replication Security18.6.1 Methods for Connection Security Management18.6.2 Securing Group Communication Connections with Secure Socket Layer [SSL]18.6.3 Securing Distributed Recovery Connections18.6.4 Group Replication IP Address Permissions18.7 Group Replication Performance and Troubleshooting18.7.1 Fine Tuning the Group Communication Thread18.7.2 Flow Control18.7.3 Single Consensus Leader18.7.4 Message Compression18.7.5 Message Fragmentation18.7.6 XCom Cache Management18.7.7 Responses to Failure Detection and Network Partitioning18.7.8 Handling a Network Partition and Loss of Quorum18.8 Upgrading Group Replication18.8.1 Combining Different Member Versions in a Group18.8.2 Group Replication Offline Upgrade18.8.3 Group Replication Online Upgrade18.9 Group Replication System Variables18.10 Frequently Asked Questions19 MySQL Shell20 Using MySQL as a Document Store20.1 Interfaces to a MySQL Document Store20.2 Document Store Concepts20.3 JavaScript Quick-Start Guide: MySQL Shell for Document Store20.3.1 MySQL Shell20.3.2 Download and Import world_x Database20.3.3 Documents and Collections20.3.4 Relational Tables20.3.5 Documents in Tables20.4 Python Quick-Start Guide: MySQL Shell for Document Store20.4.1 MySQL Shell20.4.2 Download and Import world_x Database20.4.3 Documents and Collections20.4.4 Relational Tables20.4.5 Documents in Tables20.5 X Plugin20.5.1 Checking X Plugin Installation20.5.2 Disabling X Plugin20.5.3 Using Encrypted Connections with X Plugin20.5.4 Using X Plugin with the Caching SHA-2 Authentication Plugin20.5.5 Connection Compression with X Plugin20.5.6 X Plugin Options and Variables20.5.7 Monitoring X Plugin21 InnoDB Cluster22 InnoDB ReplicaSet23 MySQL NDB Cluster 8.023.1 General Information23.2 NDB Cluster Overview23.2.1 NDB Cluster Core Concepts23.2.2 NDB Cluster Nodes, Node Groups, Fragment Replicas, and Partitions23.2.3 NDB Cluster Hardware, Software, and Networking Requirements23.2.4 What is New in NDB Cluster23.2.5 Options, Variables, and Parameters Added, Deprecated or Removed in NDB 8.023.2.6 MySQL Server Using InnoDB Compared with NDB Cluster23.2.7 Known Limitations of NDB Cluster23.3 NDB Cluster Installation23.3.1 Installation of NDB Cluster on Linux23.3.2 Installing NDB Cluster on Windows23.3.3 Initial Configuration of NDB Cluster23.3.4 Initial Startup of NDB Cluster23.3.5 NDB Cluster Example with Tables and Data23.3.6 Safe Shutdown and Restart of NDB Cluster23.3.7 Upgrading and Downgrading NDB Cluster23.3.8 The NDB Cluster Auto-Installer [No longer supported]23.4 Configuration of NDB Cluster23.4.1 Quick Test Setup of NDB Cluster23.4.2 Overview of NDB Cluster Configuration Parameters, Options, and Variables23.4.3 NDB Cluster Configuration Files23.4.4 Using High-Speed Interconnects with NDB Cluster23.5 NDB Cluster Programs23.5.1 ndbd — The NDB Cluster Data Node Daemon23.5.2 ndbinfo_select_all — Select From ndbinfo Tables23.5.3 ndbmtd — The NDB Cluster Data Node Daemon [Multi-Threaded]23.5.4 ndb_mgmd — The NDB Cluster Management Server Daemon23.5.5 ndb_mgm — The NDB Cluster Management Client23.5.6 ndb_blob_tool — Check and Repair BLOB and TEXT columns of NDB Cluster Tables23.5.7 ndb_config — Extract NDB Cluster Configuration Information23.5.8 ndb_delete_all — Delete All Rows from an NDB Table23.5.9 ndb_desc — Describe NDB Tables23.5.10 ndb_drop_index — Drop Index from an NDB Table23.5.11 ndb_drop_table — Drop an NDB Table23.5.12 ndb_error_reporter — NDB Error-Reporting Utility23.5.13 ndb_import — Import CSV Data Into NDB23.5.14 ndb_index_stat — NDB Index Statistics Utility23.5.15 ndb_move_data — NDB Data Copy Utility23.5.16 ndb_perror — Obtain NDB Error Message Information23.5.17 ndb_print_backup_file — Print NDB Backup File Contents23.5.18 ndb_print_file — Print NDB Disk Data File Contents23.5.19 ndb_print_frag_file — Print NDB Fragment List File Contents23.5.20 ndb_print_schema_file — Print NDB Schema File Contents23.5.21 ndb_print_sys_file — Print NDB System File Contents23.5.22 ndb_redo_log_reader — Check and Print Content of Cluster Redo Log23.5.23 ndb_restore — Restore an NDB Cluster Backup23.5.24 ndb_select_all — Print Rows from an NDB Table23.5.25 ndb_select_count — Print Row Counts for NDB Tables23.5.26 ndb_setup.py — Start browser-based Auto-Installer for NDB Cluster [DEPRECATED]23.5.27 ndb_show_tables — Display List of NDB Tables23.5.28 ndb_size.pl — NDBCLUSTER Size Requirement Estimator23.5.29 ndb_top — View CPU usage information for NDB threads23.5.30 ndb_waiter — Wait for NDB Cluster to Reach a Given Status23.5.31 ndbxfrm — Compress, Decompress, Encrypt, and Decrypt Files Created by NDB Cluster23.6 Management of NDB Cluster23.6.1 Commands in the NDB Cluster Management Client23.6.2 NDB Cluster Log Messages23.6.3 Event Reports Generated in NDB Cluster23.6.4 Summary of NDB Cluster Start Phases23.6.5 Performing a Rolling Restart of an NDB Cluster23.6.6 NDB Cluster Single User Mode23.6.7 Adding NDB Cluster Data Nodes Online23.6.8 Online Backup of NDB Cluster23.6.9 MySQL Server Usage for NDB Cluster23.6.10 NDB Cluster Disk Data Tables23.6.11 Online Operations with ALTER TABLE in NDB Cluster23.6.12 Privilege Synchronization and NDB_STORED_USER23.6.13 NDB API Statistics Counters and Variables23.6.14 ndbinfo: The NDB Cluster Information Database23.6.15 INFORMATION_SCHEMA Tables for NDB Cluster23.6.16 ndbcluster Plugin Threads23.6.17 Quick Reference: NDB Cluster SQL Statements23.6.18 NDB Cluster Security Issues23.7 NDB Cluster Replication23.7.1 NDB Cluster Replication: Abbreviations and Symbols23.7.2 General Requirements for NDB Cluster Replication23.7.3 Known Issues in NDB Cluster Replication23.7.4 NDB Cluster Replication Schema and Tables23.7.5 Preparing the NDB Cluster for Replication23.7.6 Starting NDB Cluster Replication [Single Replication Channel]23.7.7 Using Two Replication Channels for NDB Cluster Replication23.7.8 Implementing Failover with NDB Cluster Replication23.7.9 NDB Cluster Backups With NDB Cluster Replication23.7.10 NDB Cluster Replication: Bidirectional and Circular Replication23.7.11 NDB Cluster Replication Conflict Resolution23.8 NDB Cluster Release Notes24 Partitioning24.1 Overview of Partitioning in MySQL24.2 Partitioning Types24.2.1 RANGE Partitioning24.2.2 LIST Partitioning24.2.3 COLUMNS Partitioning24.2.4 HASH Partitioning24.2.5 KEY Partitioning24.2.6 Subpartitioning24.2.7 How MySQL Partitioning Handles NULL24.3 Partition Management24.3.1 Management of RANGE and LIST Partitions24.3.2 Management of HASH and KEY Partitions24.3.3 Exchanging Partitions and Subpartitions with Tables24.3.4 Maintenance of Partitions24.3.5 Obtaining Information About Partitions24.4 Partition Pruning24.5 Partition Selection24.6 Restrictions and Limitations on Partitioning24.6.1 Partitioning Keys, Primary Keys, and Unique Keys24.6.2 Partitioning Limitations Relating to Storage Engines24.6.3 Partitioning Limitations Relating to Functions25 Stored Objects25.1 Defining Stored Programs25.2 Using Stored Routines25.2.1 Stored Routine Syntax25.2.2 Stored Routines and MySQL Privileges25.2.3 Stored Routine Metadata25.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID[]25.3 Using Triggers25.3.1 Trigger Syntax and Examples25.3.2 Trigger Metadata25.4 Using the Event Scheduler25.4.1 Event Scheduler Overview25.4.2 Event Scheduler Configuration25.4.3 Event Syntax25.4.4 Event Metadata25.4.5 Event Scheduler Status25.4.6 The Event Scheduler and MySQL Privileges25.5 Using Views25.5.1 View Syntax25.5.2 View Processing Algorithms25.5.3 Updatable and Insertable Views25.5.4 The View WITH CHECK OPTION Clause25.5.5 View Metadata25.6 Stored Object Access Control25.7 Stored Program Binary Logging25.8 Restrictions on Stored Programs25.9 Restrictions on Views26 INFORMATION_SCHEMA Tables26.1 Introduction26.2 INFORMATION_SCHEMA Table Reference26.3 INFORMATION_SCHEMA General Tables26.3.1 INFORMATION_SCHEMA General Table Reference26.3.2 The INFORMATION_SCHEMA ADMINISTRABLE_ROLE_AUTHORIZATIONS Table26.3.3 The INFORMATION_SCHEMA APPLICABLE_ROLES Table26.3.4 The INFORMATION_SCHEMA CHARACTER_SETS Table26.3.5 The INFORMATION_SCHEMA CHECK_CONSTRAINTS Table26.3.6 The INFORMATION_SCHEMA COLLATIONS Table26.3.7 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table26.3.8 The INFORMATION_SCHEMA COLUMNS Table26.3.9 The INFORMATION_SCHEMA COLUMNS_EXTENSIONS Table26.3.10 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table26.3.11 The INFORMATION_SCHEMA COLUMN_STATISTICS Table26.3.12 The INFORMATION_SCHEMA ENABLED_ROLES Table26.3.13 The INFORMATION_SCHEMA ENGINES Table26.3.14 The INFORMATION_SCHEMA EVENTS Table26.3.15 The INFORMATION_SCHEMA FILES Table26.3.16 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table26.3.17 The INFORMATION_SCHEMA KEYWORDS Table26.3.18 The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table26.3.19 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table26.3.20 The INFORMATION_SCHEMA PARAMETERS Table26.3.21 The INFORMATION_SCHEMA PARTITIONS Table26.3.22 The INFORMATION_SCHEMA PLUGINS Table26.3.23 The INFORMATION_SCHEMA PROCESSLIST Table26.3.24 The INFORMATION_SCHEMA PROFILING Table26.3.25 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table26.3.26 The INFORMATION_SCHEMA RESOURCE_GROUPS Table26.3.27 The INFORMATION_SCHEMA ROLE_COLUMN_GRANTS Table26.3.28 The INFORMATION_SCHEMA ROLE_ROUTINE_GRANTS Table26.3.29 The INFORMATION_SCHEMA ROLE_TABLE_GRANTS Table26.3.30 The INFORMATION_SCHEMA ROUTINES Table26.3.31 The INFORMATION_SCHEMA SCHEMATA Table26.3.32 The INFORMATION_SCHEMA SCHEMATA_EXTENSIONS Table26.3.33 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table26.3.34 The INFORMATION_SCHEMA STATISTICS Table26.3.35 The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table26.3.36 The INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS Table26.3.37 The INFORMATION_SCHEMA ST_UNITS_OF_MEASURE Table26.3.38 The INFORMATION_SCHEMA TABLES Table26.3.39 The INFORMATION_SCHEMA TABLES_EXTENSIONS Table26.3.40 The INFORMATION_SCHEMA TABLESPACES Table26.3.41 The INFORMATION_SCHEMA TABLESPACES_EXTENSIONS Table26.3.42 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table26.3.43 The INFORMATION_SCHEMA TABLE_CONSTRAINTS_EXTENSIONS Table26.3.44 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table26.3.45 The INFORMATION_SCHEMA TRIGGERS Table26.3.46 The INFORMATION_SCHEMA USER_ATTRIBUTES Table26.3.47 The INFORMATION_SCHEMA USER_PRIVILEGES Table26.3.48 The INFORMATION_SCHEMA VIEWS Table26.3.49 The INFORMATION_SCHEMA VIEW_ROUTINE_USAGE Table26.3.50 The INFORMATION_SCHEMA VIEW_TABLE_USAGE Table26.4 INFORMATION_SCHEMA InnoDB Tables26.4.1 INFORMATION_SCHEMA InnoDB Table Reference26.4.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table26.4.3 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table26.4.4 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table26.4.5 The INFORMATION_SCHEMA INNODB_CACHED_INDEXES Table26.4.6 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables26.4.7 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables26.4.8 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables26.4.9 The INFORMATION_SCHEMA INNODB_COLUMNS Table26.4.10 The INFORMATION_SCHEMA INNODB_DATAFILES Table26.4.11 The INFORMATION_SCHEMA INNODB_FIELDS Table26.4.12 The INFORMATION_SCHEMA INNODB_FOREIGN Table26.4.13 The INFORMATION_SCHEMA INNODB_FOREIGN_COLS Table26.4.14 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table26.4.15 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table26.4.16 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table26.4.17 The INFORMATION_SCHEMA INNODB_FT_DELETED Table26.4.18 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table26.4.19 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table26.4.20 The INFORMATION_SCHEMA INNODB_INDEXES Table26.4.21 The INFORMATION_SCHEMA INNODB_METRICS Table26.4.22 The INFORMATION_SCHEMA INNODB_SESSION_TEMP_TABLESPACES Table26.4.23 The INFORMATION_SCHEMA INNODB_TABLES Table26.4.24 The INFORMATION_SCHEMA INNODB_TABLESPACES Table26.4.25 The INFORMATION_SCHEMA INNODB_TABLESPACES_BRIEF Table26.4.26 The INFORMATION_SCHEMA INNODB_TABLESTATS View26.4.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table26.4.28 The INFORMATION_SCHEMA INNODB_TRX Table26.4.29 The INFORMATION_SCHEMA INNODB_VIRTUAL Table26.5 INFORMATION_SCHEMA Thread Pool Tables26.5.1 INFORMATION_SCHEMA Thread Pool Table Reference26.5.2 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table26.5.3 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table26.5.4 The INFORMATION_SCHEMA TP_THREAD_STATE Table26.6 INFORMATION_SCHEMA Connection-Control Tables26.6.1 INFORMATION_SCHEMA Connection-Control Table Reference26.6.2 The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Table26.7 INFORMATION_SCHEMA MySQL Enterprise Firewall Tables26.7.1 INFORMATION_SCHEMA Firewall Table Reference26.7.2 The INFORMATION_SCHEMA MYSQL_FIREWALL_USERS Table26.7.3 The INFORMATION_SCHEMA MYSQL_FIREWALL_WHITELIST Table26.8 Extensions to SHOW Statements27 MySQL Performance Schema27.1 Performance Schema Quick Start27.2 Performance Schema Build Configuration27.3 Performance Schema Startup Configuration27.4 Performance Schema Runtime Configuration27.4.1 Performance Schema Event Timing27.4.2 Performance Schema Event Filtering27.4.3 Event Pre-Filtering27.4.4 Pre-Filtering by Instrument27.4.5 Pre-Filtering by Object27.4.6 Pre-Filtering by Thread27.4.7 Pre-Filtering by Consumer27.4.8 Example Consumer Configurations27.4.9 Naming Instruments or Consumers for Filtering Operations27.4.10 Determining What Is Instrumented27.5 Performance Schema Queries27.6 Performance Schema Instrument Naming Conventions27.7 Performance Schema Status Monitoring27.8 Performance Schema Atom and Molecule Events27.9 Performance Schema Tables for Current and Historical Events27.10 Performance Schema Statement Digests and Sampling27.11 Performance Schema General Table Characteristics27.12 Performance Schema Table Descriptions27.12.1 Performance Schema Table Reference27.12.2 Performance Schema Setup Tables27.12.3 Performance Schema Instance Tables27.12.4 Performance Schema Wait Event Tables27.12.5 Performance Schema Stage Event Tables27.12.6 Performance Schema Statement Event Tables27.12.7 Performance Schema Transaction Tables27.12.8 Performance Schema Connection Tables27.12.9 Performance Schema Connection Attribute Tables27.12.10 Performance Schema User-Defined Variable Tables27.12.11 Performance Schema Replication Tables27.12.12 Performance Schema NDB Cluster Tables27.12.13 Performance Schema Lock Tables27.12.14 Performance Schema System Variable Tables27.12.15 Performance Schema Status Variable Tables27.12.16 Performance Schema Thread Pool Tables27.12.17 Performance Schema Firewall Tables27.12.18 Performance Schema Keyring Tables27.12.19 Performance Schema Clone Tables27.12.20 Performance Schema Summary Tables27.12.21 Performance Schema Miscellaneous Tables27.13 Performance Schema Option and Variable Reference27.14 Performance Schema Command Options27.15 Performance Schema System Variables27.16 Performance Schema Status Variables27.17 The Performance Schema Memory-Allocation Model27.18 Performance Schema and Plugins27.19 Using the Performance Schema to Diagnose Problems27.19.1 Query Profiling Using Performance Schema27.19.2 Obtaining Parent Event Information27.20 Restrictions on Performance Schema28 MySQL sys Schema28.1 Prerequisites for Using the sys Schema28.2 Using the sys Schema28.3 sys Schema Progress Reporting28.4 sys Schema Object Reference28.4.1 sys Schema Object Index28.4.2 sys Schema Tables and Triggers28.4.3 sys Schema Views28.4.4 sys Schema Stored Procedures28.4.5 sys Schema Stored Functions29 Connectors and APIs29.1 MySQL Connector/C++29.2 MySQL Connector/J29.3 MySQL Connector/NET29.4 MySQL Connector/ODBC29.5 MySQL Connector/Python29.6 MySQL Connector/Node.js29.7 MySQL C API29.8 MySQL PHP API29.9 MySQL Perl API29.10 MySQL Python API29.11 MySQL Ruby APIs29.11.1 The MySQL/Ruby API29.11.2 The Ruby/MySQL API29.12 MySQL Tcl API29.13 MySQL Eiffel Wrapper30 MySQL Enterprise Edition30.1 MySQL Enterprise Monitor Overview30.2 MySQL Enterprise Backup Overview30.3 MySQL Enterprise Security Overview30.4 MySQL Enterprise Encryption Overview30.5 MySQL Enterprise Audit Overview30.6 MySQL Enterprise Firewall Overview30.7 MySQL Enterprise Thread Pool Overview30.8 MySQL Enterprise Data Masking and De-Identification Overview31 MySQL Workbench32 MySQL on the OCI Marketplace32.1 Prerequisites to Deploying MySQL EE on Oracle Cloud Infrastructure32.2 Deploying MySQL EE on Oracle Cloud Infrastructure32.3 Configuring Network Access32.4 Connecting32.5 MaintenanceA MySQL 8.0 Frequently Asked QuestionsA.1 MySQL 8.0 FAQ: GeneralA.2 MySQL 8.0 FAQ: Storage EnginesA.3 MySQL 8.0 FAQ: Server SQL ModeA.4 MySQL 8.0 FAQ: Stored Procedures and FunctionsA.5 MySQL 8.0 FAQ: TriggersA.6 MySQL 8.0 FAQ: ViewsA.7 MySQL 8.0 FAQ: INFORMATION_SCHEMAA.8 MySQL 8.0 FAQ: MigrationA.9 MySQL 8.0 FAQ: SecurityA.10 MySQL 8.0 FAQ: NDB ClusterA.11 MySQL 8.0 FAQ: MySQL Chinese, Japanese, and Korean Character SetsA.12 MySQL 8.0 FAQ: Connectors & APIsA.13 MySQL 8.0 FAQ: C API, libmysqlA.14 MySQL 8.0 FAQ: ReplicationA.15 MySQL 8.0 FAQ: MySQL Enterprise Thread PoolA.16 MySQL 8.0 FAQ: InnoDB Change BufferA.17 MySQL 8.0 FAQ: InnoDB Data-at-Rest EncryptionA.18 MySQL 8.0 FAQ: Virtualization SupportB Error Messages and Common ProblemsB.1 Error Message Sources and ElementsB.2 Error Information InterfacesB.3 Problems and Common ErrorsB.3.1 How to Determine What Is Causing a ProblemB.3.2 Common Errors When Using MySQL ProgramsB.3.3 Administration-Related IssuesB.3.4 Query-Related IssuesB.3.5 Optimizer-Related IssuesB.3.6 Table Definition-Related IssuesB.3.7 Known Issues in MySQLC IndexesGeneral IndexC Function IndexCommand IndexFunction IndexINFORMATION_SCHEMA IndexJoin Types IndexOperator IndexOption IndexPrivileges IndexSQL Modes IndexStatement/Syntax IndexStatus Variable IndexSystem Variable IndexTransaction Isolation Level IndexMySQL Glossary