link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

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

ASKER CERTIFIED SOLUTION

David Favor

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

arnold

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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.506190 Z 0 [Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/mysqld_t
mp_file_case_insensitive_t est.lower- test
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.506229 Z 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.506359 Z 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.509867 Z 0 [Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/mysqld_t
mp_file_case_insensitive_t est.lower- test
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.509880 Z 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.509960 Z 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.510081 Z 0 [MY-010119] [Server] Aborting
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.510300 Z 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

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

sunhux

ASKER

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

SOLUTION

arnold

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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:mysq ld_db_t:s0 mysqld_tmp_file_case_insen sitive_tes t.lower-te st

sunhux

ASKER

Contents of /etc/my.conf :
datadir=/opt/MySQL_DATA/my sql
socket=/var/lib/mysql/mysq l.sock => had earlier changed it to /opt/MySQL_DATA/mysql/mysq l.sock
#socket=/opt/MySQL_DATA/my sql/mysql. sock
log-error=/var/log/mysqld. log
pid-file=/var/run/mysqld/m ysqld.pid
[client]
port=3306
socket=/var/lib/mysql/mysq l.sock => had earlier changed it to /opt/MySQL_DATA/mysql/mysq l.sock
#socket=/opt/MySQL_DATA/my sql/mysql. sock

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/my sql
Jan 16 11:45:08 mySrvr mysqld: 2020-01-16T03:45:08.951346 Z 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.951467 Z 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.951611 Z 0 [MY-010119] [Server] Aborting
Jan 16 11:45:08 mySrvr mysqld: 2020-01-16T03:45:08.951905 Z 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_d b_t:s0
system_u:object_r:mysqld_d b_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/...

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.inde x
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_tem p/temp_1.i bt
mysqld 20750 mysql 17uW REG 253,0 81920 975352 /var/lib/mysql/#innodb_tem p/temp_2.i bt
mysqld 20750 mysql 18uW REG 253,0 81920 975353 /var/lib/mysql/#innodb_tem p/temp_3.i bt
mysqld 20750 mysql 19uW REG 253,0 81920 975354 /var/lib/mysql/#innodb_tem p/temp_4.i bt
mysqld 20750 mysql 20uW REG 253,0 81920 975355 /var/lib/mysql/#innodb_tem p/temp_5.i bt
mysqld 20750 mysql 21uW REG 253,0 81920 975356 /var/lib/mysql/#innodb_tem p/temp_6.i bt
mysqld 20750 mysql 22uW REG 253,0 81920 975357 /var/lib/mysql/#innodb_tem p/temp_7.i bt
mysqld 20750 mysql 23uW REG 253,0 81920 975358 /var/lib/mysql/#innodb_tem p/temp_8.i bt
mysqld 20750 mysql 24uW REG 253,0 81920 975359 /var/lib/mysql/#innodb_tem p/temp_9.i bt
mysqld 20750 mysql 25uW REG 253,0 81920 975360 /var/lib/mysql/#innodb_tem p/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.0000 17
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-re lay-bin.in dex
mysqld 20750 mysql 34u IPv6 3697780 0t0 TCP *:33060 (LISTEN)
mysqld 20750 mysql 35w REG 253,0 155 69499745 /var/lib/mysql/jpcwsu01-re lay-bin.00 0013
mysqld 20750 mysql 36u unix 0xffff8800ae4b3800 0t0 3697782 /var/run/mysqld/mysqlx.soc k

SOLUTION

arnold

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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/my sql <== added this
datadir=/opt/MySQL_DATA/my sql
socket=/opt/MySQL_DATA/mys ql/mysql.s ock <== added this
logdir=/opt/MySQL_DATA/mys ql/tmp <== added this
rundir=/opt/MySQL_DATA/mys ql/tmp <== added this

SOLUTION

arnold

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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

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.969985 Z 0 [Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/mysqld_tmp_ file_case_ insensitiv e_test.low er-test
Jan 16 14:37:02 mysqld: 2020-01-16T06:37:02.970025 Z 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.970174 Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 22740

SOLUTION

arnold

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

arnold

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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.i ni
./selinux/targeted/context s/sepgsql_ contexts
./selinux/targeted/active/ modules/10 0/hsqldb
./selinux/targeted/active/ modules/10 0/mysql
./selinux/targeted/active/ modules/10 0/postgres ql
./selinux/targeted/tmp/mod ules/100/h sqldb
./selinux/targeted/tmp/mod ules/100/m ysql
./selinux/targeted/tmp/mod ules/100/p ostgresql
./systemd/system/multi-use r.target.w ants/mysql d.service
./ld.so.conf.d/mysql-x86_6 4.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

SOLUTION

arnold

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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