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.

Welcome to StackOverflow. The proper way to mark this as solved (and make the answer more clear to future readers) is to post your solution as an answer to your own question. After a period of time, you can even accept it as the correct answer to your question. Please take a few minutes to review the [FAQ](http://stackoverflow.com/faq) to familiarize yourself with how SO is designed to operate; it's well worth the read, as SO is a great resource. :) (BTW, great first question. +1.) — Ken White, Apr 13 '12 at 01:17

score 31 · Accepted Answer · answered Apr 13 '12 at 21:20

31

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!

answered Apr 13 '12 at 21:20

John Jorgensen

521
1
4
9

2

And you get a +1 from me for a good answer to match the question. :) – Ken White Apr 13 '12 at 21:28
Save my day! Thanks! – Maciej Sikora Sep 14 '16 at 11:21
for me is unknown option '--skip-grant-tables', how to use it.? and need to install mysql5? – Sae Oct 10 '21 at 17:37

NM Pennypacker · Answer 2 · 2023-06-07T10:32:16.150

5

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, so I'm excited to be able to post this as an answer:

Turn it off and turn it back on again

edited Jun 07 '23 at 10:32

answered May 06 '20 at 18:55

NM Pennypacker

6,704
11
36
38

1

I just ran into this same issue with macOS Big Sur. I had tried everything else without success, so I figured what the heck. It worked! So yeah, turn it off and turn it back on again. :) BTW, I was using mariadb, also installed via Homebrew... – KenL Jul 01 '21 at 14:36

score 0 · Answer 3 · answered Jan 17 '23 at 15:33

0

brew services stop mariadb and then brew services start mariadb should do the trick. (Replace MariaDb if you are using MySQL). Do not use restart, since it does not have the same effect.

answered Jan 17 '23 at 15:33

Prescol

615
5
12

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

3 Answers3