PhpMyAdmin and changed MySQL data directory

Very often you would like to change the default data location of MySQL for obvious reasons. If you do that and you don’t change the phpmyadmin settings accordingly, you will not be able to login via phpmyadmin. You will constantly be getting the “Access denied” message.

Let’s say that you have added a new file system mounted under /data and you would like all MySQL data to be stored there.

Normally I set this up before I ever create new databases on the server. But in case you are moving existing databases from one data dir to another you need to copy them over to the new location first, otherwise go directly to editing the my.cnf file below.

Stop mysql:

$ sudo systemctl stop mysqld

Now that the server is shut down, we’ll copy the existing database directory to the new location with rsync. Using the -a flag preserves the permissions and other directory properties, while -v  provides verbose output so you can follow the progress.

Note: Be sure there is no trailing slash on the directory, which may be added if you use tab completion. When there’s a trailing slash, rsync will dump the contents of the directory into the mount point instead of transferring it into a containing mysql directory:

$ sudo rsync -av /var/lib/mysql /data

Now you are ready to change the MySQL configuration. To do that you need to update the /etc/my.cnf (CentOS/RHEL) /etc/mysql/my.cnf (Ubuntu) file:

$ sudo vi /etc/my.cnf

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

[mysql]
datadir=/data/mysql
socket=/data/mysql/mysql.sock

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, right above the !includedir /etc/mysql/conf.d/ line and the comment above it:

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

We are all set with the MySQL configuration and we can start db the server:

$ sudo systemctl start mysqld

If you have phpMyAdmin installed you need to reconfigure it to use socket authentication and point to the new location of the mysql socket. If you don’t do this, you will not be able to login via phpMyAdmin.

Edit the /etc/phpMyAdmin/config.inc.php (CentOS/RHEL) /etc/phpmyadmin/config.inc.php (Ubuntu) file:

$ sudo vi /etc/phpMyAdmin/config.inc.php

Change the socket and data_type. Normally the data_type would be set to ‘tcp’. Change it to ‘socket’ instead. Then set the new location of the socket:

$cfg['Servers'][$i]['socket'] = '/data/mysql/mysql.sock';          // Path to the socket - leave blank for default socket
$cfg['Servers'][$i]['connect_type'] = 'socket';       // How to connect to MySQL server ('tcp' or 'socket')

You are all set. Restart Apache and you should be good to go.