php 备份数据库代码(生成word,excel,json,xml,sql)

下面将详细讲解“PHP备份数据库代码(生成Word,Excel,JSON,XML,SQL)”的完整攻略。这个攻略包含以下几个部分：

1. 连接数据库
2. 获取数据表信息
3. 生成SQL脚本备份
4. 生成Excel备份
5. 生成Word备份
6. 生成JSON备份
7. 生成XML备份

第一步，连接数据库。连接数据库是备份操作的第一步。要连接数据库，需要使用PHP内置的mysqli或PDO扩展中的一个。使用以下代码可以连接MySQL数据库：

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

第二步，获取数据表信息。在备份数据库的时候，需要获取数据库中所有的数据表信息。可以使用以下代码来获取数据表列表：

$sql = "SHOW TABLES";
$result = $conn->query($sql);

$tables = array();

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        array_push($tables, $row['Tables_in_database']);
    }
} else {
    echo "0 results";
}

第三步，生成SQL脚本备份。SQL脚本备份是备份数据库中所有数据的最简单方法。使用以下代码可以生成SQL脚本备份：

$sql = "-- MySQL dump\n";
$sql .= "-- Host: " . $servername . "\n";
$sql .= "-- Generation Time: " . date('M j, Y h:i:s A') . "\n";
$sql .= "-- Server version: " . $conn->server_info . "\n\n";

foreach ($tables as $table) {
    $sql .= "DROP TABLE IF EXISTS $table;\n";
    $sql .= "CREATE TABLE $table LIKE `$table`;\n";
    $tableData = $conn->query("SELECT * FROM $table") or die($conn->error);
    while ($row = mysqli_fetch_assoc($tableData)) {
        $insert = "INSERT INTO $table (";
        $values = "VALUES (";
        foreach ($row as $key => $value) {
            $insert .= "`$key`, ";
            $values .= "'" . addslashes($value) . "', ";
        }
        $insert = rtrim($insert, ', ') . ")";
        $values = rtrim($values, ', ') . ")";
        $sql .= $insert . ' ' . $values . ";\n";
    }
    $sql .= "\n";
}

$file = "backup_" . date('Y-m-d-H-i-s') . ".sql";
file_put_contents($file, $sql);

如果您要恢复备份文件，只需执行生成的sql脚本文件即可。

第四步，生成Excel备份。使用以下代码可以生成Excel备份：

require_once 'vendor/autoload.php';
use PHPExcel_IOFactory;

foreach ($tables as $table) {
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0);
    $sheetIndex = 0;
    $objPHPExcel->getActiveSheet()->setTitle($table);
    $column = 'A';
    $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight('25');
    $tableHead = $conn->query("SHOW COLUMNS FROM $table") or die($conn->error);
    while ($headRow = mysqli_fetch_assoc($tableHead)) {
        $objPHPExcel->getActiveSheet()->SetCellValue($column++ . '1', $headRow['Field']);
    }
    $tableData = $conn->query("SELECT * FROM $table") or die($conn->error);
    $row = 2;
    while ($dataRow = mysqli_fetch_assoc($tableData)) {
        $column = 'A';
        foreach ($dataRow as $cell) {
            $objPHPExcel->getActiveSheet()->SetCellValue($column++ . $row, $cell);
        }
        $row++;
    }
    $writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $writer->save('backup_' . date('Y-m-d-H-i-s') . '_' . $table . '.xlsx');
}

第五步，生成Word备份。使用以下代码可以生成Word备份：

require_once 'vendor/autoload.php';
use PhpOffice\PhpWord\IOFactory;
use PhpOffice\PhpWord\PhpWord;

foreach ($tables as $table) {
    $phpWord = new PhpWord();
    $phpWord->addSection();
    $section = $phpWord->addSection();
    $sectionStyle = array(
        'marginTop' => \PhpOffice\PhpWord\Shared\Converter::pixelToTwip(5),
    );
    $tableStyle = array(
        'borderColor' => '000000',
        'borderSize' => 1,
        'cellMargin' => 50,
    );
    $table = $section->addTable($tableStyle);
    $tableHead = $conn->query("SHOW COLUMNS FROM $table") or die($conn->error);
    $headers = array();
    while ($headRow = mysqli_fetch_assoc($tableHead)) {
        $headers[] = $headRow['Field']
    }
    $table->addRow();
    foreach ($headers as $header) {
        $table->addCell()->addText($header);
    }
    $tableData = $conn->query("SELECT * FROM $table") or die($conn->error);
    while ($dataRow = mysqli_fetch_assoc($tableData)) {
        $table->addRow();
        foreach ($dataRow as $cell) {
            $table->addCell()->addText($cell);
        }
    }
    $objWriter = IOFactory::createWriter($phpWord, 'Word2007');
    $fileName = "backup_" . date('Y-m-d-H-i-s') . '_' . $table . '.docx';
    $objWriter->save($fileName);
}

第六步，生成JSON备份。JSON备份是备份数据库数据的一种简单方式。使用以下代码可以生成JSON备份：

foreach ($tables as $table) {
    $tableData = $conn->query("SELECT * FROM $table") or die($conn->error);
    $jsonData = array();
    while ($row = mysqli_fetch_assoc($tableData)) {
        array_push($jsonData, $row);
    }
    $file = "backup_" . date('Y-m-d-H-i-s') . '_' . $table . ".json";
    file_put_contents($file, json_encode($jsonData));
}

第七步，生成XML备份。XML备份是备份数据库数据的一种简单方式。使用以下代码可以生成XML备份：

require_once('vendor/autoload.php');
use Spatie\ArrayToXml\ArrayToXml;

foreach ($tables as $table) {
    $tableData = $conn->query("SELECT * FROM $table") or die($conn->error);
    $xmlData = array();
    while ($row = mysqli_fetch_assoc($tableData)) {
        array_push($xmlData, $row);
    }
    $xmlArr = array('data' => $xmlData);
    $xml = ArrayToXml::convert($xmlArr, 'table');
    $file = "backup_" . date('Y-m-d-H-i-s') . '_' . $table . ".xml";
    file_put_contents($file, $xml);
}

以上是PHP备份数据库代码(生成Word,Excel,JSON,XML,SQL)的攻略，其中包含了连接数据库、获取数据表信息和生成SQL脚本备份、Excel备份、Word备份、JSON备份和XML备份等操作。