MySQL 更改数据文件路径

• 数据库全备份：

[root@onccshavm019 ~]# mysqldump -uroot --quick --force --all-databases --events > /tmp/mysqldump-2017-09-13.sql

• 查看原来的数据文件路径：

[root@onccshavm019 ~]# mysqladmin -uroot -p variables | grep datadir
Enter password: 
| datadir                | /var/lib/mysql/|
[root@onccshavm019 ~]# 

• 停止MySQL服务：

[root@onccshavm019 ~]# systemctl stop mysql.service 
或者
[root@onccshavm019 ~]# service mysqld stop 

• 保险起见，对MySQL数据文件目录备份一次（这一步不是必须的）：

[root@onccshavm019 data]# tar cvf mysql.tar /var/lib/mysql

• 将MySQL数据文件目录移动到新位置：

[root@onccshavm019 data]# mv /var/lib/mysql /data/

• 修改MySQL配置文件

[root@onccshavm019 data]# find / -name my.cnf
/usr/share/mysql-test/suite/rpl/my.cnf
/usr/share/mysql-test/suite/federated/my.cnf
/etc/my.cnf
[root@onccshavm019 data]# vi /etc/my.cnf
[client]
#password       = your_password
port            = 3306
#socket         = /var/lib/mysql/mysql.sock  
#将位置改为新位置
socket          = /data/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
#socket         = /var/lib/mysql/mysql.sock
#将位置改为新位置
socket          = /data/mysql/mysql.sock

• 修改MySQL启动脚本：

[root@onccshavm019 data]# vi /etc/init.d/mysql 
basedir=
datadir=/data/mysql/
#默认为空的，将新数据文件位置填写进来

• 启动MySQL：

[root@onccshavm019 data]# systemctl start mysql
Warning: mysql.service changed on disk. Run 'systemctl daemon-reload' to reload units.
#在CentOS下面可能有这个提示，需要按照提示执行下面这条命令
[root@onccshavm019 data]# systemctl daemon-reload
[root@onccshavm019 data]# systemctl restart mysql.service 

• 测试MySQL：

[root@onccshavm019 data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 5.5.56 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)

mysql> use mysql;