MySQL: django.db.utils.OperationalError: (1698, "Access denied for user 'root'@'localhost'") with correct username and pw

Question

I have django.db.utils.OperationalError: (1698, "Access denied for user 'root'@'localhost'") when using mysql. The username and pw are correct:

DB_HOST = '127.0.0.1'
DB_USER = 'root'
DB_PASSWORD = ''

I can log into mysql as root:

$ sudo mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16

But not as cchilders:

$ mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

This may contribute to the problem. Last time I installed mysql this didn't happen, so it doesn't make sense to me. I have the client fine:

$ pip3 freeze
Django==1.10.5
mysqlclient==1.3.9

How can I allow mysql to be run by my normal user, so I can run django in the terminal? thank you

Dirty solution:

Without any fixes, always run mysql as sudo

Answer 1

The reason that you can login as root on your server is that you probably have specified a password in the .my.cnf file in /root (i.e., the root user's home directory). Check to see if there is a password there and use that for cchilders as well. You can then create a django-specific application user to make sure that the django app only reads/writes/etc. to the databases that it needs access to and not access through the root mysql user.

create user 'django'@'localhost' identified by 'django-user-password';
grant usage on *.* to 'django'@'localhost';
grant all privileges on django-database-1.* to 'django'@'localhost';

Answer 2

Create a non-root SQL user and change the DB_USER variable in the settings.py file of Django