We help IT Professionals succeed at work.

Questions on MySQL Concepts

dba2dba
dba2dba asked
on
Hello Experts,

I am new to MySQL and was going through the MySQL Concepts. I have few questions. I am working on MySQL 5.x on CentOS 5.7.

1) Does MySQL Server run under the privilege of any particular account
2) I installed it using YUM groupinstall. How is mysql typically installed. is it from YUM or is it from RPM's
3) I have gone through some articles for moving mysql databases to new directories which involves changing the data directory path in my.cnf. Is there a way in MySQL to move only a specific database to a new directory. If yes, how is this handled.

 Appreciate your help.

Thanks,
Comment
Watch Question

I can address the first question but not the second two.  Hopefully someone else will show up soon.

1. I believe that  mysqld server runs under the user mysql.  (That's what appears to be happening on two computers I checked, one an old RH and another Slackware).
Fixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
1. All processes run under some particular account.

2. Any install method is Ok as long as you don't mix them when you do updates.

3. I'm wondering why you want to move a database file.  You can't access the database files directly and have MySQL work properly.
CERTIFIED EXPERT
Commented:
1.  MySQL runs as user mysql, group mysql (usually uid 27, gid 27)

2.  If its installed via Yum, then it is installed using RPM, as yum only comes on RPM-enabled systems such as all the RedHat derivatives (RH, RHEL, Fedora, CentOS)  Using yum is perfect, because it satisfies all the dependencies, and puts the client libraries in the places that other applications which need to compile against them expect to find them.  I always recommend installing in this manner.  BTW, on Debian based distributions (Debian, Ubuntu) you can use "apt-get install mysql-server" to install binary packages, which is one other way of doing it without having to install from tarball of source or binary.

3.  There is a base $datadir that MySQL looks for for its databases - it doesn't care what it is as long as you specify it in the startup scripts, but you cannot move individual databases - you HAVE to move the entire directory full of databases (usually /var/lib/mysql) and keep the structure intact, because the DB internals are stored in a subdirectory (usually /var/lib/mysql/mysql).   If you want to put it on a partition with more space, do something like my commands below, which will leave MySQL _thinking_ that its still installed in /var/lib/mysql, but actually just follows a symbolic link.


#>  /etc/init.d/mysqld stop
#>  cd /var/lib
#>  mv /var/lib/mysql /other/partition/mysql
#>  ln -s /other/partition/mysql mysql
#>  /etc/init.d/mysqld start

Open in new window

Author

Commented:
Thanks for your responses.

Can you please guide me how to find out under which user the mysql server is running and how to change it to a different user (if at all we can).

Regarding moving the database - If we have a need to move a database XYZ to a different location due to space issues. Can we mount a new drive to the file system and move just the XYZ database leaving all other database in the default /var/lib/mysql database.

These questions arise due to my mssql background  :)

Thanks for your time.

Author

Commented:
Thanks for your response xterm. I did not see your post before replying.

As mentioned in my previous post, can you help me find out under which user the mysql server is running and how to change it to a different user (if at all we can).

Thanks,
CERTIFIED EXPERT

Commented:
You can look at who owns the database directory:

[xterm@home mysql]$ ls -ld /var/lib/mysql
drwxr-xr-x. 2 mysql mysql 4096 Jul 12 15:45 /var/lib/mysql

As you can see, user mysql, group mysql.  Also, if you run "top", and then sort by memory (hit capital "M") you can see the user there too.

You can actually use the symlink trick to move an individual database (see below) but MySQL will still think the DB is in /var/lib/mysql


#>  /etc/init.d/mysqld stop
#>  cd /var/lib/mysql
#>  mv dbtomove /other/partition/mysql
#>  ln -s /other/partition/mysql/dbtomove dbtomove
#>  /etc/init.d/mysqld start

Open in new window

Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
"These questions arise due to my mssql background  :) "  I thought so.  Note there are some serious differences between MySQL and MS SQL, number one being the method of locating the files.  Where a MS SQL database is all in one file (plus a log file), MySQL dedicates a directory to a database and puts a bunch of files in it that are for that database.

Author

Commented:
I see there are a lot of differences between MSSQL and MySQL. I have got mysql installed on a test machine and is going through it. Thanks to everyone for your time.
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
One thing I forgot to mention.  Database and table names are used in the file names in MySQL so on Linux, they are case sensitive.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.