Solved Can't Start mysql80 server on FreeBSD 13.2

J

jasonhirsh

From general stupidity I have managed to mangle mysql80-server when I tried too upgraded it when I upgrade to FreeBSD 13.2.
In working with the the installation If tried to follow the process set forth in

https://forums.freebsd.org/threads/failed-on-start-mysql80-server-on-freebsd12-0.68857/

and have followed this several times as sudo

Code: 
service mysql-server stop
rm -rf /var/db/mysql/*
service mysql-server start

When I run a start from the command line using sudo service mysql-server start

I get the following error.

Code: 
/usr/local/etc/rc.d/mysql-server: WARNING: failed precmd routine for mysql

I understand th is to me an arror caused by directories that aren't empty . I check the directories

Code: 
/var/db/mysql_secure
/var/db/mysql
/var/db/mysql_tmp


I think in my much reading I saw something about issues with my.cnf
can cause the error to. Here is what I THINK my applicable my.cnf contains

Code: 
[client]
port                            = 3306
socket                          = /tmp/mysql.sock
[mysql]
prompt                          = \u@\h [\d]>\_
no_auto_rehash
[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
basedir                         = /usr/local
datadir                         = /var/db/mysql
tmpdir                          = /var/db/mysql_tmpdir
#replica_load_tmp               = /var/db/mysql_tmpdir
secure-file-priv                = /var/db/mysql_secure
log-bin                         = mysql-bin
log-output                      = 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                = 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
query_cache_type                = 0
query_cache_size = 0
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_buffer_size          = 16M
innodb_write_io_threads         = 8
innodb_redo_log_capacity        = 100
innodb_read_io_threads          = 8
innodb_autoinc_lock_mode        = 2
character-set-server            = utf8mb4
collation-server                = utf8mb4_unicode_ci

[mysqldump]
max_allowed_packet              = 256M
quote_names
quick

I have FreeBSD 13.2. and have mysql80-server-8.0.33 installed
 
Last edited:
I had missed those and have commented them out


key_buffer_size = 256M
max_allowed_packet = 64M
#query_cache_type = 0
#query_cache_size = 0
long_query_time = 0.5
innodb_buffer_pool_size = 1G
Click to expand...

but no joy
mysql-server: WARNING: failed precmd routine for mysql
Click to expand...

jason@triggerfish:/usr/local/etc/mysql: sudo service mysql-server start


/usr/local/etc/rc.d/mysql-server: WARNING: failed precmd routine for mysql
Click to expand...
 
Make a backup of your my.cnf file then copy the original sample over it. Backup all databases then remove all /var/db/mysql* directories and start it again.
 
jasonhirsh said:
socket = /tmp/mysql.sock
Click to expand...
It's generally not a good idea to put your socket file in /tmp/ because the FreeBSD periodic utility will by default remove old files from there.

jasonhirsh said:
I get the following error.

Code: 
/usr/local/etc/rc.d/mysql-server: WARNING: failed precmd routine for mysql
Click to expand...
This might seem like nit-picking, but that is not an error - it is a warning.

If you can locate your MySQL error log, there might be more useful information in there. I think the error log is by default located in the datadir, and has a .err suffix. Though, my MariaDB server has it set to /var/log/mysql/mysqld.err.
 
dbdemon said:
It's generally not a good idea to put your socket file in /tmp/ because the FreeBSD periodic utility will by default remove old files from there.
Click to expand...
I was just going with the suggest provided configuration. I THINK my.cnf is pretty much the same I was running under 5.7


dbdemon said:
This might seem like nit-picking, but that is not an error - it is a warning.
Click to expand...

I sort of understand that and took it as a sign an error occurs
dbdemon said:
If you can locate your MySQL error log, there might be more useful information in there. I think the error log is by default located in the datadir, and has a .err suffix. Though, my MariaDB server has it set to /var/log/mysql/mysqld.err.
Click to expand...
found it. as triggerfish.theoceanwindow.com.err. (my server name). It shows

2023-11-12T00:49:59.383533Z 0 [ERROR] [MY-010095] [Server] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/db/mysql_secure
2023-11-12T00:49:59.383577Z 0 [ERROR] [MY-010119] [Server] Aborting
Click to expand...

I deleted that that directory (mysql_secure) and added it again.
 
SirDice said:
Make sure /var/db/mysql/ is completely empty, a rm -rf /var/db/mysql/* doesn't remove 'hidden' files.
Click to expand...
Ok did some more reading and found only as root can I see the hidden files. Did that found nothing so I deleted the referenced directories to be sure. and chown to mysql:mysql. with same results. Cant fine the correct error log.
 
dbdemon said:
Sorry, that should be /usr/local/libexec/mysqld --initialize --user=mysql.
Click to expand...
That generates the following error

mysqld: Can't get stat of '/var/db/mysql_tmpdir' (OS errno 2 - No such file or directory)
2023-11-13T14:22:04.133047Z 0 [ERROR] [MY-013236] [Server] The designated data directory /var/db/mysql/ is unusable. You can remove all files that the server added to it.
2023-11-13T14:22:04.133060Z 0 [ERROR] [MY-010119] [Server] Aborting
Click to expand...


oh I have a /var/db.mysql_tmpdir. with mysql:mysql ownership
 
jasonhirsh said:
triggerfish.theoceanwindow.com.err
Click to expand...
Hmm, that's definitely not the standard default name, and I don't see it set in the option file you posted, but maybe it is *your* default because you built it from ports? Anyway, if you put log-error=/your/preferred/path/for/log.err in your option file somewhere within the [mysqld] section, then you can make sure it goes somewhere you can read it.
jasonhirsh said:
That generates the following error
Click to expand...
Right, you need to create that directory, and with the correct permissions and ownership. (Or omit specifying it in your option file in the first place if you don't mind it using /tmp/ as the MySQL tmpdir folder.)
 
dbdemon said:
Hmm, that's definitely not the standard default name, and I don't see it set in the option file you posted, but maybe it is *your* default because you built it from ports? Anyway, if you put log-error=/your/preferred/path/for/log.err in your option file somewhere within the [mysqld] section, then you can make sure it goes somewhere you can read it.
Click to expand...
ok I tried to put it with the rest of my logs but was getting permission problems so I specified nothing and let it rock
I got the following warnings

mysqld: Error on delete of '/var/db/mysql/auto.cnf' (OS errno 2 - No such file or directory)
2023-11-13T15:09:01.620864Z 0 [Warning] [MY-010107] [Server] World-writable config file '/var/db/mysql/auto.cnf' has been removed.
2023-11-13T15:09:01.622086Z 0 [Warning] [MY-000081] [Server] option 'innodb-redo-log-capacity': unsigned value 100 adjusted to 8388608.
2023-11-13T15:09:01.652351Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-11-13T15:09:05.171209Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-11-13T15:09:06.937047Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: password
2023-11-13T15:09:08.371542Z 0 [Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.
2023-11-13T15:09:09.140018Z 0 [Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.
Click to expand...


dbdemon said:
Right, you need to create that directory, and with the correct permissions and ownership. (Or omit specifying it in your option file in the first place if you don't mind it using /tmp/ as the MySQL tmpdir folder.)
Click to expand...
iI had but mysqld doesn't (see above) seem to like stuff I create. Neither does mysql

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Click to expand...
 
jasonhirsh said:
iI had but mysqld doesn't (see above) seem to like stuff I create. Neither does mysql
Click to expand...
But now the MySQL server daemon started up? Is the process running? (Check with e.g. ps aux | grep mysql.)

If so, that's progress. However, the "correct" way of starting it is of course service mysql-server start, so you might want to try and stop the currently running process and start it the correct way.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Click to expand...
Is that where the socket file is, or did you specify somewhere else it should go? (Also note that this is specified two places in your option file (one for the client and one for mysqld), and these two need to be the same.)
 
Opps I had assumed from the generation of the temporary password that it is was operating It is not. ps aux | grep mysql just shows my grep attempt.

the only thing I can find close in err file is in /var/db/mysql/mysql. (I find the location a little strange why a mysql with in mysql). gives me this

/usr/local/libexec/mysqld, Version: 8.0.33 (Source distribution). started with:
Tcp port: 0 Unix socket: /tmp/mysql.sock
Time Id Command Argument
Click to expand...

but the socket isn't there

BTW thanks for all the help .. I feel like an idiot but then again I am
 
Don't worry, this isn't the easiest thing in the world!

How about running service mysql-server start now (as root), does that work at this point? I'm hoping it might work because the --initialize command you ran earlier seemed to report mostly happy things.
 
ok I tried that again I got no real response other the return to a prompt

root@triggerfish:/var/db # service mysql-server start
Starting mysql.
root@triggerfish:/var/db#
Click to expand...
Based on our previous experience I assumed nothing and did this

root@triggerfish:/var/db # ps aux | grep mysql
root 11755 0.0 0.0 12812 2340 0 S+ 12:26 0:00.00 grep mysql
Click to expand...
thought maybe it would run as mysqld. so I did this

root@triggerfish:/var/db # ps aux | grep mysqld
root 12077 0.0 0.0 12812 2348 0 S+ 12:30 0:00.00 grep mysqld
Click to expand...

my fault for updating mysql as I updated to to FreeBSD 13.2. Should have been one step at a time
 
Yes, I think the expected output from ps aux | grep mysql would be two processes (in addition to the grep), both running as the mysql user. (Unless things have changed dramatically in MySQL 8.0.) You can always just try to run mysql as well, to see if it connects, just to be sure.

If I were you, I would go back to the error log file again to look for clues there.

BTW, do you really need to build MySQL from ports in order to use it with dovecot and postfix? I know you need to build dovecot and postfix in order to allow them to connect to MySQL, but do you need to build MySQL itself - could you not instead install that as a package? (I'm working on setting up a mail server myself, although I decided to keep it simple and not use a database at all.)
 
I cant find the error log. I also have several other apps that use rely upon mysql. I am trying to tweak INNoB settings especially the innodb_redo_log_capaci. What I had been told in the past was that if configuration as necessary use ports.. Of course part of this is "this his the way I have always done it". for the last 15 or so years
 
ok so I worked on my configuration increasing INNoB parameters. I went back to

/usr/local/libexec/mysqld --initialize --user=mysql

response was

2023-11-13T18:40:25.701765Z 0 [System] [MY-013169] [Server] /usr/local/libexec/mysqld (mysqld 8.0.33) initializing of server in progress as process 18827
mysqld: Error on delete of '/var/db/mysql/auto.cnf' (OS errno 2 - No such file or directory)
2023-11-13T18:40:25.722165Z 0 [Warning] [MY-010107] [Server] World-writable config file '/var/db/mysql/auto.cnf' has been removed.
2023-11-13T18:40:25.725120Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-11-13T18:40:37.768217Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-11-13T18:40:41.808747Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: password
Click to expand...

ps -aux shows no mysql process

next

root@triggerfish:/var/db # service mysql-server start
Starting mysql.
Click to expand...
ps -aux shows no mysql process


I still cant figure out what mysql is using for logs


So you are suggesting I remove MYSQL based on ports and go with pkg


I hate computers
 
Some weeks ago I had a problem starting MySQL and MariaDB inside a jail. The problem was the directory perms. If the DB is inside a jail, make sure the perms are 755.
 
jasonhirsh said:
I still cant figure out what mysql is using for logs
Click to expand...
You should be able to tell it where to put the logs via the variable log_error under the [mysqld] section in you option files. This should be a file path such as /var/log/mysql/mysqld.err, but make sure the directory exists and is owned by the user.
jasonhirsh said:
So you are suggesting I remove MYSQL based on ports and go with pkg
Click to expand...
I'm not really sure - I'm a newcomer to FreeBSD, and I've heard you're not supposed to mix ports and packages.
 
You must log in or register to reply here.
Back
Top