Question MariaDB change data directory location

[BrianHyper]

Hi,

I'm trying to change the MariaDB data directory from /var/lib/mysql to /my_folder/mysql.
Everything is good I can log into mysql in command line but plesk give me a hard time with Zend Errors.

I've change my /etc/my.cnf and my /etc/psa/psa.conf to match with the new mysql location but still get problems.

Did I have to change something else or reconfigure something?

Here's the error I get when trying to access my server :


ERROR: Zend_Db_Adapter_Exception: SQLSTATE[HY000] [2002] No such file or directory

Additionally, an exception has occurred while trying to report this error: Zend_Exception
No entry is registered for key 'translate' (Abstract.php:144)

Search for related Knowledge Base articles



Thx for the help!

 

[BrianHyper]

I've create a symbolic link to workaround... it's not a good thing I think but for now it work...
It look like something is still configured to the /var/lib/mysql but I can't figure out where

If someone have a better answer it would be really appreciated I will need to do the same thing on other servers soon.

 

[IgorG]

Have you tried this solution - How to change a default MySQL directory /var/lib/mysql to another? ?

 

[BrianHyper]

Yes I already did that... but still got the problem

I just hope that when I'll do a plesk update this will not cause trouble...

 

[fferraro87]

Have you tried this solution - How to change a default MySQL directory /var/lib/mysql to another? ?

do you can send correct link?

 

[IgorG]

do you can send correct link?

Warning: Changing the location for a MySQL server is an administrative task which is not officially supported by Plesk. The solution should be applied on your risk.
As an alternative to moving the MySQL data directory to another location, consider mounting a partition from a new disk to the default MySQL data directory location /var/lib/mysql/

1. Create a backup of /etc/my.cnf(CentOS/RHEL) or /etc/mysql/my.cnf(Debian/Ubuntu)
   # cp /etc/my.cnf{,_backup}
2. Copy or move all files from /var/lib/mysql to the desired directory and set the permissions:
   # cp -pr /var/lib/mysql /data/mysql
   # chown -R mysql:mysql /data/mysql
3. Update /etc/my.cnf or /etc/mysql/my.cnf(Debian/Ubuntu) using vi text editor and change the location to where you have copied mysql directory:
   # vi /etc/my.cnf
   datadir=/data/mysql
   Note: Be sure that the path to socket mysql.sock is unchanged in my.cnf and it is /var/lib/mysql. Also the directory /var/lib/mysql itself should present on the server as changing the path to the socket can cause the Plesk to crush.
4. Create a configuration file:
   # touch /etc/systemd/system/mariadb.service.d/mariadb_custom.conf
5. Add necessary content into the created file:
   # echo "[Service]" >> /etc/systemd/system/mariadb.service.d/mariadb_custom.conf
   # echo "ProtectHome=false" >> /etc/systemd/system/mariadb.service.d/mariadb_custom.conf
6. Create a backup of /etc/passwd:
   # cp -a /etc/passwd{,.bak}
7. Modify the string below in /etc/passwd and set up the new MySQL location:
   mysql:x:27:27:MariaDB Server:/data/mysql:/sbin/nologin
8. Restart mysqld:
   Debian/Ubuntu:
   # service mysqld restart
   CentOS:
   # service mariadb restart
9. Update /etc/psa/psa.conf with a path to the new mysql directory:
   MYSQL_VAR_D /data/mysql
10. In case of Ubuntu 16.04, disable mysql in apparmor:
    Modify file /etc/apparmor.d/usr.sbin.mysqld (create it if it doesn't exist using vi editor), add:
    /usr/sbin/mysqld {
    }
    # rm /etc/apparmor.d/disable/usr.sbin.mysqld
    # apparmor_parser -a /etc/apparmor.d/usr.sbin.mysqld
    # aa-disable /etc/apparmor.d/usr.sbin.mysqld
11. For CentOS/RHEL, SELinux context should be changed or it should be disabled.

 

[nisamudeen97]

Hi,

I have followed the below steps in my latest Plesk Obsidian Version 18.0.62 which is running on AlmaLinux 8.10. I was referring the doc https://grepitout.com/change-mysql-data-directory-linux/ which works good on normal centos/almal linux servers. But with latest plesk panel, the above steps are not enough. With the above steps, mysql service will come up, but plesk panel will start throwing database errors. The database connections via websites will also wont work. Plesk panel still looks for /var/lib/mysql/mysql.sock. file hence the issues. I have followed the below steps and was able to make this working 100%.

In my case i was trying to move data directory from "/var/lib/mysql" to "/home/MYSQL_DATA/" as root partion was not having enough space and databases in the server was fast growing. I had 8TB partition for /home/.

1. Take necessary backups of mysql databases so that if some thing goes wrong, we can restore data using the same. You can refer below link

https://support.plesk.com/hc/en-us/...a-a-command-line-interface-in-Plesk-for-Linux

Stop Mysql / Mariadb service. In mycase it was mariadb.

service mariadb stop

2. Copy mysql data directory to new location

cp -pr /var/lib/mysql /home/MYSQL_DATA/
chown -R mysql.mysql /home/MYSQL_DATA/

4. Move existing /var/lib/mysql

mv /var/lib/mysql /var/lib/mysql _bak

5. Edit my.cnf loaded, update the below parameter, Important note:- socket=/var/lib/mysql/mysql.sock this should remain unchanged. In normal procedure

socket paramer can also be changed.

datadir=/home/MYSQL_DATA/

6. Recrate /var/lib/mysql as plesk looks for the same and changing the path to the socket can cause the Plesk to crash. Just folder is enough, we just it for creation of socket file during start process of service.

mkdir /var/lib/mysql

7. Update the system demon configuration file for mysql / mariadb service. We have to update ExecStart parameter with new data directory path.

edit file /usr/lib/systemd/system/mariadb.service
ExecStart=/usr/libexec/mysqld --basedir=/usr $MYSQLD_OPTS $_WSREP_NEW_CLUSTER
To
ExecStart=/usr/libexec/mysqld --datadir=/home/MYSQL_DATA/mysql --basedir=/usr $MYSQLD_OPTS $_WSREP_NEW_CLUSTER
systemctl daemon-reload

8. Create a backup of /etc/passwd and change mysql users home directory.

mysql:x:27:27:MariaDB Server:/home/MYSQL_DATA:/sbin/nologin

9. Update /etc/psa/psa.conf with a path to the new mysql directory, so that plesk understands the new path. Change MYSQL_VAR_D from /var/lib/mysql to below. Important note:- socket MYSQL_SOCKET should remain unchanged.

MYSQL_VAR_D /home/MYSQL_DATA/mysql

10. For Almal linux, SeLinux should be disabled.

11. Start the Mysql / Mariadb service.

service mariadb restart

I was able to make the mysql data directory move with the above step of steps. All worked perfect.