I cannot access local database on terminal (MAC)

Question

I have access to my databases on phpmyadmin and mysql Workbench on my local computer but I have a problem when I try to access the server using Terminal.

Originally, I had uploaded the databases on phpmyadmin. When I utilized Workbench after running new server instance localhost, I had automatic access to my databases. However, when I run mysql command in terminal and show databases, only the default information-schema and test database show up.

Furthermore, when I try to execute the .sql files to import the database on terminal, I get the following error:

ERROR 1046 (3D000) at line 11: No database selected.

After reading through the documentation, I thought the issue was that I didn't start off the script with USE Database db_name. I then implemented that in the file and then I got this message in terminal:

ERROR 1044 (42000) at line 7: Access denied for user ''@'localhost' to database 'database'.

If I have access to my databases on phpmyadmin and on Workbench, shouldn't it be the same in terminal?

Also when I first run in terminal mysql -u admin -p < text-file, it asks me for my password. If I simply press enter, then I receive the aforementioned ERROR 1046 or ERROR 1044.

If I put in my password, I then receive the following error:

ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES).

Can someone please help?

Answer 1

It could be your admin user is connecting from [email protected].

Assuming admin is the actual user you have privileges on, try connecting through:

> mysql -h 127.0.0.1 -u admin -p

The way MySQL handles users, the host is very important, and in this case 127.0.0.1 is different than localhost.

If your phpMyAdmin user has access to the mysql database, you can the following statement to see what user/host combinations exist on your install.

 SELECT User, Host FROM mysql.user

Answer 2

Have you created an admin@localhost user in MySQL via a GRANT query? e.g.

GRANT ALL PRIVILEGES ON *.* TO admin@localhost IDENTIFIED BY 'yourpassword';

Since you say phpmyadmin is working, you can check for the existence of this account via phpmyadmin by issuing a

SHOW GRANTS FOR admin@localhost

query. It'll show what (if any) rights that account has.