Instance admin directory

dbi services advices storing MariaDB database admin directories under /u01/app/mysql/admin (${MYSQL_BASE}/admin). For each MariaDB server parameters referring to the database admin directory should point to: ${MYSQL_BASE}/admin, as for example the socket file location (see my.cnf below).

Each directory which cannot be located under /u00 due to lack of space for instance (i.e. binary log directory), should be moved to another filesystem but a soft link must be created from /u00/app/mysql/admin/${MYSQL_SERVER} to this directory, see below:

/u01/app/mysql/admin/mysqld1/binlog -> /u99/mysqlbackup/mysqld1/binlog

Because all parameters in the MySQL server configuration file (my.cnf) refer to the same directory structure (${MYSQL_BASE}) maintenance efforts will be reduced.

The admin directory also contains the following directories:

Directory	Purpose
binlog	This directory contains the binary log files (ideally a link to a directory in another mount point of the OFA structure)
backup	This directory contains the MySQL server backups (ideally a link to a directory in another mount point of the OFA structure)
dump	This directory contains the mysqldump files (ideally a link to a directory in another mount point of the OFA structure)
create	MySQL server creation scripts
log	MySQL log directory (general log, slow query log, aso ...)
socket	Socket file and PID file
file .my.cnf	File containing MySQL server user, password and socket location allowing to administrate and operate the MySQL server (start/stop for instance)
sql	Instance specific SQL scripts

The hidden file: .my.cnf

The admin directory of each server should also contain the ".my.cnf" hidden file. This file hosts the [client] section. Each MariaDB server should have its own .my.cnf hidden file. It allows to connect directly to MariaDB server when using the mysql utility. This can be achieved without providing a password. In addition to this file, it is recommended to create an alias (i.e. mq) making the connection quite easy.

Hidden File .my.cnf :

/u01/app/mysql/admin/mysqld1/ [mysqld1] cat .my.cnf

[client]
socket          = /u01/app/mysql/admin/mysqld1/socket/mysqld1.sock
user            = mysql
password        = manager

Use the "chmod 600 .my.cnf" statement to change the permissions accordingly.

/u01/app/mysql/admin/mysqld1/ [mysqld1] ls -l .my.cnf
-rw------- 1 mysql mysql 105 Feb 21 18:32 .my.cnf

Use the "chmod 600 .my.cnf" statement to change the permissions accordingly.

The alias "mq" can be defined with the following statement in order to connect directly to the "mysql" server using the socket file and the root user :

/u00/app/mysql/admin/mysqld1/ [mysqld1] alias mq
alias mq='mysql --defaults-file=${MYSQL_BASE}/admin/${MYSQL_SERVER}/.my.cnf'

/u00/app/mysql/admin/mysqld1/ [mysqld1] mq
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.8-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Mysqld1-(root@localhost) [mysql]>

MariaDB server ports

Usually a MariaDB server is started on the TCP port 3306. since the dbi services MariaDBB operating concept allows to operate several releases and also several MariaDB servers on the same physical server a concept had to be defined to bind different ports. On UNIX/Linux the "ps" command extended by grep would easily allow to identify the port number and therefore the server which is really started (see below for an example).

The port range proposed by dbi services should be:

• Free in the list of TCP services already defined

• Fitting to the IPV4 port ranges which is limited to 65535. Some MySQL tools could be confronted with this issues and could not connect to the server if the ports are higher than 65535

• Recognizable as a MySQL-server like port

The proposed port range for MariaDB servers starts from 33001 to 33999 (assuming that about 999 servers could be started on the same server). This range is:

free in /etc/services

#               32897-33330 Unassigned

• below 65535

• recognizable, since it starts with the prefix "33"

The my.cnf file therefore looks like:

[mysqld1]
port    = 33001

[mysqld2]
port    = 33002

[mysqld3]
port    = 33003

The following alias performs a grep on the "ps -ef" ouput in order to present all MariaDB servers currently up and running.

mysql@OracleEnterpriseLinux01: /u01/app/mysql/admin/mysqld1/ [mysqld1] alias u
alias u='ps -ef | grep mysqld | grep -v "perl \-e" | grep port | perl -e "my \$line; my \$my_port; my \$my_serv ; while (\$line=<STDIN>) { \$_=\$line ; /--port=(\d+)/ ; \$my_port=\$1 ; \$_=\$line ; /mysqld(\d+)\.sock/ ; \$my_serv=\$1 ; print \"MySQL server mysqld\${my_serv} : \$my_port \n\"}"'

😃mysql@rocky1:/home/mysql/[mysqld2]u
*************** MariaDB Server ****************************

mysqld1 - master-v3
----------------------------
       Status : running
      Version : 10.9.2-MariaDB, (mariadb-10.9)
         Port : 33001 (bind-address:localhost)

mysqld2 - replica
----------------------------
       Status : running
      Version : 10.9.2-MariaDB, (mariadb-10.9)
         Port : 33002 (bind-address:0.0.0.0)

mysqld3 – prod-s2
----------------------------
       Status : running
      Version : 10.8.4-MariaDB, (mariadb-10.8)
         Port : 33003 (bind-address:localhost)

***********************************************************