How to implement mysql backup in php

General methods for MySQL backup

There are usually two methods for MySQL backup, one is to back up through the command line, and the other is to back up through visual tools.

Backup through the command line usually uses the mysqldump command, which can export the MySQL database into a .sql file containing the data and structure of all tables. The command to use mysqldump for backup is as follows:

mysqldump -u username -p dbname > backup.sql

In the above command, username is the MySQL user name, dbname is the name of the database that needs to be backed up, > backup.sql means outputting the backup data to backup.sql in the file.

Using visual tools for backup is usually done using tools such as MySQL Workbench or phpMyAdmin, which provide a user-friendly graphical interface to easily back up the database.

In this article, we will introduce how to use PHP language to write code to back up MySQL database.

PHP implements MySQL backup

Backing up the MySQL database is mainly divided into two steps: connecting to the MySQL database and exporting the data.

1. Connect to MySQL database

First, we need to connect to MySQL database. This can be achieved through PHP's built-in mysqli or PDO extensions.

$host = 'localhost';
$username = 'root';
$password = 'password';
$dbname = 'dbname';

$mysqli = new mysqli($host, $username, $password, $dbname);
if ($mysqli->connect_error) {
  die('Connection failed: ' . $mysqli->connect_error);
}

In the above code, $host is the MySQL host address, $username is the user name connected to MySQL, $password is the user password connected to MySQL, and $dbname is the name of the database that needs to be backed up.

2. Export data

After connecting to the MySQL database, we need to export the data. Similar to the mysqldump command mentioned above, we need to export the data and structure from each table and store the results in a .sql file.

$tables = array();
$result = $mysqli->query("SHOW TABLES");
while ($row = $result->fetch_assoc()) {
  $tables[] = $row['Tables_in_' . $dbname];
}

$sqlFile = 'backup.sql';
$handle = fopen($sqlFile, 'w');

foreach ($tables as $table) {
  $result = $mysqli->query("SHOW CREATE TABLE $table");
  $row = $result->fetch_assoc();
  $createTableSql = $row['Create Table'] . PHP_EOL;
  fwrite($handle, $createTableSql);

  $result = $mysqli->query("SELECT * FROM $table");
  while ($row = $result->fetch_assoc()) {
    $keys = array_keys($row);
    $fields = implode(',', $keys);
    $values = array();
    foreach ($row as $key => $value) {
      $values[] = "'" . $mysqli->real_escape_string($value) . "'";
    }
    $values = implode(',', $values);
    $insertSql = "INSERT INTO $table ($fields) VALUES ($values)" . PHP_EOL;
    fwrite($handle, $insertSql);
  }
}

fclose($handle);

In the above code, we first use the SHOW TABLES statement to obtain all tables in the database, and then use the SHOW CREATE TABLE statement to obtain the structure of each table. We will then use SELECT statements to retrieve the data for each table and write both the data and the structure to the backup file. We use the mysqli_real_escape_string function to escape the string to prevent problems when writing data.

The complete code is as follows:

$host = 'localhost';
$username = 'root';
$password = 'password';
$dbname = 'dbname';

$mysqli = new mysqli($host, $username, $password, $dbname);
if ($mysqli->connect_error) {
  die('Connection failed: ' . $mysqli->connect_error);
}

$tables = array();
$result = $mysqli->query("SHOW TABLES");
while ($row = $result->fetch_assoc()) {
  $tables[] = $row['Tables_in_' . $dbname];
}

$sqlFile = 'backup.sql';
$handle = fopen($sqlFile, 'w');

foreach ($tables as $table) {
  $result = $mysqli->query("SHOW CREATE TABLE $table");
  $row = $result->fetch_assoc();
  $createTableSql = $row['Create Table'] . PHP_EOL;
  fwrite($handle, $createTableSql);

  $result = $mysqli->query("SELECT * FROM $table");
  while ($row = $result->fetch_assoc()) {
    $keys = array_keys($row);
    $fields = implode(',', $keys);
    $values = array();
    foreach ($row as $key => $value) {
      $values[] = "'" . $mysqli->real_escape_string($value) . "'";
    }
    $values = implode(',', $values);
    $insertSql = "INSERT INTO $table ($fields) VALUES ($values)" . PHP_EOL;
    fwrite($handle, $insertSql);
  }
}

fclose($handle);

The above is the detailed content of How to implement mysql backup in php. For more information, please follow other related articles on the PHP Chinese website!