mysql登陆案例_本地登录多实例mysql ，默认登录数据库问题

本地登录多实例mysql ，默认登录数据库问题

本地多实例mysql 环境，有 3306和3307 两个端口的实例。

当本地登录时，以下登录方式登录进去的都是 3306 实例

[root@testdb1 ~]# mysql -uroot -pchengce243 -P3306 -e "show variables like 'port';"

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| port | 3306 |

+---------------+-------+

[root@testdb1 ~]# mysql -uroot -pchengce243 -P3307 -e "show variables like 'port';"

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| port | 3306 |

+---------------+-------+

甚至一个不存在的端口号 9088 登录进去的依然是 3306 实例

[root@testdb1 ~]# mysql -uroot -pchengce243 -P9088 -e "show variables like 'port';"

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| port | 3306 |

+---------------+-------+

要想登录到指定的端口号，必须指定 -h 参数 。

[root@testdb1 ~]# mysql -uroot -pchengce243 -h127.0.0.1 -P3306 -e "show variables like 'port';"

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| port | 3306 |

+---------------+-------+

[root@testdb1 ~]# mysql -uroot -pchengce243 -h127.0.0.1 -P3307 -e "show variables like 'port';"

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| port | 3307 |

+---------------+-------+

原因：如果不指定 -h 参数，只指定 -P 参数，默认就会去读取 默认的配置文件 中的 [client] 部分，也就是 /etc/my.cnf

查看 /etc/my.cnf 得知，配置的端口号就是 3306，以下验证一下结论，修改 /etc/my.cnf port 参数为 4406

[root@testdb1 ~]# mysql -uroot -pchengce243 -P3307 -e "show variables like 'port';"

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| port | 4406 |

+---------------+-------+

[root@testdb1 ~]# mysql -uroot -pchengce243 -P3306 -e "show variables like 'port';"

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| port | 4406 |

+---------------+-------+

[root@testdb1 ~]# mysql -uroot -pchengce243 -P9306 -e "show variables like 'port';"

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| port | 4406 |

+---------------+-------+

可以看到上面系统中并不存在的 3306端口和 9306 端口，登录进去就是修改后的 4406 端口。

[root@testdb1 ~]# mysql -uroot -pchengce243 -h127.0.0.1 -P4406 -e "show variables like 'port';"

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| port | 4406 |

+---------------+-------+

[root@testdb1 ~]# mysql -uroot -pchengce243 -h127.0.0.1 -P3307 -e "show variables like 'port';"

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| port | 3307 |

+---------------+-------+

[root@testdb1 ~]# mysql -uroot -pchengce243 -h127.0.0.1 -P3306 -e "show variables like 'port';"

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

[root@testdb1 ~]# mysql -uroot -pchengce243 -h127.0.0.1 -P9306 -e "show variables like 'port';"

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

所以为了解决本地登录时端口乱串的问题，最好指定 -h 参数，就不会发生登录到默认数据库的问题了。