LinuxQuestions.org - Having troubles changing the database directory for mysql

- Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)

- - Having troubles changing the database directory for mysql (https://www.linuxquestions.org/questions/linux-newbie-8/having-troubles-changing-the-database-directory-for-mysql-4175435765/)

Having troubles changing the database directory for mysql

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.

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?

Quote:

Originally Posted by Obscurious (Post 4822906)

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

Code:

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

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...

Quote:

Originally Posted by chrism01 (Post 4822997)

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

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.

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

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.

Quote:

Originally Posted by chrism01 (Post 4823026)

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.

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