無法用指定MySQL客戶端登陸服務器的案例分析
習慣了二進制安裝MySQL，今天心血來潮想裝個RPM包的MySQL玩玩，沒想到一裝還真碰到了點問題，下面把碰到的問題分享一下
首先去官網下載安裝包，地址是：http://downloads.mysql.com/archives/community/
根據自己的系統版本和平台選擇要安裝的包，我的測試機是32 bit的 RHEL 5.3
我選擇的是5.0.96，挺老的版本了，需要下載一個server包和一個client包，分別為：
MySQL-server-community-5.0.96-1.rhel5.i386
MySQL-client-community-5.0.96-1.rhel5.i386
把這2個包放到服務器/opt目錄下
一、安裝MySQL服務器
[root@bak ~]# rpm -ivh /opt/MySQL-server-community-5.0.96-1.rhel5.i386.rpm
Preparing... ########################################### [100%]
1:MySQL-server-community ########################################### [100%]
141017 15:36:11 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --log_slow_queries is not set
Installation of system tables failed!
Examine the logs in /data/mysql/mysql_3306/data for more information.
You can try to start the mysqld daemon with:
/usr/local/mysql/bin/mysqld --skip-grant &
and use the command line tool
/usr/local/mysql/bin/mysql to connect to the mysql
database and look at the grant tables:
shell> /usr/local/mysql/bin/mysql -u root mysql
mysql> show tables
Try 'mysqld --help' if you have problems with paths. Using --log
gives you a log in /data/mysql/mysql_3306/data that may be helpful.
The latest information about MySQL is available on the web at
http://www.mysql.com
Please consult the MySQL manual section: 'Problems running mysql_install_db',
and the manual section that describes problems on your OS.
Another information source is the MySQL email archive.
Please check all of the above before mailing us!
And if you do mail us, you MUST use the /usr/bin/mysqlbug script!
Starting MySQL[ OK ] --mysqld進程順利啟動
Giving mysqld 2 seconds to start
[root@bak ~]# ps -ef|grep mysql
root 11883 11838 0 15:28 pts/2 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe
mysql 12584 11883 0 15:28 pts/2 00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql
--log-error=/data/mysql/mysql_3306/data/error.log --open-files-limit=8192 --pid-file=/data/mysql/mysql_3306/data/bak.pid --socket=/tmp/mysql.sock --port=3306
root 13390 11838 0 15:36 pts/2 00:00:00 grep mysql
[root@bak ~]#
安裝完包以後會自動去啟動mysqld進程，並尋找/etc/my.cnf文件（如果有的話）讀取配置，顯然我這裡原來是有一個的，這是為我的5.5.39的二進制MySQL配置的，而這裡已經被讀取到了，但我現在裝的是5.0.x的RPM版本，使用這個配置文件顯然是不適合的，因此先停止mysqld進程，使用自帶的配置文件重新去啟動服務器進程
--停止mysqld進程
[root@bak ~]# service mysql stop
Shutting down MySQL.141017 15:42:12 mysqld_safe mysqld from pid file /data/mysql/mysql_3306/data/bak.pid ended
[ OK ]
[1]+ Done mysqld_safe
--也可以這樣停止mysqld進程
[root@bak ~]# /etc/init.d/mysql stop
Shutting down MySQL. [ OK ]
--把原來的配置文件改名，復制5.0.96自帶的模板配置文件到/etc/my.cnf
[root@bak ~]# mv /etc/my.cnf /etc/my.cnf-5.5.39
[root@bak ~]# cd /usr/share/mysql
[root@bak mysql]# ll
total 1152
-rwxr-xr-x 1 root root 1153 Mar 3 2012 binary-configure
drwxr-xr-x 2 root root 4096 Oct 17 15:36 charsets
drwxr-xr-x 2 root root 4096 Oct 17 15:36 czech
drwxr-xr-x 2 root root 4096 Oct 17 15:36 danish
drwxr-xr-x 2 root root 4096 Oct 17 15:36 dutch
drwxr-xr-x 2 root root 4096 Oct 17 15:36 english
-rwxr-xr-x 1 root root 346791 Mar 3 2012 errmsg.txt
drwxr-xr-x 2 root root 4096 Oct 17 15:36 estonian
-rwxr-xr-x 1 root root 548013 Mar 3 2012 fill_help_tables.sql
drwxr-xr-x 2 root root 4096 Oct 17 15:36 french
drwxr-xr-x 2 root root 4096 Oct 17 15:36 german
drwxr-xr-x 2 root root 4096 Oct 17 15:36 greek
drwxr-xr-x 2 root root 4096 Oct 17 15:36 hungarian
drwxr-xr-x 2 root root 4096 Oct 17 15:36 italian
drwxr-xr-x 2 root root 4096 Oct 17 15:36 japanese
drwxr-xr-x 2 root root 4096 Oct 17 15:36 korean
-rwxr-xr-x 1 root root 5726 Mar 3 2012 mi_test_all
-rwxr-xr-x 1 root root 1517 Mar 3 2012 mi_test_all.res
-rwxr-xr-x 1 root root 4972 Mar 3 2012 my-huge.cnf
-rwxr-xr-x 1 root root 20970 Mar 3 2012 my-innodb-heavy-4G.cnf
-rwxr-xr-x 1 root root 4948 Mar 3 2012 my-large.cnf
-rwxr-xr-x 1 root root 4955 Mar 3 2012 my-medium.cnf
-rwxr-xr-x 1 root root 2526 Mar 3 2012 my-small.cnf
-rwxr-xr-x 1 root root 1061 Mar 3 2012 mysqld_multi.server
-rwxr-xr-x 1 root root 33106 Mar 3 2012 mysql_fix_privilege_tables.sql
-rwxr-xr-x 1 root root 789 Mar 3 2012 mysql-log-rotate
-rwxr-xr-x 1 root root 11948 Mar 3 2012 mysql.server
-rwxr-xr-x 1 root root 2570 Mar 3 2012 mysql_system_tables_data.sql
-rwxr-xr-x 1 root root 12835 Mar 3 2012 mysql_system_tables.sql
-rwxr-xr-x 1 root root 10371 Mar 3 2012 mysql_test_data_timezone.sql
-rwxr-xr-x 1 root root 589 Mar 3 2012 ndb-config-2-node.ini
-rwxr-xr-x 1 root root 6295 Mar 3 2012 ndb_size.tmpl
drwxr-xr-x 2 root root 4096 Oct 17 15:36 norwegian
drwxr-xr-x 2 root root 4096 Oct 17 15:36 norwegian-ny
drwxr-xr-x 2 root root 4096 Oct 17 15:36 polish
drwxr-xr-x 2 root root 4096 Oct 17 15:36 portuguese
drwxr-xr-x 2 root root 4096 Oct 17 15:36 romanian
drwxr-xr-x 2 root root 4096 Oct 17 15:36 russian
drwxr-xr-x 3 root root 4096 Oct 17 15:36 SELinux
drwxr-xr-x 2 root root 4096 Oct 17 15:36 serbian
drwxr-xr-x 2 root root 4096 Oct 17 15:36 slovak
drwxr-xr-x 2 root root 4096 Oct 17 15:36 spanish
drwxr-xr-x 2 root root 4096 Oct 17 15:36 swedish
drwxr-xr-x 2 root root 4096 Oct 17 15:36 ukrainian
[root@bak mysql]# cp my-medium.cnf /etc/my.cnf
--重新啟動mysqld進程
[root@bak ~]# /etc/init.d/mysql start
Starting MySQL. [ OK ]
[root@bak ~]# ps -ef|grep mysql
root 13457 1 0 15:47 pts/2 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/bak.pid
mysql 13487 13457 0 15:47 pts/2 00:00:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/bak.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
root 13505 11838 0 15:47 pts/2 00:00:00 grep mysql
[root@bak ~]#
再次查看，現在使用的配置文件已經為默認配置的值了（可與之前的進程捕捉到的路徑做對比），RPM包安裝MySQL默認的data路徑是/var/lib/mysql，bin log和error log默認也都是放在這裡的
二、安裝MySQL客戶端
由於之前已經裝了5.5.39的二進制MySQL，如果不安裝5.0.96的客戶端，而直接使用原來的客戶端也是可以的，那麼就是通過5.5.39的客戶端去連接5.0.96的服務器
[root@bak ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.96-community-log MySQL Community Edition (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.5.39, for linux2.6 (i686) using readline 5.1
Connection id: 1
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.96-community-log MySQL Community Edition (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 min 5 sec
Threads: 1 Questions: 4 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 0.062
--------------
mysql>
連接很順利，紅色部分清楚地顯示了客戶端和服務器端的版本號，如果在沒有安裝過MySQL客戶端的系統上執行mysql，會提示找不到命令，我的目的是想安裝5.0.96自己的客戶端，然後連接到服務器
[root@bak ~]# rpm -ivh /opt/MySQL-client-community-5.0.96-1.rhel5.i386.rpm
Preparing... ########################################### [100%]
1:MySQL-client-community ########################################### [100%]
[root@bak ~]#
為了不讓原來的版本對5.0.39產生干擾，我把原來屬於5.5.39的軟鏈先刪掉
[root@bak ~]# cd /usr/local
[root@bak local]# ll
total 72
drwxr-xr-x 2 mysql 502 4096 Aug 25 11:39 bin
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 etc
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 games
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 include
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 lib
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 libexec
lrwxrwxrwx 1 root root 38 Oct 17 14:56 mysql -> /opt/mysql/mysql-5.5.39-linux2.6-i686/
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 sbin
drwxr-xr-x 5 mysql 502 4096 Aug 25 11:39 share
drwxr-xr-x 2 mysql 502 4096 Sep 23 20:34 src
[root@bak local]# unlink mysql
[root@bak local]# ll
total 72
drwxr-xr-x 2 mysql 502 4096 Aug 25 11:39 bin
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 etc
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 games
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 include
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 lib
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 libexec
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 sbin
drwxr-xr-x 5 mysql 502 4096 Aug 25 11:39 share
drwxr-xr-x 2 mysql 502 4096 Sep 23 20:34 src
[root@bak local]#
原來的/usr/local/mysql/bin指向的位置是/opt/mysql/mysql-5.5.39-linux2.6-i686/mysql/bin的，那麼按理說，現在已經取消了該路徑，當我們執行mysql客戶端命令的時候，應該是使用5.0.96的客戶端程序去連接服務器，但事實卻非如此
[root@bak local]# mysql
-bash: /usr/local/mysql/bin/mysql: No such file or directory
可以看到，當軟鏈取消後，再次執行mysql命令，提示沒有這個目錄了，因為這個目錄就是剛才軟鏈的目錄，已經unlink掉了，肯定是沒有的了，那不是還有/usr/bin目錄嗎？為什麼5.0.96自己的bin目錄不能被利用起來？
開始以為重啟mysqld進程可以改變這一窘境，可並沒有起作用
[root@bak local]# service mysql stop
Shutting down MySQL. [ OK ]
[root@bak local]# service mysql start
Starting MySQL. [ OK ]
[root@bak local]# mysql
-bash: /usr/local/mysql/bin/mysql: No such file or directory
嘗試退出session，重新連接到服務器
[root@bak local]# exit
斷開session……
Last login: Fri Oct 17 15:26:24 2014 from 192.168.1.100 --重新連接上來
[root@bak ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.96-community-log MySQL Community Edition (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \s
--------------
mysql Ver 14.12 Distrib 5.0.96, for pc-linux-gnu (i686) using readline 5.1 --不再報錯，已經用5.0.96的客戶端程序連到服務器了
Connection id: 1
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.96-community-log MySQL Community Edition (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 min 55 sec
Threads: 1 Questions: 4 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 0.035
--------------
mysql>
開始我理解是，刪除軟鏈，也許要重新退出session才能生效，但事實上是環境變量的問題。由於之前已經有5.5.39版本，bin路徑是 /usr/local/mysql/bin/mysql ，後來安裝了 5.0.96，bin路徑指向 /usr/bin/mysql，雖然之前把 /usr/local/mysql 軟鏈去掉了，環境變量認為mysql還是要讀取
/usr/local/mysql/bin/mysql，而不會去讀取最新的 /usr/bin/mysql，所以報路徑錯誤，退出session以後，等於是對環境變量的路徑做了一次更新，去讀取/usr/bin/mysql
那麼假設我現在又想用5.5.39客戶端程序去連5.0.96服務器呢？是不是簡單地加上軟鏈，然後退出session就可以了呢？我們來看一下
mysql> exit
Bye
[root@bak ~]# cd /usr/local
[root@bak local]# ln -s /opt/mysql/mysql-5.5.39-linux2.6-i686/ mysql
[root@bak local]# ll
total 72
drwxr-xr-x 2 mysql 502 4096 Aug 25 11:39 bin
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 etc
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 games
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 include
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 lib
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 libexec
lrwxrwxrwx 1 root root 38 Oct 17 16:14 mysql -> /opt/mysql/mysql-5.5.39-linux2.6-i686/
drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 sbin
drwxr-xr-x 5 mysql 502 4096 Aug 25 11:39 share
drwxr-xr-x 2 mysql 502 4096 Sep 23 20:34 src
[root@bak local]# exit
斷開session……
Last login: Fri Oct 17 16:07:04 2014 from 192.168.1.100 --重新連上服務器
[root@bak ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.96-community-log MySQL Community Edition (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \s
--------------
mysql Ver 14.12 Distrib 5.0.96, for pc-linux-gnu (i686) using readline 5.1
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.96-community-log MySQL Community Edition (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 9 min 17 sec
Threads: 1 Questions: 12 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 0.022
--------------
mysql>
是不是覺得很奇怪？這次重新添加了軟鏈指向了5.5.39的bin路徑，也退出了sesssion更新環境變量，怎麼mysql還是去連了5.0.96的bin路徑裡的mysql客戶端呢？再來看一下PATH環境變量的設置
mysql> exit
Bye
[root@bak ~]# echo $PATH
/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/mysql/bin:/root/bin
[root@bak ~]#
發現/usr/bin是在/usr/local/mysql這個路徑之前的，這裡做一個假設，系統先讀到/usr/bin之後就直接運行了5.0.96的mysql客戶端，而不管之後的路徑了，所以即便推出session重新連接，始終都是先進入/usr/bin讀取mysql。來驗證一下，把這2個位置對調一下
[root@bak ~]# export PATH=/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/local/mysql/bin:/usr/bin:/root/bin
[root@bak ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.96-community-log MySQL Community Edition (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.5.39, for linux2.6 (i686) using readline 5.1 --又以5.5.39的客戶端連接到5.0.96的服務器了
Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.96-community-log MySQL Community Edition (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 21 min 15 sec
Threads: 1 Questions: 32 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 0.025
--------------
mysql>
不過這樣做並沒有固化到環境變量中，退出session以後再連上，PATH又變成之前的值了，就算source .bash_profile也是如此，來看一下root用戶的.bash_profile文件
[root@bak ~]# cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
[root@bak ~]#
這裡面沒有顯示指定PATH的具體值是哪些，只是用$調用了一下PATH變量，既然這樣，我們就把期望的值寫進去，然後就可以做到固化PATH路徑的絕對值了
[root@bak ~]# vi .bash_profile
把$PATH改為
/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/local/mysql/bin:/usr/bin:/root/bin
[root@bak ~]# . .bash_profile --使環境變量在當前session生效
這樣，即使退出session也不會丟失給PATH設置的值了，當我們需要哪個版本的客戶端時，就手動調整一下PATH的路徑，讓目標路徑放在前面供系統查找即可
