mysql更改配置文件socket路径后登录报错：Can't connect to local MySQL server through socket '/var/lib/mysql.sock'

mysql或pxc的配置文件 /etc/my.cnf 中若更改了socket路径

[mysqld]
datadir=/mysql
port=3306
socket=/mysql/mysql-node1.sock #socket不要指定，配了反而会出问题，让它自己去生成 /var/lib/mysql/mysql.sock
### 可以不指定pid-file路径，默认生成路径为 $datadir/$hostname.pid ####
pid-file=/mysql/mysql-node1.pid
### 建议指定log-error路径，不指定log-error的话，也会生成一个 $datadir/$hostname.err，但该文件中经常出现找不到临时密码，导致无法登录数据库 ####
log-error=/mysql/mysql-node1.err
default_storage_engine=InnoDB
character-set-server=utf8
log_timestamps=SYSTEM #默认是UTC,需要我们指定为SYSTEM
max_connections=5000

mysql服务可以启动，但是使用密码登录mysql时却报错：

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

明明 /etc/my.cnf 配置文件中指定的 socket路径为 /mysql/mysql-node1.sock，为什么登录mysql时，找的却是/var/lib/mysql/mysql.sock ，这是因为 socket文件路径在配置文件中设置不完整：我们修改了配置文件/etc/my.cnf中“[mysqld]”选项下的 “socket” 参数，而未指定“[client]”、“[mysql]” 选项的 “socket”参数，导致mysql使用默认的socket文件位置去寻找socket文件，从而导致未找到socket文件而引发此错误。

解决方案：

追加 [mysql]、[client] 选项

[mysqld]
datadir=/mysql
port=3306
socket=/mysql/mysql-node1.sock #socket不要指定，配了反而会出问题，让它自己去生成 /var/lib/mysql/mysql.sock
### 可以不指定pid-file路径，默认生成路径为 $datadir/$hostname.pid ####
pid-file=/mysql/mysql-node1.pid
### 建议指定log-error路径，不指定log-error的话，也会生成一个 $datadir/$hostname.err，但该文件中是没有临时密码的，导致无法登录数据库 ####
log-error=/mysql/mysql-node1.err
default_storage_engine=InnoDB
character-set-server=utf8
log_timestamps=SYSTEM #默认是UTC,需要我们指定为SYSTEM
max_connections=5000

[mysql]
socket=/mysql/mysql-node1.sock

[client]
socket=/mysql/mysql-node1.sock

以pxc集群初始化第一个节点为例：

[root@sjk1 ~]# systemctl start mysql@bootstrap.service
[root@sjk1 ~]# grep 'temporary password' /mysql/mysql-node1.err 
2019-08-21T01:28:25.078055+08:00 1 [Note] A temporary password is generated for root@localhost: oIRs48j._qp)
[root@sjk1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
......

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%character%';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user root@localhost identified by 'sdbrk';
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%character%';
+--------------------------+---------------------------------------------+
| Variable_name            | Value                                       |
+--------------------------+---------------------------------------------+
| character_set_client     | utf8                                        |
| character_set_connection | utf8                                        |
| character_set_database   | utf8                                        |
| character_set_filesystem | binary                                      |
| character_set_results    | utf8                                        |
| character_set_server     | utf8                                        |
| character_set_system     | utf8                                        |
| character_sets_dir       | /usr/share/percona-xtradb-cluster/charsets/ |
+--------------------------+---------------------------------------------+
8 rows in set (0.00 sec)

mysql> 

 

参考文章：

https://www.cnblogs.com/niuli1987/p/9890328.html