Enforcing a user defined directory for the database.

[Einar Petersen]

Hi there I'm doing a MySQL Java DB project and I would like to be able to decide on where MySQL creates it's databases for backup purposes. This is part of an evaluation where I am to recommend integration of an easy to use open source database into a project for commercial deployment in the medical sector.

I have tried to go into the /etc/mysql/my.cnf file and have altered the line datadir = /var/lib/mysql to /home/ep/5sem/database as I have seen this suggested eleswhere.

It did unfortunately not work.

Tried to save the file as .my.cnf, didn't work either as suggested elsewher, did not work either.

Tried to rename the my.cnf to mmy.cnf also so that I could be sure it would not interfere with the previously changed file, sadly I could start MySQL, but the database file was created in the /var/lib/mysql directory, even though the path clearly stated a different directory....

So here I am...

To shut the DB down - used: mysqladmin -u root -p shutdown

The DB stopped - But the changes in the datadir = had the following effect when I tried to restart.

mysql -u root -p
Enter password: ***

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I did set permisions on relevant dir to mysql mysql

Also tried to reboot the system every time I made a change.

No difference, but as soon as I changed the path back into the old path in the cnf file I could work with DB's in the standard installation directory.

Version info: mysql Ver 14.12 Distrib 5.0.38, for pc-linux-gnu (i486) using readline 5.2

Excerpt from my.cnf

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
# datadir = /var/lib/mysql
datadir = /home/ep/5sem/database
tmpdir = /tmp
language = /usr/share/mysql/english

Any suggestions on how to force MySQL to use the directory /home/ep/5sem/database to save all information to ?

Is there really no simple create database order with a path option so that a database can be created in an arbitrary directory a users inputs so that all data and information relevant to that particular database gets saved in that specific directory ? I have seen hundreds of threads pertaining to this relatively obvious problem domain and wery little consistent information on the subject.

There are no data of consequence in any DB that needs to be considered, for all intents and purposes the only thing that has been done is the setting of a root pwd for database access.

Hope that you have an idea on how to solve this.