How to connect with a file socket and not with the TCP protocol?

I can connect with the command:

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

But I cannot connect with the command:

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

It fails as in:

<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="90f5e5e2ffe0f1e3e0e2f1fbd0e6e0e3bda3a5a0a6f2a0a8a3">[email protected]</a>:~/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)

The socket file exists:

<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="355040475a4554464547545e75434546180600050357050d06">[email protected]</a>:~/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

And my PHP application cannot connect with the statement:

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

When the MariaDB server starts its console shows:

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

The phpinfo() shows the myslqi to be enabled and having the values:

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

The etc/my.cnf file contains:

[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

The MariaDB server is started with the command:

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

UPDATE and solution:

I was missing a loaded my.cnf file:

<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="680d1d1a0718091b181a0903281e181b455b5d585e0a58505b">[email protected]</a>:~/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 
<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="583d2d2a3728392b282a3933182e282b756b6d686e3a68606b">[email protected]</a>:~/programs/mariadb/install$ ll /etc/mysql/my.cnf
ls: cannot access '/etc/mysql/my.cnf': No such file or directory
<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="5f3a2a2d302f3e2c2f2d3e341f292f2c726c6a6f693d6f676c">[email protected]</a>:~/programs/mariadb/install$ ll /etc/my.cnf
ls: cannot access '/etc/my.cnf': No such file or directory

Creating a simlink on the file solved the issue:

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Leave out the port number from your connect strings. You don’t use ports when you connect via sockets; that’s a TCP thing.

Try this:
bin/mysql -u europasprak -p db_europasprak
or
mysqli_connect("localhost", "europasprak", "examplepassword", "db_europasprak")


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x