修改mysql数据文件存放路径

修改mysql数据文件存放路径

[root@ecs-Hankey-0010 sbin]# df -h /var/lib/mysql/
Filesystem Size Used Avail Use% Mounted on
/dev/vda2 39G 4.7G 32G 13% /

[root@ecs-Hankey-0010 ~]# mysql -uroot -p

mysql> show global variables like "%datadir%";
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql>

systemctl stop mysqld
迁徙步骤
cp -a 保留原文件属性的前提下复制文件

cp -a /var/lib/mysql /data/mysql/

3、修改配置文件my.cnf
备份my.cnf

# cp /etc/my.cnf /etc/my.cnfbak
编辑配置文件

vi /etc/my.cnf

修改如下几项（ #为了安全起见，你可以把原来的注释掉，然后重新加入一行，改成现在的目录，也会为了快速回滚。）

修改配置vi /etc/my.cnf

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

 

ln -s /data/mysql/mysql/mysql.sock /var/lib/mysql/mysql.sock

如果要删除软链接的话
使用：记得/var/lib/mysql/mysql.sock后千万不要有斜杆“/”
rm -rf /var/lib/mysql/mysql.sock

 

systemctl stop mysqld
systemctl start mysqld


[root@ecs-Hankey-0010 mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2020-03-23 14:30:13 CST; 1min 32s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 24464 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 24488 (mysqld)
Status: "Server is operational"
CGroup: /system.slice/mysqld.service
└─24488 /usr/sbin/mysqld

Mar 23 14:30:13 ecs-Hankey-0010 systemd[1]: Starting MySQL Server...
Mar 23 14:30:13 ecs-Hankey-0010 systemd[1]: Started MySQL Server.
[root@ecs-Hankey-0010 mysql]#

[root@ecs-Hankey-0010 mysql]# mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

解决：
ln -s /data/mysql/mysql/mysql.sock /var/lib/mysql/mysql.sock

[root@ecs-Hankey-0010 mysql]# ll /var/lib/mysql/mysql.sock
lrwxrwxrwx 1 root root 28 Mar 23 14:46 /var/lib/mysql/mysql.sock -> /data/mysql/mysql/mysql.sock

[root@ecs-Hankey-0010 mysql]# mysql -uroot -p
Enter password: Hankey12#$

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

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

mysql>