Unix/Linux Go Back    


UNIX and Linux Applications Discuss UNIX and Linux software applications. This includes SQL, Databases, Middleware, MOM, SOA, EDA, CEP, BI, BPM and similar topics.

Configuring mysql for multiple instance only

UNIX and Linux Applications


Tags
solved

Closed    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 03-14-2015
jcdole jcdole is offline
Registered User
 
Join Date: Apr 2011
Last Activity: 14 February 2017, 7:02 AM EST
Location: 64500 - france ( south-ouest )
Posts: 147
Thanks: 51
Thanked 2 Times in 2 Posts
Configuring mysql for multiple instance only

Hello.
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

Code:
[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

Code:
[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

I would like some advise.

Any help is welcome.
Sponsored Links
    #2  
Old Unix and Linux 03-16-2015
jcdole jcdole is offline
Registered User
 
Join Date: Apr 2011
Last Activity: 14 February 2017, 7:02 AM EST
Location: 64500 - france ( south-ouest )
Posts: 147
Thanks: 51
Thanked 2 Times in 2 Posts
This thread is closed.

After following the remark from hendwolt, reading this thread mysqld_multi: How to run multiple instances of MySQL
and doing some more correction mariadb with multiple instance is running.
In summary:

Every things are configured in /etc/my.cnf
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 )

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

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

Code:
[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

Code:
# 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 :

Code:
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

Sponsored Links
Closed

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
configuring MYsql db on redhat cluster bobby320 Red Hat 0 08-13-2012 01:36 PM
Configuring MySQL problem vpundit AIX 9 06-28-2012 04:36 PM
Multiple instance in tomcat tuxslonik Shell Programming and Scripting 0 07-21-2011 10:05 AM
Multiple Instance Of Same Process glamo_2312 UNIX for Advanced & Expert Users 5 04-26-2009 08:25 AM
Multiple instance of same process glamo_2312 UNIX for Dummies Questions & Answers 0 04-23-2009 11:21 AM



All times are GMT -4. The time now is 07:44 PM.