1

MYSQL Version: 5, OS: RHEL5

MYSQL Newbie here. I am trying to create a database in a different directory without disturbing other databases. Looking up at stackoverflow, it seems that only way to do this in MYSQL5 is to create symlinks.

The default data directory is '/var/lib/mysql' and default user is 'mysql' as specified in /etc/my.cnf file. This data directory is owned by linux user/group mysql.

So if I create a symlink inside this directory, it has to be owned by user/group mysql. (I don't want to change permissions of this folder and mess up other databases). Also for this symlink method to work, I need to give access to intended data directory for user mysql (which is also not acceptable).

I tried creating my own configuration file ~/.my.cnf with user and datadir set to desired values and ran "mysql --defaults-file=~/.my.cnf". But it doesn't work. Keeps pulling the list of databases from the default location.

How do I move away from this 'mysql' user tangle, just for my database?

user3300676
  • 307
  • 2
  • 3
  • 8
  • `mysql --defaults-file=~/.my.cnf` ... for a start, `mysql` is the *client* program, while `mysqld` is the server. You seem to have missed the fact that what you are actually trying to do requires running a second instance of MySQL Server on the machine, listening on a different port, using a different socket file, a different system tablespace, logs... and you only use symlinks when you need to move a single database to a different device for performance or space reasons. It's not for doing things "without disturbing other databases." What are you really trying to accomplish, and why? – Michael - sqlbot Oct 16 '17 at 05:15
  • @michael: thanks for the quick response. I am trying to keep my database in a netapps mount so several hosts can access/modify the database. The problem with creating a symlink from /var/lib/mysql to the netapps directory is the link has to be owned by the Linux user mysql and also the destination directory in the netapps should also be owned by the mysql user, which is not acceptable for my case. – user3300676 Oct 16 '17 at 13:11
  • 1
    Okay, now we are getting somewhere... *"I am trying to keep my database in a netapps mount so several hosts can access/modify the database."* I'm afraid that's not how most relational databases work, including MySQL. The MySQL Server daemon on **one** machine requires *exclusive* access to the files, and the software on those other machines then connect back to the MySQL Server daemon on that one machine, across the network. You access databases from multiple machines that way, not by sharing files access. – Michael - sqlbot Oct 16 '17 at 17:39
  • Understood. Even if I have to access the database from any machine, through the server running on (lets call it) machineA, I would like to have the database in the netapps, rather than in machineA's /var/lib/mysql. Is there a way to create the symbolic link from /var/lib/mysql to the netapps directory with another user, other than 'mysql'? The issue is I cannot give permission to the netapps directory for 'mysql' user. – user3300676 Oct 16 '17 at 18:09
  • 1
    Having the backing store on a network share is really not advisable for performance reasons, but you may get away with it. You may also encounter issues if there are locking limits (e.g. max locks per user, pid, mount, etc.) imposed by the netapp. You do **not** want to tinker with this on your existing server. Set up a new machine to test with. *"The issue is I cannot give permission to the netapps directory for 'mysql' user."* Not clear why you can't, please clarify. One option, run MySQL as an entirely different user and change *all* ownership to match. – Michael - sqlbot Oct 16 '17 at 18:30
  • I cannot change the user permissions on the netapps directory because other applications will break. I was trying to run MySQL as an entirely different user and set the user and datadir to match my requirements (match the netapps permissions), which is where I ran into the issue of mysqld server not starting as the new user. It kept picking up the old directories and databases. How do I make mysqld server start with the specified my.cnf file? – user3300676 Oct 16 '17 at 19:01

0 Answers0