Case study of failure to log on to the server using the specified MySQL client

Case study of failure to log on to the server using the specified MySQL client
I am used to installing MySQL in binary mode. Today I am eager to install MySQL with an RPM package. I did not expect that I have encountered some problems. I will share the problems I encountered below.
First, go to the official website to download the installation package at: http://downloads.mysql.com/archives/community/?, and my testing machine is a 32-bit RHEL 5.3
I chose 5.0.96, very old version, need to download a server package and a client package, respectively: MySQL-server-community-5.0.96-1.rhel5.i386MySQL-client-community-5.0.96-1.rhel5.i386 put the two packages under the server/opt directory
1. Install the MySQL server

[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 setInstallation of system ta Bles failed!
Examine the logs in/data/mysql/mysql_3306/data for more information. you can try to start the mysqld daemon: /usr/local/mysql/bin/mysqld -- skip-grant & and use the command line tool/usr/local/mysql/bin/mysql to connect to the mysqldatabase and look at grant tables:
Shell>/usr/local/mysql/bin/mysql-u root mysqlmysql> show tables
Try 'mysqld -- help' if you have problems with paths. Using -- loggives you a log in/data/mysql/mysql_3306/data that may be helpful.
The latest information about MySQL is available on the web athttp: // www. mysql. comPlease 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 process successfully starts Giving mysqld 2 seconds to start [root @ bak ~] # Ps-ef | grep mysqlroot 11883 11838 0 00:00:00 pts/2 00:00:01/bin/sh/usr/local/mysql/bin/mysqld_safemysql 12584 11883 0 pts/2/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/B Ak. pid -- socket =/tmp/mysql. sock -- port = 3306 root 13390 11838 0 00:00:00 pts/2 grep mysql [root @ bak ~] #
After the package is installed, the mysqld process is automatically started and the/etc/my. the cnf file (if any) reads the configuration. Obviously, I used to have one here, which is configured for MySQL of 5.5.39, which has been read here, however, I have installed the RPM version 5.0.x. This configuration file is obviously not suitable. Therefore, stop the mysqld process and restart the server process with the built-in configuration file.
-- Stop the mysqld Process[Root @ bak ~] # Service mysql stopShutting 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
-- You can also stop the mysqld process.[Root @ bak ~] #/Etc/init. d/mysql stopShutting down MySQL. [OK]
-- Rename the original configuration file and copy the built-in template configuration file 5.0.96 to/etc/my. cnf[Root @ bak ~] # Mv/etc/my. cnf/etc/my. cnf-5.5.39
[Root @ bak ~] # Cd/usr/share/mysql [root @ bak mysql] # lltotal 1152-rwxr-xr-x 1 root 1153 Mar 3 2012 binary-configuredrwxr-xr-x 2 root 4096 Oct 17 charsetsdrwxr- xr-x 2 root 4096 Oct 17 czechdrwxr-xr-x 2 root 4096 Oct 17 danishdrwxr-xr-x 2 root 4096 Oct 17 dutchdrwxr-xr-x 2 root 4096 Oct 17 english-rwxr-xr-x 1 root 346791 Mar 3 2012 errmsg.txt drwxr-xr-x 2 root 4096 Oct 17 estonian-rwxr-xr-x 1 root 548013 Mar 3 2012 fill_help_tables.sqldrwxr-xr-x 2 root 4096 Oct 17 frenchdrwxr-xr-x 2 root 4096 Oct 17 germandrwxr-xr-x 2 root 4096 Oct 17 greekdrwxr -xr-x 2 root 4096 Oct 17 hungariandrwxr-xr-x 2 root 4096 Oct 17 italiandrwxr-xr-x 2 root 4096 Oct 17 Japan esedrwxr-xr-x 2 root 4096 Oct 17 korean-rwxr-xr-x 1 root 5726 Mar 3 2012 mi_test_all-rwxr-xr-x 1 root 1517 Mar 3 2012 mi_test_all.res-rwxr-xr-x 1 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 4948 Mar 3 2012 my-large.cnf-rwxr-xr-x 1 root 4955 Mar 3 2012 my-medium.cnf-rwxr-xr-x 1 root 2526 Mar 3 2012 my-small.cnf-rwxr-xr-x 1 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 789 Mar 3 2012 mysql-log-rotate-rwxr-xr-x 1 root 11948 Mar 3 2012 mysql. server-rwxr-xr-x 1 root 2570 Mar 3 2012 mysql_system_tables_data.sql-rwxr-xr-x 1 root 12835 Mar 3 2012 mysql_system_tables.sql-rwxr-xr-x 1 root 10371 Mar 3 2012 mysql_test_data_timezone.sql-rwxr-xr-x 1 root 589 Mar 3 2012 ndb-config-2-node.ini-rwxr-xr-x 1 root 6295 Mar 3 2012 ndb_size.tmpldrwxr-xr-x 2 root 4096 Oct 17 norwegiandrwxr-xr-x 2 root 4096 Oct 17 norwegian-nydrwxr-xr-x 2 root 4096 Oct 17 polishdrwxr-xr-x 2 root 4096 Oct 17 portuguesedrwxr-xr-x 2 root 4096 Oct 17 romaniandrwxr-xr-x 2 root 4096 Oct 17 russiandrwxr-xr- x 3 root 4096 Oct 17 SELinuxdrwxr-xr-x 2 root 4096 Oct 17 serbiandrwxr-xr-x 2 root 4096 Oct 17 slovakdrwxr-xr-x 2 root 4096 Oct 17 spanishdrwxr-xr-x 2 root 4096 Oct 17 swedishdrwxr-xr-x 2 root 4096 Oct 17 ukrainian [root @ bak mysql] # cp my-medium.cnf/etc /my. cnf
-- Restart the mysqld Process[Root @ bak ~] #/Etc/init. d/mysql startStarting MySQL. [OK]
[Root @ bak ~] # Ps-ef | grep mysqlroot 13457 1 0 00:00:00 pts/2/bin/sh/usr/bin/mysqld_safe -- datadir =/var/lib/mysql -- pid-file =/ var/lib/mysql/bak. pidmysql 13487 13457 0 00:00:00 pts/2/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. sockroot 13505 11838 0 00:00:00 pts/2 grep my SQL [root @ bak ~] #
Check again that the current configuration file is already the default configuration value (you can compare it with the path captured by the previous process ), the default data path for installing MySQL in the RPM package is/var/lib/mysql, and bin log and error log are also stored here by default.
Ii. Install the MySQL client
Because MySQL of 5.5.39 has been installed before, if the 5.0.96 client is not installed and the original client can be directly used, the 5.5.39 client is used to connect to the 5.0.96 server.
[Root @ bak ~] # MysqlWelcome 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,201 4, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
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 @ localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter:; Server version: 5.0.96-community-log MySQL Community Edition (GPL) Protocol version: 10 Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. characterset: utf8UNIX socket:/var/lib/mysql. sockUptime: 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>
The connection is smooth. The red part clearly shows the version numbers of the client and server. If you run MySQL on a system without the mysql client installed, the system will prompt that the command cannot be found, my goal is to install 5.0.96 client and connect to the server
[Root @ bak ~] # Rpm-ivh/opt/MySQL-client-community-5.0.96-1.rhel5.i386.rpm Preparing... ######################################## ### [100%] 1: mySQL-client-community ################################### ######## [100%] [root @ bak ~] #
To prevent the original version from interfering with 5.0.39, I deleted the soft chain that originally belonged to 5.5.39.
[Root @ bak ~] # Cd/usr/local [root @ bak local] # lltotal 72drwxr-xr-x 2 mysql 502 4096 Aug 25 39 bindrwxr-xr-x 2 mysql 502 4096 Aug 8 2008 etcdrwxr-xr-x 2 mysql 502 4096 2008 Aug 8 502 gamesdrwxr-xr-x 2 mysql 4096 2008 502 Aug 8 4096 runtime dedrwxr-xr-x 2 mysql 2008 502 4096 Aug 8 libdrwxr-xr-x 2 mysql Aug 8 2008 libexeclrwxrwxrwx 1 root 38 Oct 17 mysql->/opt/mysql/mysql-5.5.39-linux2.6-i686/drwxr-xr-x 2 mysql 502 4096 Aug 8 2008 sbindrwxr-xr-x 5 mysql 502 4096 Aug 25 sharedrwxr-xr-x 2 mysql 502 4096 Sep 23 src [root @ bak local] # unlink mysql [root @ bak local] # lltotal 72drwxr-xr-x 2 mysql 502 4096 Aug 25 bindrwxr-xr-x 2 mysql 502 4096 Aug 8 2008 etcdrwxr-xr-x 2 mysql 502 4096 Aug 8 2008 gamesdrwxr-xr-x 2 mysql 502 4096 Aug 8 2008 running dedrwxr- xr-x 2 mysql 502 4096 Aug 8 2008 libdrwxr-xr-x 2 mysql 502 4096 Aug 8 2008 libexecdrwxr-xr-x 2 mysql 502 4096 Aug 8 2008 sbindrwxr-xr-x 5 mysql 502 4096 Aug 25 sharedrwxr-xr-x 2 mysql 502 4096 Sep 23 src [root @ bak local] #
The original/usr/local/mysql/bin point to the location is/opt/mysql/mysql-5.5.39-linux2.6-i686/mysql/bin, then it is reasonable to say that the path has been canceled, when we execute the mysql client command, we should use the 5.0.96 client program to connect to the server, but this is not the case.
[Root @ bak local] # mysql-bash:/usr/local/mysql/bin/mysql: No such file or directory
As you can see, when the soft link is canceled, run the mysql command again, prompting that this directory is not available, because this directory is the directory of the soft link just now and has been unlinked, there must be no more. Isn't there a/usr/bin directory? Why can't 5.0.96 bin directories be used?
I thought restarting the mysqld process could change this dilemma, but it didn't work.
[Root @ bak local] # service mysql stopShutting down MySQL. [OK] [root @ bak local] # service mysql startStarting MySQL. [OK] [root @ bak local] # mysql-bash:/usr/local/mysql/bin/mysql: No such file or directory
Try to exit the session and re-connect to the server [root @ bak local] # exit
Disconnect session ......
Last login: Fri Oct 17 15:26:24 2014 from 192.168.1.100 -- reconnect [root @ bak ~] # MysqlWelcome 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,201 1, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
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 -- no error is reported, and the 5.0.96 client program has been used to connect to the server.
Connection id: 1 Current database: Current user: root @ localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter:; Server version: 5.0.96-community-log MySQL Community Edition (GPL) Protocol version: 10 Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: latin1Conn. characterset: latin1UNIX socket:/var/lib/mysql. sockUptime: 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>
At first, I understand that deleting a soft chain may take effect only after the session is exited again, but it is actually a problem with environment variables. The bin path is/usr/local/mysql/bin/mysql, and then 5.0.96 is installed. The bin path points to/usr/bin/mysql, although the/usr/local/mysql soft chain has been removed before, the environment variable determines that mysql still needs to read/usr/local/mysql/bin/mysql, instead of reading the latest/usr/bin/mysql, a path error is reported. After the session is exited, the environment variable path is updated, read/usr/bin/mysql
So now I want to use the 5.5.39 client program to connect to the 5.0.96 server? Is it possible to simply add a soft link and then exit the session? Let's take a look.
Mysql> exitBye [root @ bak ~] # Cd/usr/local [root @ bak local] # ln-s/opt/mysql/mysql-5.5.39-linux2.6-i686/mysql [root @ bak local] # lltotal 72drwxr-xr-x 2 mysql 502 4096 Aug 25 bindrwxr-xr-x 2 mysql 502 4096 Aug 8 2008 etcdrwxr-xr-x 2 mysql 502 4096 Aug 8 2008 gamesdrwxr-xr-x 2 mysql 502 4096 Aug 8 2008 running dedrwxr-xr- x 2 mysql 502 4096 Aug 8 2008 libdrwxr-xr-x 2 mysql 502 4096 Aug 8 2008 libexeclrwxrwxrwx 1 root 38 Oct 17 mysql->/opt/mysql/mysql-5.5.39-linux2.6-i686/drwxr- xr-x 2 mysql 502 4096 Aug 8 2008 sbindrwxr-xr-x 5 mysql 502 4096 Aug 25 39 sharedrwxr-xr-x 2 mysql 502 4096 Sep 23 src [root @ bak local] # exit
Disconnect session ......

Last login: Fri Oct 17 16:07:04 2014 from 192.168.1.100 -- Re-connect to the server [root @ bak ~] # MysqlWelcome 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,201 1, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
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 @ localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter:; Server version: 5.0.96-community-log MySQL Community Edition (GPL) Protocol version: 10 Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: latin1Conn. characterset: latin1UNIX socket:/var/lib/mysql. sockUptime: 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>
Is it strange? This time, I added a soft link pointing to the bin path 5.5.39 and exited the sesssion to update the environment variable. Why does mysql connect to the mysql client in the bin path 5.0.96? Let's take a look at the setting of PATH environment variables.
Mysql> exitBye [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 ~] #
It is found that/usr/bin is located before the path/usr/local/mysql. Here we make a hypothesis that the system directly runs the mysql client 5.0.96 after reading/usr/bin, regardless of the subsequent path, even if the session is re-connected, mysql is always read in/usr/bin first. To verify it. Check the two locations.
[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,201 4, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
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 -- connect the 5.5.39 client to the 5.0.96 Server
Connection id: 8 Current database: Current user: root @ localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter:; Server version: 5.0.96-community-log MySQL Community Edition (GPL) Protocol version: 10 Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. characterset: utf8UNIX socket:/var/lib/mysql. sockUptime: 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>
However, this is not fixed to the environment variable. After the session is exited, the PATH becomes the previous value, even if source. bash_profile is the same. Let's take a look at the root user's. bash_profile file.
[Root @ bak ~] # Cat. bash_profile #. bash_profile
# Get the aliases and functionsif [-f ~ /. Bashrc]; then .~ /. Bashrcfi
# User specific environment and startup programs
PATH = $ PATH: $ HOME/bin
Export PATHunset USERNAME [root @ bak ~] #
The specific values of the specified PATH are not displayed, but the PATH variable is called with $. In this case, we will write the expected values, then the absolute value of the PATH can be solidified.
[Root @ bak ~] # Vi. bash_profile: Change $ PATH to/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 -- make the environment variable take effect in the current session
In this way, the value set for PATH will not be lost even if you exit the session. When you need a client of any version, manually adjust the PATH of PATH, put the target path in front of the system for search.