Unable to start mysql-server service

[The_Fireplace]

I have a fresh install of databases/mysql80-server version 8.0.21 (installed using pkg install mysql80-server), and when I attempt to start the service, it doesn't seem to actually start. I've looked through threads on this forum and a few others, and still haven't found a solution, so I'll post what I have done to track down the issue so far and hope someone can point me in the right direction.
System details from freebsd-version ; uname -a:

12.1-RELEASE-p8
FreeBSD freebsd 12.1-RELEASE-p8 FreeBSD 12.1-RELEASE-p8 GENERIC  amd64

To start off, when I run service mysql-server start as superuser, it prints "Starting mysql."
If I run it as a regular user, it prints "/usr/local/etc/rc.d/mysql-server: WARNING: failed precmd routine for mysql"
After running it either way, when I run service mysql-server status, it prints "mysql is not running."

So, next I checked the contents of /var/db/mysql/freebsd.err:

2020-08-14T05:45:27.6NZ mysqld_safe Logging to '/var/db/mysql/freebsd.err'.
2020-08-14T05:45:27.6NZ mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
2020-08-14T05:45:27.460682Z 0 [System] [MY-010116] [Server] /usr/local/libexec/mysqld (mysqld 8.0.21) starting as process 36670
2020-08-14T05:45:27.505797Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-08-14T05:45:30.6NZ mysqld_safe mysqld from pid file /var/db/mysql/freebsd.pid ended

I've seen reports that issues may happen when permissions aren't right for the mysql directories, so I've checked them to ensure that the mysql user is the owner.
ls -lh /var/db

drwxr-x---  6 mysql     mysql      1.0K Aug 14 05:45 mysql
drwxr-x---  2 mysql     mysql      512B Aug  9 01:23 mysql_secure
drwxr-x---  2 mysql     mysql      512B Aug 14 05:45 mysql_tmpdir

I've also made sure to add

mysql_enable="YES"

to /etc/rc.conf

My configuration file /usr/local/etc/mysql/my.cnf

# $FreeBSD: branches/2020Q3/databases/mysql80-server/files/my.cnf.sample.in 469734 2018-05-12 15:35:25Z mmokhi $

[client]
port                            = 3306
socket                          = /tmp/mysql.sock

[mysql]
prompt                          = \u@\h [\d]>\_
no_auto_rehash

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /var/db/mysql
tmpdir                          = /var/db/mysql_tmpdir
slave-load-tmpdir               = /var/db/mysql_tmpdir
secure-file-priv                = /var/db/mysql_secure
log-bin                         = mysql-bin
log-output                      = TABLE
master-info-repository          = TABLE
relay-log-info-repository       = TABLE
relay-log-recovery              = 1
slow-query-log                  = 1
server-id                       = 1
sync_binlog                     = 1
sync_relay_log                  = 1
binlog_cache_size               = 16M
binlog_expire_logs_seconds      = 2592000
default_password_lifetime       = 0
enforce-gtid-consistency        = 1
gtid-mode                       = ON
safe-user-create                = 1
lower_case_table_names          = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options          = BACKUP,FORCE
open_files_limit                = 13635
table_open_cache                = 6737
table_definition_cache          = 8192
net_retry_count                 = 16384
key_buffer_size                 = 256M
max_allowed_packet              = 64M
long_query_time                 = 1
innodb_buffer_pool_size         = 1G
innodb_data_home_dir            = /var/db/mysql
innodb_log_group_home_dir       = /var/db/mysql
innodb_data_file_path           = ibdata1:128M:autoextend
innodb_temp_data_file_path      = ibtmp1:128M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_autoinc_lock_mode        = 2
#skip-symbolic-links
character-set-client-handshake  = FALSE
character-set-server            = utf8mb4
collation-server                = utf8mb4_unicode_ci

[mysqldump]
max_allowed_packet              = 256M
quote_names
quick

Some other things I've tried to get this working:

Deleting the contents of /var/db/mysql then running service mysql-server start.
Deleting the contents of /var/db/mysql then running /usr/local/libexec/mysqld --initialize --user=mysql, which produces:

2020-08-14T06:17:33.347813Z 0 [System] [MY-013169] [Server] /usr/local/libexec/mysqld (mysqld 8.0.21) initializing of server in progress as process 37427
2020-08-14T06:17:33.382282Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-08-14T06:17:47.056786Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
mysqld: Error on delete of './auto.cnf' (OS errno 2 - No such file or directory)
2020-08-14T06:17:49.456251Z 0 [Warning] [MY-010107] [Server] World-writable config file './auto.cnf' has been removed.
2020-08-14T06:17:50.001328Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: REDACTED

After that, running service mysql-server start still just prints "Starting mysql." and the contents of /var/db/mysql/freebsd.err are the same as earlier with different timestamps.

I've also tried running mysqld_safe directly, which produces the following console output:

2020-08-14T06:07:41.6NZ mysqld_safe Logging to '/var/db/mysql/freebsd.err'.
2020-08-14T06:07:41.6NZ mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
$ Killed
2020-08-14T06:07:45.6NZ mysqld_safe mysqld from pid file /var/db/mysql/freebsd.pid ended

After running that, I checked /var/db/mysql/freebsd.err again, and it only has two new lines:

2020-08-14T06:07:41.570113Z 0 [System] [MY-010116] [Server] /usr/local/libexec/mysqld (mysqld 8.0.21) starting as process 37389
2020-08-14T06:07:41.606885Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.

I've tried uninstalling, deleting the old mysql user, then reinstalling, and the issue still happens. I've tried uninstalling and using databases/mysql57-server instead, but that version had the same issue, and I'd rather get the latest version working right anyways.

At this point I'm out of ideas, so any help would be appreciated.

 

[chrbr]

I am a begninner with respect to mysql. I have tried the version 5.7. To start the server without asking for permissions it should be done with the argument --skip-grant-tables. The suggestion has been to add the line to /etc/rc.conf as

mysql_args="--skip-grant-tables"

which worked for my first steps. I have not yet tried to fix or to set the permissions correctly.

 

[The_Fireplace]

To start the server without asking for permissions it should be done with the argument --skip-grant-tables. The suggestion has been to add the line to /etc/rc.conf as

mysql_args="--skip-grant-tables"

which worked for my first steps. I have not yet tried to fix or to set the permissions correctly.

I just tried service mysql-server start after doing that, the results and error logs did not change.

 

[Zvoni]

This reminds of the same problem i've had in the past.
IIRC, it had to do with not empty data-directory and/or write-permissions on folders --> incl. the Folder for the logs (i see a "mysql-bin" in your my.cnf)
in General the steps i do installing mysql.
1) empty out/delete data-directories (incl. tmp and secure)
2) checking folder-permissions for data-dir and log-dir
3) initialize insecure
4) start with skip grant-tables
5) set root-password
6) restart without skip-grant-tables

EDIT: You do know, that mysql changed the standard authentication-plugin for MySQL8? And that not all connectors are capable of using that plugin?
EDIT2: Is that server a member of a Replication-Setup? I see a server-id=1 in your my.cnf. IIRC, you shouldn't set Replication-Parameters for first start

 

[The_Fireplace]

This reminds of the same problem i've had in the past.
IIRC, it had to do with not empty data-directory and/or write-permissions on folders --> incl. the Folder for the logs (i see a "mysql-bin" in your my.cnf)
in General the steps i do installing mysql.
1) empty out/delete data-directories (incl. tmp and secure)
2) checking folder-permissions for data-dir and log-dir
3) initialize insecure
4) start with skip grant-tables
5) set root-password
6) restart without skip-grant-tables

EDIT: You do know, that mysql changed the standard authentication-plugin for MySQL8? And that not all connectors are capable of using that plugin?
EDIT2: Is that server a member of a Replication-Setup? I see a server-id=1 in your my.cnf. IIRC, you shouldn't set Replication-Parameters for first start

I tried this, by:
1. Deleting /var/db/mysql/, /var/db/mysql_tmpdir/, and /var/db/mysql_secure/. I tried to find where mysql-bin points to, and from what I gather it's supposed to be relative to basedir, in this case, /usr/local/, so I checked there, and there was no mysql-bin folder.
2. Skipped because I just deleted the data dir and the log dir didn't exist.
3. I added --initialize-insecure to the mysql_args in /etc/rc.conf

mysql_args="--initialize-insecure --skip-grant-tables"

4. I ran service mysql-server start
This time, there was a change, running that as superuser also caused "/usr/local/etc/rc.d/mysql-server: WARNING: failed precmd routine for mysql"
So I tried creating the directories that were deleted earlier and my guess for what the log dir was, and set the owner to mysql, but the same issue happened. The only file that was created (as far as I can tell) when that ran was /var/db/mysql/mysql-bin.index, and it was empty.

As for your edits:
1. I knew they changed something potentially breaking, but not what specifically. I've checked, the things I'll be using with it are already updated to support it.
2. It isn't a member of a Replication-Setup. That server-id=1 was there by default.

 

[Zvoni]

OK.
1) Remove that server-id=1 from your cnf --> EDIT: Also, remove gtid-mode=on --> That's a Replication-Parameter
2) My guess is /var/log for the logfiles. If you can wait, i could look up my own cnf-file, how i set up my logs, when i get home today
3) the mysql_args are wrong.
a) mysql_args="--initialize-insecure --user=mysql", start mysql (it stops itself after finishing)
b) sysrc mysql_args="--skip-grant-tables", then service mysql-server start
c) mysql -uroot --> change root-PW
d) sysrc -x mysql_args
e) service mysql-server restart

EDIT:

MySQL :: MySQL 8.0 Reference Manual :: 19.1.6.4 Binary Logging Options and Variables

dev.mysql.com

The default location for binary log files is the data directory. You can use the --log-bin option to specify an alternative location, by adding a leading absolute path name to the base name to specify a different directory.

In your case it would be /var/db/mysql

EDIT2: Looking again at your cnf, i'm pretty sure it's the cnf that's bugging you. Will have to check my own cnf at home

 

[The_Fireplace]

1) Remove that server-id=1 from your cnf --> EDIT: Also, remove gtid-mode=on --> That's a Replication-Parameter
2) My guess is /var/log for the logfiles. If you can wait, i could look up my own cnf-file, how i set up my logs, when i get home today
3) the mysql_args are wrong.
a) mysql_args="--initialize-insecure --user=mysql", start mysql (it stops itself after finishing)
b) sysrc mysql_args="--skip-grant-tables", then service mysql-server start
c) mysql -uroot --> change root-PW
d) sysrc -x mysql_args
e) service mysql-server restart

1. Done, I've commented both out.
a. I tried this, I'm still getting "/usr/local/etc/rc.d/mysql-server: WARNING: failed precmd routine for mysql" with no logs generated.

I decided to try running mysql_safe directly to see if I could find out what the problem is, and got this in /var/db/mysql/freebsd.err:

2020-08-15T03:35:21.148593Z 0 [System] [MY-010116] [Server] /usr/local/libexec/mysqld (mysqld 8.0.21) starting as process 44126
2020-08-15T03:35:21.254476Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2020-08-15T03:35:21.263212Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-08-15T03:35:21.263257Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-08-15T03:35:21.266068Z 0 [System] [MY-010910] [Server] /usr/local/libexec/mysqld: Shutdown complete (mysqld 8.0.21)  Source distribution.

Now this seems to think there isn't a valid data directory, though according to my config, it should be /var/db/mysql, which definitely exists, is owned by the mysql user and group, and has permissions drwxr-x---.

 

[Jailer]

I don't know if this will help or not but I recently ran across this same issue trying to get mariadb running. It seemed to be a permissions issue with the /var/run/mysql folder and it wasn't able to create the mysql.sock file in that folder. Temporarily changing folder permissions to 777 and starting mariadb worked. I changed the permissions back to 755 after it was running and I haven't had any issues since.

Just for kicks I fired up a freebsd install in virtualbox and tried with mysql-server and ran into the same issue and doing the same thing described above got it running. Don't ask me why but it's working now and that's all that matters.

 

[The_Fireplace]

I removed my config, mysql folders, uninstalled mysql80-server, removed the mysql user, then reinstalled to get it back to a totally clean install.
I then did

a) mysql_args="--initialize-insecure --user=mysql", start mysql (it stops itself after finishing)
b) sysrc mysql_args="--skip-grant-tables", then service mysql-server start

After which, I checked the logs and I was back to my initial issue. I then tried the next suggestion:

I don't know if this will help or not but I recently ran across this same issue trying to get mariadb running. It seemed to be a permissions issue with the /var/run/mysql folder and it wasn't able to create the mysql.sock file in that folder. Temporarily changing folder permissions to 777 and starting mariadb worked. I changed the permissions back to 755 after it was running and I haven't had any issues since.

Just for kicks I fired up a freebsd install in virtualbox and tried with mysql-server and ran into the same issue and doing the same thing described above got it running. Don't ask me why but it's working now and that's all that matters.

I temporarily set the folder permissions for /tmp/ (which is where my socket file is supposed to be created) to 777 to see if it was that, but my issue persisted. Would you mind sharing your /usr/local/etc/mysql/my.cnf so I can see how your directory configs compare to mine? Mine don't have /var/run/mysql/, or if you meant /var/db/mysql/, mine still aren't set up to put mysql.sock there.

My newly generated config, for reference:

# $FreeBSD: branches/2020Q3/databases/mysql80-server/files/my.cnf.sample.in 469734 2018-05-12 15:35:25Z mmokhi $

[client]
port                            = 3306
socket                          = /tmp/mysql.sock

[mysql]
prompt                          = \u@\h [\d]>\_
expire_logs_days                = 30
default_password_lifetime       = 0
enforce-gtid-consistency        = 1
gtid-mode                       = ON
safe-user-create                = 1
lower_case_table_names          = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options          = BACKUP,FORCE
open_files_limit                = 32768
table_open_cache                = 16384
table_definition_cache          = 8192
net_retry_count                 = 16384
key_buffer_size                 = 256M
max_allowed_packet              = 64M
long_query_time                 = 0.5
innodb_buffer_pool_size         = 1G
innodb_data_home_dir            = /var/db/mysql
innodb_log_group_home_dir       = /var/db/mysql
innodb_data_file_path           = ibdata1:128M:autoextend
innodb_temp_data_file_path      = ibtmp1:128M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_autoinc_lock_mode        = 2
skip-symbolic-links

[mysqldump]
max_allowed_packet              = 256M
quote_names
quick

 

[Jailer]

Would you mind sharing your /usr/local/etc/mysql/my.cnf so I can see how your directory configs compare to mine?

Certainly. I should also add mine was installed in a jail if that makes any difference.

#
# This group is read both by the client and the server
# use it for options that affect everything, see
# https://mariadb.com/kb/en/configuring-mariadb-with-option-files/#option-groups
#
[client-server]
port    = 3306
socket    = /var/run/mysql/mysql.sock

#
# include *.cnf from the config directory
#
!includedir /usr/local/etc/mysql/conf.d/

 

[Zvoni]

I just fired up a new VBox and installed FreeBSD 12.1-p8
I installed MySQL-Server 8.0.21 from packages, left the my.cnf as it is
1) sysrc mysql_args="--initialize-insecure --user=mysql"
2) sysrc mysql_enable="YES"
3) service mysql-server start --> waiting to finish. It's not running at this point. check status!
4) sysrc mysql_args="--skip-grant-tables"
5) service mysql-server start
6) mysql -uroot
7) succesful login --> change password for root. Quit!
8) sysrc -x mysql_args
9) service mysql-server restart
Having fun!
Bottom line: I don't know what you're doing, but there is something fishy.
The my.cnf shipped with MySQL80 works.
The first thing to change in the cnf is the bind-address, but that's it, at least at the beginning.

 

[The_Fireplace]

I installed MySQL-Server 8.0.21 from packages, left the my.cnf as it is
1) sysrc mysql_args="--initialize-insecure --user=mysql"
2) sysrc mysql_enable="YES"
3) service mysql-server start --> waiting to finish. It's not running at this point. check status!
4) sysrc mysql_args="--skip-grant-tables"
5) service mysql-server start
6) mysql -uroot

Something is definitely strange with what's happening on my end, I just followed these exact steps, the only difference being that I did it on my Amazon lightsail instance, and ran into the issue. I'm going to do it in a virtualbox to see the correct results, then compare.

 

[Zvoni]

I have to amend my steps.
7) after login in Step 6, issue "use mysql;" then: "update user set authentication_string='' where user='root';" --> you delete the password! Then quit
10) mysql -uroot again
11) issue: alter user 'root'@'localhost' identified by 'MyNewAndSecretPassword';
12) quit
13) now you can do a mysql -uroot -p --> you'll be asked for your new password
Have fun