Can't SSH even with iptables stopped?

The scenario is :

1. You try to ssh from server A (192.168.56.90) to a remove server B (192.168.56.95);
2. Both server A and server B are within the same network segment. (This means no hardware firewall involved)

The error is:

Let find the reason, for simple, just stop the iptables on remote server B.

#service iptables stop

I use "telnet" to troubleshoot, still refused. Check /etc/hosts.deny and /etc/hosts.allow, no findings.

Let's check sshd service on remote server B.

This is a low-level mistake, we just assume sshd is running. ok, we start sshd.

#service sshd start
#chkconfig sshd on

Actually we can tell the cause of the error from the error messages.

• iptables port 22 not open, the error probably is "No route to host"
• /etc/hosts.deny, error probably is "Connection closed by foreign host."

"Error: Failed while fetching Server version! Could be due to unauthorized access." when you run mysql_upgrade

Recently I met this error "Error: Failed while fetching Server version! Could be due to unauthorized access." when I have upgrade mysql to a new version and run "mysql_upgrade", which version doesn't matter here.

# mysql_upgrade -uroot -p                     
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Error: Failed while fetching Server version! Could be due to unauthorized access.
FATAL ERROR: Upgrade failed

How to fix the error? Let's start troubleshooting.

The first clue is "Failed while fetching Server version", so we skip version check and see how.

# mysql_upgrade --skip-version-check -uroot -p

Enter password:

Looking for 'mysql' as: mysql

Looking for 'mysqlcheck' as: mysqlcheck

Running 'mysqlcheck with default connection arguments

mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect

FATAL ERROR: Error during call to mysql_check for fixing the db/tables names.

OK, the problem is much clearer,  it's can not connect through socket, so we check the socket file.

# ll /var/lib/mysql/mysql.sock

ls: cannot access /var/lib/mysql/mysql.sock: No such file or directory

The socket file doesn't exist in /var/lib/mysql. 

Let's check mysql process.

# ps axu |grep mysql

root      6098  0.0  0.0  11440  1536 ?        S    17:24   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/data/mysql/ --pid-file=/data/mysql/mysql.pid

mysql     6931  3.0  7.7 20851736 2544580 ?    Sl   17:24   7:21 /usr/sbin/mysqld --basedir=/usr --datadir=/data/mysql/ --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/data/mysql/mysql-error.log --open-files-limit=65535 --pid-file=/data/mysql/mysql.pid --socket=/data/mysql/mysql.sock --port=3306

See? The socket file is under /data/mysql. This is because in my.cnf

datadir   =   /data/mysql

So we create a soft link /data/mysql to /var/lib/mysql

#ln -s /data/mysql /var/lib/mysql

Problem solved.

You should disable have_symlink for mysql

Have you ever noticed a variables named "have_symlink", by default it's set "YES".

What have_symlink is capable of? Enabling symbolic links means that you can link a MyISAM index file or data file to another directory with the INDEX DIRECTORY or DATA DIRECTORY options of the CREATE TABLE statement. If you delete or rename the table, the files that its symbolic links point to also are deleted or renamed.

      mysql> create table test_myisam (id int not null) engine=myisam data directory='/tmp';

This will make a symbolic link on data file of table test1:

You may use it if you are running low on disk space on the partition where your mysql data directory is, or you want to move some tables to a different partition or disk for performance reasons or you just leave it as default.

This is insecure, for two reasons:

1.  mysqld can be used to drop or rename a file outside of the data directory.


2. MySQL before 5.0.67 allows local users to bypass certain privilege checks by calling CREATE TABLE on a MyISAM table with modified (1) DATA DIRECTORY or (2) INDEX DIRECTORY arguments that are originally associated with pathnames without symlinks, and that can point to tables created at a future time at which a pathname is modified to contain a symlink to a subdirectory of the MySQL home data directory. NOTE: this vulnerability exists because of an incomplete fix for CVE-2008-4097.

So if you are not using symlinks, you should disable it with the --skip-symbolic-links option, it's not dynamic, you need to restart your mysql.

How does Error 'You cannot 'ALTER' a log table if logging is enabled' on query happen when running mysql_upgrade? and why?

Last_Error: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER

TABLE slow_log ...

Normally when you met above error is because you are running msyql_upgrade on master, subsequently the error happens on the slave.

The solution is:

1.  stop slave;
2. set global slow_query_log = 'OFF';
3. start slave;
4. set global slow_query_log = 'ON';

why does it happen?

When you run mysql_upgrade, it actually contains 'alter table' sql:

ALTER TABLE general_log

   MODIFY event_time TIMESTAMP NOT NULL,

   MODIFY user_host MEDIUMTEXT NOT NULL,

   MODIFY thread_id INTEGER NOT NULL,

   MODIFY server_id INTEGER UNSIGNED NOT NULL,

   MODIFY command_type VARCHAR(64) NOT NULL,

   MODIFY argument MEDIUMTEXT NOT NULL;

ALTER TABLE slow_log

   MODIFY start_time TIMESTAMP NOT NULL,

   MODIFY user_host MEDIUMTEXT NOT NULL,

   MODIFY query_time TIME NOT NULL,

   MODIFY lock_time TIME NOT NULL,

   MODIFY rows_sent INTEGER NOT NULL,

   MODIFY rows_examined INTEGER NOT NULL,

   MODIFY db VARCHAR(512) NOT NULL,

   MODIFY last_insert_id INTEGER NOT NULL,

   MODIFY insert_id INTEGER NOT NULL,

   MODIFY server_id INTEGER UNSIGNED NOT NULL,

   MODIFY sql_text MEDIUMTEXT NOT NULL;

Before running "ALTER TABLE", mysql_upgrade run "set global slow_query_log = 'OFF';", however, this will not be logged into binary log, subsequently "ALTER TABLE" sql are logged, so the slave run "ALTER TABLE" with slow_query_log=ON.