MySQL 8.0, Ubuntu 20: Permission Error after Changing Datadir after Reinstall

Question

hope to be in the right place to ask this.

I previously used a Debian cloud instance with an added data drive to store a MySQL database. I moved the datadir there and it worked fine for several months.

A few weeks ago, the cloud instance crashed, so I had to rebuild the instance (as Ubuntu), deleting the main drive and MySQL. The data disk on which the data dir was stored seems fine, all the files (ibd files etc) are complete.

I reinstalled MySQL Server 8.0 and now tried to reset the data dir to the path where I put the data dir previously.

I changed the ownership to mysql, changed the rights (user mysql has read write access). Still, when I try to restart mysql, I get permission denied error.

To test where the issues are, I did the standard procedure of defining a new datadir following the instructions found elsewhere here. I copied the /var/lib/mysql to a new folder on the mounted drive (/media/newhd/pseudo/mysql).

datadir =       /media/newhd/pseudo/mysql
socket=   /media/newhd/pesudo/mysql/mysql.sock

I deactivated AppArmor, SELinux is not installed. I also changed ownership and permissions:

drw-rw-rw- 15 mysql  mysql         4096 May  7 20:32 mysql

I still get this error

    : 2022-05-07T21:09:17.973463Z 0 [Warning] [MY-010091] [Server] Can't create test file /media/newhd/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
: 2022-05-07T21:09:17.973531Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.29-0ubuntu0.22.04.2) starting as process 87289
: 2022-05-07T21:09:17.977076Z 0 [Warning] [MY-010091] [Server] Can't create test file /media/newhd/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
: 2022-05-07T21:09:17.977085Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /media/newhd/mysql/ is case insensitive
: 2022-05-07T21:09:17.977233Z 0 [ERROR] [MY-013276] [Server] Failed to set datadir to '/media/newhd/mysql/' (OS errno: 13 - Permission denied)
: 2022-05-07T21:09:17.977335Z 0 [ERROR] [MY-010119] [Server] Aborting
: 2022-05-07T21:09:17.977485Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.29-0ubuntu0.22.04.2)  (Ubuntu).
ysql.service: Main process exited, code=exited, status=1/FAILURE

Still, permission denied...

Anyone have an idea?

Thanks in advance, Marius

Answer 1

The solution was to remove Ubuntu, reinstall debian + mysql in the same way as installed on debian. The error on the old datadir persisted, but moving the data dir now suceeded. Copying all files from the old datadir in the new one recovered the database in full.