mysql5 - As 'root' can't create database or do anything (Access denied)

Question

I'm on Mac OS X Lion and just installed mysql5 using MacPorts.

Then I successfully ran:

sudo /opt/local/lib/mysql5/bin/mysql_install_db --user=mysql

I'm able to start the server and connect as 'root' fine, but I can't create any databases.

$ mysql5 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.61 Source distribution

mysql> create database dbname;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'dbname'

I've done a lot of Googling trying to figure this out, and it appears that the problem might have to do with file system permissions for /opt/local/var/db/mysql5, but I've tried changing these to no avail:

$ ls -l /opt/local/var/db/
total 0
drwxrwxrwx  8 _mysql  _mysql  272 Apr 12 11:55 mysql5

I've experimented with the owner being '_mysql', 'mysql', and 'root:wheel', but none of them have made a difference.

Answer 1

FIXED -- mysql created my 'root' account with no privileges (I'm a mysql newb).

I solved by starting mysql with

--skip-grant-tables

Then launching with:

mysql5

And running:

mysql> UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> GRANT ALL ON *.* TO 'root'@'localhost';

Hope this helps someone!

Answer 2

I know this is an old question, but I ran into this issue on a new Macbook running Catalina. The project I'm working on requires me to run an older version of MySQL (5.7), so I installed it via Homebrew and ran into the same issue; unable to do pretty much everything, even as root user.

Rebooting my machine fixed the problem, and I've waited my whole Stack Overflow career to post this as an answer, so here it is:

Turn it off and turn it back on again