MySQL – Resolving Connection Issues from Apache Server

centosMySQL

I am operating on Centos7 and Mysql 5.6.46. I needed to move the default datadir location to an encrypted partition and I changed my default /etc/my.cnf file accordingly:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/NEW_FOLDER/mysql
socket=/NEW_FOLDER/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
sql_mode=""

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port=3306
socket=/NEW_FOLDER/mysql/mysql.sock

After restarting, I verify that I can connect to Mysql by writing: mysql -u <USERNAME> -p, no issue there.
What my issue is has to do with when my CGI-based server is trying to access the DB. I get the error: Error connecting to database: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) which does not make much sense to me since the default socket directory has changed.

I looked into the Apache error log and I see this:

DBI connect('testuser:localhost','testdb',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /srv/www/TestSite/perl-lib/Database.pm line 31.

I go to line 31 and I see this:

my $dsn = "DBI:mysql:testdb:localhost";
    $dbh = DBI->connect($dsn, 'testuser', 'testpasswd')
      or throw Portal::DatabaseError("Error connecting to database: $DBI::errstr");

The way I see it, Apache is trying to access Mysql but it cannot use the mysql.socket from the new default mysql datadir location. Any ideas what to do?

Best Answer

put the new socket location under the [client] section (as well as [mysqld] like:

[client]
socket=/NEW_FOLDER/mysql/mysql.sock