Configuring mysql for multiple instance only


 
Thread Tools Search this Thread
Special Forums UNIX and Linux Applications Configuring mysql for multiple instance only
# 1  
Old 03-14-2015
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.
# 2  
Old 03-16-2015
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

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Red Hat

configuring MYsql db on redhat cluster

Hello, can someone please suggest me in configuring the mysql db on redhat cluster, I have few questions. 1. where do I have to configure heart beat links, and is there any file in the redhat cluster that we update it to use these ipaddress and these interface on the node. 2. I am configuring... (0 Replies)
Discussion started by: bobby320
0 Replies

2. AIX

Configuring MySQL problem

Hi, Trying to setup mediawiki on a system but having problems configuring mysql: CC=/usr/bin ./configure checking build system type... powerpc-ibm-aix6.1.0.0 checking host system type... powerpc-ibm-aix6.1.0.0 checking target system type... powerpc-ibm-aix6.1.0.0 checking for a... (9 Replies)
Discussion started by: vpundit
9 Replies

3. Shell Programming and Scripting

No lock file: Preventing multiple instance of a script

I've been bitten by using a lock or pid file to prevent multiple instances of a script. A user typed kill -9, and the pid file didn't go away. You can't trap -9. So when he tried to restart, it said "already running", and I got trouble report. Argh. So here's what we came up with: # Stop if... (1 Reply)
Discussion started by: McFadden586
1 Replies

4. Shell Programming and Scripting

Multiple instance in tomcat

I need to install a tomcat6 with multiple instances like instance1,instance2 and instance3 in a server. I came to know that for that we need to install tomcat6,apache2.0,mod_jk1.2 and jre with tools.jar installed.And we need to create multiple instances with same web.xml and difference... (0 Replies)
Discussion started by: tuxslonik
0 Replies

5. Shell Programming and Scripting

A script that spawns multiple instance of itself.

Hi all,I have some questions of forking new process,the code is below.Any help will be appreciated. 1 #! /bin/bash 2 3 PIDS=$(pidof sh $0) 4 P_array=( $PIDS ) 5 echo $PIDS 6 let "instances = ${#P_array}-1" 7 8 echo "$instances instance(s)" of this script running." 9... (4 Replies)
Discussion started by: homeboy
4 Replies

6. UNIX for Advanced & Expert Users

Multiple Instance of Unix Shell Script

Hi All, I have a problem mentioned below. I have a script which performs line by line operations on several files. I have a temp_file storing the list of names of the file to be validated. Right not in while loop i validate these files one by one. Is there anyway that i can modify... (1 Reply)
Discussion started by: amitaryans
1 Replies

7. Shell Programming and Scripting

Help with multiple instance script checking

I am trying to debug the following script. It appears that when the check for script running occurs, it's finding the actual grep statement and causing the script believe the script is already running. This is deployed on two different servers where one works fine, the other doesn't. Any ideas? ... (2 Replies)
Discussion started by: DaddyMoose
2 Replies

8. UNIX for Advanced & Expert Users

Multiple Instance Of Same Process

Hi Everyone, I am using solaris 5.10. I have a java process running in server mode in unix. The problem is that it automatically forks i.e creates a child process. I mean suddenly two instances of that process start running , in which the process-id of first instance is the parent... (5 Replies)
Discussion started by: glamo_2312
5 Replies

9. UNIX for Dummies Questions & Answers

Multiple instance of same process

;)Hi Everyone, I am using solaris 5.10. I have a java process running in server mode in unix. The problem is that it automatically forks i.e creates a child process. I mean suddenly two instances of that process start running , in which the process-id of first instance is the parent... (0 Replies)
Discussion started by: glamo_2312
0 Replies

10. Shell Programming and Scripting

Multiple PHP sessions within the same browser instance

Dear all..... I am currently writing a Help-Desk / Knowledge Base application using PHP/PostGreSQL. I authenticate the user using a quite elaborate mechanism of cookies. The problem is that using cookies (I also have a version using sessions with the same problem), I can only seem to get one... (4 Replies)
Discussion started by: zazzybob
4 Replies
Login or Register to Ask a Question