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
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
# 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
$ /usr/sbin/getenforce Disabled
Thats it start your mysql server, it should start up without problems.