Php cannot connect to mysql

Php cannot connect to mysql

Background:

Use lighttpd as the web server, php as the server script, and mysql as the database. When accessing the browser client, the server php script will try to connect to mysql, query the data and return it to the client. However, mysql cannot be connected twice during the debugging process. Here is a record.

Body:

It is relatively simple to connect to mysql in a php script. First paste the code

<span style="font-size:14px;"><?php$servername = "192.168.110.2";$port=3306;$username = "username";$password = "password";$dbname = "testDB";$socket="mysqli.default_socket";// Create connection$conn = new mysqli($servername, $username, $password, $dbname,$port,$socket);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);} $sql = "SELECT id, firstname, lastname FROM MyGuests";$result = $conn->query($sql);if ($result->num_rows > 0) {    // output data of each row    while($row = $result->fetch_assoc()) {        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";    }} else {    echo "0 results";}$conn->close();?></span>

(1) The above code can be run.

If you run the command line interface in the form of "php + Script Name", the code can smoothly print the data of a table in mysql.

(2) but when I access this script through a web browser, the desired result is not displayed, and the code is dead in the mysql connection.

Later, I analyzed the cause, which should be the problem of mysql configuration parameters.

<span style="font-size:14px;">$server="192.168.110.2";</span>

The remote connection to mysql is used here. However, when mysql is installed by default, only 127.0.0. 1 (the IP address of the Local Machine) is monitored ).

Therefore, to allow remote access to mysql, you must configure the IP address of the mysql listener.

Open the mysql configuration file/etc/mysql/my. cnf and comment out the following line.

bind-address = 127.0.0.1

(3) After the comments, pay attention to another problem.

If the root user is used to remotely access mysql, the permission issue will pop up (not during mysql local debugging, but during remote connection ).

Solution: create a common user, add the permission to modify or query mysql for the user, and then use the common user to access mysql.