Database backup and recovery practice: application in PHP programming

With the development of the Internet, database backup and recovery have become a very important part of the website operation and development process. The database is the core of the website and contains a lot of important information. If data is lost or damaged, it will have a great impact on the operation of the website and the user experience. Therefore, for website operators and developers, the importance of database backup and recovery is self-evident.

This article will focus on database backup and recovery operations in PHP programming for reference by developers.

1. Database backup

Database backup can be understood as copying the data in the database and saving it to another location in case it needs to be restored in the future. Backups can be performed according to time, period, frequency, etc. Once data is lost or damaged, backup files can be used to quickly restore and ensure the normal operation of the data.

Backup methods are divided into two types: manual backup and automatic backup. Manually generating backup requires manually exporting the database file through tools such as phpMyAdmin, which is cumbersome and requires frequent operations, which is not practical enough. Automatic backup can be done by writing PHP scripts to perform backup according to time period, frequency, etc., and has the characteristics of automation and timing.

The following describes the methods of manually generating backups and automatic backups.

1. Manually generate backups

Manually generating backups requires the use of database management tools such as phpMyAdmin. The specific steps are as follows:

1.1 Log in to phpMyAdmin

Enter the server address and phpMyAdmin path information in the browser, enter the user name and password, and you can log in to phpMyAdmin to see the database list.

1.2 Select the database that needs to be backed up

Select the database that needs to be backed up in the left navigation bar and enter the database management page.

1.3 Select Export

Click "Export" in the upper right corner of the page, and the export option will appear.

1.4 Export

Select the options to export, such as export type, compression format, character set, etc., and select the table to export. Finally, click the "Export" button as prompted.

2. Automatic backup

Automatic backup can be achieved by writing PHP scripts. The following is a simple backup code example:

<?php

//数据库信息
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '123456';
$dbname = 'test_db';

//备份文件信息
$backup_file = "backup/{$dbname}_".date("YmdHis").'.sql';
$file_handle = fopen($backup_file, 'w+');

//连接数据库
$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
if (!$conn){
    die("连接失败：" . mysqli_error());
}

//获取数据库所有表信息
$table_result = mysqli_query($conn, 'SHOW TABLES');
$tables = array();
while ($row = mysqli_fetch_row($table_result)) {
    $tables[] = $row[0];
}

//获取所有表数据
foreach($tables as $table){
    $result = mysqli_query($conn, "SELECT * FROM {$table}");
    while ($row = mysqli_fetch_row($result)){
        $sql = "INSERT INTO {$table} VALUES ('".implode("', '",$row)."');
";
        fwrite($file_handle, $sql);
    }
}

//备份结束
fclose($file_handle);
mysqli_close($conn);
echo "备份成功：{$backup_file}";

?>

The above code can automatically back up the data of all tables in the specified database. The backup file is saved in the backup directory, and the file name is according to "database name_current time.sql" Format naming.

2. Database recovery

Database recovery is to re-import the backed-up files into the database to restore the previous data state. The specific steps are as follows:

2.1 Log in to phpMyAdmin

Enter the server address and phpMyAdmin path information in the browser, enter the user name and password, and you can log in to phpMyAdmin to see the database list.

2.2 Select the database that needs to be restored

Select the database that needs to be restored in the left navigation bar and enter the database management page.

2.3 Select Import

Click "Import" in the upper right corner of the page, and the import option will appear.

2.4 Import

Select the backup file to be imported, and configure options, such as character set, SQL statement delimiter, etc. Finally, click the "Start Import" button as prompted.

It is worth noting that when there is a primary and foreign key relationship in the database, you need to manually delete the corresponding primary and foreign key relationship during recovery and remove it from the backup file to avoid data anomalies.

The above are database backup and recovery practices in PHP programming. I believe it can help developers better maintain and protect the data in the database, while also improving the stability and security of website operations.

The above is the detailed content of Database backup and recovery practice: application in PHP programming. For more information, please follow other related articles on the PHP Chinese website!