How to use PHP to implement data backup and recovery functions

How to use PHP to implement data backup and recovery functions

With the continuous development of information technology, data backup and recovery functions are becoming more and more important. When developing and managing websites or applications, we often encounter the need for backup and recovery of data in databases. This article will teach you how to use PHP to implement data backup and recovery functions.

1. Data backup
When we need to back up the data in the database, we can use PHP's MySQL function library to complete the operation. The following is a sample code for backing up data in the database:

<?php
// 数据库连接配置
$host = 'localhost';
$user = 'root';
$pass = 'password';
$dbname = 'database';

// 连接数据库
$conn = mysqli_connect($host, $user, $pass, $dbname);

// 查询数据表
$tables = array();
$result = mysqli_query($conn, "SHOW TABLES");
while($row = mysqli_fetch_row($result)){
    $tables[] = $row[0];
}

// 备份数据
foreach($tables as $table){
    $result = mysqli_query($conn, "SELECT * FROM $table");
    $num_fields = mysqli_num_fields($result);

    $backup .= 'DROP TABLE IF EXISTS '.$table.';';
    $row2 = mysqli_fetch_row(mysqli_query($conn, "SHOW CREATE TABLE $table"));
    $backup .= "

".$row2[1].";

";

    for($i=0; $i<$num_fields; $i++){
        while($row = mysqli_fetch_row($result)){
            $backup .= 'INSERT INTO '.$table.' VALUES(';
            for($j=0; $j<$num_fields; $j++){
                $row[$j] = addslashes($row[$j]);
                $row[$j] = str_replace("
", "\n", $row[$j]);
                if(isset($row[$j])){
                    $backup .= '"'.$row[$j].'"';
                }else{
                    $backup .= '""';
                }
                if($j<$num_fields-1){
                    $backup .= ',';
                }
            }
            $backup .= ");
";
        }
    }
    $backup .= "


";
}

// 保存备份文件
$filename = 'backup_'.date('Y-m-d_H-i-s').'.sql';
file_put_contents($filename, $backup);

echo '数据备份成功！';
?>

Code description:

1. First, we need to set the configuration information of the database connection and pass mysqli_connect () function connects to the database.
2. Then, we obtain all the data tables in the database by executing the SHOW TABLES query statement.
3. Next, we traverse all data tables and execute the SHOW CREATE TABLE and SELECT * FROM query statements respectively to obtain the structure and data of the data table.
4. During the backup process, we use the DROP TABLE IF EXISTS statement to delete the existing data table, and use the CREATE TABLE statement to create a new data table structure . Then, we insert each row of data into the backup file using the INSERT INTO statement.
5. Finally, we save the backup file to the specified path on the server and name it backup_year-month-day_hour-minute-second.sql.

2. Data recovery
When we need to restore the backed up data, we can use the following code to achieve it:

<?php
// 数据库连接配置
$host = 'localhost';
$user = 'root';
$pass = 'password';
$dbname = 'database';

// 连接数据库
$conn = mysqli_connect($host, $user, $pass, $dbname);

// 读取备份文件
$backup_file = 'backup_2021-01-01_00-00-00.sql';
$backup_content = file_get_contents($backup_file);

// 按照分号拆分SQL语句
$queries = explode(';', $backup_content);

// 恢复数据
foreach($queries as $query){
    if(!empty(trim($query))){
        mysqli_query($conn, $query);
    }
}

echo '数据恢复成功！';
?>

Code description:

1. First, we need to set the configuration information of the database connection and connect to the database through the mysqli_connect() function.
2. Then, we use the file_get_contents() function to read the backup file contents.
3. Next, we split the contents of the backup file into independent SQL statements according to semicolons.
4. During the recovery process, we traverse all SQL statements and execute the mysqli_query() function one by one to recover the data.
5. Finally, we output a prompt message indicating successful recovery.

Through the above sample code, we can implement PHP's data backup and recovery functions for the database. In practical applications, we can optimize and improve the code according to specific needs and project structure. At the same time, we can also combine scheduled tasks and compression technology to achieve regular backup and save storage space.

The above is the detailed content of How to use PHP to implement data backup and recovery functions. For more information, please follow other related articles on the PHP Chinese website!