Multi MariaDB services creation on the same server

 Add [mysqld2] in existing server.cnf

# Configuration Settings for mysqld_multi , as well as separate instances. 

#

[mysqld_multi]

mysqld     = /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

[mysqld]

socket     = /var/lib/mysql/mysql.sock

port       = 3306

pid-file   = /var/lib/mysql/100-066-152-021.pid

datadir    = /var/lib/mysql

log-error  = /var/log/mysql/error.log

server-id              = 1

expire_logs_days        = 10

[mysqld2]

socket     = /var/lib/mysql2/mysql.sock

port       = 3307

pid-file   = /var/lib/mysql2/100-066-152-021.pid

datadir    = /var/lib/mysql2

log-error  = /var/log/mysql/error2.log

server-id              = 2

expire_logs_days        = 10

mkdir /var/lib/mysql2

chown mysql:mysql -v -R /var/lib/mysql2

chown mysql:mysql -v -R /var/lib/mysql2

mysqld_multi --defaults-file=/etc/my.cnf.d/server.cnf --log=/etc/my.cnf.d/multi_ins.log start

chmod o-r /etc/my.cnf.d/multi_ins.log

chown mysql:mysql -v -R /etc/my.cnf.d/multi_ins.log

mysqld_multi --defaults-file=/etc/my.cnf.d/server.cnf report

mysql -A -S /var/lib/mysql2/mysql.sock

set root password;

mysqld_multi --defaults-file=/etc/my.cnf.d/server.cnf --log=/etc/my.cnf.d/multi_ins.log stop 2

-- ------------

-- running as a service

vi /etc/systemd/system/mariadb_multi.service

[Unit]

Description=MySQL Multi-Server

After=network.target

[Service]

Type=forking

ExecStart=/usr/bin/mysqld_multi --defaults-file=/etc/my.cnf.d/server.cnf  --log=/etc/my.cnf.d/multi_ins.log start 2

ExecStop=/usr/bin/mysqld_multi --defaults-file=/etc/my.cnf.d/server.cnf --log=/etc/my.cnf.d/multi_ins.log stop 2

ExecReload=/usr/bin/mysqld_multi --defaults-file=/etc/my.cnf.d/server.cnf --log=/etc/my.cnf.d/multi_ins.log reload 2

User=mysql

Group=mysql

[Install]

WantedBy=multi-user.target

-- ------------------------

sudo systemctl daemon-reload

sudo systemctl enable mariadb_multi.service

-- references

chown -v -R mysql:mysql /usr/local/mysql/mdb1/ansible-host1.pid1

sudo chown -R mysql:mysql /usr/local/mysql/mdb2

sudo chmod -R 755 /usr/local/mysql/mdb2 

stat -c "%a %U %G" /var/lib/mysql/100-066-152-021.pid

chmod 644 /usr/local/mysql/mdb1/ansible-host1.pid1

chown mysql:mysql /usr/local/mysql/mdb1/ansible-host1.pid1

support-files/mariadb.service

mysql -A -S /tmp/mysql.sock1

mysqladmin -u root -p -P3307 shutdown

mysqladmin -S /tmp/mysql.sock1 shutdown

stat -c "%a %U %G" /etc/mysql/mysqld_multi_cls.cnf

chmod 644 /etc/mysql/mysqld_multi_cls.cnf

chown -R mysql:mysql /etc/mysql/mysqld_multi_cls.cnf

locate mariadb.service

systemctl status mysqld_multi.service

-- Un installation

1. Stop the Second Service

mysqladmin --defaults-file=/etc/my.cnf.d/my_multi2.cnf shutdown

2. Remove Service Configuration

Edit your my.cnf (or /etc/my.cnf.d/my_multi.cnf if its used) and remove the configuration sections related to the second service. These sections typically look like:

ini

Copy code

[mysqld2]

port=3307

datadir=/var/lib/mysql2

socket=/var/lib/mysql2/mysql.sock

log-error=/var/log/mysqld2.log

pid-file=/var/run/mysqld/mysqld2.pid

3. Delete the Data Directory

If you no longer need the data, you can remove the data directory for the second instance:

bash

Copy code

rm -rf /var/lib/mysql2

4. Remove Logs and PID Files

Clean up any log or PID files associated with the second service:

bash

Copy code

rm -f /var/log/mysqld2.log

rm -f /var/run/mysqld/mysqld2.pid

5. Verify Service Removal

If you use mysql_multi, you should ensure the [mysqld2] section is no longer referenced. Run:

bash

Copy code

mysql_multi --example

6. Restart mysql_multi Services

To refresh the mysql_multi setup:

bash

Copy code

mysql_multi stop

mysql_multi start