更改mysql数据保存路径_修改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>