我計劃更換資料庫的存放目錄，下面是我的操作步驟
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資料存放路徑移動完成！
其中，有幾個小疑問
-
我看到有的博文裡面說需要修改啟動指令碼/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資料庫時沒有問題，但是當我在命令列連線時報錯：
[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. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."doc_type"`, ignored. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."documents"`, ignored. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."menu_event_response_config"`, ignored. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."pictures"`, ignored. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."response_config"`, ignored. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."sys_function"`, ignored. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."sys_function_catalog"`, ignored. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."sys_orgnization"`, ignored. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."sys_role"`, ignored. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."sys_role_function"`, ignored. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."sys_user"`, ignored. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."sys_user_role"`, ignored. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."usr_chat_his"`, ignored. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."usr_message"`, ignored. 2013-12-26 13:56:19 7fac674d7720 InnoDB: 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 [ERROR] 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 [ERROR] InnoDB: Tablespace open failed for `"weixin"."wx_config"`, ignored. 2013-12-26 13:56:19 7837 [Note] InnoDB: 128 rollback segment(s) are active. 2013-12-26 13:56:20 7837 [Note] InnoDB: Waiting for purge to start 2013-12-26 13:56:20 7837 [Note] InnoDB: 5.6.14 started; log sequence number 2138639 2013-12-26 13:56:20 7837 [Note] Server hostname (bind-address): `*`; port: 3306 2013-12-26 13:56:20 7837 [Note] IPv6 is available. 2013-12-26 13:56:20 7837 [Note] - `::` resolves to `::`; 2013-12-26 13:56:20 7837 [Note] Server socket created on IP: `::`. 2013-12-26 13:56:20 7837 [Note] Event Scheduler: Loaded 0 events 2013-12-26 13:56:20 7837 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: `5.6.14` socket: `/mnt/mntsdc2formysql/mysqldata/mysql.sock` port: 3306 Source distribution 2013-12-26 14:42:17 7837 [Warning] 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. [client] socket =/mnt/resource/mysqldate/mysql.sock
哇咔，改為現在目錄啟動OK
參考：