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

 

posted @ 2019-05-10 17:22  AYard  阅读(45)  评论(0编辑  收藏