Having troubles changing the database directory for mysql

mmorton89 · 11-05-2012, 01:31 PM

Hello, I have been trying to change the directory of my databases for mysql with no success.

I have changed the directories in /etc/my.cnf to the new directory.

Code:

[mysqld]
# datadir=/var/lib/mysql
# socket=/var/lib/mysql/mysql.sock
datadir=/home/mc/mysql
socket=/home/mc/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I changed the owner and permissions for the folder and everything in the folder.

Code:

[root@localhost ~]# ls -l /home/mc/mysql
total 266272
drwx------. 2 mysql mysql      4096 Nov  6 01:32 creativecontrol
-rwx------. 1 mysql mysql 262144000 Nov  6 01:32 ibdata1
-rwx------. 1 mysql mysql   5242880 Nov  6 01:32 ib_logfile0
-rwx------. 1 mysql mysql   5242880 Nov  6 01:32 ib_logfile1
drwx------. 2 mysql mysql      4096 Nov  6 01:32 iconomy
drwx------. 2 mysql mysql      4096 Nov  6 01:32 jobs
drwx------. 2 mysql mysql      4096 Nov  6 01:32 logblock
drwx------. 2 mysql mysql      4096 Nov  6 01:32 lwc
drwx------. 2 mysql mysql      4096 Nov  6 01:32 mysql
drwx------. 2 mysql mysql      4096 Nov  6 01:32 towny

Yet when I try to start the mysqld service I get this in the mysql.log

Code:

121106 02:10:56 mysqld_safe Starting mysqld daemon with databases from /home/mc/mysql
121106  2:10:56 [Warning] Can't create test file /home/mc/mysql/localhost.lower-test
121106  2:10:56 [Warning] Can't create test file /home/mc/mysql/localhost.lower-test
121106  2:10:56  InnoDB: Initializing buffer pool, size = 8.0M
121106  2:10:56  InnoDB: Completed initialization of buffer pool
121106  2:10:56  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
121106 02:10:56 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

I am clueless to why it does not have permissions in the directory? I gave ownership and 700 permissions on that directory.

Obscurious · 11-05-2012, 03:22 PM

From the logfile it really looks to be a permission problem. My guess is that the mysql user can't write to some file or directory in /home/mc/mysql. Double check all your permissions, even hidden files and make sure to chmod recursively.

1) What operating system are you running? This is important.
2) Have you migrated the pre-existing database data?

mmorton89 · 11-05-2012, 04:31 PM

Quote:

Originally Posted by Obscurious

From the logfile it really looks to be a permission problem. My guess is that the mysql user can't write to some file or directory in /home/mc/mysql. Double check all your permissions, even hidden files and make sure to chmod recursively.

1) What operating system are you running? This is important.
2) Have you migrated the pre-existing database data?

1) CentOS 6
2) Yes I copied the whole directory with

Code:

cp -R /var/lib/mysql/ /home/mc/mysql/

chrism01 · 11-05-2012, 05:37 PM

Looks lie SELinux

Code:

cd /var/lib/mysql
$ ls -lZ
drwx------. mysql mysql system_u:object_r:mysqld_db_t:s0 console
drwx------. mysql mysql system_u:object_r:mysqld_db_t:s0 console_auth
drwx------. mysql mysql system_u:object_r:mysqld_db_t:s0 console_inventory_service
-rw-rw----. mysql mysql system_u:object_r:mysqld_db_t:s0 ibdata1
-rw-rw----. mysql mysql system_u:object_r:mysqld_db_t:s0 ib_logfile0
-rw-rw----. mysql mysql system_u:object_r:mysqld_db_t:s0 ib_logfile1
drwx------. mysql mysql system_u:object_r:mysqld_db_t:s0 mysql
srwxrwxrwx. mysql mysql system_u:object_r:mysqld_var_run_t:s0 mysql.sock
drwx------. mysql mysql system_u:object_r:mysqld_db_t:s0 test

If you use cp, the file inherit the SELinux setting of the new parent dir. mv would have kept the original settings.
In any case, why would you want to do this; its only going to cause confusion/issues...

mmorton89 · 11-05-2012, 05:50 PM

Quote:

Originally Posted by chrism01

Looks lie SELinux

Code:

cd /var/lib/mysql
$ ls -lZ
drwx------. mysql mysql system_u:object_r:mysqld_db_t:s0 console
drwx------. mysql mysql system_u:object_r:mysqld_db_t:s0 console_auth
drwx------. mysql mysql system_u:object_r:mysqld_db_t:s0 console_inventory_service
-rw-rw----. mysql mysql system_u:object_r:mysqld_db_t:s0 ibdata1
-rw-rw----. mysql mysql system_u:object_r:mysqld_db_t:s0 ib_logfile0
-rw-rw----. mysql mysql system_u:object_r:mysqld_db_t:s0 ib_logfile1
drwx------. mysql mysql system_u:object_r:mysqld_db_t:s0 mysql
srwxrwxrwx. mysql mysql system_u:object_r:mysqld_var_run_t:s0 mysql.sock
drwx------. mysql mysql system_u:object_r:mysqld_db_t:s0 test

If you use cp, the file inherit the SELinux setting of the new parent dir. mv would have kept the original settings.
In any case, why would you want to do this; its only going to cause confusion/issues...

well my /var directory in on my first hard drive which is only a 64 GB SSD and I want mysql to save its databases on my 500 GB HDD.

This seemed like the easiest way to get the databases on my HDD since my /home directory is on my 2nd hard drive.

Also I just assumed, since I am running a game server, that if I put mysql on my second hard drive it would help because the game server is running off the SSD and using the HDD for mysql would help reduce the I/O usage on the SSD.

michaelk · 11-05-2012, 06:31 PM

mysql is being started as the user mysql. I would guess that even though mysql might have permissions to /home/mc/mysql it still does not have permissions to access /home/mc. I would not suggest changing your users home directory permissions.

It is possible to run mysqld as any regular user. Be sure to change the msqld data permissions appropriately for whatever user you choose.

http://dev.mysql.com/doc/refman/5.5/...ysql-user.html

chrism01 · 11-05-2012, 06:37 PM

I'd move the whole of /var to go on the HDD in that case; that's where the logs for everything go. Its gets a lot of i/o usage.

mmorton89 · 11-06-2012, 05:33 AM

Quote:

Originally Posted by chrism01

I'd move the whole of /var to go on the HDD in that case; that's where the logs for everything go. Its gets a lot of i/o usage.

How would I do that?

I tried to look up how to move directories to another hard drive but I could not figure out how to, so I resorted to this. That would make things a whole lot simpler.

mmorton89 · 11-06-2012, 11:25 AM

Should I make a new thread? For asking how to move the /var directory?