Thursday, November 22, 2012

Change MySQL database directory

Why do I need to change MySQL database directory or location?

  1. Current disk is out of space
  2. Current partition is out of space
  3. There is a better filesystem to improve disk IO performance.
Steps to do this on Centos 6 with SELinux enforced.

Step 1: Stop MySQL server

Step 2: Create the new directory

A new directory can be created in the new disk or partition.

Step 3: Copy existing database to new location
 mv /var/lib/mysql/* /srv/mysql/

Step 4: Configure MySQL server
Edit /etc/my.cnf with following

datadir=
socket=

[client]
socket=

Save and close the file.

5. Configure SELinux to allow new database
View current settings

setstatus
ls -lZ /var/lib/mysql
ls -lZ

Edit settings with e.g. as /media/server/mysql

semanage fcontext -a -t mysqld_db_t "/media/server/mysql(/.*)?"
OR
chcon -R -t mysqld_db_t /media/server/mysql

restorecon -Rv /media/server/mysql

Check settings are in the config file

grep -i mysql /etc/selinux/targeted/contexts/files/file_contexts.local

6. Start up MySQL
I had to reboot the server and change selinux to permissive for all this to work.

No comments: