MySQL 5.1: change datadir variable value in the.cnf

linuxMySQLmysql-5.1performance

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?

Best 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.
Related Question