Wednesday, February 16, 2022

[SOLVED] When importing SQL file to the database using command line, wrong MySQL user is used

Issue

I have MySQL user company_dev who has access to DB company_dev, and user company_prod who has access to DB company_prod.

When I'm logged as a root on my Debian server, I try to import SQL file to my company_dev DB.

This is the command I'm using: mysql -u company_dev -pMyPassword company_dev < some_db.sql

But I get this error:

Access denied for user 'company_dev'@'localhost' to database 'company_prod'

If I correctly understand this message - there was an attempt to write a file some_db.sql to the company_prod DB, even though I put company_dev in the command?!

How is it possible?


Solution

Note: logged in as root on the server is not analogous to the root user of the mysql server, they are different.

Please check the following:

  • Check the password is right, also might need to put it in quotes: -p'passWordHere'

  • The file some_db.sql probably has some statement to insert into someDbName. Check it.

  • the user you are importing with is -u company_dev you need to make sure this user has permissions.

To grant permissions, use this sql, you will need something like this:

 GRANT [type of permission] ON company_prod TO ‘company_dev’@'localhost’;

This all depends on the permissions you need to give the user, all privileges would be something like this:

GRANT ALL PRIVILEGES ON * . * TO 'company_dev'@'localhost';


Answered By - visualex
Answer Checked By - Pedro (WPSolving Volunteer)
at