MySQL on VMware Data Services Manager

Supported MySQL Versions

VMware Data Services Manager supports provisioning databases running MySQL version 8.0.28.

Configuration

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

Database Configuration

The Database Configuration properties identify the name of the default database, the database server port number, and the database administrator user credentials.

Property Name	Description
Database Name	The name of the default database.
Database Port No	The port number on which the database server runs. (The default is 3306.) (Read-only)
Username	The database administrator user name.
Password	The password for the database adminstrator.

Note: The default values for these properties are provided through the user interface, but you should change them as required. VMware Data Services Manager does not support changing the Password from the console or API. You must change this password using a database client. If you change the password in this manner, the new password is not reflected in VMware Data Services Manager.

DB Options Configuration

The DB Options Configuration properties control certain runtime characteristics of the MySQL database:

Property Name	Description	Default Value	Restart Required on Update?
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	Yes
max_connections	The maximum number of concurrent connections allowed to the server.	100	No
char‑set‑server	The character set with which to start the MySQL server.1	utf8mb4	No
collation‑server	The collation with which to start the MySQL server.1	utf8mb4_0900_ai_ci	No
slow‑query‑log	Activate or deactivate the logging of slow running queries.	OFF	No
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	No
long‑query‑time	The amount of time after which a query is considered long-running.	Database server default	No
max‑allowed‑packet	The upper limit on the size of a message between client and server. A minimum value of 16384 (16MB) is suggested.	32MB	No
innodb‑buffer‑pool‑size	The size (in bytes) of the innodb buffer pool.2	Database server default	No
innodb‑buffer‑pool‑chunk‑size	The chunk size (in bytes) when resizing the innodb buffer pool.2	Database server default	Yes
innodb‑buffer‑pool‑instances	The number of regions in which the innodb buffer pool is divided.2	Database server default	Yes
binlog‑transaction‑compression	Enable compression for transactions that are written to binary log files.	ON	Yes
local‑infile	Activate or deactivate local load capability.	Database server default	Yes

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

¹ Run the following MySQL query to obtain the possible character set values: SHOW CHARACTER SET;

² During database creation, the innodb-buffer-pool-xxx option values are calculated to use 75% of the configured VM memory. These settings will also be adjusted if you scale up the VM memory. While the default values of these options should be sufficient, you may need to change them if 75% is not optimal for your application.

Note: The innodb-buffer-pool-xxx options are related, and may requiring tuning together. Consult the MySQL InnoDB documentation to research and understand the effects before you change these these options.

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.

Connecting 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 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. You can obtain this information from the VMware Data Services Manager console as described in Locating the Database Connection Parameters.

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.

Requiring TLS

Note: The default SSL/TLS configuration for MySQL on VMware Data Services Manager does not require secure client connections.

You can configure a MySQL database to require SSL/TLS client connections during creation or after provisioning as follows:

• You specify Require Client TLS when you create the database.
• You require TLS by configuring the database after it is provisioned as described in Requiring TLS-Secured Client Connections.

Importing and Exporting Data

Enabling SSH access to a database, provides the db-admin OS user the capability to use the following native MySQL client tools to import or export data directly to or from the database and perform related operations:

• mysql
• mysqladmin
• mysqlbinlog
• mysqlcheck
• mysql_config_editor
• mysqldump
• mysqldumpslow
• mysqlimport
• mysqlpump
• mysqlshow
• mysqlslap

The $HOME directory of the db-admin user on the database contains a subdirectory named transfer/. This directory is a symbolic link to the database data disk. You can copy large data files for import operations to the transfer/ directory, or use it as a write target for data export operations.

Note: Any files that reside in the transfer/ directory use storage on the database data disk, limiting the amount of disk space available to the database. Ensure that you promptly remove from this directory any files that you no longer require. You may also choose to extend the size of the data disk which is used by the database.

Note: VMware Data Services Manager removes the contents of the transfer/ directory when a database is recovered.

Configuring Default Connection Parameters

The db-admin user may choose to run the mysql_config_editor command to create an encrypted login file that contains the database connection parameters for use with MySQL client programs. An example command invocation that prompts you for a password follows:

db-admin@<db-vm>$ mysql_config_editor set --login-path=client --host=127.0.0.1 --user=dbaas --password

With these settings in place, running the mysql <database-name> command with no other options attempts to connect to on the localhost as the user named dbaas.

Importing Data

You can copy data files to the database to locally load data into the database, potentially avoiding network latency or connection issues that could arise with a remote operation.

To import data locally, the data file(s) must reside on the database. You can use ssh or other similar command to copy the data file(s) to the transfer/ directory on the database.

For example, the following command copies a local file named datafile.csv to the $HOME/transfer directory for the db-admin OS user on the database:

$ scp datafile.csv db-admin@<db-vm>:transfer

When you reference a file that you have copied to the database, you must specify the absolute path to the file.

Examples

Load a data file on the database file system into a MySQL table:

db-admin@<db-vm>$ mysql <database-name> -e "LOAD DATA INFILE '/home/db-admin/transfer/<data-file-name>' INTO TABLE <table-name> (<column-name>[, ...])"

Use the mysqlimport command to load the data file on the database file system named t1.csv into the MySQL table named t1:

db-admin@<db-vm>$ mysqlimport <database-name> --columns=<column-name>[,...] /home/db-admin/transfer/t1.csv

Exporting Data

You may choose to export data from a database to examine the data or to load it into another database. Using MySQL tools, you can export data from one or more MySQL tables, or back up an entire database to a file.

When you export data, the exported data file(s) will be written to the database. You can use ssh or other similar command to copy the data file(s) from the database to your local host:

$ scp db-admin@<db-vm>:transfer/<export-file-name> ./localdir/

When you reference a file on the database in a MySQL export operation, you must specify the absolute path to the file.

Examples

Use the mysqldump command to create a logical backup of a database:

db-admin@<db-vm>$ mysqldump <database-name> > transfer/<database-name>.dmp