migrating mySql DB to another partition resulted in mySql cant start with an exit code

Also I best ask for a bit more detail about why you're doing this.

For example, you might be setting up a Docker volume or some other reason.

Same steps + there may be other considerations too.

Suggestion: Consider opening another question describing the problem which triggered your requirement to move /var/lib/mysql somewhere.

Likely you'll have some great suggestions about problem resolution.

>more detail about why you're doing this.
It's an Audit requirement as part of mySql hardening.


mySql startup logs as David requested:
Jan 14 14:37:01 ourServer systemd: Starting Session 4582 of user root.
Jan 14 14:39:23 ourServer systemd: Starting MySQL Server...
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.506190Z 0 [Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/mysqld_t
mp_file_case_insensitive_test.lower-test
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.506229Z 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv:
Location is accessible to all OS users. Consider choosing a different directory.
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.506359Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as pr
ocess 31605
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.509867Z 0 [Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/mysqld_t
mp_file_case_insensitive_test.lower-test
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.509880Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.509960Z 0 [MY-013276] [Server] Failed to set datadir to '/var/lib/mysql/' (OS errno: 13 - Permission denied)
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.510081Z 0 [MY-010119] [Server] Aborting
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.510300Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
Jan 14 14:39:58 ourServer systemd: mysqld.service: main process exited, code=exited, status=1/FAILURE
Jan 14 14:39:58 ourServer systemd: Failed to start MySQL Server.
Jan 14 14:39:58 ourServer systemd: Unit mysqld.service entered failed state.
Jan 14 14:39:58 ourServer systemd: mysqld.service failed.

DBA made the dir  world writeable as well,
still getting the same but of course that's
based on his words

Is there any method or tools in RHEL7 that could
assist in troubleshootg which folder / files mysql
startup attempts to access?

is 'lsof' good enough?  Or anyone has a script
that will trace based on the pid of mysql, which
files/folders it's attempting to access at startup?

Note that we could create the lower-test file using mysql account
(by issuing  touch ...)

If we leave the original dir  /var/lib/mysql there (ie still writing temp files there),
won't have this issue but Audit wants us to migrate out of /var/lib/mysql to
/opt/...

# ls -lad /var/lib/mysql
lrwxrwxrwx. 1 root root 25 Jan 16 10:36 /var/lib/mysql -> /opt/MySQL_DATA/mysql/tmp

# ls -lZ /var/lib/mysql
lrwxrwxrwx. root root unconfined_u:object_r:var_lib_t:s0 /var/lib/mysql -> /opt/MySQL_DATA/mysql/tmp

# ls -lZ /opt/MySQL_DATA/mysql/tmp
-rw-r--r--. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 mysqld_tmp_file_case_insensitive_test.lower-test

Contents of /etc/my.conf :

datadir=/opt/MySQL_DATA/mysql
socket=/var/lib/mysql/mysql.sock  => had earlier changed it to /opt/MySQL_DATA/mysql/mysql.sock
#socket=/opt/MySQL_DATA/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port=3306
socket=/var/lib/mysql/mysql.sock   => had earlier changed it to /opt/MySQL_DATA/mysql/mysql.sock
#socket=/opt/MySQL_DATA/mysql/mysql.sock

Despite datadir is set to new dir, during startup, it still go to
the old /var/lib/mysql:  what's missed?
# grep datadir /etc/my.cnf
datadir=/opt/MySQL_DATA/mysql


Jan 16 11:45:08 mySrvr mysqld: 2020-01-16T03:45:08.951346Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
Jan 16 11:45:08 mySrvr mysqld: 2020-01-16T03:45:08.951467Z 0 [MY-013276] [Server] Failed to set datadir to '/var/lib/mysql/' (OS errno: 13 - Permission denied)
Jan 16 11:45:08  mySrvr mysqld: 2020-01-16T03:45:08.951611Z 0 [MY-010119] [Server] Aborting
Jan 16 11:45:08 mySrvr mysqld: 2020-01-16T03:45:08.951905Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.

Also, we noted that "ls -lZ ..."  gives  unconfined_u:object_r:var_lib_t:s0
 for the new dir while the old original dir has it as
system_u:object_r:mysqld_db_t:s0

system_u:object_r:mysqld_db_t:s0

Last question:

if we move the DB to new dir (ie new datadir) but
leave the socket & other temp files in the old dir, any
concern?   For me this will fulfill Audit's requiremt
but our DBA felt this is "messy" & a new DBA in
future will be confused as to why datadir is in
/opt/...   while the temp/socket remain in /var/lib/...

What Audit wants is just to move datadir to non-
system (ie not in /var/...) partition & Audit is Ok
with /opt/...

managed to bring up mysqld if we leave the old dir there for
socket & temp files (one of them is the  ..insensitive...testfile)
to write to.

Somehow mysqld still attempts to write the temp/socket files
to the old dir tho datadir has moved:

# ps -ef |grep mysql
mysql    20750     1  1 12:01 ?        00:00:02 /usr/sbin/mysqld

[root@ lib]# lsof -p 20750

mysqld  20750 mysql    4u      REG              253,0       272  69156933 /var/lib/mysql/binlog.index
mysqld  20750 mysql    5uW     REG              253,0  50331648  68639979 /var/lib/mysql/ib_logfile0
mysqld  20750 mysql    6u      REG              253,0         0    975346 /tmp/ibkyqrIu (deleted)
mysqld  20750 mysql    7u      REG              253,0         0    975347 /tmp/ibyyUhtx (deleted)
mysqld  20750 mysql    8u      REG              253,0         0    975348 /tmp/ibM4G9dA (deleted)
mysqld  20750 mysql    9u      REG              253,0         0    975349 /tmp/ib22bqLF (deleted)
mysqld  20750 mysql   10uW     REG              253,0  79691776  68637680 /var/lib/mysql/ibdata1
mysqld  20750 mysql   11uW     REG              253,0  50331648  68639978 /var/lib/mysql/ib_logfile1
mysqld  20750 mysql   12uW     REG              253,0  12582912  68639980 /var/lib/mysql/undo_001
mysqld  20750 mysql   13u      REG              253,0         0    975350 /tmp/ib9lq6YI (deleted)
mysqld  20750 mysql   14uW     REG              253,0  12582912  68639972 /var/lib/mysql/undo_002
mysqld  20750 mysql   15uW     REG              253,0  12582912  69126235 /var/lib/mysql/ibtmp1
mysqld  20750 mysql   16uW     REG              253,0     81920    975351 /var/lib/mysql/#innodb_temp/temp_1.ibt
mysqld  20750 mysql   17uW     REG              253,0     81920    975352 /var/lib/mysql/#innodb_temp/temp_2.ibt
mysqld  20750 mysql   18uW     REG              253,0     81920    975353 /var/lib/mysql/#innodb_temp/temp_3.ibt
mysqld  20750 mysql   19uW     REG              253,0     81920    975354 /var/lib/mysql/#innodb_temp/temp_4.ibt
mysqld  20750 mysql   20uW     REG              253,0     81920    975355 /var/lib/mysql/#innodb_temp/temp_5.ibt
mysqld  20750 mysql   21uW     REG              253,0     81920    975356 /var/lib/mysql/#innodb_temp/temp_6.ibt
mysqld  20750 mysql   22uW     REG              253,0     81920    975357 /var/lib/mysql/#innodb_temp/temp_7.ibt
mysqld  20750 mysql   23uW     REG              253,0     81920    975358 /var/lib/mysql/#innodb_temp/temp_8.ibt
mysqld  20750 mysql   24uW     REG              253,0     81920    975359 /var/lib/mysql/#innodb_temp/temp_9.ibt
mysqld  20750 mysql   25uW     REG              253,0     81920    975360 /var/lib/mysql/#innodb_temp/temp_10.ibt
mysqld  20750 mysql   26uW     REG              253,0  29360128  68639973 /var/lib/mysql/mysql.ibd
...
mysqld  20750 mysql   31w      REG              253,0       155  69143859 /var/lib/mysql/binlog.000017
mysqld  20750 mysql   32u     unix 0xffff880236bae400       0t0   3696672 /var/lib/mysql/mysql.sock
mysqld  20750 mysql   33u      REG              253,0       364  69499746 /var/lib/mysql/jpcwsu01-relay-bin.index
mysqld  20750 mysql   34u     IPv6            3697780       0t0       TCP *:33060 (LISTEN)
mysqld  20750 mysql   35w      REG              253,0       155  69499745 /var/lib/mysql/jpcwsu01-relay-bin.000013
mysqld  20750 mysql   36u     unix 0xffff8800ae4b3800       0t0   3697782 /var/run/mysqld/mysqlx.sock

Updated my.cnf  to use the new dir but still no joy.
Do we need to initialize (ie somethg like
'systemctl restart mysqld --initialize' ?


[mysqld]   <== added this
basedir=/opt/MySQL_DATA/mysql   <== added this
datadir=/opt/MySQL_DATA/mysql
socket=/opt/MySQL_DATA/mysql/mysql.sock   <== added this
logdir=/opt/MySQL_DATA/mysql/tmp   <== added this
rundir=/opt/MySQL_DATA/mysql/tmp   <== added this

All owned by mysql with exact structure:

[root@ mysql]# ls -lad * |grep dr
drwxr-x--x.  2 mysql mysql        6 Jan 16 09:58 #innodb_temp
drwxr-x--x.  2 mysql mysql     4096 Jan  9 17:17 jpwebp
drwxr-x--x.  2 mysql mysql     4096 Jan  9 17:17 jpwebu
drwxr-x--x.  2 mysql mysql     4096 Jan  9 17:18 mysql
drwxr-xr-x.  2 mysql mysql        6 Jan 16 11:54 mysql-files
drwxr-xr-x.  2 mysql mysql        6 Jan 16 11:54 mysql-keyring
drwxr-x--x.  2 mysql mysql     8192 Jan  9 17:17 performance_schema
drwxr-x--x.  2 mysql mysql       27 Jan  9 17:17 sys
drwxrwxrwx. 10 mysql mysql     4096 Jan 16 11:52 tmp <== this is an extra folder I created

[root@ mysql]# cd /var/lib/mysql
[root@ mysql]# ls -lad * |grep dr
drwxr-x--x. 2 mysql mysql     4096 Jan 16 14:29 #innodb_temp
drwxr-x--x. 2 mysql mysql     4096 Jan  9 17:17 jpwebp
drwxr-x--x. 2 mysql mysql     4096 Jan  9 17:17 jpwebu
drwxr-x--x. 2 mysql mysql     4096 Jan  9 17:18 mysql
drwxr-x--x. 2 mysql mysql     4096 Jan  9 17:17 performance_schema
drwxr-x--x. 2 mysql mysql       27 Jan  9 17:17 sys

when starting, the message is as given earlier:

Jan 16 14:37:02  systemd: Starting MySQL Server...
Jan 16 14:37:02 mysqld: 2020-01-16T06:37:02.969985Z 0 [Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
Jan 16 14:37:02  mysqld: 2020-01-16T06:37:02.970025Z 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
Jan 16 14:37:02  mysqld: 2020-01-16T06:37:02.970174Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 22740

There's only one  my.cnf    in the entire system:
it's odd that despite what's set for tmpdir, logdir,
basedir, rundir, those temp files still get created
in the old dir

# cd /etc/system
-bash: cd: /etc/system: No such file or directory

# cd /etc
# find . -name *sql* -print
./pki/rpm-gpg/RPM-GPG-KEY-mysql
./php-zts.d/20-sqlite3.ini
./php-zts.d/30-pdo_sqlite.ini
./php-zts.d/20-mysqlnd.ini
./php-zts.d/30-mysqli.ini
./php-zts.d/30-pdo_mysql.ini
./selinux/targeted/contexts/sepgsql_contexts
./selinux/targeted/active/modules/100/hsqldb
./selinux/targeted/active/modules/100/mysql
./selinux/targeted/active/modules/100/postgresql
./selinux/targeted/tmp/modules/100/hsqldb
./selinux/targeted/tmp/modules/100/mysql
./selinux/targeted/tmp/modules/100/postgresql
./systemd/system/multi-user.target.wants/mysqld.service
./ld.so.conf.d/mysql-x86_64.conf
./php.d/20-sqlite3.ini
./php.d/30-pdo_sqlite.ini
./php.d/20-mysqlnd.ini
./php.d/30-mysqli.ini
./php.d/30-pdo_mysql.ini
./logrotate.d/mysql

First, based on reading the above, your permissions are wrong + MySQL will never work.

Here's how to fix this.

1) Do not ever use a symlink to your datadir. This will work, if you understand MySQL permissions clearly. And there are many subtle nuances to using symlinks which can create hours of wasted time.

Fix: Create /opt/MySQL_DATA/mysql as a directory.

2) MySQL permissions must be exactly correct or MySQL will either fail at start time or during table creation time.

Fix: Set all correct permissions. Refer to your old /var/lib/mysql dir + all files for your exact Distro's permissions.

Guessing, looks like the following command will fix your problems (after you do #1).

chown -R mysql:mysql /opt/MySQL_DATA/mysql

Select allOpen in new window

3) Be sure to check your original /var/lib/mysql files for any Distro specific files, like flag files. Best to set these to root ownership, as is required in some cases.

Said another way, having any root ownership of MySQL files will fail with write errors on all the major Distros I've worked with over the years, including RedHat/CentOS/Fedora + Debian/Ubuntu.