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
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
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.
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.
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
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
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.
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.506190
mp_file_case_insensitive_t
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.506229
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
ocess 31605
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.509867
mp_file_case_insensitive_t
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.509880
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.509960
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.510081
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.510300
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.
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
mp_file_case_insensitive_t
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.506229
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
ocess 31605
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.509867
mp_file_case_insensitive_t
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.509880
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.509960
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.510081
Jan 14 14:39:58 ourServer mysqld: 2020-01-14T06:39:58.510300
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.
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
All errors point to permission errors on /var/lib/mysql
DBA made the dir world writeable as well,
still getting the same but of course that's
based on his words
still getting the same but of course that's
based on his words
it should not be world writeable, it should be owned by the MySQL user only.
Access is through MySQL...
Ls -l /var/lib/mysql
Access is through MySQL...
Ls -l /var/lib/mysql
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?
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?
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.
Check permissions ls -l
Ls -lZ on /var/lib/mysql
The -Z deals with SeLinux settings.
It can not be world writeable.
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_
# ls -lZ /opt/MySQL_DATA/mysql/tmp
-rw-r--r--. mysql mysql unconfined_u:object_r:mysq
(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_
# ls -lZ /opt/MySQL_DATA/mysql/tmp
-rw-r--r--. mysql mysql unconfined_u:object_r:mysq
Contents of /etc/my.conf :
datadir=/opt/MySQL_DATA/my
socket=/var/lib/mysql/mysq
#socket=/opt/MySQL_DATA/my
log-error=/var/log/mysqld.
pid-file=/var/run/mysqld/m
[client]
port=3306
socket=/var/lib/mysql/mysq
#socket=/opt/MySQL_DATA/my
datadir=/opt/MySQL_DATA/my
socket=/var/lib/mysql/mysq
#socket=/opt/MySQL_DATA/my
log-error=/var/log/mysqld.
pid-file=/var/run/mysqld/m
[client]
port=3306
socket=/var/lib/mysql/mysq
#socket=/opt/MySQL_DATA/my
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
Jan 16 11:45:08 mySrvr mysqld: 2020-01-16T03:45:08.951346
Jan 16 11:45:08 mySrvr mysqld: 2020-01-16T03:45:08.951467
Jan 16 11:45:08 mySrvr mysqld: 2020-01-16T03:45:08.951611
Jan 16 11:45:08 mySrvr mysqld: 2020-01-16T03:45:08.951905
the old /var/lib/mysql: what's missed?
# grep datadir /etc/my.cnf
datadir=/opt/MySQL_DATA/my
Jan 16 11:45:08 mySrvr mysqld: 2020-01-16T03:45:08.951346
Jan 16 11:45:08 mySrvr mysqld: 2020-01-16T03:45:08.951467
Jan 16 11:45:08 mySrvr mysqld: 2020-01-16T03:45:08.951611
Jan 16 11:45:08 mySrvr mysqld: 2020-01-16T03:45:08.951905
Also, we noted that "ls -lZ ..." gives unconfined_u:object_r:var_
for the new dir while the old original dir has it as
system_u:object_r:mysqld_d
system_u:object_r:mysqld_d
for the new dir while the old original dir has it as
system_u:object_r:mysqld_d
system_u:object_r:mysqld_d
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/...
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.inde
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
mysqld 20750 mysql 17uW REG 253,0 81920 975352 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 18uW REG 253,0 81920 975353 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 19uW REG 253,0 81920 975354 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 20uW REG 253,0 81920 975355 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 21uW REG 253,0 81920 975356 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 22uW REG 253,0 81920 975357 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 23uW REG 253,0 81920 975358 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 24uW REG 253,0 81920 975359 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 25uW REG 253,0 81920 975360 /var/lib/mysql/#innodb_tem
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
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
mysqld 20750 mysql 34u IPv6 3697780 0t0 TCP *:33060 (LISTEN)
mysqld 20750 mysql 35w REG 253,0 155 69499745 /var/lib/mysql/jpcwsu01-re
mysqld 20750 mysql 36u unix 0xffff8800ae4b3800 0t0 3697782 /var/run/mysqld/mysqlx.soc
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
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
mysqld 20750 mysql 17uW REG 253,0 81920 975352 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 18uW REG 253,0 81920 975353 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 19uW REG 253,0 81920 975354 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 20uW REG 253,0 81920 975355 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 21uW REG 253,0 81920 975356 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 22uW REG 253,0 81920 975357 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 23uW REG 253,0 81920 975358 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 24uW REG 253,0 81920 975359 /var/lib/mysql/#innodb_tem
mysqld 20750 mysql 25uW REG 253,0 81920 975360 /var/lib/mysql/#innodb_tem
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
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
mysqld 20750 mysql 34u IPv6 3697780 0t0 TCP *:33060 (LISTEN)
mysqld 20750 mysql 35w REG 253,0 155 69499745 /var/lib/mysql/jpcwsu01-re
mysqld 20750 mysql 36u unix 0xffff8800ae4b3800 0t0 3697782 /var/run/mysqld/mysqlx.soc
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.
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.
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
datadir=/opt/MySQL_DATA/my
socket=/opt/MySQL_DATA/mys
logdir=/opt/MySQL_DATA/mys
rundir=/opt/MySQL_DATA/mys
Do we need to initialize (ie somethg like
'systemctl restart mysqld --initialize' ?
[mysqld] <== added this
basedir=/opt/MySQL_DATA/my
datadir=/opt/MySQL_DATA/my
socket=/opt/MySQL_DATA/mys
logdir=/opt/MySQL_DATA/mys
rundir=/opt/MySQL_DATA/mys
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.
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.
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
[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.969985
Jan 16 14:37:02 mysqld: 2020-01-16T06:37:02.970025
Jan 16 14:37:02 mysqld: 2020-01-16T06:37:02.970174
Jan 16 14:37:02 systemd: Starting MySQL Server...
Jan 16 14:37:02 mysqld: 2020-01-16T06:37:02.969985
Jan 16 14:37:02 mysqld: 2020-01-16T06:37:02.970025
Jan 16 14:37:02 mysqld: 2020-01-16T06:37:02.970174
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.
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.
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
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
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
it's odd that despite what's set for tmpdir, logdir,
basedir, rundir, those temp files still get created
in the old dir
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.
--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.
# cd /etc/system
-bash: cd: /etc/system: No such file or directory
# cd /etc
# find . -name *sql* -print
./pki/rpm-gpg/RPM-GPG-KEY-
./php-zts.d/20-sqlite3.ini
./php-zts.d/30-pdo_sqlite.
./php-zts.d/20-mysqlnd.ini
./php-zts.d/30-mysqli.ini
./php-zts.d/30-pdo_mysql.i
./selinux/targeted/context
./selinux/targeted/active/
./selinux/targeted/active/
./selinux/targeted/active/
./selinux/targeted/tmp/mod
./selinux/targeted/tmp/mod
./selinux/targeted/tmp/mod
./systemd/system/multi-use
./ld.so.conf.d/mysql-x86_6
./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
-bash: cd: /etc/system: No such file or directory
# cd /etc
# find . -name *sql* -print
./pki/rpm-gpg/RPM-GPG-KEY-
./php-zts.d/20-sqlite3.ini
./php-zts.d/30-pdo_sqlite.
./php-zts.d/20-mysqlnd.ini
./php-zts.d/30-mysqli.ini
./php-zts.d/30-pdo_mysql.i
./selinux/targeted/context
./selinux/targeted/active/
./selinux/targeted/active/
./selinux/targeted/active/
./selinux/targeted/tmp/mod
./selinux/targeted/tmp/mod
./selinux/targeted/tmp/mod
./systemd/system/multi-use
./ld.so.conf.d/mysql-x86_6
./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
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.
The warning does not make sense based on your my.cnf BASEDIR, datadir.
Get process list for MySQL and what the parameters listed there.
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).
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.
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
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial