'brew install mysql Can't set root password

I installed MySQL via brew, but instead of letting me set a root password after mysql_secure_installation, I get prompted for the root password instead.

% brew install mysql
==> Downloading https://ghcr.io/v2/homebrew/core/mysql/manifests/8.0.27
Already downloaded: /Users/msd/Library/Caches/Homebrew/downloads/c521714052ea597d404fc2862523de823536b9d9c0f4be0bcefc34b8e280a2cf--mysql-8.0.27.bottle_manifest.json
==> Downloading https://ghcr.io/v2/homebrew/core/mysql/blobs/sha256:dfd1d6855666
Already downloaded: /Users/msd/Library/Caches/Homebrew/downloads/9a51cb7727b7e2d85fd2a1e02469666dff8e9d816b4d96ac68568577f741dea2--mysql--8.0.27.arm64_monterey.bottle.tar.gz
==> Pouring mysql--8.0.27.arm64_monterey.bottle.tar.gz
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To restart mysql after an upgrade:
  brew services restart mysql
Or, if you don't want/need a background service you can just run:
  /opt/homebrew/opt/mysql/bin/mysqld_safe --datadir=/opt/homebrew/var/mysql
==> Summary
🍺  /opt/homebrew/Cellar/mysql/8.0.27: 304 files, 294MB
==> Running `brew cleanup mysql`...
Disable this behaviour by setting HOMEBREW_NO_INSTALL_CLEANUP.
Hide these hints with HOMEBREW_NO_ENV_HINTS (see `man brew`).

% mysql_secure_installation

Enter password for user root: 
Error: Access denied for user 'root'@'localhost' (using password: YES)

I then try % mysqld --skip-grant-tables to reset the password, but get another error:

2021-12-05T18:28:46.319410Z 0 [System] [MY-010116] [Server] /opt/homebrew/Cellar/mysql/8.0.27/bin/mysqld (mysqld 8.0.27) starting as process 3482
2021-12-05T18:28:46.320508Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /opt/homebrew/var/mysql/ is case insensitive
2021-12-05T18:28:46.323138Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-12-05T18:28:46.328311Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 35
2021-12-05T18:28:47.333722Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 35
2021-12-05T18:28:48.339275Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 ```

I've uninstalled MySQL using these steps, and before reinstalling I do brew remove mysql, which results in Error: No such keg: /opt/homebrew/Cellar/mysql. So I am fairly sure MySQL is no longer installed.

So is there a way to reset the root password (or failing that, reinstall MySQL)?

mysqlhomebrew


Solution 1:[1]

I had this same issue. Something during the upgrade corrupted the ibdata1 file and it failed to start as a result. Best solution I came up with was to uninstall MySQL

brew uninstall mysql

Then delete the data directory:

rm -rf /usr/local/var/mysql

and the custom conf

 rm /usr/local/etc/my.cnf

And then reinstall MySQL

brew install mysql

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 danielwashbrook

Related Questions