Using the policy of using mysqli::debug when debugging multi-database connections

M66 2025-05-13

Debugging database operations can become complicated when doing PHP development, especially when handling multiple database connections in one application. PHP's mysqli::debug method is a less-mentioned but very useful debugging tool, especially suitable for analyzing database connections, executing statements, and even viewing underlying network activities.

This article will introduce in detail how to effectively use the mysqli::debug method when debugging multiple database connections, and explain it in combination with sample code.

What is the mysqli::debug method?

mysqli::debug() is a static method provided in the MySQLi extension, which is used to send debugging information to the MySQL client library, mainly used in the development and debugging process. This function will not return anything, its main function is to write debug information into the debug file configured by the client library.

To make it work, debug support must be enabled in my.cnf or my.ini (usually configured via debug=d:t:o,/tmp/client.trace in the client section) and then trigger the logging using this method.

 mysqli::debug("d:t:o,/tmp/client.trace");

This will write debug information to /tmp/client.trace .

Debugging strategies under multi-database connection

When multiple databases are connected to your application, such as the primary database and multiple read replicas, debugging needs to be more targeted. Here are some recommended strategies.

1. Set a separate identifier for each connection

You can encapsulate the database connection process, add an identification name to each connection, and distinguish the source in the output log.

 function connect_to_db($label, $host, $user, $pass, $db) {
    mysqli::debug("d:t:o,/tmp/{$label}_trace.log"); // Set up the debug file for each connection
    $conn = new mysqli($host, $user, $pass, $db);
    if ($conn->connect_error) {
        die("Connection failed ({$label}): " . $conn->connect_error);
    }
    return $conn;
}

$main_db = connect_to_db("main", "localhost", "root", "password", "main_db");
$replica_db = connect_to_db("replica", "localhost", "root", "password", "replica_db");

2. Use logs to cooperate with debugging and tracking

Although mysqli::debug is the underlying debugging information, it can form a complete debugging link with PHP's log output.

 mysqli::debug("d:t:o,/tmp/replica_trace.log");
$replica_conn = new mysqli("localhost", "user", "pass", "replica_db");

if ($replica_conn->connect_error) {
    error_log("Failed to connect to the replica database: " . $replica_conn->connect_error);
} else {
    error_log("Connecting to the replica database successfully: " . $replica_conn->host_info);
}

You can use such as http://m66.net/log-viewer to build a log viewing interface to quickly locate problems.

3. Set up a unified entrance to control the debug switch

In a development or testing environment, you may not want to manually call mysqli::debug every time. At this time, you can use environment variables or configuration files to control whether to enable debugging.

 if (getenv("DB_DEBUG") === "true") {
    mysqli::debug("d:t:o,/tmp/debug_trace.log");
}

Add in the .env file:

 DB_DEBUG=true

4. Use MYSQLI_REPORT_ALL to report an error level

Combining mysqli_report() can better catch errors:

 mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
mysqli::debug("d:t:o,/tmp/debug_trace.log");

$conn = new mysqli("localhost", "user", "pass", "some_db");

This will throw exceptions instead of silent failures, making it easier for you to track problems at the code level.

mysqli::debug