MySQLi忽略主机参数

Question

This is my setup (the IP numbers are fictional of course):

Server A (10.0.0.1)

hosts a database called database1 with the user db_user and the password db_pass. This user has access to the database and remote connections from any host are permitted (I know it's a leak and I will fix it once it works)

Update 1: This server shows no signs of receiving the connection (like connection refused or something like that) Port 3306 is open

Server B (20.0.20.0) hosts a PHP script which connects to the database with the following command:

$connection = mysqli_connect("10.0.0.1","db_user","db_pass","database1",3306);

My log on Server B says:

Access denied for user 'db_user'@'20.0.20.0' (using password: YES) in <path-to-php-file> in line 42

The line number matches the statement, so it is indeed the statement above which fails.

Why? I explicitly specified the IP of Server A (also tried server-a.com instead of 10.0.0.1)

Update 2: I ran the following query via commandline as MySQL-Root and this is the output:

mysql> SHOW GRANTS FOR 'db_user';
GRANT USAGE ON *.* TO 'db_user'@'%' IDENTIFIED BY PASSWORD '<password hash>'
GRANT ALL PRIVILEGES ON `database1`.* TO 'db_user1'@'%'

Seems valid to me. What strikes me as odd is that in the log of B it shows it own address (B's address) instead of A's where the Database is located. My idea is it tries to connect to a database on server B where no MySQL user db_user exists.

Update 3: I connected via SSH to server B and ran mysql --host=10.0.0.1 -udb_user -p and typed in the password => it worked. SHOW GRANTS FOR current_user; returned the same like on server A.

Answer 1

If you can't connect using mysqli, try using PDO instead. I'm not sure why, but apparently in this case PDO works.

Personally, I like PDO better than mysqli, because of named parameters instead of ?, and the ability to provide an array of values when calling PDOStatement::execute(). You may find you like it as well.