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.
I tried to do the same in CENTOS 6.4 and https://www.centos.org/forums/viewtopic.php?t=6732 this link worked perfectly!
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.
[root@localhost ~]# mysql -u root -p –protocol tcp
mysql: unknown variable ‘symbolic-links=0’
i got this error how fix this….?
After changing data directory, is it necessary to check MySQL listining port before restarting services?
If just the directory was changed and service restarted nothing to do.