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/rpc4939.pid

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?
  • Search for my.cnf in one of the default locations - from root $/: sudo find . -name "my.cnf" and see if your picking up defaults from somewhere.
    – Vérace
    Jun 30, 2014 at 9:47
  • You really should look at upgrading. 5.1 is very dated. 5.6 has been out long enough to be production ready.
    – atxdba
    Jun 30, 2014 at 16:55

2 Answers 2

  • 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.
  • Thanks @jynus. Have you ever tried to change the datadir by following above steps?
    – ursitesion
    Jul 1, 2014 at 7:59
  • @ursitesion As a MySQL consultant, and having worked for Sun, Oracle and Percona during the past 5 years I can assure you that the above is correct way to move around the datadir (sauf any strange settings, filesystem problems, etc). I've done such a thing thousands of times. If all settings are by default, the only thing you need to change is the "datadir". Use the error log (or mysqld output) to check for potential errors. Please note that mysql datadir, unlike Oracle's is completely migrable and portable, even between platforms and OSs (except for paths and case sensitiveness).
    – jynus
    Jul 4, 2014 at 7:25
  • @ursitesion there are ways to migrate it in a hotter way, like Percona XtraBackup/Oracle Enterprise Backup, or rsyncing it twice (the first time while running). But if uptime is not a huge problem, the described one is the simplest way.
    – jynus
    Jul 4, 2014 at 7:28

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
  • Terrible suggestion. Figure out the permissions problem rather than weakening your security. Jan 16, 2015 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.