How to dump database into SQL file using PHP

During the PHP development process, it is often necessary to back up the database. When backing up the database to a remote server or local disk, we usually dump the database into a SQL file. SQL file is a text file that contains the original SQL query statement, which can be used to restore or clone the entire database or a part of it. This article will introduce how to use PHP to dump the database into a SQL file.

Step 1: Establish a database connection

In PHP, to connect to the database, you must use mysqli or PDO. This article takes mysqli as an example. First, we need to establish a connection to the database and obtain the tables and other information in the database.

<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "myDB";
 
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检查连接是否成功
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

Step 2: Get the tables in the database

Next, we need to use SQL query statements to get all the table names in the database. We will use SHOW TABLES to get the list of tables in the database. The query returns a result set named "Tables_in_databaseName".

<?php
$sql = "SHOW TABLES";
$result = $conn->query($sql);
 
if ($result->num_rows > 0) {
    // 输出每个表的名称
    while($row = $result->fetch_assoc()) {
        echo $row["Tables_in_myDB"]. "<br>";
    }
} else {
    echo "0 个结果";
}

Step 3: Dump the tables in the database to SQL

Now that we have obtained the tables in the database, we can dump them using the mysqldump command into a SQL file. mysqldump is a command line tool for MySQL that can output SQL statements to back up or dump MySQL databases or tables.

Here is a sample code that uses PHP to execute the mysqldump command to export the entire database to a SQL file:

<?php
$command = "mysqldump --user={$username} --password={$password} --host={$servername} {$dbname} > {$dbname}.sql";
exec($command);

In the above code, we use exec The function passes the mysqldump command to the command line. This command dumps the database into a file named "databaseName.sql". Note that, as opposed to input, output uses the ">" symbol.

If you only need to dump one table in the database into a SQL file, you can use the following code:

<?php
$command = "mysqldump --user={$username} --password={$password} --host={$servername} {$dbname} {$tableName} > {$tableName}.sql";
exec($command);

In the above code, you only need the name of the table to be dumped Just pass it to the command mysqldump.

Step 4: Close the database connection

After completing the data dump, the connection established with the database must be closed. The connection can be closed through the close function of mysqli.

<?php
$conn->close();

Conclusion

Through the above steps, we can dump the entire database or a certain table into a SQL file. This makes it easy to migrate databases from one server to another, while also providing useful tools when backing up and restoring databases.

The above is the detailed content of How to dump database into SQL file using PHP. For more information, please follow other related articles on the PHP Chinese website!