In new version of RaspberryPi OS, when u install mySql it installs MariaBD.

When I first time installed mysql-server (apt-get install). I did not get prompted for a password. It just says that it is setting user password for mysql and soon there after it looks as if it is done – no protests.

Now I have no idea what password has been set to mysql. I tried to setup apache web server, php and chose mysql as db but an empty password was not accepted so I guess there is some password. On the command line I type mysql -p -u root, and if I type no password I do not get in : “ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)” If I guess a password, the result is almost the same, except the last word is YES instead of NO.

To access newer versions of mysql/mariadb after as the root user, after a new install, you need to be in a root shell

$ sudo mysql -u root

but problem with accessing database using php. and can not login with

$ mysql -u root -p

What to do?

There are many links can be found when you google but, the correct solution is difficult It took me 6 hours to find correct solution.

To get access as root, whilst all the other users could still access fine.

Looking at the mysql.user table I noticed for root the plugin column is set to unix_socket whereas all other users it is set to ‘mysql_native_password’. A quick look at this page: https://mariadb.com/kb/en/mariadb/unix_socket-authentication-plugin/ explains that the Unix Socket enables logging in by matching uid of the process running the client with that of the user in the mysql.user table. In other words to access mariadb as root you have to be logged in as root.

Solution is very simple just follow these steps

Step 1: Stop mariaDB Database server using command

$ sudo systemctl stop mariadb

Step 2: Restarting the Database Server Without Permission Checking

If you run MySQL and MariaDB without loading information about user privileges, it will allow you to access the database command line with root privileges without providing a password. This will allow you to gain access to the database without knowing it.

To do this, you need to stop the database from loading the grant tables, which store user privilege information. Because this is a bit of a security risk, you should also skip networking as well to prevent other clients from connecting.

Start the database without loading the grant tables:

$  sudo mysqld_safe –skip-grant-tables &

The ampersand at the end of this command will make this process run in the background so you can continue to use your terminal.

Step 3: Now you can connect to the database as the root user

$ sudo mysql

Step 4:Changing the Root Password and Privileges/plugin

GRANT ALL PRIVILEGES on *.* to 'root'@'localhost' IDENTIFIED BY '<password>';
FLUSH PRIVILEGES;

or

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'unix_socket';
FLUSH PRIVILEGES;

Step 5: Restart MariaDB

$ sudo service mysql restart

You now have administrative access to the MySQL or MariaDB server restored. Make sure the new root password you choose is strong and secure and keep it in safe place.