LinuxQuestions.org
Help answer threads with 0 replies.
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 09-02-2009, 10:14 PM   #1
winairmvs
Member
 
Registered: Aug 2009
Posts: 42

Rep: Reputation: 16
mysql on shared storage ocfs2


I just built a shared storage platform using ocfs2, which was working fantastically. I moved the datadir in /etc/my.cnf to the mount point that my shared disk was located at, /mnt/mysql, and everything was good to go. I created a database on one server, and I could view that database on another server connected to the shared disk running the same type of mysql my.cnf configuration. The problem is when I try to create a password for the root user of the mysql install the other machines connected to the shared storage cannot get their mysql installation to authenticate to the shared database after the change. Any ideas?
 
Old 09-03-2009, 12:34 AM   #2
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
Are you saying that you're running two (or more) mysql servers that access the same database files? If so, I think that it is a recipe for problems; it definitely does not sound right.

If not, can you explain in a little more detail the setup?

PS Not familiar with ocfs2 and its options
 
Old 09-03-2009, 08:30 AM   #3
winairmvs
Member
 
Registered: Aug 2009
Posts: 42

Original Poster
Rep: Reputation: 16
Quote:
Originally Posted by Wim Sturkenboom View Post
Are you saying that you're running two (or more) mysql servers that access the same database files? If so, I think that it is a recipe for problems; it definitely does not sound right.

If not, can you explain in a little more detail the setup?

PS Not familiar with ocfs2 and its options
We have a lun mapped to three servers as shared storage which is facilitated by the ocfs2 clustering service and file system. The disk is accessible by all the servers, using dlm locking to protect file from corruption.

I configured my /etc/my.cnf to look like this:
Code:
[mysqld]
datadir=/mnt/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
As you can see I changed the datadir on all three servers to have them look at /mnt/mysql for the databases. I really don't know much about mysql, but I was under the assumption that the databases were just a pile of tables, and that they didn't hold anything specific to the server they "lived on". I can create a database in mysql on any of the three servers, have it show up in /mnt/mysql, and then I can access the table from any of the three servers, which is pretty slick . The problem is when I try to change authentication to a database to use a username and password, the other servers give me the error:

Code:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
I have restarted the mysql service, and any combination of uninstalling and reinstalling packages you can imagine to get these mysql servers to play nicely, but I don't know where to go from here.

Thanks!

Last edited by winairmvs; 09-03-2009 at 08:32 AM.
 
Old 09-03-2009, 10:51 AM   #4
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
Somehow I'm missing something. You have three servers, each running the mysqld daemon (note that the d is important for me to understand). All three daemons store their databases on the same 'disk' that is mounted in the subdirectory mysql under /mnt.

Is that correct? If so, I don't think that it's supposed to work like that. It's quite funny that if you grant access to a database to a user (not being root I assume), a root user can no longer connect. This might indicate a file locking issue. Don't forget that there is a database called mysql that stores the users and their privileges. By writing to that database, there might be a (filesystem) lock set by the one daemon that prevents read access for the other daemons. The other daemons need that access at the moment that you connect to the daemon using e.g. the mysql command.

There should be one daemon (mysqld) on one of the servers providing access to all databases. Systems that need access to a database should do that through that one daemon.
 
Old 09-03-2009, 04:16 PM   #5
winairmvs
Member
 
Registered: Aug 2009
Posts: 42

Original Poster
Rep: Reputation: 16
Quote:
Originally Posted by Wim Sturkenboom View Post
Somehow I'm missing something. You have three servers, each running the mysqld daemon (note that the d is important for me to understand). All three daemons store their databases on the same 'disk' that is mounted in the subdirectory mysql under /mnt.

Is that correct? If so, I don't think that it's supposed to work like that. It's quite funny that if you grant access to a database to a user (not being root I assume), a root user can no longer connect. This might indicate a file locking issue. Don't forget that there is a database called mysql that stores the users and their privileges. By writing to that database, there might be a (filesystem) lock set by the one daemon that prevents read access for the other daemons. The other daemons need that access at the moment that you connect to the daemon using e.g. the mysql command.

There should be one daemon (mysqld) on one of the servers providing access to all databases. Systems that need access to a database should do that through that one daemon.
Thanks for all the information, you answered one of my questions about where the authentication is stored for database access (being the mysql database) Essentially, each instance of mysqld (That is what I meant sorry) is running separately on each server, and I am trying to get them to read and write to databases living on a shared drive. This is for redundancy and cpu clustering purposes, so if one server goes down the others can continue to server the databases. In terms of locking, ocfs2 is designed specifically to do dlm locking on files writes. There can never be two servers writing the same file in the cluster. I just want to share the databases between the servers so that I can spin up a mysql server, add it to the cluster, change the datadir to the mounted mysql datadir, and have a clustered mysql server.

Edit: I also found that you cannot separate the datadir= and the socket= directories cannot be separated. At least in my experiments the service will not start if the database mysql and the file mysql.socket are in different places.

Last edited by winairmvs; 09-03-2009 at 04:20 PM.
 
Old 09-03-2009, 11:26 PM   #6
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
With regards to redundancy: MySQL has a mechanism called master/slave replication.

With regards to (dlm) locking: from an enduser perspective, it is not defined what the mysql daemon will do with files. As opening and closing files is time consuming, I assume that the daemon will keep a file open for a while. Your locking on the system level will therefore happily prevent the other daemons from accessing it. One way to close the file might be with statements like flush privileges and flush tables.
You can force flushing with the --flush option when starting the daemon; I can however imagine that that will come at a performance penalty.

With regards to the socket: the location of the socket file should not matter in my opinion; if it would not work, why would there be options to seperate them? On one of my servers, the databases are in /var/lib/mysql/... and the socket is in /var/run . If I remember correctly, I have had a system where the mysql databases where stored on a different disk.

Good luck
 
Old 09-04-2009, 08:52 AM   #7
winairmvs
Member
 
Registered: Aug 2009
Posts: 42

Original Poster
Rep: Reputation: 16
Thanks again for the information, I had originally intentioned to use mysql replication, or mysql clustering but using shared storage is much simpler and would have been a better solution had mysql been designed in a fashion that allowed its use.
 
  


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
fdisk for Shared Storage? your_shadow03 Linux - Newbie 2 08-20-2008 04:09 AM
using lvm/ocfs2 to configure san storage mn9 Linux - Server 1 06-12-2007 03:35 AM
shared Storage for cluster hardeep_ubhi Linux - Enterprise 2 03-04-2007 11:54 PM
Shared Storage/SAN with linux fatemehtaj Linux - Networking 1 08-05-2006 05:11 PM
Shared Storage/SAN with linux fatemehtaj Linux - Enterprise 0 08-02-2006 06:24 AM


All times are GMT -5. The time now is 04:07 AM.

Main Menu
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
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration