mysql.connector.errors.ProgrammingError: 1698 (28000): Access denied for user 'root'@'localhost'

Question

I'm a newbie in Raspberry Pi and MySQL.

I've created a code, database in phpmyadmin which ran by wamp server in my PC. Meanwhile, I want to run this code in Raspberry Pi 3. Besides that, when I run the code in Raspberry Pi 3, I want the data can be save in phpmyadmin, thus, when I run localhost on my PC, the data that I enter from Raspberry Pi can be saved in the PC too. But, when I ran it, it shows an error message like this:

mysql.connector.errors.ProgrammingError: 1698 (28000): Access denied for user 'root'@'localhost'

Here is my db:

#connect to db
db = mysql.connector.connect(host = 'localhost', user = 'root', password = 
'', database = 'user')

Anyone know how to solve this problem?

Answer 1

I have a Raspberry Pi and did the following steps:

Update the operating system:

sudo apt-get update && sudo apt-get upgrade

Install mySQL server:

sudo apt-get install mysql-server

Install mySQL client:

sudo apt-get install mysql-client

Check status of mySQL:

sudo service mysql status

Install mySQL python connector:

sudo apt-get -y install python3-mysql.connector

Give mySQL a password:

sudo mysql -uroot

use mysql;

update user set authentication_string=PASSWORD('mynewpassword') where User='root';

Note: In the above command keep the root user, just set the password.

Exit mySQL:

\q

Stop mySQL services:

sudo /etc/init.d/mysql stop

Start mySQL services:

sudo /etc/init.d/mysql start

Test the new password by logging in to the database:

sudo mysql -u root -p

SHOW DATABASES;

CREATE DATABASE YOUR_DATABASE_NAME_HERE;

CREATE USER 'YOUR_NEW_USERNAME'@'localhost' IDENTIFIED BY 'YOUR_NEW_PASSWORD';

Note: Python needs a new user to allow communication to mySQL! I have no idea why but it works.

In Python:

import mysql.connector  

# The following code tests Pythons connection to mySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="YOUR_NEW_USER",
  password="YOUR_NEW_PASSWORD"
)

print(mydb)

It took me several days to piece this together from a dozen different websites. Hopefully my notes didn't miss anything. Creating the new user at the end seems to be the key issue. Mine isn't a Windows installation but the installation should be similar.

Answer 2

have the same error

mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: NO)

but I was trying to use python scripts like CGI.

this worked for me:

$ sudo mysql -u root
> use mysql;                                                    
> update user set authentication_string=PASSWORD("") where user = "root";
> update user set plugin="mysql_native_password" where user = "root";
> flush privileges;

This is part of my code /usr/lib/cgi-bin/script_cgi.py:

#!/usr/bin/python3

import mysql.connector

connection = mysql.connector.connect(user='root', password='', host='127.0.0.1', database='books')

you have to give it permissions: sudo chmod 755 /usr/lib/cgi-bin/script_cgi.py

You must also enable the apache module: sudo a2enmod cgid

then visit localhost and Apache Server can now use Python scripts as CGI: http://127.0.0.1/cgi-bin/script_cgi.py

For more of CGI whith python in https://www.tutorialspoint.com/python/python_cgi_programming.htm

Answer 3

Use below query and execute it on your database.

INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'root', password('YOURPASSWORD'));
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;

Answer 4

You should only create a user in the database(mysql) through which you are accessing the database.

CREATE USER 'nikhil'@'localhost' IDENTIFIED BY 'Password';

Password must satisfy the requirement(1.One Upper case,2.One special character). Now use python

import mysql.connector

mydb = mysql.connector.connect(host="localhost",

user="nikhil",

password="Nikhil_12345",

database="database_name")

print(mydb)

Now add privilege to the user

USE database_name;

GRANT ALL PRIVILEGES ON database_name.* TO 'nikhil'@'localhost';