No mysql database in mysql server - how do I give users permissions?

Question

I have a mysql server (5.5.44) running on my local Ubuntu 14 machine. I can start the interactive shell via

$ mysql

When I ask to see all the databases, there is only one

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+

If I try to make a new database I get an error

mysql> CREATE DATABASE purchases;
ERROR 1044 (42000): Access denied for user 'danielsank'@'localhost' to database 'purchases'

I am puzzled by how to resolve this problem. I'm used to mysql servers always having a database called mysql containing user access data: passwords, allowed hosts, etc. My server installation doesn't seem to have this.

How do I give myself permission to create new tables, given that there is no mysql database in the server?

Answer 1

The problem here is that I am invoking mysql as user danielsank, but that user does not have privileges to make new tables, nor to see the mysql database!

The issue becomes clear by invoking mysql as root

$ mysql -u root
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+

Now we can see the mysql database, and we can check it for user information

mysql> USE DATABASE mysql;
mysql> SELECT User, Host, Password FROM user;
+------------------+-------------------------+-----------------------------+
| User | Host | Password |
+------------------+-------------------------+-----------------------------+
| root | localhost | |
| root | <my machine name> | |
| root | 127.0.0.1 | |
| root | ::1 | |
| debian-sys-maint | localhost | *<a really long string> |
| danielsank | localhost | |
+------------------+-------------------------+-----------------------------+

We can create the new database

mysql> CREATE DATABASE purchases;

and grant permissions to danielsank

mysql> GRANT ALL PRIVILEGES ON purchases . * TO 'danielsank'@'localhost';

• DanielSank: Although this mistake addressed by this answer is fairly elementary, I posted a question-answer because elsewhere on the internet people are advocating "fixing" this problem by nuking all of the permissions and starting over.