Avatar of sunhux
sunhux
 asked on

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

We tried to move current mySql DB out of Linux system
partition by following the instructions in the links below
but mySql gave an error after migration:

https://www.digitalocean.com/community/tutorials/how-to-change-a-mysql-data-directory-to-a-new-location-on-centos-7

& how do I change the location of the data directory? (Doc ID 1023178.1)
https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04#step-3-%E2%80%94-configuring-apparmor-access-control-rules

# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

DBA colleague told me there's not much of a clue from journalctl -xe
We're on RHEL 7.   Have also tried to create soft links to new partition
but it did not work.

What's missed?
Hopefull don't have to export all data out, recreate DB & re-import:
we don't want this option
LinuxMySQL Server

Avatar of undefined
Last Comment
David Favor

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
David Favor

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
David Favor

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

ASKER
>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.
SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sunhux

ASKER
DBA made the dir  world writeable as well,
still getting the same but of course that's
based on his words
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sunhux

ASKER
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?
SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sunhux

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

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sunhux

ASKER
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.
sunhux

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

ASKER
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/...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
sunhux

ASKER
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
SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sunhux

ASKER
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
SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sunhux

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sunhux

ASKER
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
SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sunhux

ASKER
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
SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sunhux

ASKER
# 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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
David Favor

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

Open 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.
David Favor

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.