OS 13.2 - MYSQL MARIADB - Using multiple instances

English Applications
#1

I plan to use mysql with only instance database so I can stop one database for maintenance without stopping every thing.

When one reads through the my.cnf config file, it is not clear if we must use at the same time a single database mysql plus any instances mysqld2 (for app1), mysqld3 (for app2), and so on.

Or may I get rid of the mysqld process and start only mysqld2, mysqld3, …
I have try that but could not start any instance. Of course I have configured a systemd service file.

Or may I use mysqld1 as mysql database pointing to mysql as first mandatory database and then add more instance when necessary.

here code for mysqld@.service

[Unit]
Description=MySQL Multi Server for instance %i
After=syslog.target
After=network.target

[Service]
User=mysql
Group=mysql
Type=forking
ExecStart=/usr/bin/mysqld_multi --defaults-file=/etc/mysql/cfg_%i.cnf  --log=/var/log/mysql/mysql_multi.log --verbose  start %i
ExecStop=/usr/bin/mysqld_multi --log=/var/log/mysql/mysql_multi.log  stop %i
#Restart=always
#RestartSec=5
PrivateTmp=true

[Install]
WantedBy=multi-user.target

my config file in /etc/my.cnf


[client]
port       = 3306
socket     = /bdd/bd_1/sock/mysql_cli.sock

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log

[mysqld2]
port       = 63332
datadir    = /bdd/bd_2/app2
pid-file   = /bdd/bd_2/app2/mysqld.pid
socket     = /bdd/bd_2/app2/mysql.sock
user       = mysql

[mysqld3]
port       = 63333
datadir    = /bdd/bd_3/app3
pid-file   = /bdd/bd_2/app3/mysqld.pid
socket     = /bdd/bd_2/app3/mysql.sock
user       = mysql

!includedir /etc/my.cnf.d

Must I run mysql_secure_installation for each instance mysqld2, mysqld3, …

I have follow this link : http://www.anntoin.com/Blog/Tutorials/MultipleDBInstances.html

[LEFT]This is somehow a general question to help me to continue in my installation.

[/LEFT]
I would like some advise.

Any help is welcome.

#2

Hi,

your service file uses different configuration files instead of /etc/my.cnf:

--defaults-file=/etc/mysql/cfg_%i.cnf

IMHO you should either create these files or omit them in ExecStart.

Hendrik

#3

Yes.
I have change my mysqld@.service to ignore specific *.cfg file.

Thank you very much.:slight_smile:

#4

This thread is closed.

After following the remark from hendwolt, reading this thread http://www.percona.com/blog/2014/08/26/mysqld_multi-how-to-run-multiple-instances-of-mysql
and doing some more correction mariadb with multiple instance is running.

[LEFT]In summary:
[/LEFT]

Every things are configured in /etc/my.cnf
[LEFT]Accordingly, nothing particular is configured in /etc/systemd/system/mysqld@.service file.
My database files, socket files, pid files are located in /bdd/bd_1/project1, /bdd/bd_2/project2…
The owner of all files is set to mysql.
The log files are located in /var/log/mysql/project1, /var/log/mysql/project2…
The owner of all files is set to mysql.
In the config file /etc/my.cnf, rename [mysqld] to [mysqld0] so you get rid of the single database.
Don’t forget to open port for mysql on the firewall

Enabling instances ( instances start at bootup )

systemctl enable mysqld@1
systemctl enable mysqld@2
systemctl enable mysqld@3
systemctl enable mysqld@4

here is /etc/systemd/system/mysqld@.service

[Unit]
Description=MySQL Multi Server for instance %i
After=syslog.target
After=network.target

[Service]
User=mysql
Group=mysql
Type=forking
ExecStart=/usr/bin/mysqld_multi start %i
ExecStop=/usr/bin/mysqld_multi stop %i
Restart=always
RestartSec=5
PrivateTmp=true

[Install]
WantedBy=multi-user.target

here is /etc/my.cnf

# The following options will be passed to all MariaDB clients
[client]
port       = 3306
socket     = /bdd/client/sock

[mysqld0]
innodb_file_format=Barracuda
innodb_file_per_table=ON

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id    = 1

bind-address = 0.0.0.0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysql/mysqld_multi.log

# WARNING
# --------
# If you uncomment mysqld1 than make absolutely sure, that database mysql,
# configured above, is not started.  This may result in corrupted data!
#
# renamed as [mysqld0]
#

[mysqld1]
bind-address = 0.0.0.0
port       = 63331
datadir    = /bdd/bd_1/mysql
pid-file   = /bdd/bd_1/mysql/sock/mysqld.pid
socket     = /bdd/bd_1/mysql/sock/mysql.sock
user       = mysql
general_log         = 1
general_log_file    = /var/log/mysql/mysql/mysql.log
log-error             = /var/log/mysql/mysql/mysql_error.log
log-warnings   = 0
symbolic-links    = 0

[mysqld2]
bind-address = 0.0.0.0
port       = 63332
datadir    = /bdd/bd_2/postfix
pid-file   = /bdd/bd_2/postfix/sock/mysqld.pid
socket     = /bdd/bd_2/postfix/sock/mysql.sock
### user       = postfix
user       = mysql
general_log         = 1
general_log_file    = /var/log/mysql/postfix/postfix.log
log-error             = /var/log/mysql/postfix/postfix_error.log
log-warnings   = 0
symbolic-links    = 0

[mysqld3]
bind-address = 0.0.0.0
port       = 63333
datadir    = /bdd/bd_3/bacula
pid-file   = /bdd/bd_3/bacula/sock/mysqld.pid
socket     = /bdd/bd_3/bacula/sock/mysql.sock
### user       = bacula
user       = mysql
general_log         = 1
general_log_file    = /var/log/mysql/bacula/bacula.log
log-error             = /var/log/mysql/bacula/bacula_error.log
log-warnings   = 0
symbolic-links    = 0

[mysqld4]
bind-address = 0.0.0.0
port       = 63334
datadir    = /bdd/bd_4/fxa
pid-file   = /bdd/bd_4/fxa/sock/mysqld.pid
socket     = /bdd/bd_4/fxa/sock/mysql.sock
### user       = fxa
user       = mysql
general_log         = 1
general_log_file    = /var/log/mysql/fxa/fxa.log
log-error             = /var/log/mysql/fxa/fxa_error.log
log-warnings   = 0
symbolic-links    = 0

!includedir /etc/my.cnf.d

Here status for instance 3 :

linux-cc07:~ # systemctl -l status mysqld@3
mysqld@3.service - MySQL Multi Server for instance 3
   Loaded: loaded (/etc/systemd/system/mysqld@.service; enabled)
   Active: active (running) since Mon 2015-03-16 15:16:09 CET; 1h 32min ago
 Main PID: 1920 (mysqld_safe)
   CGroup: /system.slice/system-mysqld.slice/mysqld@3.service
           ├─1920 /bin/sh /usr/bin/mysqld_safe --bind-address=0.0.0.0 --port=63333 --datadir=/bdd/bd_3/bacula --pid-file=/bdd/bd_3/bacula/sock/mysqld.pid --socket=/bdd/bd_3/bacula/sock/mysql.sock --user=mysql --general_log=1 --general_log_file=/var/log/mysql/bacula/bacula.log --log-error=/var/log/mysql/bacula/bacula_error.log --log-warnings=6 --symbolic-links=0
           └─2128 /usr/sbin/mysqld --basedir=/usr --datadir=/bdd/bd_3/bacula --plugin-dir=/usr/lib64/mysql/plugin --bind-address=0.0.0.0 --general_log=1 --general_log_file=/var/log/mysql/bacula/bacula.log --log-warnings=6 --symbolic-links=0 --log-error=/var/log/mysql/bacula/bacula_error.log --pid-file=/bdd/bd_3/bacula/sock/mysqld.pid --socket=/bdd/bd_3/bacula/sock/mysql.sock --port=63333

[/LEFT]