Chapter 19 Using MySQL as a Document Store
Prev   Next

Chapter 19 Using MySQL as a Document Store

Table of Contents

19.1 Key Concepts
19.2 Setting Up MySQL as a Document Store
19.2.1 Installing MySQL Shell
19.2.2 Starting MySQL Shell
19.3 Quick-Start Guide: MySQL for Visual Studio
19.4 X Plugin
19.4.1 Using Secure Connections with X Plugin
19.4.2 X Plugin Options and Variables
19.4.3 Monitoring X Plugin

This chapter introduces an alternative way of working with MySQL as a document store, sometimes referred to as “using NoSQL”. If your intention is to use MySQL in a traditional (SQL) way, this chapter is probably not relevant to you.

Important

MySQL Shell 8.0 is the most recent version and is highly recommended for use with MySQL Server 5.7. Please upgrade to MySQL Shell 8.0. If you have not yet installed MySQL Shell, download it from the download site. See the MySQL Shell 8.0 (part of MySQL 8.0) documentation for the latest documentation. This chapter covers configuring MySQL 5.7 server as a document store and is compatible with version 8.0 clients such as MySQL Shell and MySQL Connectors.

Relational databases such as MySQL usually required a document schema to be defined before documents can be stored. The features described in this section enable you to use MySQL as a document store, which is a schema-less, and therefore schema-flexible, storage system for documents. When using MySQL as a document store, to create documents describing products you do not need to know and define all possible attributes of any products before storing them and operating with them. This differs from working with a relational database and storing products in a table, when all columns of the table must be known and defined before adding any products to the database. The features described in this chapter enable you to choose how you configure MySQL, using only the document store model, or combining the flexibility of the document store model with the power of the relational model.

These sections cover the usage of MySQL as a document store:

19.1 Key Concepts

This section explains the concepts introduced as part of using MySQL as a document store.

Document

A Document is a set of key and value pairs, as represented by a JSON object. A Document is represented internally using the MySQL binary JSON object, through the JSON MySQL datatype. The values of fields can contain other documents, arrays, and lists of documents. 

{
    "GNP": .6,
    "IndepYear": 1967,
    "Name": "Sealand",
    "_id": "SEA",
    "demographics": {
        "LifeExpectancy": 79,
        "Population": 27
    },
    "geography": {
        "Continent": "Europe",
        "Region": "British Islands",
        "SurfaceArea": 193
    },
    "government": {
        "GovernmentForm": "Monarchy",
        "HeadOfState": "Michael Bates"
    }
}

Collection

A Collection is a container that may be used to store Documents in a MySQL database.

CRUD Operations

Create, Read, Update and Delete (CRUD) operations are the four basic operations that can be performed on a database Collection or Table. In terms of MySQL this means:

  • Create a new entry (insertion or addition)

  • Read entries (queries)

  • Update entries

  • Delete entries

X Plugin

The MySQL Server plugin which enables communication using X Protocol. Supports clients that implement X DevAPI and enables you to use MySQL as a document store.

X Protocol

A protocol to communicate with a MySQL Server running X Plugin. X Protocol supports both CRUD and SQL operations, authentication via SASL, allows streaming (pipelining) of commands and is extensible on the protocol and the message layer.

19.2 Setting Up MySQL as a Document Store

19.2.1 Installing MySQL Shell
19.2.2 Starting MySQL Shell

To use MySQL 5.7 as a document store, the X Plugin needs to be installed. Then you can use X Protocol to communicate with the server. Without the X Plugin running, X Protocol clients cannot connect to the server. The X Plugin is supplied with MySQL (5.7.12 or higher) — installing it does not involve a separate download. This section describes how to install X Plugin.

Follow the steps outlined here:

  1. Install or upgrade to MySQL 5.7.12 or higher.

    When the installation or upgrade is done, start the server. For server startup instructions, see Section 2.10.2, “Starting the Server”.

    Note

    MySQL Installer enables you to perform this and the next step (Install the X Plugin) at the same time for new installations on Microsoft Windows. In the Plugin and Extensions screen, check mark the Enable X Protocol/MySQL as a Document Store check box. After the installation, verify that the X Plugin has been installed.

  2. Install the X Plugin. A non-root account can be used to install the plugin as long as the account has INSERT privilege for the mysql.plugin table.

    Always save your existing configuration settings before reconfiguring the server.

    To install the built-in X Plugin, do one of the following:

    • Using MySQL Installer for Windows:

      1. Launch MySQL Installer for Windows. MySQL Installer dashboard opens.

      2. Click the Reconfigure quick action for MySQL Server. Use Next and Back to configure the following items:

        • In Accounts and Roles, confirm the current root account password.

        • In Plugin and Extensions, check mark the Enable X Protocol/MySQL as a Document Store check box. MySQL Installer provides a default port number and opens the firewall port for network access.

        • In Apply Server Configuration, click Execute.

        • Click Finish to close MySQL Installer.

      3. Install MySQL Shell.

    • Using MySQL Shell:

      1. Install MySQL Shell.

      2. Open a terminal window (command prompt on Windows) and navigate to the MySQL binaries location (for example, /usr/bin/ on Linux).

      3. Run the following command: 

        mysqlsh -u user -h localhost --classic --dba enableXProtocol

    • Using the MySQL Client program:

      1. Open a terminal window (command prompt on Windows) and navigate to the MySQL binaries location (for example, /usr/bin/ on Linux).

      2. Invoke the mysql command-line client: 

        mysql -u user -p

      3. Issue the following statement: 

        mysql> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';

        Replace mysqlx.so with mysqlx.dll for Windows.

        Important

        The mysql.session user must exist before you can load X Plugin. mysql.session was added in MySQL version 5.7.19. If your data dictionary was initialized using an earlier version you must run the mysql_upgrade procedure. If the upgrade is not run, X Plugin fails to start with the error message There was an error when trying to access the server with user: mysql.session@localhost. Make sure the user is present in the server and that mysql_upgrade was ran after a server update..

      4. Install MySQL Shell.

  3. Verify that the X Plugin has been installed.

    When the X Plugin is installed properly, it shows up in the list when you query for active plugins on the server with one of the following commands:

    • MySQL Shell command: 

      mysqlsh -u user --sqlc -e "show plugins"

    • MySQL Client program command: 

      mysql -u user -p -e "show plugins"

    If you encounter problems with the X Plugin installation, or if you want to learn about alternative ways of installing, configuring, or uninstalling server plugins, see Section 5.5.1, “Installing and Uninstalling Plugins”.

mysqlxsys@localhost User Account

Installing the X Plugin creates a mysqlxsys@localhost user account. If, for some reason, creating the user account fails, the X Plugin installation fails, too. Here is an explanation on what the mysqlxsys@localhost user account is for and what to do when its creation fails.

The X Plugin installation process uses the MySQL root user to create an internal account for the mysqlxsys@localhost user. The mysqlxsys@localhost account is used by the X Plugin for authentication of external users against the MySQL account system and for killing sessions when requested by a privileged user. The mysqlxsys@localhost account is created as locked, so it cannot be used to log in by external users. If for some reason the MySQL root account is not available, before you start the X Plugin installation you must manually create the mysqlxsys@localhost user by issuing the following statements in the mysql command-line client: 

CREATE USER IF NOT EXISTS mysqlxsys@localhost IDENTIFIED WITH
mysql_native_password AS 'password' ACCOUNT LOCK;
GRANT SELECT ON mysql.user TO mysqlxsys@localhost;
GRANT SUPER ON *.* TO mysqlxsys@localhost;

Uninstalling the X Plugin

If you ever want to uninstall (deactivate) the X Plugin, issue the following statement in the mysql command-line client: 

UNINSTALL PLUGIN mysqlx;

Do not use MySQL Shell to issue the previous statement. It works from MySQL Shell, but you get an error (code 1130). Also, uninstalling the plugin removes the mysqlxsys user.

19.2.1 Installing MySQL Shell

19.2.1.1 Installing MySQL Shell on Microsoft Windows
19.2.1.2 Installing MySQL Shell on Linux
19.2.1.3 Installing MySQL Shell on macOS

This section describes how to download, install, and start MySQL Shell, which is an interactive JavaScript, Python, or SQL interface supporting development and administration for the MySQL Server. MySQL Shell is a component that you can install separately.

Requirements

MySQL Shell is available on Microsoft Windows, Linux, and macOS for 64-bit platforms. MySQL Shell requires that the built-in X Plugin be active. You can install the server plugin before or after you install MySQL Shell. For instructions, see Installing the X Plugin.

19.2.1.1 Installing MySQL Shell on Microsoft Windows

Important

The Community version of MySQL Shell requires the Visual C++ Redistributable for Visual Studio 2013 (available at the Microsoft Download Center) to work; make sure that is installed on your Windows system before installing MySQL Shell.

Note

MySQL Shell is currently not supplied with an MSI Installer. See Installing MySQL Shell Binaries for the manual install procedure.

To install MySQL Shell on Microsoft Windows using the MSI Installer, do the following:

  1. Download the Windows (x86, 64-bit), MSI Installer package from http://dev.mysql.com/downloads/shell/.

  2. When prompted, click Run.

  3. Follow the steps in the Setup Wizard.

    Figure 19.1 Installation of MySQL Shell on Windows

    Installation of MySQL Shell on Windows

If you have installed MySQL without enabling the X Plugin, then later on decide you want to install the X Plugin, or if you are installing MySQL without using MySQL Installer, see Installing the X Plugin.

Installing MySQL Shell Binaries

To install MySQL Shell binaries:

  1. Unzip the content of the Zip file to the MySQL products directory, for example C:\Program Files\MySQL\.

  2. To be able to start MySQL Shell from a command prompt add the bin directory C:\Program Files\MySQL\mysql-shell-1.0.8-rc-windows-x86-64bit\bin to the PATH system variable.

19.2.1.2 Installing MySQL Shell on Linux

Note

Installation packages for MySQL Shell are available only for a limited number of Linux distributions, and only for 64-bit systems.

For supported Linux distributions, the easiest way to install MySQL Shell on Linux is to use the MySQL APT repository or MySQL Yum repository. For systems not using the MySQL repositories, MySQL Shell can also be downloaded and installed directly.

Installing MySQL Shell with the MySQL APT Repository

For Linux distributions supported by the MySQL APT repository, follow one of the paths below:

  • If you do not yet have the MySQL APT repository as a software repository on your system, do the following:

    • Follow the steps given in Adding the MySQL APT Repository, paying special attention to the following:

      • During the installation of the configuration package, when asked in the dialogue box to configure the repository, make sure you choose MySQL 5.7 (which is the default option) as the release series you want, and enable the MySQL Preview Packages component.

      • Make sure you do not skip the step for updating package information for the MySQL APT repository: 

        sudo apt-get update

    • Install MySQL Shell with this command: 

      sudo apt-get install mysql-shell

  • If you already have the MySQL APT repository as a software repository on your system, do the following:

    • Update package information for the MySQL APT repository: 

      sudo apt-get update

    • Update the MySQL APT repository configuration package with the following command: 

      sudo apt-get install mysql-apt-config

      When asked in the dialogue box to configure the repository, make sure you choose MySQL 5.7 (which is the default option) as the release series you want, and enable the MySQL Preview Packages component.

    • Install MySQL Shell with this command: 

      sudo apt-get install mysql-shell
Installing MySQL Shell with the MySQL Yum Repository

For Linux distributions supported by the MySQL Yum repository, follow these steps to install MySQL Shell:

  • Do one of the following:

    • If you already have the MySQL Yum repository as a software repository on your system and the repository was configured with the new release package mysql57-community-release, skip to the next step (“Enable the MySQL Tools Preview subrepository...”).

    • If you already have the MySQL Yum repository as a software repository on your system but have configured the repository with the old release package mysql-community-release, it is easiest to install MySQL Shell by first reconfiguring the MySQL Yum repository with the new mysql57-community-release package. To do so, you need to remove your old release package first, with the following command : 

      sudo yum remove mysql-community-release

      For dnf-enabled systems, do this instead: 

      sudo dnf erase mysql-community-release

      Then, follow the steps given in Adding the MySQL Yum Repository to install the new release package, mysql57-community-release.

    • If you do not yet have the MySQL Yum repository as a software repository on your system, follow the steps given in Adding the MySQL Yum Repository.

  • Enable the MySQL Tools Preview subrepository. You can do that by editing manually the /etc/yum.repos.d/mysql-community.repo file. This is an example of the subrepository's default entry in the file (the baseurl entry in your file might look different, depending on your Linux distribution): 

    [mysql-tools-preview]
name=MySQL Tools Preview
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

    Change the entry enabled=0 to enabled=1 to enable the subrepository.

  • Install MySQL Shell with this command: 

    sudo yum install mysql-shell

    For dnf-enabled systems, do this instead: 

    sudo dnf install mysql-shell
Installing MySQL Shell from Direct Downloads from the MySQL Developer Zone

RPM, Debian, and source packages for installing MySQL Shell are also available for download at Download MySQL Shell.

19.2.1.3 Installing MySQL Shell on macOS

To install MySQL Shell on macOS, do the following:

  1. Download the package from http://dev.mysql.com/downloads/shell/.

  2. Double-click the downloaded DMG to mount it. Finder opens.

  3. Double-click the .pkg file shown in the Finder window.

  4. Follow the steps in the installation wizard.

    Figure 19.2 Installation of MySQL Shell on macOS

    Installation of MySQL Shell on macOS

  5. When the installer finishes, eject the DMG. (It can be deleted.)

19.2.2 Starting MySQL Shell

You need an account name and password to establish a session using MySQL Shell. Replace user with your account name.

On the same system where the server instance is running, open a terminal window (command prompt on Windows) and start MySQL Shell with the following command: 

mysqlsh --uri user@localhost

You are prompted to input your password and then this establishes an X Session.

For instructions to get you started using MySQL as a document store, see the following quick-start guides:

19.3 Quick-Start Guide: MySQL for Visual Studio

This section explains how to use MySQL Shell to script a server using MySQL for Visual Studio.

Introduction

MySQL for Visual Studio provides access to MySQL objects and data without forcing developers to leave Visual Studio. Designed and developed as a Visual Studio package, MySQL for Visual Studio integrates directly into Server Explorer providing a seamless experience for setting up new connections and working with database objects.

The following MySQL for Visual Studio features are available as of version 2.0.2:

  • JavaScript and Python code editors, where scripts in those languages can be executed to query data from a MySQL database.

  • Better integration with the Server Explorer to open MySQL, JavaScript, and Python code editors directly from a connected MySQL instance.

  • A newer user interface for displaying query results, where different views are presented from result sets returned by a MySQL Server like:

    • Multiple tabs for each result set returned by an executed query.

    • Results view, where the information can be seen in grid, tree, or text representation for JSON results.

    • Field types view, where information about the columns of a result set is shown, such as names, data types, character sets, and more.

    • Query statistics view, displaying information about the executed query such as execution times, processed rows, index and temporary tables usage, and more.

    • Execution plan view, displaying an explanation of the query execution done internally by the MySQL Server.

Getting Started

The requirements are MySQL for Visual Studio 2.0.2 or higher, and Visual Studio 2010 or higher. X DevAPI support requires MySQL Server 5.7.12 or higher with the X plugin enabled.

Opening a Code Editor

Before opening a code editor that can execute queries against a MySQL server, a connection needs to be established:

  1. Open the Server Explorer pane through the View menu, or with Control + W, K.

  2. Right-click on the Data Connections node, select Add Connection....

  3. In the Add Connection dialog, make sure the MySQL Data Provider is being used and fill in all the information.

    Note

    To enter the port number, click Advanced... and set the Port among the list of connection properties.

  4. Click Test Connection to ensure you have a valid connection, then click OK.

  5. Right-click your newly created connection, select New MySQL Script and then the language for the code editor you want to open.

For existing MySQL connections, to create a new editor you need only to do the last step.

Using the Code Editor

The MySQL script editors have a toolbar at the start where information about the session is displayed, along with the actions that can be executed.

Note

Note the first two buttons in the toolbar represent a way to connect or disconnect from a MySQL server. If the editor was opened from the Server Explorer, the connection will be already established for the new editor window.

The third button is the Run button, the script contained in the editor window is executed by clicking it and results from the script execution are displayed below the script window.

Note

Some commands in the MySQL Shell can be executed without appending execute() while in interactive mode. In MySQL for Visual Studio, these commands do require execute(). In other words, append ".execute()" to execute commands.

19.4 X Plugin

19.4.1 Using Secure Connections with X Plugin
19.4.2 X Plugin Options and Variables
19.4.3 Monitoring X Plugin

This section explains how to configure and monitor the X Plugin.

19.4.1 Using Secure Connections with X Plugin

This section explains how to configure X Plugin to use secure connections. For more background information, see Section 6.3, “Using Encrypted Connections”.

X Plugin has its own SSL settings which can differ from those used with MySQL Server. This means that X Plugin can be configured with a different SSL key, certificate, and certificate authorities file than MySQL Server. Similarly, X Plugin has its own SSL status variables calculated independently from the MySQL Server SSL related variables. By default the X Plugin SSL configuration is taken from the mysqlx_ssl_* variables, described at Section 19.4.2.2, “X Plugin Options and System Variables”. If no configuration is provided using the mysqlx_ssl_* variables, X Plugin falls back to using the MySQL Server SSL system variables. This means you can choose to either have separate SSL configurations for MySQL Protocol and X Protocol connections by configuring each separately, or share the SSL configuration between MySQL Protocol and X Protocol connections by only configuring the ssl-* variables.

On a server with X Plugin installed, to configure MySQL Protocol and X Protocol connections with separate SSL configurations use both the ssl-* and mysqlx-ssl-* variables in my.cnf: 

[mysqld]
ssl-ca=ca1.pem
ssl-cert=server-cert1.pem
ssl-key=server-key1.pem

mysqlx-ssl-ca=ca2.pem
mysqlx-ssl-cert=server-cert2.pem
mysqlx-ssl-key=server-key2.pem

The available mysqlx_ssl_* variables mirror the SSL variables in MySQL Server, so the files and techniques described for configuring MySQL Server to use SSL at Section 6.3.1, “Configuring MySQL to Use Encrypted Connections” are relevant to configuring X Plugin to use secure connections.

You can configure the TLS versions used by X Protocol SSL connections using the tls_version system variable. The TLS version used by MySQL Protocol and X Protocol connections is therefore the same TLS version.

Encryption per connection is optional, but a specific user can be forced to use encryption for X Protocol and MySQL Protocol connections. You configure such a user by issuing a GRANT statement with the REQUIRE option. For more details see Section 13.7.1.4, “GRANT Statement”. Alternatively all X Protocol and MySQL Protocol connections can be forced to use encryption by setting require_secure_transport.

19.4.2 X Plugin Options and Variables

19.4.2.1 X Plugin Option and Variable Reference
19.4.2.2 X Plugin Options and System Variables

This section describes the command options and system variables which configure X Plugin. If values specified at startup time are incorrect, X Plugin could fail to initialize properly and the server does not load it. In this case, the server could also produce error messages for other X Plugin settings because it cannot recognize them.

19.4.2.1 X Plugin Option and Variable Reference

This table provides an overview of the command options, and system and status variables provided by X Plugin.

Table 19.1 X Plugin Option and Variable Reference

Name Cmd-Line Option File System Var Status Var Var Scope Dynamic
mysqlx Yes Yes
Mysqlx_address Yes Global No
mysqlx_bind_address Yes Yes Yes Global No
Mysqlx_bytes_received Yes Both No
Mysqlx_bytes_sent Yes Both No
mysqlx_connect_timeout Yes Yes Yes Global Yes
Mysqlx_connection_accept_errors Yes Both No
Mysqlx_connection_errors Yes Both No
Mysqlx_connections_accepted Yes Global No
Mysqlx_connections_closed Yes Global No
Mysqlx_connections_rejected Yes Global No
Mysqlx_crud_create_view Yes Both No
Mysqlx_crud_delete Yes Both No
Mysqlx_crud_drop_view Yes Both No
Mysqlx_crud_find Yes Both No
Mysqlx_crud_insert Yes Both No
Mysqlx_crud_modify_view Yes Both No
Mysqlx_crud_update Yes Both No
Mysqlx_errors_sent Yes Both No
Mysqlx_errors_unknown_message_type Yes Both No
Mysqlx_expect_close Yes Both No
Mysqlx_expect_open Yes Both No
mysqlx_idle_worker_thread_timeout Yes Yes Yes Global Yes
Mysqlx_init_error Yes Both No
mysqlx_max_allowed_packet Yes Yes Yes Global Yes
mysqlx_max_connections Yes Yes Yes Global Yes
mysqlx_min_worker_threads Yes Yes Yes Global Yes
Mysqlx_notice_other_sent Yes Both No
Mysqlx_notice_warning_sent Yes Both No
Mysqlx_port Yes Global No
mysqlx_port Yes Yes Yes Global No
mysqlx_port_open_timeout Yes Yes Yes Global No
Mysqlx_rows_sent Yes Both No
Mysqlx_sessions Yes Global No
Mysqlx_sessions_accepted Yes Global No
Mysqlx_sessions_closed Yes Global No
Mysqlx_sessions_fatal_error Yes Global No
Mysqlx_sessions_killed Yes Global No
Mysqlx_sessions_rejected Yes Global No
Mysqlx_socket Yes Global No
mysqlx_socket Yes Yes Yes Global No
Mysqlx_ssl_accept_renegotiates Yes Global No
Mysqlx_ssl_accepts Yes Global No
Mysqlx_ssl_active Yes Both No
mysqlx_ssl_ca Yes Yes Yes Global No
mysqlx_ssl_capath Yes Yes Yes Global No
mysqlx_ssl_cert Yes Yes Yes Global No
Mysqlx_ssl_cipher Yes Both No
mysqlx_ssl_cipher Yes Yes Yes Global No
Mysqlx_ssl_cipher_list Yes Both No
mysqlx_ssl_crl Yes Yes Yes Global No
mysqlx_ssl_crlpath Yes Yes Yes Global No
Mysqlx_ssl_ctx_verify_depth Yes Both No
Mysqlx_ssl_ctx_verify_mode Yes Both No
Mysqlx_ssl_finished_accepts Yes Global No
mysqlx_ssl_key Yes Yes Yes Global No
Mysqlx_ssl_server_not_after Yes Global No
Mysqlx_ssl_server_not_before Yes Global No
Mysqlx_ssl_verify_depth Yes Global No
Mysqlx_ssl_verify_mode Yes Global No
Mysqlx_ssl_version Yes Both No
Mysqlx_stmt_create_collection Yes Both No
Mysqlx_stmt_create_collection_index Yes Both No
Mysqlx_stmt_disable_notices Yes Both No
Mysqlx_stmt_drop_collection Yes Both No
Mysqlx_stmt_drop_collection_index Yes Both No
Mysqlx_stmt_enable_notices Yes Both No
Mysqlx_stmt_ensure_collection Yes Both No
Mysqlx_stmt_execute_mysqlx Yes Both No
Mysqlx_stmt_execute_sql Yes Both No
Mysqlx_stmt_execute_xplugin Yes Both No
Mysqlx_stmt_kill_client Yes Both No
Mysqlx_stmt_list_clients Yes Both No
Mysqlx_stmt_list_notices Yes Both No
Mysqlx_stmt_list_objects Yes Both No
Mysqlx_stmt_ping Yes Both No
Mysqlx_worker_threads Yes Global No
Mysqlx_worker_threads_active Yes Global No

19.4.2.2 X Plugin Options and System Variables

To control activation of X Plugin, use this option:

If X Plugin is enabled, it exposes several system variables that permit control over its operation:

  • mysqlx_bind_address

    Property Value
    Command-Line Format --mysqlx-bind-address=addr
    Introduced 5.7.17
    System Variable mysqlx_bind_address
    Scope Global
    Dynamic No
    Type String
    Default Value *

    The network address on which X Plugin listens for TCP/IP connections. This variable is not dynamic and can be configured only at startup. This is the X Plugin equivalent of the bind_address system variable; see that variable description for more information.

    mysqlx_bind_address accepts a single address value, which may specify a single non-wildcard IP address or host name, or one of the wildcard address formats that permit listening on multiple network interfaces (*, 0.0.0.0, or ::).

    An IP address can be specified as an IPv4 or IPv6 address. If the value is a host name, X Plugin resolves the name to an IP address and binds to that address. If a host name resolves to multiple IP addresses, X Plugin uses the first IPv4 address if there are any, or the first IPv6 address otherwise.

    X Plugin treats different types of addresses as follows:

    • If the address is *, X Plugin accepts TCP/IP connections on all server host IPv4 interfaces, and, if the server host supports IPv6, on all IPv6 interfaces. Use this address to permit both IPv4 and IPv6 connections for X Plugin. This value is the default.

    • If the address is 0.0.0.0, X Plugin accepts TCP/IP connections on all server host IPv4 interfaces.

    • If the address is ::, X Plugin accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces.

    • If the address is an IPv4-mapped address, X Plugin accepts TCP/IP connections for that address, in either IPv4 or IPv6 format. For example, if X Plugin is bound to ::ffff:127.0.0.1, a client such as MySQL Shell can connect using --host=127.0.0.1 or --host=::ffff:127.0.0.1.

    • If the address is a “regular” IPv4 or IPv6 address (such as 127.0.0.1 or ::1), X Plugin accepts TCP/IP connections only for that IPv4 or IPv6 address.

    If binding to the address fails, X Plugin produces an error and the server does not load it.

  • mysqlx_connect_timeout

    Property Value
    Command-Line Format --mysqlx-connect-timeout=#
    Introduced 5.7.12
    System Variable mysqlx_connect_timeout
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 30
    Minimum Value 1
    Maximum Value 1000000000

    The number of seconds X Plugin waits for the first packet to be received from newly connected clients. This is the X Plugin equivalent of connect_timeout; see that variable for more information.

  • mysqlx_idle_worker_thread_timeout

    Property Value
    Command-Line Format --mysqlx-idle-worker-thread-timeout=#
    Introduced 5.7.12
    System Variable mysqlx_idle_worker_thread_timeout
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 60
    Minimum Value 0
    Maximum Value 3600

    The number of seconds after which idle worker threads are terminated.

  • mysqlx_max_allowed_packet

    Property Value
    Command-Line Format --mysqlx-max-allowed-packet=#
    Introduced 5.7.12
    System Variable mysqlx_max_allowed_packet
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 67108864
    Minimum Value 512
    Maximum Value 1073741824

    The maximum size of network packets that can be received by X Plugin. This is the X Plugin equivalent of max_allowed_packet; see that variable for more information.

  • mysqlx_max_connections

    Property Value
    Command-Line Format --mysqlx-max-connections=#
    Introduced 5.7.12
    System Variable mysqlx_max_connections
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 100
    Minimum Value 1
    Maximum Value 65535

    The maximum number of concurrent client connections X Plugin can accept. This is the X Plugin equivalent of max_connections; see that variable for more information.

    For modifications to this variable, if the new value is smaller than the current number of connections, the new limit is taken into account only for new connections.

  • mysqlx_min_worker_threads

    Property Value
    Command-Line Format --mysqlx-min-worker-threads=#
    Introduced 5.7.12
    System Variable mysqlx_min_worker_threads
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 2
    Minimum Value 1
    Maximum Value 100

    The minimum number of worker threads used by X Plugin for handling client requests.

  • mysqlx_port

    Property Value
    Command-Line Format --mysqlx-port=port_num
    Introduced 5.7.12
    System Variable mysqlx_port
    Scope Global
    Dynamic No
    Type Integer
    Default Value 33060
    Minimum Value 1
    Maximum Value 65535

    The network port on which X Plugin listens for TCP/IP connections. This is the X Plugin equivalent of port; see that variable for more information.

  • mysqlx_port_open_timeout

    Property Value
    Command-Line Format --mysqlx-port-open-timeout=#
    Introduced 5.7.17
    System Variable mysqlx_port_open_timeout
    Scope Global
    Dynamic No
    Type Integer
    Default Value 0
    Minimum Value 0
    Maximum Value 120

    The number of seconds X Plugin waits for a TCP/IP port to become free.

  • mysqlx_socket

    Property Value
    Command-Line Format --mysqlx-socket=file_name
    Introduced 5.7.15
    System Variable mysqlx_socket
    Scope Global
    Dynamic No
    Type String
    Default Value /tmp/mysqlx.sock

    The path to a Unix socket file which X Plugin uses for connections. This setting is only used by MySQL Server when running on Unix operating systems. Clients can use this socket to connect to MySQL Server using X Plugin.

    The default mysqlx_socket path and file name is based on the default path and file name for the main socket file for MySQL Server, with the addition of an x appended to the file name. The default path and file name for the main socket file is /tmp/mysql.sock, therefore the default path and file name for the X Plugin socket file is /tmp/mysqlx.sock.

    If you specify an alternative path and file name for the main socket file at server startup using the socket system variable, this does not affect the default for the X Plugin socket file. In this situation, if you want to store both sockets at a single path, you must set the mysqlx_socket system variable as well. For example in a configuration file: 

    socket=/home/sockets/mysqld/mysql.sock
mysqlx_socket=/home/sockets/xplugin/xplugin.sock

    If you change the default path and file name for the main socket file at compile time using the MYSQL_UNIX_ADDR compile option, this does affect the default for the X Plugin socket file, which is formed by appending an x to the MYSQL_UNIX_ADDR file name. If you want to set a different default for the X Plugin socket file at compile time, use the MYSQLX_UNIX_ADDR compile option.

    The MYSQLX_UNIX_PORT environment variable can also be used to set a default for the X Plugin socket file at server startup (see Section 4.9, “Environment Variables”). If you set this environment variable, it overrides the compiled MYSQLX_UNIX_ADDR value, but is overridden by the mysqlx_socket value.

  • mysqlx_ssl_ca

    Property Value
    Command-Line Format --mysqlx-ssl-ca=file_name
    Introduced 5.7.12
    System Variable mysqlx_ssl_ca
    Scope Global
    Dynamic No
    Type File name

    This is the X Plugin equivalent of ssl_ca; see that variable for more information.

  • mysqlx_ssl_capath

    Property Value
    Command-Line Format --mysqlx-ssl-capath=dir_name
    Introduced 5.7.12
    System Variable mysqlx_ssl_capath
    Scope Global
    Dynamic No
    Type Directory name

    This is the X Plugin equivalent of ssl_capath; see that variable for more information.

  • mysqlx_ssl_cert

    Property Value
    Command-Line Format --mysqlx-ssl-cert=name
    Introduced 5.7.12
    System Variable mysqlx_ssl_cert
    Scope Global
    Dynamic No
    Type File name

    This is the X Plugin equivalent of ssl_cert; see that variable for more information.

  • mysqlx_ssl_cipher

    Property Value
    Command-Line Format --mysqlx-ssl-cipher=name
    Introduced 5.7.12
    System Variable mysqlx_ssl_cipher
    Scope Global
    Dynamic No
    Type String

    The SSL cipher to use for X Protocol connections. This is the X Plugin equivalent of ssl_cipher; see that variable for more information.

  • mysqlx_ssl_crl

    Property Value
    Command-Line Format --mysqlx-ssl-crl=file_name
    Introduced 5.7.12
    System Variable mysqlx_ssl_crl
    Scope Global
    Dynamic No
    Type File name

    This is the X Plugin equivalent of ssl_crl; see that variable for more information.

  • mysqlx_ssl_crlpath

    Property Value
    Command-Line Format --mysqlx-ssl-crlpath=dir_name
    Introduced 5.7.12
    System Variable mysqlx_ssl_crlpath
    Scope Global
    Dynamic No
    Type Directory name

    This is the X Plugin equivalent of ssl_crlpath; see that variable for more information.

  • mysqlx_ssl_key

    Property Value
    Command-Line Format --mysqlx-ssl-key=file_name
    Introduced 5.7.12
    System Variable mysqlx_ssl_key
    Scope Global
    Dynamic No
    Type File name

    This is the X Plugin equivalent of ssl_key; see that variable for more information.

19.4.3 Monitoring X Plugin

19.4.3.1 Status Variables for X Plugin

This section describes how to monitor X Plugin. There are two available methods of monitoring, using Performance Schema tables or status variables.

19.4.3.1 Status Variables for X Plugin

The status variables have the following meanings.

Prev Up  Next
Chapter 18 MySQL Shell Home Chapter 20 InnoDB Cluster