Linux php mysql database backup implementation code

Linux php mysql database backup implementation code_PHP tutorial

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Release： 2016-07-21 15:47:24

Original

951 people have browsed it

But there are problems:
First, the user running php is the apche user, such as nobody, then it generally does not have permission to access the /usr/local/mysql/data directory
Second, even if it can access, So how can you copy the files in the /usr/local/mysql/data directory? Because mysql is not accessible when it is running, then the user nobody has the authority to stop the mysql service, which is impossible!
The more I thought about it, the more something was wrong. I had no choice but to see if I could start by operating the database in PHP, so I looked at phpMyadmin and Discuz! The code, haha, so I copied Discuz! The code forms the following method of backing up the database. (Thank you to the developer of Discuz!)

There are two ways to back up the database. One is to back up only the structure of the database, and the other is to back up the structure and all data. Of course, the second way The method is good, but I just did it to consider possible needs.

/****** Back up database structure ******/

/*
Function name: table2sql()
Function: Convert the table structure into SQL
Function parameters: $table: table name to be extracted
Return value: Returns the extracted result, SQL collection
Function author: heiyeluren
*/

function table2sql($table)
{
global $db;
$tabledump = "DROP TABLE IF EXISTS $table;n";
$createtable = $db->query("SHOW CREATE TABLE $table");
$create = $db->fetch_row($createtable);
$tabledump .= $create[1].";nn";

return $tabledump;
}

/****** Back up the database structure and all data ******/
/*
Function name: data2sql()
Function function: Convert the table structure and data into SQL
function parameters ：$table: The name of the table to be extracted
Return value: Return the extracted result, SQL collection
Function author: heiyeluren
*/
function data2sql($table)
{
global $db;
$tabledump = "DROP TABLE IF EXISTS $table;n";
$createtable = $db->query("SHOW CREATE TABLE $table");
$create = $db->fetch_row($createtable);
$tabledump .= $create[1].";nn";

$rows = $db->query("SELECT * FROM $table");
$numfields = $db->num_fields($rows);
$numrows = $db->num_rows($rows);
while ($row = $db- >fetch_row($rows))
{
$comma = "";
$tabledump .= "INSERT INTO $table VALUES(";
for($i = 0; $i < ; $numfields; $i++)
{
$tabledump .= $comma."'".mysql_escape_string($row[$i])."'";
$comma = ",";
}
$tabledump .= ");n";
}
$tabledump .= "n";

return $tabledump;
}

/****** Specific implementation operations ******/
Okay, now that we have written out the code, how do we implement backup in a specific program? Let’s look at the following code.

/* Back up database*/
// Note: Our database operation uses the DB class of phplib

// Define the data table to be saved, prefix, Where to save
$tables = array('us_sort', 'us_download', 'us_article', 'us_guestbook'); //Define the data table to be saved, an array
$prefix = 'us_'; // The prefix of the .sql file to be saved
$saveto = 'server'; // Where to save it, whether locally or on the server, the default is the server
$back_mode = 'all'; // To How to save, whether to back up all or only save the database structure
$admin = 'heiyeluren'; //Administrator name
$admin_email = 'heiyeluren@163.com'; //Administrator email

// Define the file name for data saving
$local_filename = $prefix.date('Ymd_His').'.sql"';
if (!$filename) { $filename = $db_backup_path . $prefix . date('Ymd_His_'). create_check_code(4) . ".sql"; }
$filename = $prefix.date(Ymd_His). create_check_ code(6).".sql"; // Save on the server The file name
// Pay attention to the create_check_code() function at the end. This is a function that generates random codes. For details, please refer to:
// http://www.jb51.net/article/17423.htm

//Get the database structure and data content
foreach($tables as $table)
{
if ($back_mode == 'all') { $sqldump .= data2sql($table ); }
if ($back_mode == 'table') { $sqldump .= table2sql($table); }
}

// Start saving if the data content is not empty
if(trim($sqldump))
{
//Write the beginning information
$sqldump =
"# ----------------- ---------------------------------------n".
"# Data table backup n".
"#n".
"# Server: $db->Hostn".
"# Database: $db->Databasen".
"# Backup number: " . create_sess_id() ."n". // Here is a function to generate session id
"# Backup time: ".time_to_date('',6)."n". // Here is a function to get the current time
"#n".
"# Administrator: $admin ($admin_email)n". // Administrator's username and email address
"# $copyrightn".
"# -- -------------------------------------------------- ----nnn".
$sqldump;

// Save to local
if($saveto == "local")
{
ob_end_clean();
header('Content-Encoding: none');
header('Content-Type: '.(strpos($HTTP_SERVER_VARS['HTTP_USER_AGENT'], 'MSIE') ? 'application/octetstream' : 'application/octet -stream'));
header('Content-Disposition: '.(strpos($HTTP_SERVER_VARS['HTTP_USER_AGENT'], 'MSIE') ? 'inline; ' : 'attachment; ').'filename="' .$local_filename);
header('Content-Length: '.strlen($sqldump));
header('Pragma: no-cache');
header('Expires: 0');
echo $sqldump;
}
// Save to local end

// Save on server
if($saveto == "server")
{
if($filename != "")
{
@$fp = fopen($filename, "w+");
if ($fp)
{
@flock($ fp, 3);
if(@!fwrite($fp, $sqldump))
{
@fclose($fp);
exit_msg("The data file cannot be saved to the server, please check Do you have write permission for the directory attributes? ");
}
else
{
exit_msg("Data was successfully backed up to server$filename.");
}
}
else
{
exit_msg("Cannot open the directory you specified ". $filename .", please make sure the directory exists or has the corresponding permissions");
}
}
else
{
exit_msg("You did not enter the backup file name, please return to modify.");
}
}
// Save to server End
}
else
{
exit_msg("Data table has no content");
}

/* End of backup database*/

Haha, that’s basically it. Then one of the issues involved is how to restore the data to the database. I think this is not complicated, but it is best to be able to satisfy both the slave client and the slave server. Data recovery function.