We help IT Professionals succeed at work.

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

sunhux
sunhux asked
on
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
Comment
Watch Question

Fractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Change datadir is fairly straight forward.

Provide all steps you issued to move your /var/lib/mysq/* files.

Also attach a copy of you're mysqld startup log, usually found in /var/log/mysql for analysis.

Note: Please attach all data as text, rather than images.

Note: There will never be any useful output from systemd commands. The actually mysqld log file will show the exact problem.
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Make sure the path to where you moved it is accessible by the MySQL user as well as the folder is owned by MySQL.

the question did you move all ..

Without a description what is the new location?
If you needed more space, an option is to create a dedicated partition that will mount on /var/lib/mysql

While MySQL is off
And the new partition mounted as /mnt
Cd /var/lib/mysql
Find . |cpio -pdvmu /mnt
cd ..
Mv mysql mysql.orig
Mkdir mysql
Chown mysql:mysql mysql
Umount /mnt
Mount the new partition on /var/lib/mysql
Confirm ownership start MySQL
Edir /etc/fstab to make sure the new partition mounts over /var/lib/mysql
David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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.

Author

Commented:
>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.
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
You are trying to relocate a privilege the error, failure to create means access, check permission.

All errors point to permission errors on /var/lib/mysql

Author

Commented:
DBA made the dir  world writeable as well,
still getting the same but of course that's
based on his words
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
it should not be world writeable, it should be owned by the MySQL user only.

Access is through MySQL...
Ls -l /var/lib/mysql

Author

Commented:
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?
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
The error states that it failed to create in /var/lib/myswl/mysql_t

Check permissions ls -l
Ls -lZ on /var/lib/mysql

The -Z deals with SeLinux settings.

It can not be world writeable.

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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.

Author

Commented:
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

Author

Commented:
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/...

Author

Commented:
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
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
logdir are you trying to change that? rundir?

You have to identify and see in /etc/my.cnf mysqld section and set the parameters you need for the thing to run.  The link you are following presumably should include all caveats. If you are anything like me, you start but then start skipping sections.
And when too many are skipped retracing takes twice as long.

Author

Commented:
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
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
what does the log say when you start?
does /opt/MYSQL_DATA and things below owned by mysql?
does mysql have rights to get through /opt to MYSQL_DATA....

why not create a similar structure
/opt/MYSQL_DATA as the base of all databases
have within
mysql
any other user database

var/
   log
   tmp
   run


note people on the system running mysql -u theirusername -p
will use the socket info from the /etc/my.cnf

always look at the error logs to see what happened when it tried to start.

Author

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
It is a warning, the secure-file-priv

MySQL starts based on the log.
Though your my.cnf seems to be pointing to other locations than reported in the log.

Double check, confirm which my.cnf your MySQL is using, relying on.
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Change permissions to 750 instead of 751
To limit world access note, the placement of socket in a place accessible to all..

Which version.

If you are looking for a secure setup, look at https://dev.mysql.com/doc/refman/8.0/en/mysql-secure-installation.html

Author

Commented:
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
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
main issue, the location you specify in
--secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.

Check the startup command /etc/system/functions just in case you have mysql related definitions there.

I think if you do not set -secure-file-priv it would use the defaults
try
show variables where variable_name like 'secure%';
you may need to define it in my.cnf.
the other issue restrict the permissions on the mysql folders from being world accessible.

Author

Commented:
# 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
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
BASEDIR shoukd be one level above datadir

The warning does not make sense based on your my.cnf BASEDIR, datadir.

Get process list for MySQL and what the parameters listed there.
David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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 FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.