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 usemysqliorPDO. This article takesmysqlias an example. First, we need to establish a connection to the database and obtain the tables and other information in the database.

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 useSHOW TABLESto get the list of tables in the database. The query returns a result set named "Tables_in_databaseName".

query($sql); if ($result->num_rows > 0) { // 输出每个表的名称 while($row = $result->fetch_assoc()) { echo $row["Tables_in_myDB"]. "
"; } } 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 themysqldumpcommand into a SQL file.mysqldumpis 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 themysqldumpcommand to export the entire database to a SQL file:

 {$dbname}.sql"; exec($command);

In the above code, we useexec Thefunction passes themysqldumpcommand 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:

 {$tableName}.sql"; exec($command);

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

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 theclosefunction ofmysqli.

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!