Home / server / Questions / 673854
Accepted
gijs007
Asked: 2015-03-09 08:42:43 +0800 CST

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

  • 772

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/
mysql

2 Answers

  1. Best Answer

    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';
    • 4

  2. In libraries/joomla/database/database.php is defined the abstract class JDatabase, which then is implemented for different database types in libraries/joomla/database/driver/, in this case mysql or mysqli..

    In mysqli.php (Joomla! 3.3.6) __construct($options) sets defaults if nothing specified in configuration.php, which will change it into 'localhost'. From line 64:

    public function __construct($options)
{
// Get some basic values from the options.
$options['host']     = (isset($options['host'])) ? $options['host'] : 'localhost';
$options['user']     = (isset($options['user'])) ? $options['user'] : 'root';
$options['password'] = (isset($options['password'])) ? $options['password'] : '';
$options['database'] = (isset($options['database'])) ? $options['database'] : '';
$options['select']   = (isset($options['select'])) ? (bool) $options['select'] : true;
$options['port']     = null;
$options['socket']   = null;

// Finalize initialisation.
parent::__construct($options);
}

    Then, function connect(), finally uses them to create connection on lines 163-165:

        $this->connection = @mysqli_connect(
        $this->options['host'], $this->options['user'], $this->options['password'],
        null, $this->options['port'], $this->options['socket']
    );

    If nothing else given, mysqli_connect() uses defaults from php.ini for hostname, port and socket

    mysqli mysqli_connect ([ string $host = ini_get("mysqli.default_host") 
   [, string $username = ini_get("mysqli.default_user") 
   [, string $passwd = ini_get("mysqli.default_pw") 
   [, string $dbname = "" 
   [, int $port = ini_get("mysqli.default_port") 
   [, string $socket = ini_get("mysqli.default_socket") ]]]]]] )

    Now you know the whole track affecting these connection parameters. By editing mysqli.php you should be able to enforce settings the configuration.php doesn't allow you to change. However, you should be careful and consider documenting these modifications well, since

    1. configuration.php doesn't work like documented anymore
    2. Joomla updates can (and eventually will) override your modifications
    3. Something else may be depending on this particular configuration
    • 0