Change MySQL data directory in PHPMyAdmin

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:

image 77 - Change MySQL data directory in PHPMyAdmin

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:

image 78 - Change MySQL data directory in PHPMyAdmin

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 file


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

nano /usr/local/cpanel/base/3rdparty/phpMyAdmin/

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';
image 79 1024x658 - Change MySQL data directory in PHPMyAdmin

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
Stefan Pejcic
Join the discussion

I enjoy constructive responses and professional comments to my posts, and invite anyone to comment or link to my site.