Change Mysql database datadir path – Problems and Solutions


By default when you install Mysql on a Linux System or Server it takes the default Path that is /var/lib/mysql/. It is fine as it is but you might run into memory problems on the Partition to which the path belongs and hence the need of changing the datadir path arises. It seems to be a easy three step procedure to change the datadir path to another partition.

Following are the Steps:

Step 1. Stop mysql : service mysqld stop OR /etc/init.d/mysqld stop OR locate mysqld and use the stop option.

Step 2. Copy /var/lib/mysql to another path you desire preserving the permissions for the mysql user: cp -rp /var/lib/mysql /opt/mydrive/. Confirm that the directory /opt/mydrive/mysql is owned by mysql:mysql or use the chown -R mysql:mysql /opt/mydrive/mysql to change the ownership. The ownership point is really crucial here.

Step 3. Change the datadir path in /etc/my.cnf to

datadir=/opt/mydrive/mysql

and start the mysql server by using start option in the Step 1 .

While in many cases the above 3 step procedure works great without any problems. But in some cases there are problems. I would like to discuss one of the problem and its solution here due to which the mysql server wont startup after changing the datadir path.

When you change the datadir path and to start the mysql sever it complains cannot create test… bla.. bla.. into the directory /opt/mydrive/mysql and will show some Permission erros. First of of in this case check that you used the step 2 explained above to copy the default datadir if not rectify it. If it still gives errors then the solution i think of is disabling SELinux. Following are the Steps on RHEL 6 server to disable SELinux:

To disable SELinux, configure SELINUX=disabled in /etc/selinux/config:

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#       enforcing - SELinux security policy is enforced.
#       permissive - SELinux prints warnings instead of enforcing.
#       disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
#       targeted - Targeted processes are protected,
#       mls - Multi Level Security protection.
SELINUXTYPE=targeted

Reboot your system. After reboot, confirm that the getenforce command returns Disabled:

$ /usr/sbin/getenforce
Disabled

Thats it start your mysql server, it should start up without problems.

Advertisements

About Dominic

J for JAVA more about me : http://about.me/dominicdsouza
This entry was posted in Thechy Stuff. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s