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?

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

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!

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.

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

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.