无法用指定MySQL客户端登陆服务器的案例分析

最新推荐文章于 2023-05-17 15:33:55 发布

aaron8219

最新推荐文章于 2023-05-17 15:33:55 发布

阅读量1.4k

点赞数

分类专栏： MySQL 文章标签： MySQL 客户端登陆 mysqld 环境变量

本文链接：https://blog.csdn.net/aaron8219/article/details/40188369

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

习惯了二进制安装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)

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)

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)

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)

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