Mac Monterey下Homebrew安装的MariaDB无mysql表无法登录问题

问题背景 #

在Mac Monterey系统上通过Homebrew安装MariaDB后，删除了root及其他本地用户，导致无法登录数据库。尝试重装MariaDB后问题依旧，通过mysqld_safe --skip-grant-tables --skip-networking命令能成功登录，但发现mysql系统数据库内没有任何表。

环境详情 #

MariaDB版本信息 #

# moonwatcher @ MoonWatcherdeMacBook-Air in /usr/local/etc [14:52:33] 
$ brew info mariadb            
mariadb: stable 10.8.3 (bottled)
Drop-in replacement for MySQL
https://mariadb.org/
Conflicts with:
  mariadb-connector-c (because both install `mariadb_config`)
  mysql (because mariadb, mysql, and percona install the same binaries)
  mytop (because both install `mytop` binaries)
  percona-server (because mariadb, mysql, and percona install the same binaries)
/usr/local/Cellar/mariadb/10.8.3_1 (920 files, 187.5MB) *
  Poured from bottle on 2022-08-14 at 13:40:39
From: https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/mariadb.rb
License: GPL-2.0-only
==> Dependencies
Build: bison ✘, cmake ✘, fmt ✘, pkg-config ✔
Required: groonga ✔, openssl@1.1 ✔, pcre2 ✔, zstd ✔
==> Caveats
A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

MySQL is configured to only allow connections from localhost by default

To restart mariadb after an upgrade:
  brew services restart mariadb
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/mariadb/bin/mysqld_safe --datadir=/usr/local/var/mysql
==> Analytics
install: 12,338 (30 days), 43,510 (90 days), 161,252 (365 days)
install-on-request: 12,321 (30 days), 43,452 (90 days), 160,988 (365 days)
build-error: 29 (30 days)
(base) 

相关my.cnf配置文件 #

# moonwatcher @ MoonWatcherdeMacBook-Air in /usr/local/etc [14:53:42] 
$ ls
bash_completion.d      gitconfig              init.d                 mecabrc                my.cnf.d               my.cnf.default.default wgetrc
ca-certificates        groonga                logrotate.d            my.cnf                 my.cnf.default         openssl@1.1
(base) 
# moonwatcher @ MoonWatcherdeMacBook-Air in /usr/local/etc [14:53:43] 
$ cat my.cnf                
#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]

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

(base) 
# moonwatcher @ MoonWatcherdeMacBook-Air in /usr/local/etc [14:53:52] 
$ cat my.cnf.default        
# Default Homebrew MySQL server config
[mysqld]
#skip-grant-tables
# Only allow connections from localhost
bind-address = 127.0.0.1
(base) 
# moonwatcher @ MoonWatcherdeMacBook-Air in /usr/local/etc [14:53:57] 
$ cat my.cnf.default.default 
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
(base) 

mysql数据库无表异常 #

# moonwatcher @ MoonWatcherdeMacBook-Air in ~ [14:54:23] 
$ mysql        

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 41
Server version: 10.8.3-MariaDB Homebrew

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| homebrew           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.002 sec)

MariaDB [(none)]> use mysql;
Database changed
MariaDB [mysql]> show tables;
Empty set (0.001 sec)

MariaDB [mysql]> 

解决步骤 #

核心原因是mysql系统库的权限表被完全删除，且重装Homebrew的MariaDB时不会自动清理旧数据目录，导致损坏的系统库被保留。解决方法是重新初始化系统数据库：

1. 停止MariaDB服务
   如果用brew services管理：

brew services stop mariadb

如果是手动用mysqld_safe启动的，先终止进程：

ps aux | grep mysqld
kill -9 [进程ID]

2. 备份旧数据目录（可选但推荐）
   避免数据丢失，先备份原有数据：

mv /usr/local/var/mysql /usr/local/var/mysql_backup

3. 重新初始化数据库
   生成全新的系统表：

mysql_install_db --user=$(whoami) --basedir=/usr/local/opt/mariadb --datadir=/usr/local/var/mysql

4. 启动MariaDB服务

brew services start mariadb

5. 设置root用户密码
   初始化后root默认无密码，直接登录：

mysql -u root

在MariaDB终端内设置新密码：

ALTER USER 'root'@'localhost' IDENTIFIED BY '你的新密码';
FLUSH PRIVILEGES;

完成以上步骤后，即可正常登录MariaDB，mysql系统库也会恢复完整的权限表。

内容的提问来源于stack exchange，提问作者Marvin Wu