连接到远程服务器上托管的MySQL数据库

Question

Recently i have purchased a domain "domain.com". The hosting company has the usual CPanel to handle databases.

Using the "MySQL Databases" I:

1. Created a database with the name "database1"
2. Created a user with the username "user1" and password "pass1"
3. Added user "user1" to the database "database1"

So far so good.

After that i clicked on PHPMyAdmin and redirected to the PHPMyAdmin webpage. There i see the "database1" that i have previously created. Clicking on that database, i used IMPORT to import a very simple table named "test" [columns (id,name,surname)]. Importing the table created table "test" below the database "database1", which is correct.

After that i have tried to connect to that database using the code below (connect_to_db.php):

// Create connection
$con=mysqli_connect("domain.com","user1","pass1","database1");

// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error() ."<br>";
}

I have uploaded the connect_to_db.php into the /home/domain/public_html/.

When i try to connect to the database1 i get the following error:

Warning: mysqli_connect() [function.mysqli-connect]: (28000/1045): Access denied for user 'user1'@'xxx.xxx.xxx.xxx' (using password: YES) in /home/karkoona/public_html/connect_to_db.php on line 13

Where xxx.xxx.xxx.xxx is the public IP of domain.com (if i get it right).

Also on the right of landing page of PHPMyAdmin i see:

user: domain@localhost

Any idea why i cannot access the database with the current user? Is there any mistake in my code?

Thanks.

Answer 1

You need to grant access to user1 using it's IP to access the remote server:

GRANT ALL PRIVILEGES ON database1.* to 'user1'@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'pass1';

NOTE: replace the relevant info to the current info such as xxx.xxx.xxx.xxx to the current server IP that will access the remote MySQL.

---

Since you're using cPanel you can do this to allow the IP:

• Login to your cPanel (if not already logged in).
• Scroll down to the Databases section.
• Click on the Remote MySQL icon.
• Enter the IP address of the computer that will be making the remote connection.
• Click the Add Host button.