Mysql and moving the data directory.

RimBlock · 16 Feb 2012 at 16:01

Hi,

I am building a mysql server and have a second set of disks for the database data area. The disks are partitioned and mounted on the fs as /database.

I then followed one of the many guides on moving the data;

Code:

service mysqld stop
cp -rv /var/lib/mysql /database
chown -R mysql:mysql/database
vi /etc/my.cnf
paste;

[mysqld]
 user = mysql
 datadir = /database/mysql
 port = 3306
 socket = /var/lib/mysql/mysql.sock
 pid-file = /var/run/mysqld/mysqld.pid
# 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

:wq (save and exit)

service mysqld start

Now when trying to start I get the following in the log files;

120217 06:52:08 mysqld_safe Starting mysqld daemon with databases from /database/mysql
120217 6:52:08 [Warning] Can't create test file /database/mysql/localhost.lower-test
120217 6:52:08 [Warning] Can't create test file /database/mysql/localhost.lower-test
/usr/libexec/mysqld: Can't change dir to '/database/mysql/' (Errcode: 13)
120217 6:52:08 [ERROR] Aborting
120217 6:52:08 [Note] /usr/libexec/mysqld: Shutdown complete
120217 06:52:08 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

I suspect that maybe selinux is causing issues again but am open to other suggestions.

What is the best way to resolve ?

Thanks
RB

RimBlock · 16 Feb 2012 at 16:08

Also, running the following also has no effect.

Code:

chcon -R system_u:object_r:mysqld_db_t /database/

Djwayne · 16 Feb 2012 at 17:11

If you are unsure if selinux is causing your problem disable it as a test if it still doesnt work you have eliminted it from the equation.

EDIT : found this that looks pretty similar

http://askubuntu.com/questions/75059/mysqld-gives-errcode13-because-it-cant-create-test-files

RimBlock · 17 Feb 2012 at 09:30

Thanks for that. I will give it a go tonight.

I tried starting mysql using the

service mysql start

But got the 'unrecognized service' error (was running it as root).

Will try again as mysql and see if that works.

RB

unp|dissy · 17 Feb 2012 at 12:13

RimBlock said:
Thanks for that. I will give it a go tonight.

I tried starting mysql using the

service mysql start

But got the 'unrecognized service' error (was running it as root).

Will try again as mysql and see if that works.

RB

mysqld?

./etc/init.d/mysql* start

RimBlock · 20 Feb 2012 at 02:54

unp|dissy said:
mysqld?

./etc/init.d/mysql* start

The only file in /etc/init.d matching that wildcard search is mysqld

RB

RimBlock · 20 Feb 2012 at 03:02

Ok,

Did lots of testing over the weekend and still not working.

Have tried the following...

Setting SELinux to permissive and checking the /var/log/audit/audit.log. A number of SELinux messages came up.

Installed and used audit2why to generate custom rules and then load them in tot he kernal as modules. Repeated until no messages appeared in the audit log. Still mysql daemon will not start. Turned off SELinux completely and rebooted. Still would not start. Uninstalled and linked the /var/lib/mysql to the mount point for the second array then installed. Still will not start. Removed mysql and mysql-server and then mounted the second array on /var/lib/mysql and installed again. Still will not start up.

If I install mysql and mysql server to the standard location with no links or custom mounts etc it works fine. It is getting ti to startup with the data area being somewhere else that seems to be causing a big problem.

Any other suggestions most welcome. I really didn't expect there to be such an issue with this.

RB

AndrewP · 20 Feb 2012 at 13:41

Still sounds like a permissions issue to me. I would use cp -pr to preserve file mode, as I'm sure some of the directories and database files need group writable permissions.

Djwayne · 20 Feb 2012 at 15:25

This should point you in the right direction regarding the location of your data directory being different to the default

http://dev.mysql.com/doc/refman/5.1/en/starting-server.html

RimBlock · 21 Feb 2012 at 01:37

Thanks for the suggestions guys.

I will give the cp a go and see how that works out.

I will also take a good read through the troubleshooting guide. It looks very helpful.

Probably also worth noting that the database engine is InnoDB.

I have currently resorted to clearing Mysql from the server and mounting the drives on the /var/lib/mysql directory, where the datafiles are usually created, and then intend to install Mysql and see if it installs correctly. I would much rather have them in a separate clearly visible mount point but this is more of a proof of concept at the moment to make sure I can at least get something working.

Of course, it never rains but pours.... It now seems that the Intel 82574L nic chipset is having issues with CentOS 6.2. The Nic based on the 82579LMseems fine so now I have to 'down' one of the nics or I keep getting 'network unreachable' some times. That is for another thread though which I should go and start now.

Thanks for the suggestions, I shall report back with any new error messages and results.

RB

RimBlock · 24 Feb 2012 at 03:04

Small update on this.

I mounted the array on /vat/lib/mysql and made sure owner and group were mysql and it installed and started.

I then turned on SELinux again and it ran fine (audit2allow must have worked I guess).

Next step would be to remount the array on the mount point I want, edit the config file to point there and then see if mysql will start.

Some progress at last.

The problem with the network port only seems to come up if one or more of the ports is set to DHCP. Setting both to a static IP and they are fine. The people I am building these two servers for have static addresses so no real problem there. I shall of course make them aware and make a note in the documention I will be supplying.

RB