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
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.
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.