5

I am sure they are passwords to different things but i am not sure what. When in terminal to connect to MySQL I enter /usr/LOCAL/mysql/BIN/mysql -h host -u username -p I am then prompted for a password and the password is ''. But when connecting to MySQL with PHP I use the following code and it works

DEFINE('DB_HOST', 'localhost'); 
DEFINE('DB_USER', 'root'); 
DEFINE('DB_PASS', 'root'); 

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASS,) or 
die('could not connect: '. mysqli_connect_error() );

If i were to use DEFINE('DB_PASS', ''); it returns "Access denied for user 'root'@'localhost' (using password: NO)", why does there appear to be two separate passwords?

lurker
  • 56,987
  • 9
  • 69
  • 103
Justin O'Brien
  • 77
  • 2
  • 7
  • What are you using for `host` and `username` at the command line? Are they `localhost` and `root`? – lurker May 13 '15 at 02:02
  • The answers below are right on spot. If you would like to make their password consistent, you could run `select password from mysql.user where user='username' and host='localhost'`. Copy the password. Then run `update mysql.user set password='' where user='username'`. Now the passwords should be consistent for the user. – zedfoxus May 13 '15 at 02:13
  • Accept the answer that worked for you. – ReinstateMonica3167040 Jul 30 '17 at 15:35

2 Answers2

10

Q: Why does there appear to be two separate passwords?

A: Because you are connecting as two different users.

Each user has its own password and privileges.

A MySQL database "user" is identified by two parts: the user name and the host.

For example, these are three distinct users:

'username'@'localhost'
'username'@'127.0.0.1'
'username'@'%'

To view the users defined on your database, you can query mysql.user table, e.g.

SELECT user, host FROM mysql.user ;

You might want to review the relevant section of the MySQL Reference Manual.

Reference: https://dev.mysql.com/doc/refman/5.5/en/adding-users.html

NOTE: A value localhost in the mysql.user table or a connection is not synonymous with the TCP loopback address (127.0.0.1). It does not resolve to that IP address, or any other IP address. It's a special value.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
0

MySQL uses $user/$host/$password comparison by default, so your current setup has two different user accounts in MySQL - root and whatever username you supply on the command line. Each of those has its own password.

Also, if the account has no (ie empty) password you don't need to specify the -p flag on the CLI

Stephen
  • 18,597
  • 4
  • 32
  • 33