CentOS 6.4 - help with MySQL on another drive

[jr3151006]

Hi,

I have a fresh install of CentOS 6.4, so I followed this post "http://dragkh.wordpress.com/2013/01/16/install-mysql-at-non-default-data-directory-on-different-drive-on-centos6-3rhel-6/#comment-421" but trying to create a database I realized that MySQL was still using its default path '/var/lib/mysql'; so I stoped the service, changed the '/etc/my.cnf' but now I´m not able to connect to it since shows a msg as:


[code]
[root@hostweb01 mysql]# mysql -r root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

[root@hostweb01 mysql]# mysql -r root -p -h 127.0.0.1
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[root@hostweb01 mysql]# mysql -r root -p --host 127.0.0.1
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[/code]

>>>> Pls, any help is appreciated in a way to do the MySQL to use another drive (I created a LVM volume with 3 disks).

tks,

Renato P

[TrevorH]

I haven't read the article you are following but changing the default mysql data directory will get you into issues with selinux. The best thing to do is to

Create a new LVM Logical Volume of the required size
Format it with your intended filesystem
Stop mysql
Move /var/lib/mysql to /var/lib/oldmmysql
Create a new /var/lib/mysql directory
Add a line to /etc/fstab to mount your new LV on /var/lib/mysql
Run `mount -a` to mount the new /var/lib/mysql
Set the permissions on the newly mounted filesystem to the same as on /var/lib/oldmysql
Run `restorecon -r /var/lib/mysql`
Copy the data from /var/lib/oldmysql to /var/lib/mysql
Restart the mysql daemon

Once you are sure it all works as intended, remove /var/lib/oldmysql.

[jr3151006]

The article say to disable selinux and I already did.

* I already have LVM working fine since I defined it during install/disk partition process.

[jr3151006]

I think that the problem is related with symbolic links and permissions.

[code]
cd /var/lib/
mv mysql mysql.old
ln -s /mysql/mysql /var/lib/
chown -R mysql:mysql /mysql/mysql
service mysqld start
mysql -u root -p --host 127.0.0.1
[/code]

I´ll test again and post a complete step-by-step.

[jr3151006]

As our slang here in Brazil: "Water soft, rock hard, too many knocks until pass through"
#######################################################
Now its working!!!! I can´t believe...



#disable selinx
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

#restart
reboot

#Stopping the Default Install/Instance
service mysqld stop

#Clear Current Config
rm /etc/my.cnf

#Uninstal the Default Install/Instance
yum remove mysql mysql-server -y

#Clear Current Datadir
test -d /var/lib/mysql/ && rm -rf /var/lib/mysql/

#Clear the 'New' Datadir
test -d /mysql/mysql/ && rm -rf /mysql/mysql/

#Install it again
yum install mysql mysql-server -y

#Check the service status
service mysqld status

#Start it - just to create a first/default structure
service mysqld start

#Check the service status
service mysqld status

#Interrupt the current MySQL server installation
service mysqld stop

#Ensure that you don´t have anymore instance/service running
ps axu | grep mysql

#Move the mysql data directory to '/mysql' partition and create the symbolic link
test -d /var/lib/mysql/ && mv /var/lib/mysql/ /mysql/ && ln -s /mysql/mysql /var/lib/

#Check symbolic link and the real path
ls -lrth /var/lib/ | grep mysql

#Set permission on new Datadir
chown -R mysql:mysql /mysql/mysql

#Start it
service mysqld start

#Try to connect (keep in mind that the default install of MySQL doesn´t set a 'pwd' for 'root' user and then you should connect with 'blank password'
mysql -u root -p --host 127.0.0.1

#Once connected to MySQL, create a new db just to test if it´s working and where MySQL will create folder/file structure
create database DBTesteNew;
exit

#Check if the new db is on the 'new datadir'
ls /mysql/mysql

#Make sure the mysqld is set to start on boot time
chkconfig mysqld on

#restart
reboot

* Kee in mind that since you have changed the symbolic link, you dont need change the variable 'datadir' inside '/etc/my.cnf'.
* a tip: for security reason, you should enable SElinux again

[TrevorH]

Disabling selinux is never the correct answer.

[gssajith87]

This is perfect! Was so frustrated referring too many links and nothing worked perfectly as this did!

[jklapas]

From what I ve read searching lots of articles this is so far the only succesfull config and has to do with the disabling of Selinux.
I ve succeded in moving the datarir (create test db) but when i ve tried to enforce Selinux back i got:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
So i disabled Selinux again and I m looking it up now.

[TrevorH]

You need to change the selinux context on the new data directory location. Use chcon or semanage/restorecon to do that.

[jklapas]

Thanx TrevorH, you ve been helpful.

So i looked it up and Concerning the enabling Selinux back again, I ve followed:
WARNING: Bellow procedure is provided after successfully followed above config (move datadir, reastablish owner ...)
#install semanage (In Centos 7 minimal is not installed)
yum provides /usr/sbin/semanage
yum -y install policycoreutils-python

# need to stop mysql
systemctl stop mysqld

# edit /etc/my.conf to redirect socket to new path
# in my case this is /DATABASE/mysql so
vi /etc/my.conf
# and replace , socket=/var/lib/mysql/mysql.soc with
socket=/DATABASE/mysql/mysql.soc

getenforce
# Concerning my datadir is in /DATABASE/mysql
semanage fcontext -a -t mysqld_db_t "/DATABASE/mysql(/.*)?"
restorecon -Rv /DATABASE/mysql
systemctl start mysqld