Can't move the MYSQL database files

Installing, Configuring, Troubleshooting server daemons such as Web and Mail

Can't move the MYSQL database files

Postby davecgs » 2009/11/08 17:12:49

Hey all,

Here is my query (ya pun intended)
Anyway, I just installed mysql and need to move the databases to a new location. I say 'no problem'
I shutdown mysql (service mysqld stop)
I configure my.cnf to point to the new location, which in my case is: /mnt/data/mysql. I know not very original naming.
I do the old chown -R mysql:mysql /mnt/datal/mysql AND I copy all the files over (cp -R /var/lib/mysql /mnt/cgsvol/mysql)
chmod 777 /mnt/data/mysql (I know that's overkill but I was getting frustrated)
I ensure the chown worked (ls -a -l) and the files are there from the old directory.
Now...would ya believe it, I can't load the daemon again.
I check the log (/var/log/mysqld.log) and it can't write any test file to that directory.
Of course I change /etc/my.cnf back to the old directory and everyone is happy -- well except me.

So of course, I'm thinking selinux. Ya...but selinux has always been bad breakfast food for me. I know enough to remove it, but I actually don't want to do that...I do like it but there must be SOMETHING I can do in selinux. Now I could be wrong...let me know. What do you think is the problem?
Thanks as always,



(log file exact data).
091107 23:22:21 mysqld started
091107 23:22:22 [Warning] option 'max_join_size': unsigned value 18446744073709$
091107 23:22:22 [Warning] option 'max_join_size': unsigned value 18446744073709$
091107 23:22:22 [Warning] Can't create test file /mnt/data/mysql/LittleJupiter.$
091107 23:22:22 [Warning] Can't create test file /mnt/data/mysql/LittleJupiter.$
^G/usr/libexec/mysqld: Can't change dir to '/mnt/data/mysql/' (Errcode: 13)
091107 23:22:22 [ERROR] Aborting
davecgs
 
Posts: 46
Joined: 2009/03/29 16:01:58

Can't move the MYSQL database files

Postby pschaff » 2009/11/09 14:36:30

Perhaps a Wiki search for "selinux"? Let's see...
http://wiki.centos.org/HowTos/SELinux
http://wiki.centos.org/TipsAndTricks/SelinuxBooleans

The following google may be useful as well:
Code: Select all
selinux mysql site:centos.org
pschaff
Retired Moderator
 
Posts: 18277
Joined: 2006/12/13 20:15:34
Location: Tidewater, Virginia, North America

Re: Can't move the MYSQL database files

Postby davecgs » 2009/11/10 18:19:41

Thank you. That was helpful. Here are the details for people to use.

How to change selinux when moving the default location (/var/lib/mysql).

In the default directory (/var/lib/mysql)
Run ls -lZ /var/lib/mysql
(ls is the selinux list function to detail the labels for each object.

This is the default directory and will list these objects.
drwx------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 mysql

This shows that mysqld_db_t is the default selinux context for the directory. Naturally when you move to a new location that directory context will be different. You have to add that to the object list for selinux to make it work.

The new directory I created was /mnt/data/sqldata

When I do a ls -LZ in the new location.
drwxr-xr-x. mysql mysql unconfined_u:object_r:usr_t:s0 sqldata

ug...not a mysql directory label. Have to do something about that!

Type:
semanage fcontext -a -t mysqld_db_t "/mnt/data/sqldata(/.*)?"

This adds an entry to the selinux context file.

/etc/selinux/targeted/contexts/files/file_contexts.local
This is where the file is located. In it you'll see

/mnt/data/sqldata(/.*)? system_u:object_r:mysqld_db_t:s0

But...to write it to the disk, you need an additional command.

restorecon -R -v /mnt/data/sqldata

Now when you do a ls -Z you'll see the new labels and be able to start up mysql and have it work.

ls -lZ /opt
drwxr-xr-x. mysql mysql system_u:object_r:mysqld_db_t:s0 mysql

Ya!
davecgs
 
Posts: 46
Joined: 2009/03/29 16:01:58

Re: Can't move the MYSQL database files

Postby pschaff » 2009/11/10 19:44:40

Thank you for posting the detailed solution!
pschaff
Retired Moderator
 
Posts: 18277
Joined: 2006/12/13 20:15:34
Location: Tidewater, Virginia, North America

Re: Can't move the MYSQL database files

Postby gjs68 » 2012/03/25 13:40:41

very very helpful indeed! thanks. stuck with the same issue trying moving. (figured SElinux may interfere, but those helpful error message....)

added to this two things:

the HowTo page has similar good explanation:
http://wiki.centos.org/HowTos/SELinux#h ... 839067db71

on CentOS (minimal install) you might be faced with another challenge, semanage missing.

yum -y install policycoreutils-python
source: http://www.cyberciti.biz/faq/redhat-ins ... mmand-rpm/
gjs68
 
Posts: 2
Joined: 2011/12/06 15:04:17


Return to CentOS 5 - Server Support

Who is online

Users browsing this forum: No registered users and 1 guest