When I ran a SQL python script it occurred these errors
MySQLdb._exceptions.OperationalError: (1698, "Access denied for user 'root'@'localhost'") sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1698, "Access denied for user 'root'@'localhost'") (Background on this error at: http://sqlalche.me/e/13/e3q8)
I looked in the mysql.user table and get the following:
+------------------+-----------+-----------------------+ | User | Host | plugin | +------------------+-----------+-----------------------+ | debian-sys-maint | localhost | caching_sha2_password | | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | auth_socket | +------------------+-----------+-----------------------+
Advertisement
Answer
I just had the same problem with MariaDB and found myself here expectating some answers, but no lucky. After some research I did an workaround, and judging by the similarities in both DB managers, I think it will help anyone having this problem too. I’m somewhat newbie so I can’t explain the technical stuff behind it but its the default for how mySQL(in my case MariaDB) works.
What I did was create a user on the DB manager (mySQL or MariaDB) for my user on linux and, for don’t potentially expose my password having to pass it to the script, I used the authentication plugin ‘unix_socket’ so you don’t need a password at all (of course it’s only for linux users):
MySQL -u root CREATE USER user@localhost IDENTIFIED VIA 'unix_socket';
Then grant privileges to your new user:
GRANT ALL ON database_name.* TO user@localhost WITH GRANT OPTION;
If you want to connect remotely change ‘localhost’ to ‘%’; and if you are not a linux user I think you can create a user with a password and pass it to sqlalchemy and it will work.
OBS: I’m not sure but I think the problem is that ‘root’ uses both the ‘unix_socket’ and ‘mysql_native_password’ authentication plugins to increase security.