How to Change default MySQL Data Directory in Linux111

Instruction’s to Change Default MySQL Data Directory:

Follow the below steps to make all the changes. In some cases service name, default data directory or MySQL configuration file path change. So use all the command as per you system settings.

1. Stop MySQL

Before making any changes, first make sure to stop mysql service

# service mysqld stop

2. Change Data Directory

Now copy default MySQL data directory (/var/lib/mysql) to other location as per your requirement. Also set the required MySQL ownership on new directory location. As per below command, we are relocating data directory to /data/mysql.

# cp -rap /var/lib/mysql /data/mysql
# chown mysql.mysql /data/mysql

Now edit MySQL default configuration file /etc/my.cnf and update values of datadir and socketvariable.

Change From:
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

Change To: 
datadir=/data/mysql
socket=/data/mysql/mysql.sock

3. Start MySQL

After making all above changes. finally start MySQL service. Now it will use new data directory path

# service mysqld start

This article has been tested with CentOS 6.5 with MySQL 5.5.37. If you face any issue during MySQL startup check mysql log file /var/log/mysqld.log for any errors.

需要修改的文件：

1./etc/my.cnf

2./usr/bin/mysqld_safe

3./etc/init.d/mysqld

4./usr/lib64/mysql/mysql_config(/usr/lib/mysql/mysql_config)

这个不能少 ln -s /data/mysql/mysql.sock /var/lib/mysql/mysql.sock

setenforce 0 解决问题:Can't create test file /xxx/mysql/centos5.lower-test

错误解决方案：

1.在CentOS上，如果mysql是通过yum安装的，那么可能使用上面的方法不能完全凑效。

原因：mysql的配置文件有多处，除了更改/etc/my.cnf文件之外，还需要更改/usr/lib64/mysql/mysql_config(/usr/lib/mysql/mysql_config)

这个文件里面有一行“ldata='/var/lib/mysql'”和"socket=/var/lib/mysql/mysql.sock"，这里也需要改掉

 

2.另外，还有权限问题，查看/var/log/mysqld.log,发现Can't create test file /xxx/mysql/centos5.lower-test

这里是因为没有权限创建或读取文件。

解决办法就是使用setenforce 0 这个命令，让系统关闭权限校验，然后再运行命令 service mysqld start 发现OK了。

另外一个办法是restorecon -FRv /home/data/mysql，或者chcon -R -t mysqld_db_t /home/data/mysql