无法用指定MySQL客户端登陆服务器的案例分析_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的路径，让目标路径放在前面供系统查找即可