CentOS 6.4 - help with MySQL on another drive

Issues related to applications and software problems
Post Reply
jr3151006
Posts: 24
Joined: 2013/06/01 02:08:47
Contact:

CentOS 6.4 - help with MySQL on another drive

Post by jr3151006 » 2013/09/06 08:33:50

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

User avatar
TrevorH
Forum Moderator
Posts: 27175
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

CentOS 6.4 - help with MySQL on another drive

Post by TrevorH » 2013/09/06 08:47:09

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
Posts: 24
Joined: 2013/06/01 02:08:47
Contact:

Re: CentOS 6.4 - help with MySQL on another drive

Post by jr3151006 » 2013/09/06 09:09:52

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
Posts: 24
Joined: 2013/06/01 02:08:47
Contact:

Re: CentOS 6.4 - help with MySQL on another drive

Post by jr3151006 » 2013/09/06 09:37:37

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
Posts: 24
Joined: 2013/06/01 02:08:47
Contact:

[SOLVED] Re: CentOS 6.4 - help with MySQL on another drive

Post by jr3151006 » 2013/09/06 10:27:05

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

User avatar
TrevorH
Forum Moderator
Posts: 27175
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: [SOLVED] CentOS 6.4 - help with MySQL on another drive

Post by TrevorH » 2013/09/06 11:38:09

Disabling selinux is never the correct answer.

gssajith87
Posts: 1
Joined: 2014/09/15 02:48:48

Re: CentOS 6.4 - help with MySQL on another drive

Post by gssajith87 » 2014/10/04 02:02:28

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

jklapas
Posts: 2
Joined: 2015/03/15 07:17:50

Re: CentOS 6.4 - help with MySQL on another drive

Post by jklapas » 2015/03/15 07:36:16

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.
Don't force it, get a bigger hammer !

User avatar
TrevorH
Forum Moderator
Posts: 27175
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: CentOS 6.4 - help with MySQL on another drive

Post by TrevorH » 2015/03/15 12:16:21

You need to change the selinux context on the new data directory location. Use chcon or semanage/restorecon to do that.
CentOS 6 will die in November 2020 - migrate sooner rather than later!
CentOS 5 has been EOL for nearly 3 years and should no longer be used for anything!
Full time Geek, part time moderator. Use the FAQ Luke

jklapas
Posts: 2
Joined: 2015/03/15 07:17:50

Re: CentOS 6.4 - help with MySQL on another drive

Post by jklapas » 2015/03/16 10:35:22

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
Don't force it, get a bigger hammer !

Post Reply

Return to “CentOS 6 - Software Support”