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