mysql移动数据目录_mysql移动数据存放目录及问题

最新推荐文章于 2021-11-15 22:47:09 发布

沈蓁蓁

最新推荐文章于 2021-11-15 22:47:09 发布

阅读量190

点赞数

文章标签： mysql移动数据目录

本文链接：https://blog.csdn.net/weixin_42528780/article/details/113306330

版权

本文详细记录了将MySQL数据库从旧目录迁移到新目录的步骤，包括创建新目录、更改权限、停止MySQL服务、移动数据、修改配置文件、启动服务等。在迁移过程中遇到了通过socket连接数据库时的错误，原因是启动脚本中socket路径未更新。通过检查错误日志发现是InnoDB无法找到有效的表空间文件，最终通过修改my.cnf中的socket路径解决了问题。

摘要由CSDN通过智能技术生成

我计划更换数据库的存放目录，下面是我的操作步骤

1：首先创建新的数据存放路径并授予权限[root@pacteralinux mntsdc2formysql]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda1 29G 20G 7.5G 73% /

tmpfs 776M 0 776M 0% /dev/shm

/dev/sdb1 69G 4.0G 62G 6% /mnt/resource

/dev/sdc1 362G 53G 291G 16% /usr/local/Tomcat_Trans/webapps/infoSocketService/logs

/dev/sdc2 131G 2.2G 122G 2% /mnt/mntsdc2formysql

[root@pacteralinux mntsdc2formysql]# pwd

/mnt/mntsdc2formysql

[root@pacteralinux mntsdc2formysql]# mkdir mysqldata

[root@pacteralinux mntsdc2formysql]# ll

total 2050024

drwx------. 2 root root 16384 Dec 2 14:20 lost+found

drwxr-xr-x. 2 root root 4096 Dec 26 13:29 mysqldata

-rw-r--r--. 1 root root 2097152000 Dec 7 23:20 swapfile

[root@pacteralinux mntsdc2formysql]# cd mysqldata/

[root@pacteralinux mysqldata]# chown mysql:mysql .

[root@pacteralinux mysqldata]# cd ..

[root@pacteralinux mntsdc2formysql]# ll

total 2050024

drwx------. 2 root root 16384 Dec 2 14:20 lost+found

drwxr-xr-x. 2 mysql mysql 4096 Dec 26 13:29 mysqldata

-rw-r--r--. 1 root root 2097152000 Dec 7 23:20 swapfile

[root@pacteralinux mntsdc2formysql]# chmod 755 mysqldata/

[root@pacteralinux mntsdc2formysql]# ll

total 2050024

drwx------. 2 root root 16384 Dec 2 14:20 lost+found

drwxr-xr-x. 2 mysql mysql 4096 Dec 26 13:29 mysqldata

-rw-r--r--. 1 root root 2097152000 Dec 7 23:20 swapfile

2：停止mysql[root@pacteralinux mntsdc2formysql]# ps -ef|grep mysql

root 7451 7425 0 13:28 pts/1 00:00:00 grep mysql

root 56138 1 0 Dec24 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mnt/resource/mysqldate --pid-file=/mnt/resource/mysqldate/pacteralinux.pid

mysql 56391 56138 0 Dec24 ? 00:00:44 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mnt/resource/mysqldate --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mnt/resource/mysqldate/pacteralinux.err --pid-file=/mnt/resource/mysqldate/pacteralinux.pid --socket=/mnt/resource/mysqldate/mysql.sock --port=3306

[root@pacteralinux ~]# service mysqld stop

Shutting down MySQL..[ OK ]

[root@pacteralinux ~]# service mysqld stop

Shutting down MySQL..[ OK ]

[root@pacteralinux ~]# ps -ef|grep mysql

root 7505 7376 0 13:32 pts/0 00:00:00 grep mysql

3：移动目录

我现在数据库的存放路径为[root@pacteralinux ~]# cd /mnt/resource/mysqldate

[root@pacteralinux mysqldate]# ll

total 176676

-rw-rw----. 1 mysql mysql 56 Nov 25 17:17 auto.cnf

-rw-rw----. 1 mysql mysql 79691776 Dec 26 13:32 ibdata1

-rw-rw----. 1 mysql mysql 50331648 Dec 26 13:32 ib_logfile0

-rw-rw----. 1 mysql mysql 50331648 Nov 26 13:39 ib_logfile1

drwx------. 2 mysql mysql 4096 Nov 26 13:41 mysql

drwx------. 2 mysql mysql 20480 Nov 26 17:00 mysqldb

-rw-rw----. 1 mysql root 514836 Dec 26 13:32 pacteralinux.err

drwx------. 2 mysql mysql 4096 Nov 26 13:41 performance_schema

drwx------. 2 mysql mysql 4096 Nov 26 13:41 test

drwx------. 2 mysql mysql 4096 Dec 9 16:49 weixindemo

移动[root@pacteralinux resource]# mv mysqldate/ /mnt/mntsdc2formysql/mysqldata/

[root@pacteralinux resource]# ll

total 24

drwx------. 2 root root 16384 Nov 20 13:57 lost+found

drwxr-xr-x. 2 root root 4096 Dec 2 15:02 mysqldataper

drwxr-xr-x. 2 root root 4096 Dec 2 15:02 sdc3mnt

[root@pacteralinux resource]# pwd

/mnt/resource

[root@pacteralinux resource]# cd /mnt/mntsdc2formysql/

[root@pacteralinux mntsdc2formysql]# ll

total 2050024

drwx------. 2 root root 16384 Dec 2 14:20 lost+found

drwxr-xr-x. 3 mysql mysql 4096 Dec 26 13:42 mysqldata

-rw-r--r--. 1 root root 2097152000 Dec 7 23:20 swapfile

[root@pacteralinux mntsdc2formysql]# cd mysqldata/

[root@pacteralinux mysqldata]# ll

total 4

drwxr-xr-x. 7 mysql mysql 4096 Dec 26 13:32 mysqldate

[root@pacteralinux mysqldata]# cd mysqldate/

[root@pacteralinux mysqldate]# ll

total 176860

-rw-rw----. 1 mysql mysql 56 Nov 25 17:17 auto.cnf

-rw-rw----. 1 mysql mysql 79691776 Dec 26 13:32 ibdata1

-rw-rw----. 1 mysql mysql 50331648 Dec 26 13:32 ib_logfile0

-rw-rw----. 1 mysql mysql 50331648 Nov 26 13:39 ib_logfile1

drwx------. 2 mysql mysql 4096 Nov 26 13:41 mysql

drwx------. 2 mysql mysql 20480 Nov 26 17:00 mysqldb

-rw-rw----. 1 mysql root 514836 Dec 26 13:32 pacteralinux.err

drwx------. 2 mysql mysql 4096 Nov 26 13:41 performance_schema

drwx------. 2 mysql mysql 4096 Nov 26 13:41 test

drwx------. 2 mysql mysql 4096 Dec 9 16:49 weixindemo

[root@pacteralinux mysqldate]# mv * ../

[root@pacteralinux mysqldate]# ll

total 0

[root@pacteralinux mysqldate]# cd ..

[root@pacteralinux mysqldata]# ll

total 176864

-rw-rw----. 1 mysql mysql 56 Nov 25 17:17 auto.cnf

-rw-rw----. 1 mysql mysql 79691776 Dec 26 13:32 ibdata1

-rw-rw----. 1 mysql mysql 50331648 Dec 26 13:32 ib_logfile0

-rw-rw----. 1 mysql mysql 50331648 Nov 26 13:39 ib_logfile1

drwx------. 2 mysql mysql 4096 Nov 26 13:41 mysql

drwxr-xr-x. 2 mysql mysql 4096 Dec 26 13:48 mysqldate

drwx------. 2 mysql mysql 20480 Nov 26 17:00 mysqldb

-rw-rw----. 1 mysql root 514836 Dec 26 13:32 pacteralinux.err

drwx------. 2 mysql mysql 4096 Nov 26 13:41 performance_schema

drwx------. 2 mysql mysql 4096 Nov 26 13:41 test

drwx------. 2 mysql mysql 4096 Dec 9 16:49 weixindemo

[root@pacteralinux mysqldata]# rm -rf mysqldate/

[root@pacteralinux mysqldata]# ll

total 176860

-rw-rw----. 1 mysql mysql 56 Nov 25 17:17 auto.cnf

-rw-rw----. 1 mysql mysql 79691776 Dec 26 13:32 ibdata1

-rw-rw----. 1 mysql mysql 50331648 Dec 26 13:32 ib_logfile0

-rw-rw----. 1 mysql mysql 50331648 Nov 26 13:39 ib_logfile1

drwx------. 2 mysql mysql 4096 Nov 26 13:41 mysql

drwx------. 2 mysql mysql 20480 Nov 26 17:00 mysqldb

-rw-rw----. 1 mysql root 514836 Dec 26 13:32 pacteralinux.err

drwx------. 2 mysql mysql 4096 Nov 26 13:41 performance_schema

drwx------. 2 mysql mysql 4096 Nov 26 13:41 test

drwx------. 2 mysql mysql 4096 Dec 9 16:49 weixindemo

[root@pacteralinux mysqldata]#

4:修改配置文件basedir =/usr/local/mysql

#datadir =/mnt/resource/mysqldate

datadir=/mnt/mntsdc2formysql/mysqldata

port =3306

#socket =/mnt/resource/mysqldate/mysql.sock

socket =/mnt/mntsdc2formysql/mysqldata/mysql.sock

5:启动mysql[root@pacteralinux mysqldata]# service mysqld start

Starting MySQL...[ OK ]

[root@pacteralinux mysqldata]# ps -ef|grep mysql

root 7584 1 0 13:56 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mnt/mntsdc2formysql/mysqldata --pid-file=/mnt/mntsdc2formysql/mysqldata/pacteralinux.pid

mysql 7837 7584 7 13:56 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mnt/mntsdc2formysql/mysqldata --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mnt/mntsdc2formysql/mysqldata/pacteralinux.err --pid-file=/mnt/mntsdc2formysql/mysqldata/pacteralinux.pid --socket=/mnt/mntsdc2formysql/mysqldata/mysql.sock --port=3306

root 7869 7376 0 13:56 pts/0 00:00:00 grep mysql

[root@pacteralinux mysqldata]#

至此，mysql数据存放路径移动完成！

其中，有几个小疑问basedir=

datadir=

# Default value, in seconds, afterwhich the script should timeout waiting

# for server start.

# Value here is overriden by value in my.cnf.

# 0 means don't wait at all

# Negative numbers mean to wait indefinitely

service_startup_timeout=900

# Lock directory for RedHat / SuSE.

lockdir='/var/lock/subsys'

lock_file_path="$lockdir/mysql"

# The following variables are only set for letting mysql.server find things.

# Set some defaults

mysqld_pid_file_path=

if test -z "$basedir"

then

basedir=/usr/local/mysql

bindir=/usr/local/mysql/bin

if test -z "$datadir"

then

datadir=/mnt/resource/mysqldate

我看到datadir还是之前的那个路径，不知道要不要改！

在这个过程中出现的问题

问题描述：当我在mysql-front工具上连接mysql数据库时没有问题，但是当我在命令行连接时报错：[root@pacteralinux weixindemo]# mysql -u root -p

Enter password:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/mnt/resource/mysqldate/mysql.sock' (2)

很奇怪，这个路径不是改过嘛，看代码：#socket =/mnt/resource/mysqldate/mysql.sock

socket =/mnt/mntsdc2formysql/mysqldata/mysql.sock

但是为什么还是报以前的路径错误，以为问题出在启动脚本上/etc/init.d/mysqld ，但是看了下，启动脚本里面没有设置socket相关项了！没办法，还是把/etc/init.d/mysqld的datedir改为现在的安装路径[root@pacteralinux ~]# vi /etc/init.d/mysqld

basedir=/mnt/resource/mysqldate

datadir=

重启还是报同样的错，没办法，看下错误日志吧：131226 13:32:38 mysqld_safe mysqld from pid file /mnt/resource/mysqldate/pacteralinux.pid ended

131226 13:56:18 mysqld_safe Starting mysqld daemon with databases from /mnt/mntsdc2formysql/mysqldata

2013-12-26 13:56:19 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2013-12-26 13:56:19 7837 [Note] Plugin 'FEDERATED' is disabled.

2013-12-26 13:56:19 7837 [Note] InnoDB: The InnoDB memory heap is disabled

2013-12-26 13:56:19 7837 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2013-12-26 13:56:19 7837 [Note] InnoDB: Compressed tables use zlib 1.2.3

2013-12-26 13:56:19 7837 [Note] InnoDB: Not using CPU crc32 instructions

2013-12-26 13:56:19 7837 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2013-12-26 13:56:19 7837 [Note] InnoDB: Completed initialization of buffer pool

2013-12-26 13:56:19 7837 [Note] InnoDB: Highest supported file format is Barracuda.