mysql - 如何连接文件套接字而不是 TCP 协议？

问题描述

我可以使用以下命令连接：

bin/mysql -h localhost -P 3306 --protocol=tcp -u europasprak -p db_europasprak

但我无法连接命令：

bin/mysql -P 3306 -u europasprak -p db_europasprak

它失败如下：

europasprak@vps-3506b083:~/programs/mariadb/install$ bin/mysql -P 3306 -u europasprak -p db_europasprak
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '~/programs/mariadb/install/tmp/mariadb.sock' (2)

套接字文件存在：

europasprak@vps-3506b083:~/programs/mariadb/install$ ll ~/programs/mariadb/install/tmp/mariadb.sock
srwxrwxrwx 1 root root 0 juil. 11 20:20 /home/europasprak/programs/mariadb/install/tmp/mariadb.sock

并且我的 PHP 应用程序无法连接语句：

mysqli_connect("localhost", "europasprak", "examplepassword", "db_europasprak", "3306")

当 MariaDB 服务器启动时，它的控制台显示：

210711 20:20:56 mysqld_safe Starting mariadbd daemon with databases from /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data
2021-07-11 20:20:56 0 [Note] /home/europasprak/programs/mariadb/install/bin/mariadbd (mysqld 10.5.9-MariaDB-log) starting as process 3477606 ...
2021-07-11 20:20:56 0 [Note] InnoDB: Uses event mutexes
2021-07-11 20:20:56 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-07-11 20:20:56 0 [Note] InnoDB: Number of pools: 1
2021-07-11 20:20:56 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2021-07-11 20:20:56 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
2021-07-11 20:20:56 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
2021-07-11 20:20:56 0 [Note] InnoDB: Completed initialization of buffer pool
2021-07-11 20:20:56 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 100663296 bytes
2021-07-11 20:20:56 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2021-07-11 20:20:56 0 [Note] InnoDB: New log file created, LSN=957285982
2021-07-11 20:20:56 0 [Note] InnoDB: 128 rollback segments are active.
2021-07-11 20:20:56 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-07-11 20:20:56 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-07-11 20:20:56 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-07-11 20:20:56 0 [Note] InnoDB: 10.5.9 started; log sequence number 0; transaction id 9570669
2021-07-11 20:20:56 0 [Note] InnoDB: Loading buffer pool(s) from /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data/ib_buffer_pool
2021-07-11 20:20:56 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-07-11 20:20:56 0 [Note] Server socket created on IP: '0.0.0.0'.
2021-07-11 20:20:56 0 [Note] Reading of all Master_info entries succeeded
2021-07-11 20:20:56 0 [Note] Added new Master_info '' to hash table
2021-07-11 20:20:56 0 [Note] /home/europasprak/programs/mariadb/install/bin/mariadbd: ready for connections.
Version: '10.5.9-MariaDB-log'  socket: '/home/europasprak/programs/mariadb/install/tmp/mariadb.sock'  port: 3306  Source distribution
2021-07-11 20:20:56 0 [Note] InnoDB: Buffer pool(s) load completed at 210711 20:20:56

显示phpinfo()要myslqi启用并具有以下值：

Client API library version  mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $
Active Persistent Links 0
Inactive Persistent Links   0
Active Links    0
Directive   Local Value Master Value
mysqli.allow_local_infile   Off Off
mysqli.allow_persistent On  On
mysqli.default_host no value    no value
mysqli.default_port 3306    3306
mysqli.default_pw   no value    no value
mysqli.default_socket   /home/europasprak/programs/mariadb/install/tmp/mysql.sock   /home/europasprak/programs/mariadb/install/tmp/mysql.sock
mysqli.default_user no value    no value
mysqli.max_links    Unlimited   Unlimited
mysqli.max_persistent   Unlimited   Unlimited
mysqli.reconnect    Off Off
mysqli.rollback_on_cached_plink Off Off

该etc/my.cnf文件包含：

[mysqld]
bind-address    = 0.0.0.0 # Allow client binding from any IP address instead of just 127.0.0.1
port            = 3306
sql_mode        = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION # This is strict mode: NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
user            = root
basedir         = /home/europasprak/programs/mariadb/install
datadir         = /home/europasprak/dev/docker/projects/common/volumes/database/mariadb/data
log-error       = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.error.log
general_log     = 1
general-log-file     = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.log
slow-query-log-file  = /home/europasprak/dev/docker/projects/common/volumes/logs/mariadb.slow.queries.log
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
wait_timeout            = 28800 # amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection
interactive_timeout     = 28800 # same, but for interactive sessions
max_allowed_packet = 256M
net_write_timeout = 180

# Creating a binary log for change data capture
server-id = 1
log_bin = master
expire_logs_days = 1
binlog_format = row
binlog_row_image = full

[client]
socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock
loose-default-character-set = utf8mb4

[mysql]
loose-default-character-set = utf8mb4

[mysqladmin]
socket          = /home/europasprak/programs/mariadb/install/tmp/mariadb.sock

MariaDB 服务器使用以下命令启动：

cd /home/europasprak/programs/mariadb/install
./bin/mysqld_safe --defaults-file=/home/europasprak/programs/mariadb/install/etc/my.cnf

更新和解决方案：

我错过了一个加载的my.cnf文件：

europasprak@vps-3506b083:~/programs/mariadb/install$ bin/mysqld --verbose --help | grep -A 1 "Default options"
bin/mysqld: One can only use the --user switch if running as root
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 
europasprak@vps-3506b083:~/programs/mariadb/install$ ll /etc/mysql/my.cnf
ls: cannot access '/etc/mysql/my.cnf': No such file or directory
europasprak@vps-3506b083:~/programs/mariadb/install$ ll /etc/my.cnf
ls: cannot access '/etc/my.cnf': No such file or directory

在文件上创建一个 simlink 解决了这个问题：

 ln -s /home/europasprak/programs/mariadb/install/etc/my.cnf ~/.my.cnf

标签： mysqlmariadb

mysql - 如何连接文件套接字而不是 TCP 协议？

问题描述

解决方案

推荐阅读