LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 12-17-2018, 12:24 PM   #1
rene_p
LQ Newbie
 
Registered: Jan 2015
Posts: 8

Rep: Reputation: Disabled
MySQL 5.7 multiple instance issue on Oracle 7.6


I am currently trying to install a multiple instance of MySQL 5.7 on Oracle Linux 7 server and I am having issues. Here is what I have done so far

Create mysqld@.service in /usr/lib/systemd/system with contents

[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
PrivateTmp=true

[Install]
WantedBy=multi-user.target



Edited the /etc/my.cnf as below

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe --basedir=/usr

[mysqld1]
port=3307
datadir=/var/lib/mysql/1/
socket=/var/lib/mysql/1/mysql.sock
pid-file=/var/run/mysqld/mysqld1.pid
log-error=/var/log/mysqld1.log
symbolic-links=0

[mysqld2]
port=3308
datadir=/var/lib/mysql/2/
socket=/var/lib/mysql/2/mysql.sock
pid-file=/var/run/mysqld/mysqld2.pid
log-error=/var/log/mysqld2.log
symbolic-links=0

ran the following commands:
mkdir /var/lib/mysql/{1,2}
chown mysql:mysql /var/lib/mysql/1
chown mysql:mysql /var/lib/mysql/2
mysql_install_db --user=mysql --datadir=/var/lib/mysql/1/
mysql_install_db --user=mysql --datadir=/var/lib/mysql/2/
touch /var/log/mysqld{1,2}.log
chmod o-r /var/log/mysqld{1,2}.log
chcon --reference=/var/log/mysqld.log /var/log/mysqld{1,2}.log
systemctl daemon-reload

Then tried to start the mysqld1 service which fails:
mysqld@1.service - MySQL Multi Server for instance 1
Loaded: loaded (/usr/lib/systemd/system/mysqld@.service; enabled; vendor preset: disabled)
Active: failed (Result: start-limit) since Mon 2018-12-10 14:02:42 EST; 56s ago
Process: 8790 ExecStart=/usr/bin/mysqld_multi start %i (code=exited, status=1/FAILURE)

Dec 10 14:02:41 ictmysql02 systemd[1]: Failed to start MySQL Multi Server for instance 1.
Dec 10 14:02:41 ictmysql02 systemd[1]: Unit mysqld@1.service entered failed state.
Dec 10 14:02:41 ictmysql02 systemd[1]: mysqld@1.service failed.
Dec 10 14:02:42 ictmysql02 systemd[1]: mysqld@1.service holdoff time over, scheduling restart.
Dec 10 14:02:42 ictmysql02 systemd[1]: Stopped MySQL Multi Server for instance 1.
Dec 10 14:02:42 ictmysql02 systemd[1]: start request repeated too quickly for mysqld@1.service
Dec 10 14:02:42 ictmysql02 systemd[1]: Failed to start MySQL Multi Server for instance 1.
Dec 10 14:02:42 ictmysql02 systemd[1]: Unit mysqld@1.service entered failed state.
Dec 10 14:02:42 ictmysql02 systemd[1]: mysqld@1.service failed.

I am not sure where I went wrong or what is missing. Any help is greatly appreciated.
Thank you,
Rene
 
Old 12-17-2018, 12:55 PM   #2
scasey
Senior Member
 
Registered: Feb 2013
Location: Tucson, AZ, USA
Distribution: CentOS 7.7.1908
Posts: 4,376

Rep: Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550
Please use [code] tags for commands and output.

What's in the /var/log/mysqld1.log?

What does
Code:
systemctl status mysqld@1.service
show?
 
Old 12-17-2018, 02:35 PM   #3
rene_p
LQ Newbie
 
Registered: Jan 2015
Posts: 8

Original Poster
Rep: Reputation: Disabled
Thanks for responding. The mysqld1.log is empty as the service cannot start and the status is

Code:
 mysqld@1.service - MySQL Multi Server for instance 1
   Loaded: loaded (/usr/lib/systemd/system/mysqld@.service; enabled; vendor preset: disabled)
   Active: failed (Result: start-limit) since Mon 2018-12-17 11:44:37 EST; 2h 49min ago
  Process: 15862 ExecStart=/usr/bin/mysqld_multi start %i (code=exited, status=1/FAILURE)

Dec 17 11:44:37 ictmysql02 systemd[1]: Failed to start MySQL Multi Server for instance 1.
Dec 17 11:44:37 ictmysql02 systemd[1]: Unit mysqld@1.service entered failed state.
Dec 17 11:44:37 ictmysql02 systemd[1]: mysqld@1.service failed.
Dec 17 11:44:37 ictmysql02 systemd[1]: mysqld@1.service holdoff time over, scheduling restart.
Dec 17 11:44:37 ictmysql02 systemd[1]: Stopped MySQL Multi Server for instance 1.
Dec 17 11:44:37 ictmysql02 systemd[1]: start request repeated too quickly for mysqld@1.service
Dec 17 11:44:37 ictmysql02 systemd[1]: Failed to start MySQL Multi Server for instance 1.
Dec 17 11:44:37 ictmysql02 systemd[1]: Unit mysqld@1.service entered failed state.
Dec 17 11:44:37 ictmysql02 systemd[1]: mysqld@1.service failed
 
Old 12-18-2018, 11:33 PM   #4
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.10, Centos 7.5
Posts: 17,705

Rep: Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496
Can I ask why you think you need to do this?
You do know MySQL can have multiple schemas (ie databases) in the same instance quite safely ?
Multiple instances would also chew up the performance of your system.
 
Old 12-19-2018, 08:03 AM   #5
rene_p
LQ Newbie
 
Registered: Jan 2015
Posts: 8

Original Poster
Rep: Reputation: Disabled
It comes down to that each instance is owned by different departments who want some control over their database. I have had issues in the past where others have created problems for other schemas in the same instance. The best way I know of to ensure that it does not happen is to provide their own instance so they can connect via MySQL workbench and administer without impacting others. I currently have multiple instances on a window server but I would like to move to a Linux platform.

Thanks.
Rene

Last edited by rene_p; 12-19-2018 at 08:05 AM.
 
Old 12-19-2018, 09:52 AM   #6
urbanwks
Member
 
Registered: Sep 2003
Distribution: Slackware64-Current, FreeBSD 11.0
Posts: 193

Rep: Reputation: 205Reputation: 205Reputation: 205
Quote:
Originally Posted by rene_p View Post
It comes down to that each instance is owned by different departments who want some control over their database. I have had issues in the past where others have created problems for other schemas in the same instance. The best way I know of to ensure that it does not happen is to provide their own instance so they can connect via MySQL workbench and administer without impacting others. I currently have multiple instances on a window server but I would like to move to a Linux platform.
What I do in this situation is create specific mysql users with access only to the schema they need, for what it's worth. Granted, I'm not familiar with your exact limitations.
 
Old 12-19-2018, 10:56 AM   #7
scasey
Senior Member
 
Registered: Feb 2013
Location: Tucson, AZ, USA
Distribution: CentOS 7.7.1908
Posts: 4,376

Rep: Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550Reputation: 1550
I agree with Rene; you should be able to manage limited access, etc. within a single instance of mysql.

That said, you're starting with mysql_multi which doesn't seem to have any specifics configured, so it can't start.

Have you tried starting with mysqld1 or mysqld2 ?

[and please edit your OP to put code tags around code and output. It's very difficult to read as it is]
 
Old 12-19-2018, 11:45 PM   #8
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.10, Centos 7.5
Posts: 17,705

Rep: Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496Reputation: 2496
As per urbanwks, creating specific mysql users with access only to the schema they need, is the normal way to go in the IT industry.
Only the DBA should have access to all the schemas.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't ping or ssh to the cirros instance of the openstack instance visuharsha Linux - Networking 0 12-21-2017 02:44 PM
Postfix multiple instance only default instance work Dante K Linux - Server 0 09-05-2017 10:45 AM
[SOLVED] Switch to instance instead of creating a new instance of a program javascriptninja Linux - Newbie 8 01-28-2012 04:39 PM
LXer: Install Oracle 11gR2 on SL6 KVM at KVM-QEMU Instance on SL 6 (alpha 3) LXer Syndicated Linux News 2 01-01-2011 07:42 AM
Oracle Database Connected to an idle instance. RMLinux Linux - Newbie 1 11-25-2008 01:58 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 11:47 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration