MySQL修改datadir目录

更改MySQL的数据目录由/usr/local/mysql/data/更换至/data/mysql/

第一种方法修改my.cnf

1）关闭MySQL

[root@test2 ~]# service mysql stop
Shutting down MySQL..................                      [  OK  ]

2）修改/etc/my.cnf的datadir和socket指向新的目录：

datadir                 = /data/mysql  
socket                  = /data/mysql/mysql.sock

3）拷贝数据文件

mkdir -p /data/mysql  
chown -R mysql:mysql /data/mysql
cp -a /usr/local/mysql/data/* /data/mysql/

4）启动MySQL

[root@test2 ~]# service mysql start
Starting MySQL.........                                    [  OK  ]

第二种方法，利用软连接的方法

1）关闭MySQL

[root@test2 ~]# service mysql stop
Shutting down MySQL..................                      [  OK  ]

2）拷贝数据文件

mkdir -p /mysql  
chown -R mysql:mysql /mysql
cp -a /data/mysql/* /mysql/

3）添加目录软连接

ln -s /mysql /data/mysql

将/data/mysql软连接指向目录/mysql

ln说明ln -s source dest

4）更改软连接权限

chown -R mysql:mysql /data/mysql

测试了一下，不修改软连接权限，也可以的，但保险起见还是修改权限

5）启动MySQL

[root@test2 ~]# service mysql start
Starting MySQL.........                                    [  OK  ]

测试

mysql> show variables like '%datadir%';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| datadir       | /data/mysql/ |
+---------------+--------------+
1 row in set (0.01 sec)

mysql> create database hch;
Query OK, 1 row affected (0.10 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| OMServer           |
| ceshi              |
| hch                |
| hchtest            |
| hchtest1           |
| mysql              |
| percona            |
| performance_schema |
| rep_test           |
| repldb             |
| repldb1            |
| test               |
| wna                |
+--------------------+
14 rows in set (0.00 sec)

mysql> use hch;
Database changed

mysql> create table t1(id int);
Query OK, 0 rows affected (0.15 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.08 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

参考

Mysql 数据目录的迁移方法 - CSDN博客
http://blog.csdn.net/donglynn/article/details/49491447

centos下mysql数据迁移方法 - 勤奋的雪雪大人 - 博客园
https://www.cnblogs.com/qfdxxdr/p/7066165.html

mysql数据库datadir路径更改记录 - CSDN博客
http://blog.csdn.net/xizaihui/article/details/52953379

将MySQL默认datadir修改为自己想要的路径_数据库技术_Linux公社-Linux系统门户网站
http://www.linuxidc.com/Linux/2013-07/88024.htm