How to let PHP connect to database by using UNIX socket (Joomla)

mysql php unix socket joomla

23,057

127.0.0.1 is used for TCP socket.

'localhost' is used for Unix filesystem socket.

We can use netstat -ln | grep 'mysql' to determine the connection method.And explore option to enforce a particular type

shell> mysql --host=127.0.0.1
shell> mysql --protocol=TCP
shell> mysql --host=localhost
shell> mysql --host=localhost --protocol=TCP

The connection parameters should be like this

$link = mysql_connect('localhost:/var/run/mysqld/mysqld.sock', 'mysql_user', 'mysql_password');

var $host = ':/var/run/mysqld/mysqld.sock';
var $user = 'your_user_db_name';
var $db = 'your_db_name';
var $password = 'your_db_password';

23,057

gijs007

Updated on September 18, 2022

Comments

gijs007 over 1 year

I'd like to have my PHP applications such as Joomla make use of the UNIX socket to connect to the local mysql database.

I've already configured the php.ini with:

mysqli.default_socket = /var/run/mysqld/mysqld.sock
mysql.default_socket = /var/run/mysqld/mysqld.sock
pdo_mysql.default_socket=/var/run/mysqld/mysqld.sock

But in Joomla I can only set localhost or 127.0.0.1 as database, is it possible to override this? How can I test the UNIX socket is actually being used?

My MySQL/MariaDB is configured like:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
bind-address        = 127.0.0.1
max_connections     = 500
connect_timeout     = 10
wait_timeout        = 600
max_allowed_packet  = 16M
thread_cache_size       = 1000
sort_buffer_size    = 512M
bulk_insert_buffer_size = 16M
tmp_table_size      = 8G
max_heap_table_size = 8G

myisam_recover          = FORCE,BACKUP
key_buffer_size     = 128M
open-files-limit    = 65535
table_open_cache    = 10240
table_open_cache_instances = 8
table-definition-cache = 4096
myisam_sort_buffer_size = 512M
key-cache-segments=8
concurrent_insert   = 2
read_buffer_size    = 32M
read_rnd_buffer_size    = 64M

query_cache_limit       = 96M
query_cache_size        = 128M
query_cache_min_res_unit=7108
query_cache_type        = 1

log_warnings        = 2

slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
log_slow_verbosity  = query_plan


log_bin         = /var/log/mysql/mariadb-bin
log_bin_index       = /var/log/mysql/mariadb-bin.index
sync_binlog     = 1
expire_logs_days    = 14
max_binlog_size         = 100M

default_storage_engine  = InnoDB
innodb_log_file_size    = 50M
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances=8
innodb_log_buffer_size  = 32M
innodb_file_per_table   = 1
innodb_concurrency_tickets=5000
innodb_open_files   = 240000
innodb_io_capacity  = 240000
innodb_flush_method = O_DIRECT
innodb-log-files-in-group      = 2
innodb-flush-log-at-trx-commit = 1

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 512M

!includedir /etc/mysql/conf.d/

gijs007 about 9 years

When using localhost joomla is unable to connect to the MySQL server: Error displaying the error page: Application Instantiation Error: Could not connect to MySQL. I thought it was because of the line; skip-name-resolve in the mysql config, but after removing this and rebooting the mysql server the issue persists.
koustuv about 9 years

checkout ':/var/run/mysqld/mysqld.sock' and confirm. I mean $config['db_hostname'] = ':/var/run/mysqld/mysqld.sock'; Make sure you have right permission on the file in use. Reboot mysql if needed.
gijs007 about 9 years

How can I open the socket "file"? Nano says it doesn't exist but when looking it up with the LS command its there (in pink) Where should I place the: $config['db_hostname'] = ':/var/run/mysqld/mysqld.sock'
koustuv about 9 years

Joomla configuration.php you will get public $host
gijs007 about 9 years

When I do that I get a blank page.
koustuv about 9 years

Can you post $host and $dbtype here
koustuv about 9 years

Let us continue this discussion in chat.
Admin about 9 years

Using "localhost" as the hostname means that PHP will try to connect to the local UNIX socket before trying via TCP/IP.
Vladimir Panteleev over 5 years

If you are using mysqli instead of the now-deprecated mysql, the socket path is now a separate parameter. Trying to apply this answer to mysqli will not work.