Change MySQL data directory in PHPMyAdmin

In a recent article, I discussed many methods for removing the large ibdata file, one of which involves transferring the MySQL data directory to a different partition.

However, if you do so, you must also inform PHPMyAdmin of the location of the data, or you will receive an “Access denied” notice

In this example, I have a new file system mounted under /data and will use it for MySQL data.

Step 1. Edit my.cnf file

nano /etc/my.cnf

Find the datadir and socket entries under [mysql] and update them to point to the new data directory:

So in this example, I’m changing /var to /data

After updating the existing lines, we’ll need to add configuration for the mysql client. Add the following three lines at the end of that file:

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

Step 2. Restart MySQL

systemctl stop mysqld
systemctl start mysqld

Now We need to reconfigure PHPMyAdmin to use socket authentication and point to the new location of the MySQL socket.

Step 3. Edit the config.inc.php file

I’m using cPanel, so I will edit the /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php file

nano /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php

Change the socket and data_type. By default, the data_type would be set to tcp. Change it to socket instead and set the new location of the socket:

$cfg['Servers'][$i]['socket'] = '/data/mysql/mysql.sock';
$cfg['Servers'][$i]['connect_type'] = 'socket';

NOTE: For cPanel, you also need to disable WHM MySQL monitoring to prevent restarts while making changes: whmapi1 configureservice service=mysql enabled=1 monitored=0

Step 4. restart Apache and you should be good to go.

service httpd restart