Learn about specifics of using MySQL on VMware Data Services Manager.

Supported MySQL Versions

For information about MySQL versions that this release of VMware Data Services Manager supports, see VMware Data Services Manager Release Notes.

Configuration

When you create a MySQL database with VMware Data Services Manager, you configure certain MySQL properties.

Database Basic Information

The Basic Information properties identify the database version, the name of the default database, and the replica mode.

For a full list of properties, see Database Configuration Reference.

Property Name Description
Database Version Use the dropdown menu to select from the available template versions of the database.
Note: Availability of template versions depends on whether your data service is enabled in full or limited preview mode. For more information, see Activate Data Services in VMware Data Services Manager.
Group Name Enter a unique instance name.
Replica Mode Configure the number of nodes to create for this cluster. Select one of the following:
  • Single Server - One node with no replicas. You can use vSphere HA.

  • Single vSphere Cluster - Multiple nodes co-located on a single vSphere cluster providing automatic failover and resiliency to a single compute node failure.

    Only three-node topology is available.

    MySQL clustering configuration is a three node InnoDB Cluster, which uses Group Replication that provides resiliency to a single node failure. However, in the event of a total cluster failure or unplanned restart, the most recently committed transactions might be lost. For more information, see the official MySQL Group Replication documentation.
Topology Specify configuration for the database nodes appropriate for the replica mode you selected.

Advanced Database Settings

The Advanced Settings properties control certain runtime characteristics of the MySQL database.

The following table lists several of the settings you can configure, but there can be more.

If you want to set a parameter to the MySQL server default, you must clear the entry.

Note: Restart is performed on every update. The data pods associated with the MySQL instance are automatically restarted when you add, update, or remove the ConfigMap.
Table 1. Editable Advanced Database Settings
Property Name Description Default Value
default‑time‑zone The time zone for the database server. Possible values are SYSTEM or any time in the range ["-13:59","+14:00"]. UTC
max-connections The maximum number of concurrent connections allowed to the server. 151
char‑set‑server The character set with which to start the MySQL server.
Note: Run the following MySQL query to obtain the possible character set values: SHOW CHARACTER SET;
utf8mb4
collation‑server The collation with which to start the MySQL server.
Note: Run the following MySQL query to obtain the possible character set values: SHOW CHARACTER SET;
utf8mb4_0900_ai_ci
slow‑query‑log Activate or deactivate the logging of slow running queries. OFF
log‑queries‑not‑using‑indexes Activate or deactivate the logging of queries that are expected to retrieve all rows. (Used with slow-query-log.) OFF
long‑query‑time The amount of time after which a query is considered long-running. Database server default
innodb‑buffer‑pool‑size The size (in bytes) of the innodb buffer pool.
Note:

The innodb-buffer-pool-xxx options are related, and might need to be tuned together. Consult the MySQL InnoDB documentation to research and understand the effects before you change these options.

During database creation, the innodb-buffer-pool-xxx option values are calculated to use 50% of the configured VM memory on large VMs. The number is lower for smaller VMs. These settings will also be adjusted if you scale up the VM memory. While the default values of these options should be sufficient, you can change them if they are not optimal for your application. Generally, the recommended values are between 50% and 30%.

 Database server default
innodb‑buffer‑pool‑chunk‑size The chunk size (in bytes) when resizing the innodb buffer pool.
Note:

The innodb-buffer-pool-xxx options are related, and might need to be tuned together. Consult the MySQL InnoDB documentation to research and understand the effects before you change these options.

During database creation, the innodb-buffer-pool-xxx option values are calculated to use 50% of the configured VM memory on large VMs. The number is lower for smaller VMs. These settings will also be adjusted if you scale up the VM memory. While the default values of these options should be sufficient, you can change them if they are not optimal for your application. Generally, the recommended values are between 50% and 30%.

 Database server default
innodb‑buffer‑pool‑instances The number of regions in which the innodb buffer pool is divided.
Note:

The innodb-buffer-pool-xxx options are related, and might need to be tuned together. Consult the MySQL InnoDB documentation to research and understand the effects before you change these options.

During database creation, the innodb-buffer-pool-xxx option values are calculated to use 50% of the configured VM memory on large VMs. The number is lower for smaller VMs. These settings will also be adjusted if you scale up the VM memory. While the default values of these options should be sufficient, you can change them if they are not optimal for your application. Generally, the recommended values are between 50% and 30%.

 Database server default
binlog‑transaction‑compression Enable compression for transactions that are written to binary log files. ON
local‑infile Activate or deactivate local load capability. Database server default

You cannot change or set certain parameters. VMware Data Services Manager always overwrites them. These parameters include the following.

Table 2. Non-Editable Advanced Database Settings
Property Name Description Value
sync_relay_log MySQL server synchronizes its relay log to disk (using fdatasync()) after every sync_relay_log event is written to the relay log. 1

About the Privileges Assigned to the Default Database User

When you create a database, the default MySQL database user name is dbaas. You can change this name, if you choose, before you provision the database.

While the default user is not a MySQL superuser, VMware Data Services Manager grants this user a robust set of privileges, including those required to create other roles and databases, import and export data from the database, and execute MySQL functions.

The specific privileges granted to the default MySQL user follow:

  • ALTER
  • CREATE
  • CREATE ROUTINE, ALTER ROUTINE
  • CREATE TEMPORARY TABLES
  • CREATE USER
  • CREATE VIEW, SHOW VIEW
  • DELETE
  • DROP
  • EVENT
  • EXECUTE
  • FILE
  • GRANT OPTION
  • INDEX
  • INSERT
  • LOCK TABLES
  • RELOAD
  • PROCESS
  • REFERENCES
  • REPLICATION CLIENT, REPLICATION SLAVE
  • SELECT
  • SHOW DATABASES
  • SYSTEM_VARIABLES_ADMIN
  • TRIGGER
  • UPDATE

VMware Data Services Manager grants these privileges on all objects, and at the same time revokes the create and update privileges on objects in the mysql. system schema.

Using TLS with a MySQL Database

Refer to Configuring MySQL to Use Encrypted Connections in the MySQL documentation for more information on using TLS with MySQL.

Note: VMware Data Services Manager does not support verify-full TLS configuration for client applications connecting to a database.

Connecting to a MySQL Database

This section provides information on how to connect to a MySQL database. If needed, you can learn how to create an LDAP user for the MySQL database.

Connect to a MySQL Database

To access a MySQL database, the host on which the client application is running must have connectivity to the Application Network configured for the database.

You can use any SQL client application to connect to a database in your MySQL database. To connect, you must be able to identify the IP or FQDN of the MySQL server host, the port on which the server is running, and the database name. You must also have the MySQL admin credentials on hand.

Prerequisites

To be able to connect to a database using its FQDN, add the name when creating the database. See Creating a Database.

If you don't specify the FQDN at the database creation, you can add it later. See Edit Basic Information of the Database in VMware Data Services Manager.

Procedure

  1. Install any SQL client application on your system.
  2. In the DSM console, navigate to Databases > MySQL tab, and click the database you want to access.
  3. On the Summary tab, click COPY CONNECTION STRING.
    COPY CONNECTION STRING for the database.
  4. To connect to the database, provide the connection string to the tool you are using.

Create an LDAP User for a MySQL Database

Configure LDAP authentication with MySQL in VMware Data Services Manager environment.

Prerequisites

Procedure

  1. Create a database user corresponding to the LDAP user. 
    mysql> CREATE USER `user`@'%' IDENTIFIED WITH authentication_ldap_simple

    where user is the name of the user to create.

    This action creates a MySQL user named user using the LDAP simple authentication plug-in with a unique entry for the LDAP user.

  2. Grant needed privileges.
    For example,
    mysql> GRANT ALL ON `<database>` TO `user01`@'%'

    For additional information, see the CREATE USER documentation.