Instance admin directory

dbi services advices storing MySQL database admin directories under /u01/app/mysql/admin (${MYSQL_BASE}/admin). For each MySQL 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:

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 MySQL server should have its own .my.cnf hidden file. It allows to connect directly to MySQL 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 :

/u01/app/mysql/admin/mysqld1/ [mysqld1] alias mq
alias mq='mysql --defaults-file=${MYSQL_BASE}/admin/${MYSQL_SERVER}/.my.cnf'
/u01/app/mysql/admin/mysqld1/ [mysqld1] mq
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24-log
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
mysqld1-(root@localhost) [mysql]>

MySQL Shell

The alias "mqs" can also be used in order to connect to "mysql" using "MySQL Shell".

It will connect to MySQL Shell using its binaries store in: $MYSQL_BASE/product

An symbolic link need to be created using "mysql-shell", e.g. with MySQL Shell 8.2.1:

/u01/app/mysql/ [mysqld1] > cd $MYSQL_BASE/product
/u01/app/mysql/product/ [mysqld1] ln -s ln -s mysql-shell-8.2.1-linux-glibc2.28-x86-64bit mysql-shell
/u01/app/mysql/product/ [mysqld1] ll
total 2
lrwxrwxrwx.  1 mysql mysql   36 Dec 1  2022 mysql-8.0.18 -> mysql-8.0.18-linux-glibc2.12-x86_64/
lrwxrwxrwx.  1 mysql mysql   43 Dec  1 2023 mysql-shell -> mysql-shell-8.2.1-linux-glibc2.28-x86-64bit

MySQL server ports

Usually a MySQL server is started on the TCP port 3306. since the dbi services MySQL operating concept allows to operate several releases and also several MySQL 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 MySQL 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 MySQL 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
*************** MySQL Server ****************************

mysqld1 - master-v3
----------------------------
       Status : running
      Version : 8.0.18 (mysql-8.0.18)
         Port : 33001 (bind-address:localhost)

mysqld2 - replica
----------------------------
       Status : running
      Version : 8.0.18 (mysql-8.0.18)
         Port : 33002 (bind-address:0.0.0.0)

mysqld3 – prod-s2
----------------------------
       Status : running
      Version : 8.0.16 (mysql-8.0.16)
         Port : 33003 (bind-address:localhost)

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

Previousmysql users and groups

Last updated 1 year ago