how to set up mysql database using homebrew-CodePudding

I want to set up a local mysql database on MacOS.

I've used:

brew install mysql

As a means of installing the software.

Tried

mysql -u root

But this returns

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

You would expect this since I didn't have a password to offer initially.

After digging around I found this code as a means of getting mysql running without password.

mysqld_safe --skip-grant-tables --user=root

The response sounds promising, I thought:

2023-01-08T07:40:02.6NZ mysqld_safe Logging to '/opt/homebrew/var/mysql/Davids-MacBook-Pro.local.err'.
2023-01-08T07:40:02.6NZ mysqld_safe Starting mysqld daemon with databases from /opt/homebrew/var/mysql
2023-01-08T07:40:03.6NZ mysqld_safe mysqld from pid file /opt/homebrew/var/mysql/Davids-MacBook-Pro.local.pid ended

But when I tried

mysql -u root

I received

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Then tried

    mysql -u root -p

Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

How can I set up a password for mysql database if I can't actually have initial access?

Confused.

UPDATE: Then I've looked at this link https://database.guide/install-mysql-on-a-mac/

It suggested running

mysql_secure_installation

But foiled again

Securing the MySQL server deployment.

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

CodePudding user response：

Normally it won't allow you without starting mysql in safe mode. Please follow the below steps.

Stop the MySQL server if it is running:

brew services stop mysql
mysqld_safe --skip-grant-tables &

This will start the MySQL server without loading the grant tables, which store the user privileges. This allows you to connect to the MySQL server as the root user without a password.

mysql -u root

USE mysql;
UPDATE user SET authentication_string=PASSWORD('<<**Custom_Password**>>') WHERE User='root';
FLUSH PRIVILEGES;
exit;

Stop the MySQL server:

mysqladmin -u root -p shutdown

Start the MySQL server normally:

brew services start mysql

You should now be able to connect to the MySQL server as the root user using the new password.