Create MySQL servers

This chapter presents the MySQL server creation script “mcreate.sh”

"mcreate.sh” performs all creation MySQL Server creation steps and furthermore.

• Creation of the admin and data directories

• Creation of the MYSQL_SERVER based on mysql_install_db (official MySQL script)

  • Starting with MySQL 5.7 the new script “mysqld” is used with

    the option “—initializer-insecure” in order to freely set the root password

• Creation of the schema multi_admin (script mysqld_multi)

• Creation of the MySQL client option file ($MYSQL_BASE/admin/$MYSQL_SERVER/.my.cnf)

• Extension of the MySQL server option file ($MYSQL_CNF – etc/my.cnf)

• Secure MYSQL_SERVER based on mysql_secure_installation (official MySQL script)

• Automatic port matching with the MYSQL_SERVER digit (see response file)

The execution of "mcreate.sh” requires a configuration file and a response file. The configuration file contains administrative information (users, password, directories) and the response file contains server specific information (all information required for the MYSQL_SERVER in the MySQL server option file).

Parameters

–h or –help

Display the Synopsis.

–s or –-sid

The MYSQL_SERVER name you wish to create. This parameter is always required:

mcreate.sh –s ${MYSQL_SERVER}

Two options can be used for mcreate.sh script:

• Format mysqldX or only a number X: X is a number between 1 and 9, corresponding to the port 3300X.

  e.g.: “mysqld4” or “4” are equivalent

mcreate.sh –s mysqld4
mcreate.sh –s 4

• Custom name: it will create the instance in the first slot available founded.

  e.g., “master-vmoraoel05”

mcreate.sh –s master-vmoraoel05

It will add a line to the $DMK_HOME/etc/instance.cnf file:

mysqld7=master-vmoraoel05

⚠️ It is not recommended to edit manually the $DMK_HOME/etc/instance.cnf file, especially if you are using special characters.

–c or --configfile

The dbi services best practice consists of saving the configuration file under $DMK_HOME/etc which makes the command-line parameter “ConfigFile” useless.

cp ${DMK_HOME}/templates/mcreate.cfg ${DMK_HOME}/etc

Otherwise, the command-line parameter becomes mandatory

mcreate.sh –s ${MYSQL_SERVER} –c <path to configuration file>

An option is to create a symbolic link of the response file you want to use from $DMK_HOME/templates/mcreate.rsp_version under $DMK_HOME/etc

Here is used the response file for MariaDB 10.7:

ln -s $DMK_HOME/templates/mcreate.rsp_version/mcreate.rsp_v10.7 ${DMK_HOME}/etc/mcreate.rsp

For simplicity, the parameter -r | --release can also be used, which auto create/update the symbolic link.

r or –release

A specific version of MySQL can be used, several method can be used. It will automatically create or update a symbolic link of the response file you want to use from $DMK_HOME/templates/mcreate.rsp_version under $DMK_HOME/etc

mcreate.sh –s ${MYSQL_SERVER} –r <MySQL release>

e.g. with MySQL 8.0.16 with different ways to use it:

mcreate.sh –s ${MYSQL_SERVER} --release 8.0.16
mcreate.sh –s ${MYSQL_SERVER} -r 8.0.16
mcreate.sh –s ${MYSQL_SERVER} -r 8.0.18

It will create a symbolic link for the asked release if every followed conditions are validated:

• The MySQL source of this release is present in the system

• The response file exists in $DMK_HOME/templates/mcreate.rsp_version directory respecting the format: mcreate.rsp_v[VERSION]

⚠️If the response file according to your MySQL doesn't exist, you have to create a new response file. Create your reponse file in the $DMK_HOME/templates/mcreate.rsp_version directory.

vi $DMK_HOME/templates/mcreate.rsp_version/mcreate.rsp_v[MySQL version]

Configuration file

The configuration file contains the following parameters:

mysql@vmoel58:/home/mysql/ [mysqld1] cat ${DMK_HOME}/etc/mcreate.cfg
#Define all mcreate parameters

# admin directory and datadir location
mysql_base                     = /u01/app/mysql
mysql_data                     = /u02/mysqldata

#User/Client specific options ( $MYSQL_BASE/admin/$MYSQL_SERVER/.my.cnf)
#[client_cnf]
client_user                           = root
client_password                       = manager

#mysqld_multi information, used for MySQL server option file (/etc/my.cnf)
mysqld_multi_user                     = multi_admin
mysqld_multi_password                 = manager

#MySQL Server specific options (/etc/my.cnf)
# Contains the definition for the MYSQL_SERVER
#[server_cnf]
tpl_server_cnf=/u01/app/mysql/local/dmk/etc/mcreate.rsp

#[post_install] 
#dbi services best practices
#post creation SQL script
# Start the MYSQL_SERVER after creation
start_mysql_server             = yes
# Secure the installation
# Note: mysql_secure_installation not yet running in "silent/batch" mode
# Please run the mysql_secure.sql SQL Script
#mysql_secure_installation      = yes
# Launch a script after MYSQL_SERVER creation (as MYSQL) to secure the MySQL installation
sql_script                     = /u01/app/mysql/local/dmk/sql/mysql_secure.sql

⚠️ The parameter tpl_server_cnf is required as it contains all information regarding the MYSQL_SERVER configuration, which will be added to the MySQL server option file.

⚠️ Configuration files hasn't been tested for all version of MySQL. You can find different templates in DMK templates directory. Please feel free to adapt/add/remove any parameter in coherence with your MySQL release. Several configuration files can coexist.

Response file

mysql@vmoel58:/home/mysql/ [mysqld1] cat ${DMK_HOME}/etc/mcreate.rsp
#MySQL Server specific options (/etc/my.cnf)
#[server_cnf]
# the port number correspond to the last digits of the MYSQL_SERVER

port = 3300<STD_PORT>
bind-address = localhost
mysqladmin = <MYSQL_BASE>/product/mysql5.7.26/bin/mysqladmin
mysqld = <MYSQL_BASE>/product/mysql-5.7.26/bin/mysqld

socket =
<MYSQL_BASE>/admin/<MYSQL_SERVER>/socket/<MYSQL_SERVER>.sock
pid-file =
<MYSQL_BASE>/admin/<MYSQL_SERVER>/socket/<MYSQL_SERVER>.pid
log-error =
<MYSQL_BASE>/admin/<MYSQL_SERVER>/log/<MYSQL_SERVER>.err

datadir = <MYSQL_DATA>/<MYSQL_SERVER>
basedir = <MYSQL_BASE>/product/mysql-5.7.26

slow_query_log = 1
slow_query_log_file =
<MYSQL_BASE>/admin/<MYSQL_SERVER>/log/<MYSQL_SERVER>-slow-query.log

log-bin =
<MYSQL_BASE>/admin/<MYSQL_SERVER>/binlog/<MYSQL_SERVER>-bin
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
expire_logs_days = 7
local-infile = 0
log_timestamps = SYSTEM
general_log = 0
general_log_file =
<MYSQL_BASE>/admin/<MYSQL_SERVER>/log/<MYSQL_SERVER>.log
#secure-file-priv = ""
lc_messages_dir = <MYSQL_BASE>/product/mysql-5.7.26/share/
lc_messages = en_US
server_id = <STD_PORT>
#innodb_file_per_table = 1

ℹ️ The MySQL server specified as argument to the script is automatically appended in place of the placeholders “<MYSQL_SERVER>”. The placeholder „<STD_PORT>“ is replaced by the last digits of the MYSQL_SERVER specified as argument.