Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Below is my system configuration:

OS: RHEL5
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?
share|improve this question
    
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 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 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.
share|improve this answer
    
Thanks @jynus. Have you ever tried to change the datadir by following above steps? –  ursitesion Jul 1 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 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 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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