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
|
1 |
# service mysqld stop |
Create a new data diretory and move the content from the old one
Creating a new data directory
|
1 |
# mkdir /app/mysql/ |
|
1 |
# chown mysql:mysql /app/mysql |
Moving the original data files
|
1 |
# mv /var/lib/mysql/* /app/mysql/ |
Correct the MySQL configuration file
Edit the /etc/my.cnf file.
|
1 |
# vi /etc/mysql/my.cnf |
Change
|
1 |
datadir=/var/lib/mysql |
to
|
1 |
datadir=/app/mysql |
and
|
1 |
socket=/var/lib/mysql/mysql.sock |
to
|
1 |
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.
|
1 |
# getenforce |
Run the semanage command to add a context mapping for /app/mysql.
|
1 |
# semanage fcontext -a -t mysqld_db_t "/app/mysql(/.*)?" |
Now use the restorecon command to apply this context mapping to the running system.
|
1 |
# restorecon -Rv /app/mysql |
Starting the MySQL server
|
1 |
# service mysqld start |
Verifying access and connectivity
|
1 |
$ mysql -u root -p |
|
1 |
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
|
1 2 |
[client] socket = /app/mysql/mysql.sock |
Optionally you can just use
|
1 |
$ 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.