MySQL 5.1: change datadir variable value in my.cnf

Posted on

Question :

Below is my system configuration:

MySQL: 5.1.73
Default datadir=/var/lib/mysql/

But, the above directory has space crunch. So, I decided to change the path. I found /u000 directory has enough space. So, I created /lib/mysql/ under /u000 and assign permission (chmod) as 0777.
Then after, I changed the below values in mysql configuration file: /etc/my.cnf:

 innodb_data_home_dir = /u000/lib/mysql
 innodb_data_file_path = ibdata1:10M:autoextend
 innodb_log_group_home_dir = /u000/lib/mysql
 log_error = /u000/lib/mysql/rpc4939.err
 general_log_file = /u000/lib/mysql/rpc4939.log
 slow_query_log_file = /u000/lib/mysql/rpc4939-slow.log
 datadir = /u000/lib/mysql/
 pid_file = /u000/lib/mysql/

Then, tried to restart mysql service but it failed.
When, I changed the datadir as datadir = /var/lib/mysql/ it started working.

  • Can I know Why am I unable to change the datadir?
  • Is there any special way to change the datadir?
  • log_error value is always pointing to /var/lib/mysql/ instead /u000/lib/mysql/. How this variable can be changed?

Answer :

  • Changing the datadir requires the following steps: 1) Stopping the service completely mysqladmin shutdown / service mysql stop. 2) Changing the options on the config file for everything that points to the old location to the new one 3) moving the actual files and directories at the old datadir manually to the new location with cp/rsync (make sure that it has the right permissions in the end, normally owned by the mysql user) 4) Starting the server again
  • You can check that new values on my.cnf have effect by executing: mysqld --print-defaults which will confirm you that the options changed are actually applied and that there are no syntax errors on the file.

I had same problem in Centos 6.5 couple of months ago.
Make sure your mysql folder at new location has same ownerships and permissions.
Problematic thing for me was SELinux permissions. Didn’t get chance to find a way to stop SeLinux for mysql only. But I disabled my SeLinux for overall system using

Check if its Enabled with

selinuxenabled && echo enabled || echo disabled

Disable SeLinux & Reboot your system

sudo echo 0 > /selinux/enforce

Leave a Reply

Your email address will not be published. Required fields are marked *