Data Management for VMware Tanzu supports provisioning databases running MySQL version 8.0.28.
When you create a MySQL database with Data Management for VMware Tanzu, you configure certain MySQL properties.
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. |
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 | Enable or disable the logging of slow running queries. | OFF | No |
log‑queries‑not‑using‑indexes | Enable or disable 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 | Enable or disable local load capability. | Database server default | Yes |
1 Run the following MySQL query to obtain the possible character set values: SHOW CHARACTER SET;
2 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.
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.
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, Data Management for VMware Tanzu 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:
Data Management for VMware Tanzu grants these privileges on all objects, and at the same time revokes the create and update privileges on objects in the mysql.
system schema.
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 Data Management for VMware Tanzu console as described in Locating the Database Connection Parameters.
Refer to Configuring MySQL to Use Encrypted Connections in the MySQL documentation for more information on using TLS with MySQL.
verify-full
TLS configuration for client applications connecting to a database.
You can configure a MySQL database to require SSL/TLS client connections during creation or after provisioning as follows:
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:
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.
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.
transfer/
directory when a database is recovered.
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
dbaas
.
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.
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
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.
Use the mysqldump
command to create a logical backup of a database:
db-admin@<db-vm>$ mysqldump <database-name> > transfer/<database-name>.dmp