mysql移动数据存放目录及问题
我计划更换数据库的存放目录，下面是我的操作步骤1：首先创建新的数据存放路径并授予权限
# df -h
Filesystem SizeUsed Avail Use% Mounted on
/dev/sda1 29G 20G7.5G73% /
tmpfs 776M 0776M 0% /dev/shm
/dev/sdb1 69G4.0G 62G 6% /mnt/resource
/dev/sdc1 362G 53G291G16% /usr/local/Tomcat_Trans/webapps/infoSocketService/logs
/dev/sdc2 131G2.2G122G 2% /mnt/mntsdc2formysql
# pwd
/mnt/mntsdc2formysql
# mkdir mysqldata
# ll
total 2050024
drwx------. 2 root root 16384 Dec2 14:20 lost+found
drwxr-xr-x. 2 root root 4096 Dec 26 13:29 mysqldata
-rw-r--r--. 1 root root 2097152000 Dec7 23:20 swapfile
# cd mysqldata/
# chown mysql:mysql .
# cd ..
# ll
total 2050024
drwx------. 2 rootroot 16384 Dec2 14:20 lost+found
drwxr-xr-x. 2 mysql mysql 4096 Dec 26 13:29 mysqldata
-rw-r--r--. 1 rootroot2097152000 Dec7 23:20 swapfile
# chmod 755 mysqldata/
# ll
total 2050024
drwx------. 2 rootroot 16384 Dec2 14:20 lost+found
drwxr-xr-x. 2 mysql mysql 4096 Dec 26 13:29 mysqldata
-rw-r--r--. 1 rootroot2097152000 Dec7 23:20 swapfile 2：停止mysql
# ps -ef|grep mysql
root 745174250 13:28 pts/1 00:00:00 grep mysql
root 56138 10 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 561380 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
# service mysqld stop
Shutting down MySQL..
# service mysqld stop
Shutting down MySQL..
# ps -ef|grep mysql
root 750573760 13:32 pts/0 00:00:00 grep mysql 3：移动目录
我现在数据库的存放路径为
# cd /mnt/resource/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 Dec9 16:49 weixindemo 移动
# mv mysqldate//mnt/mntsdc2formysql/mysqldata/
# ll
total 24
drwx------. 2 root root 16384 Nov 20 13:57 lost+found
drwxr-xr-x. 2 root root4096 Dec2 15:02 mysqldataper
drwxr-xr-x. 2 root root4096 Dec2 15:02 sdc3mnt
# pwd
/mnt/resource
# cd /mnt/mntsdc2formysql/
# ll
total 2050024
drwx------. 2 rootroot 16384 Dec2 14:20 lost+found
drwxr-xr-x. 3 mysql mysql 4096 Dec 26 13:42 mysqldata
-rw-r--r--. 1 rootroot2097152000 Dec7 23:20 swapfile
# cd mysqldata/
# ll
total 4
drwxr-xr-x. 7 mysql mysql 4096 Dec 26 13:32 mysqldate
# cd 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 Dec9 16:49 weixindemo
# mv * ../
# ll
total 0
# cd ..
# 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 Dec9 16:49 weixindemo
# rm -rf 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 Dec9 16:49 weixindemo
# 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
# service mysqld start
Starting MySQL...
# ps -ef|grep mysql
root 7584 10 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 783775847 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 786973760 13:56 pts/0 00:00:00 grep mysql
# 至此，mysql数据存放路径移动完成！
其中，有几个小疑问
[*] 我看到有的博文里面说需要修改启动脚本/etc/init.d/mysqld的datadir参数,我看了一下该脚本，如下
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
fi 我看到datadir还是之前的那个路径，不知道要不要改！
[*] 还有，我看到有人说新路径要设置为777权限，设置为755权限报错，但是我的却没有报错
在这个过程中出现的问题
问题描述：当我在mysql-front工具上连接mysql数据库时没有问题，但是当我在命令行连接时报错：
# 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改为现在的安装路径
# 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 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 Plugin 'FEDERATED' is disabled.
2013-12-26 13:56:19 7837 InnoDB: The InnoDB memory heap is disabled
2013-12-26 13:56:19 7837 InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-12-26 13:56:19 7837 InnoDB: Compressed tables use zlib 1.2.3
2013-12-26 13:56:19 7837 InnoDB: Not using CPU crc32 instructions
2013-12-26 13:56:19 7837 InnoDB: Initializing buffer pool, size = 128.0M
2013-12-26 13:56:19 7837 InnoDB: Completed initialization of buffer pool
2013-12-26 13:56:19 7837 InnoDB: Highest supported file format is Barracuda.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/doc_type'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."doc_type"', ignored.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/documents'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."documents"', ignored.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/menu_event_response_config'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."menu_event_response_config"', ignored.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/pictures'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."pictures"', ignored.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/response_config'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."response_config"', ignored.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/sys_function'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."sys_function"', ignored.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/sys_function_catalog'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."sys_function_catalog"', ignored.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/sys_orgnization'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."sys_orgnization"', ignored.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/sys_role'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."sys_role"', ignored.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/sys_role_function'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."sys_role_function"', ignored.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/sys_user'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."sys_user"', ignored.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/sys_user_role'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."sys_user_role"', ignored.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/usr_chat_his'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."usr_chat_his"', ignored.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/usr_message'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."usr_message"', ignored.
2013-12-26 13:56:19 7fac674d7720InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-12-26 13:56:19 7837 InnoDB: Could not find a valid tablespace file for 'weixin/wx_config'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-12-26 13:56:19 7837 InnoDB: Tablespace open failed for '"weixin"."wx_config"', ignored.
2013-12-26 13:56:19 7837 InnoDB: 128 rollback segment(s) are active.
2013-12-26 13:56:20 7837 InnoDB: Waiting for purge to start
2013-12-26 13:56:20 7837 InnoDB: 5.6.14 started; log sequence number 2138639
2013-12-26 13:56:20 7837 Server hostname (bind-address): '*'; port: 3306
2013-12-26 13:56:20 7837 IPv6 is available.
2013-12-26 13:56:20 7837 - '::' resolves to '::';
2013-12-26 13:56:20 7837 Server socket created on IP: '::'.
2013-12-26 13:56:20 7837 Event Scheduler: Loaded 0 events
2013-12-26 13:56:20 7837 /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.14'socket: '/mnt/mntsdc2formysql/mysqldata/mysql.sock'port: 3306Source distribution
2013-12-26 14:42:17 7837 IP address '182.151.205.254' could not be resolved: Name or service not known
算了，其他的错先不看，最后几行说的那是啥,不能解析主机，越来不懂了
不死心，再看下/etc/my.cnf文件
哈哈，发现了这个
# *** upgrade to a newer version of MySQL.
socket =/mnt/resource/mysqldate/mysql.sock 哇咔，改为现在目录启动OK
参考：
http://blog.csdn.net/tianlesoftware/article/details/7028733
http://www.centos.bz/2012/06/change-mysql-data-location/
页:
[1]