Feb 27, 2013 Posted by newlife007 on Feb 27, 2013 in Linux | 2 comments

How To Change the default MySQL data directory on RHEL 6

You’ve been using MySQL for sometime now and the database has been growing and you are at the point where it is time to move to another location or to newly added storage that is in a different location.

Stopping the MySQL server

# service mysqld stop

Create a new data diretory and move the content from the old one
Creating a new data directory

# mkdir /app/mysql/

# chown mysql:mysql /app/mysql

Moving the original data files

# mv /var/lib/mysql/* /app/mysql/

Correct the MySQL configuration file

Edit the /etc/my.cnf file.

# vi /etc/mysql/my.cnf

Change

datadir=/var/lib/mysql

to

datadir=/app/mysql

and

socket=/var/lib/mysql/mysql.sock

to

socket=/app/mysql/mysql.sock

and save the file.
If you are using SELinux, adjust parameters to accept the change

Should the following command output “Permissive” or “Disabled” then you may skip the details for SELinux.

# getenforce

Run the semanage command to add a context mapping for /app/mysql.

# semanage fcontext -a -t mysqld_db_t "/app/mysql(/.*)?"

Now use the restorecon command to apply this context mapping to the running system.

# restorecon -Rv /app/mysql

Starting the MySQL server

# service mysqld start

Verifying access and connectivity

$ mysql -u root -p

mysql> show databases;

If this is working, you’re up and running. It is possible you could get a message that says

Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

then add the following to your /etc/my.cnf

[client]
 socket = /app/mysql/mysql.sock

Optionally you can just use

$ mysql -u root -p --protocol tcp

You have successfully moved your MySQL database.

2 Responses to “How To Change the default MySQL data directory on RHEL 6”

1. Sajith says:

   October 4, 2014 at 2:15 am

   I tried to do the same in CENTOS 6.4 and https://www.centos.org/forums/viewtopic.php?t=6732 this link worked perfectly!

   Reply
2. ryan says:

   April 2, 2015 at 8:43 pm

   the last section
   
   Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’
   then add the following to your /etc/my.cnf
   [client]
   socket = /app/mysql/mysql.sock
   Optionally you can just use
   $ mysql -u root -p –protocol tcp
   You have successfully moved your MySQL database.
   

   is really helpful and most of the post online does not contain this. Thank you.

   Reply