MySQL登陆 socket 问题

最近使用二进制安装的方式安装了一台MySQL5.7，发现使用之前（源码安装）的连接方式报错。

[root@oracle_db ~]# mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

 

我的配置文件（/etc/my.cnf）：

[mysqld]
log_bin = server-130-bin
gtid_mode = ON
log_slave_updates = 1
enforce_gtid_consistency = ON
binlog_format=row
#read_only=1
relay_log_purge=0

basedir = /usr/local/mysql/
#datadir = /home/mysql/data/
datadir = /usr/local/mysql/data/
port = 3306
server_id = 130
socket = /usr/local/mysql/mysql.sock

# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
# sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]

 

当MySQL处于关闭状态时，连接MySQL也会报这种错。所以要先确保mysql确实已经是在运行。用ps检查一下就可以了。

[root@oracle_db ~]# ps -ef|grep mysql
root      94151  94019  0 01:28 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=mysql
mysql     94364  94151 16 01:28 pts/2    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql//lib/plugin --user=mysql --log-error=oracle_db.err --pid-file=oracle_db.pid --socket=/usr/local/mysql/mysql.sock --port=3306
root      94393  94019  0 01:28 pts/2    00:00:00 grep mysql　

从mysqld的运行参数可以看到，MySQL实际的socket文件在/usr/local/mysql/mysql.sock，而使用mysql客户程序使用的默认socket 在/tmp/mysql.sock，这个当然是不对的。解决的办法有下面几种。

 

第一种方法：加 --socket/-S 参数

[root@oracle_db ~]# mysql -uroot -p -S/usr/local/mysql/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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>

这种方法每次登陆都需要指定socket参数，稍显麻烦，于是有了第二种方法。

 

第二种方法：在配置文件指定socket参数

[client]
socket = /usr/local/mysql/mysql.sock

 

第三种方法：指定--host/-h参数

[root@oracle_db ~]# mysql -uroot -p -h127.0.0.1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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>

指定host参数登陆时，使用tcp/ip 连接MySQL，而不是socket连接。

注：-h localhost ,也是使用socket这种方式连接MySQL。

 

第四种方法：

在配置文件中注释socket参数，然后重启MySQL

[mysqld]
#socket = /usr/local/mysql/mysql.sock

 

再回到最开始的问题，为什么我用源码安装，然后直接使用mysql -uroot -p就可以连接呢？

我在编译MySQL的时候加上了-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock 这个参数，这可能会修改mysql程序soket参数的默认值。即将默认值/tmp/mysql.sock 修改为 /usr/local/mysql/mysql.sock，于是直接使用mysql -uroot -p登陆，socket参数的值是正确的。

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0