Homebrew Mariadb Mysql installation root access denied

Question

So I basically am installing mariadb with mysql on my mac using homebrew. These are the steps I made:

• brew doctor -> worked
• brew update -> worked
• brew install mariadb -> worked

• mysql_install_db -> Failed

  WARNING: The host 'Toms-MacBook-Pro.local' could not be looked up with /usr/local/Cellar/mariadb/10.4.6_1/bin/resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MariaDB version. The MariaDB daemon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MariaDB privileges ! mysql.user table already exists!

Running mysql_upgrade afterwards gave me following error:

Version check failed. Got the following error when calling the 'mysql' command line client ERROR 1698 (28000): Access denied for user 'root'@'localhost' FATAL ERROR: Upgrade failed

I can't enter mysql like this:

mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

but like this:

sudo mysql -u root

The user table returns this:

MariaDB [(none)]> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> SELECT User, Host, plugin FROM mysql.user;
+---------------+-------------------------+-----------------------+
| User          | Host                    | plugin                |
+---------------+-------------------------+-----------------------+
| root          | localhost               | mysql_native_password |
| toms          | localhost               | mysql_native_password |
|               | localhost               |                       |
|               | toms-macbook-pro.local |                       |
+---------------+-------------------------+-----------------------+
4 rows in set (0.004 sec)

Answer 1

You could try to update the root password and access it afterwards

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

Exit Mysql and try to login

mysql -uroot -p # then use root as a password

Answer 2

What is the issue?

• Install MariaDB using brew, brew install [email protected].

• Try to reset root password.

  • Method 1: mysqld_safe command

    • run command: brew services stop [email protected]
    • run command: mysqld_safe --skip-grant-tables --skip-networking
    • on a new terminal tab,
      run command for MariaDB <= 10.4: mysql_secure_installation
      run command for MariaDB >= 10.4 mariadb-secure-installation
      
    • this will ask to enter root password
    • hit enter without entering any password (this step might never go away!)
    • if empty root password is granted in previous step
      enter and re-enter new password in the next steps
    • this could show some errors Password update failed!

  • Method 2: /usr/local/mysql/bin/mysqladmin -u root -p password

    • this will ask to enter password
    • hit enter without entering any password
    • this will show some errors!

But preceding two methods did not work!

• Follow the working method:

  • start the [email protected] service brew services start [email protected]

  • run mysql.servert start

  • this will show an error with error log file location

  • typical mariadb error file location: /usr/local/var/mysql/<filename>.local.err

  • run tail -f /usr/local/var/mysql/<filename>.local.err

  • then re-run mysql.servert start

  • there will be an error related to Invalid flags lib

  • run brew services stop [email protected]

  • (BACKUP, BACKUP, BACKUP YOUR DBS! THIS WILL DELETE ALL DBs!)
    run sudo rm -rf /usr/local/var/mysql

  • run

    mysql_install_db --verbose --user=`whoami`
    --basedir="$(brew --prefix [email protected])"
    --datadir="/usr/local/var/mysql" --tempdir="/tmp"
    

    This will get the mariaDB Cellar installation path from brew and this will install the initial db.

  • instead of running mysql_secure_installation or mariadb-secure-installation run: sudo mysql -u root

  • this will drop to mysql shell

  • enter command: use mysql;

  • enter command: ALTER USER 'root@localhost' IDENTIFIED BY '<password>'; (replace the )

  • enter command: ALTER USER '[email protected]' IDENTIFIED BY '<password>'; (replace the )

  • enter command: FLUSH PRIVILEGES;

  • enter command: exit

  • now you can run mysql -u root -p and use the <password> entered in earlier step.

That's all!