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>
参考文章: