PHP implements mysql backup

With the development of the Internet, the use of databases is becoming more and more widespread, and database backup is becoming more and more important. Database backup allows us to quickly restore data in the event of data loss or damage, ensuring system stability. This article will introduce how to use PHP to implement MySQL backup.

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. Next, we use a SELECT statement to obtain the data for each table and write the data and structure together into the backup file. When writing data, we use the mysqli_real_escape_string function to escape the string.

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);

Summary

This article introduces how to use PHP to implement MySQL backup. Backing up the MySQL database allows us to quickly restore data in the event of data loss and ensure system stability. Backing up the MySQL database can be achieved through the mysqldump command line or visual tools. This article introduces how to use PHP to write code to back up MySQL data.

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