MySQL Server

uim20.3
CA UIM supports only Enterprise Edition of the MySQL database software.
Review the following information in this section:
  • MySQL Server Variables
  • View the Variable Setting
  • MySQL in Large Environments
  • Manual Creation of the Database Schema and User
MySQL Server Variables
MySQL variables must be set as follows:
  • lower_case_table_names=1
  • local_infile=ON
  • table_definition_cache=2000
  • innodb_file_per_table=0
  • max_connections = 1000
  • location =
    default location of my.cnf or my.ini configuration
    • In Linux, default location of my.cnf file is /etc/my.cnf
    • In Windows, to get the location of my.ini
      go to->Services.msc->mysqld service->properties->default-fil
  • session =
    the session in which these variables need to be set are under mysqld process : [mysqld]
    If you have a replication server configuration, the variable "gtid-mode" should be set to "
    OFF
    " and the variable "enforce-gtid-consistency" should be set to "
    0
    " in my.cnf or my.ini configuration as below:
    gtid-mode=off
    enforce-gtid-consistency=0
Enable the binary logs only if you use a backup or replication service, which requires the binary log files. To do so, set the following variables:
  • log_bin
    The status of the system variable log_bin specifies whether the binary log is enabled. The
    --log-bin [=base_name]
    command-line option enables the binary logging. When you set the
    --log-bin
    option, the
    log_bin
    system variable is set to
    ON
    , not to the base name. The binary log file name is present in the
    log_bin_basename
    variable. For more information, see your MySQL documentation.
  • log_bin_trust_function_creators=ON
    (if log_bin is enabled)
  • binlog_format=mixed
    (if log_bin is enabled)
  • expire_logs_days=<number of days after which to remove binary log files>
    (if log_bin is enabled)
View the Variable Setting
Use the following procedure to view the setting for each variable.
Follow these steps:
  1. Log in to the MySQL server as the administrator.
  2. For each variable, execute:
    show variables like 'variable_name';
  3. If a variable is incorrect or missing, edit the MySQL server configuration file as instructed in your MySQL documentation.
  4. Restart the database if you made any changes,
MySQL in Large Environments
If you are preparing for a large-scale or major deployment, you can change more database parameters to allow for greater demands of such an environment. We recommend that you begin with the values shown in the following example, and then fine-tune settings depending on your circumstances.
As the MySQL administrator, add these lines to the MySQL server configuration file:
[mysqld] max_heap_table_size=134217728 query_cache_limit=4194304 query_cache_size=268435456 sort_buffer_size=25165824 join_buffer_size=67108864 max_tmp_tables=64
Manual Creation of the Database Schema and User
Manually create the MySQL database schema and user.
Follow these steps:
  1. Log in as the MySQL administrator.
  2. Create the database. Execute:
    CREATE DATABASE IF NOT EXISTS <uim_db_name> DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_unicode_ci;
    Where <
    uim_db_name>
    is the desired database name
  3. Create the user and assign required privileges. Execute:
    CREATE USER '<uim_db_owner>'@'%' IDENTIFIED BY '<uim_db_owner_password>'; GRANT ALL PRIVILEGES ON <uim_db_name>.* TO 'uim_db_owner'@'%'; FLUSH PRIVILEGES;
    Where
    uim_db_owner
    is the desired user name for the owner,
    uim_db_owner_password
    is the desired password, and
    uim_db_name
    is the name of the database you created.
    The single-quotation marks (') are required.