PHP Background Backup MySQL Database Source Code Instance

PHP backup mysql database source code, in the perfect PHP + Mysql project, in the background will have the backup Mysql database function, with this function, for some inconvenient to write shell script backup VPS, do not use FTP or use mysql management tools for mysql database backup download, very convenient.

The following is an php database backup source code, we can also modify according to their own needs.

<?php 
//  Backup database 
$host = "localhost";
$user = "root"; // Database account number 
$password = ""; // Database password 
$dbname = "mysql"; // Database name 
//  The account number, password and name here are all sent from the page 
if (!mysql_connect($host, $user, $password)) //  Connect mysql Database 
{
 echo ' Database connection failed, please check and try again ';
  exit;
} 
if (!mysql_select_db($dbname)) //  Whether the database exists 
{
 echo ' No database exists :' . $dbname . ', Please check and try again ';
  exit;
} 
mysql_query("set names 'utf8'");
$mysql = "set charset utf8;\r\n";
$q1 = mysql_query("show tables");
while ($t = mysql_fetch_array($q1))
{
  $table = $t[0];
  $q2 = mysql_query("show create table `$table`");
  $sql = mysql_fetch_array($q2);
  $mysql .= $sql['Create Table'] . ";\r\n";
  $q3 = mysql_query("select * from `$table`");
  while ($data = mysql_fetch_assoc($q3))
  {
    $keys = array_keys($data);
    $keys = array_map('addslashes', $keys);
    $keys = join('`,`', $keys);
    $keys = "`" . $keys . "`";
    $vals = array_values($data);
    $vals = array_map('addslashes', $vals);
    $vals = join("','", $vals);
    $vals = "'" . $vals . "'";
    $mysql .= "insert into `$table`($keys) values($vals);\r\n";
  } 
} 
$filename = $dbname . date('Ymjgi') . ".sql"; // Storage path, which is stored to the outermost layer of the project by default 
$fp = fopen($filename, 'w');
fputs($fp, $mysql);
fclose($fp);
echo " Data backup succeeded ";
?>

PHP performs backup and restore of Mysql database

Backup using the mysqldump command

The mysqldump command backs up the data in the database into a text file. The structure of the table and the data in the table will be stored in the generated text file.

The mysqldump command works simply. It first finds out the structure of the table to be backed up, and then generates an CREATE statement in the text file. Then, all the records in the table are converted into one INSERT statement. Then, with these statements, you can create tables and insert data.

mysqldump Basic Syntax:

mysqldump -u username -p password dbname table1 table2 ...-> BackupName.sql

username is the database user name; password is the database password The dbname parameter represents the name of the database; The table1 and table2 parameters indicate the name of the table to be backed up, and if it is empty, the entire database will be backed up; The BackupName. sql parameter table designs the name of the backup file, which can be preceded by an absolute path. Usually, the database is divided into one file with the suffix sql;

$exec="D:/phpstudy/mysql/bin/mysqldump -u".$db_user." -p".$db_pwd." ".$db_name." > ".$name;
exec($exec);
// To set the mysql The path to the execution file. 

Note: There should be no spaces in password after-p, otherwise only an empty sql file may be generated, and $name is the generated file path and name.

A simple backup of the database is done.

Syntax for restoring databases backed up using the mysqldump command

mysql -u root -p password dbname < backup.sql

The parameters are the same as those in backup, backup.sql Is the database file to be restored, and the file path is the absolute path.

Summarize