But there was a problem:
First, the user running php is apche, such as nobody, so it generally does not have permission to access the /usr/local/mysql/data directory.
Second, even if you can access it, how can you copy the files in the /usr/local/mysql/data directory? Because mysql does not run access when it is running, then the nobody user has permission to stop mysql service, which is impossible!
The more I thought about it, the more I became, the more I was wrong. There was no way to see if I could start with php operating the database, so I went to check out phpMyadmin and Discuz! The code, haha, so I secretly copied Discuz! The code of the database is formed as follows. (Thanks to the developer of Discuz!)
There are two ways to back up the database. One is to only backup the structure of the database, and the other is to back up the structure and all the data. Of course, the second method is good, but I did it all in order to consider possible needs.
/****** Backup database structure *******/
/*
Function name: table2sql()
Function function: convert table structure into SQL
Function parameter: $table: The 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].";\n\n";
return $tabledump;
}
/****** Backup database structure and all data *******/
/*
Function name: data2sql()
Function function: convert table structure and data into SQL
Function parameter: $table: The table name to be extracted
Return Value: Returns 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].";\n\n";
$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 operation *******/
OK, since we have written all the code, how can we implement backups in specific programs? Let’s look at the following code.
/* Backup database */
// Note: Our database operation uses the phplib DB class
// Define the data table, prefix, and where to save it
$tables = array('us_sort', 'us_download', 'us_article', 'us_guestbook'); //Define the data table to be saved, an array
$prefix = 'us_'; // Prefix for the .sql file to be saved
$saveto = 'server'; // Where to save it, is it local or on the server, the default is the server
$back_mode = 'all'; // The way to save is to backup all or just save the database structure
$admin = 'heiyeluren'; //Admin name
$admin_email = 'heiyeluren@'; // Admin Email
// Define the file name for data storage
$local_filename = $('Ymd_His').'.sql"';
if (!$filename) { $filename = $db_backup_path . $prefix . date('Ymd_His_'). create_check_code(4) . ".sql"; }
$filename = $(Ymd_His). create_check_code(6).".sql"; // File name saved on the server
// Note the create_check_code() function below, which is a function that generates random codes. For details, please refer to:
// https:///article/
// Get database structure and data content
foreach($tables as $table)
{
if ($back_mode == 'all') { $sqldump .= data2sql($table); }
if ($back_mode == 'table') { $sqldump .= table2sql($table); }
}
// If the data content is not empty, start saving
if(trim($sqldump))
{
// Write the beginning information
$sqldump =
"# --------------------------------------------------------\n".
"# Datasheet Backup\n".
"#\n".
"# Server: $db->Host\n".
"# Database: $db->Database\n".
"# Backup number: ". create_sess_id() ."\n". // Here is a function that generates session id
"# Backup time: ".time_to_date('',6)."\n". // Here is the function to get the current time
"#\n".
"# Admin: $admin ($admin_email)\n". // Admin username and email address
"# $copyright\n".
"# --------------------------------------------------------\n\n\n".
$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 end locally
// Save on the 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 whether you have permission to write the directory attributes.");
}
else
{
exit_msg("Data is successfully backed up to the server <a href=\"$filename\">$filename</a>.");
}
}
else
{
exit_msg("Cannot open the directory you specified". $filename .", please determine whether 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 end of server
}
else
{
exit_msg("The data table has nothing");
}
/* The backup database ends */
Haha, basically this ends, and then the problem involved is how to restore data to the database. I think this is not complicated, but it is best to meet the functions of recovering data from the client and the server.
First, the user running php is apche, such as nobody, so it generally does not have permission to access the /usr/local/mysql/data directory.
Second, even if you can access it, how can you copy the files in the /usr/local/mysql/data directory? Because mysql does not run access when it is running, then the nobody user has permission to stop mysql service, which is impossible!
The more I thought about it, the more I became, the more I was wrong. There was no way to see if I could start with php operating the database, so I went to check out phpMyadmin and Discuz! The code, haha, so I secretly copied Discuz! The code of the database is formed as follows. (Thanks to the developer of Discuz!)
There are two ways to back up the database. One is to only backup the structure of the database, and the other is to back up the structure and all the data. Of course, the second method is good, but I did it all in order to consider possible needs.
/****** Backup database structure *******/
/*
Function name: table2sql()
Function function: convert table structure into SQL
Function parameter: $table: The 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].";\n\n";
return $tabledump;
}
/****** Backup database structure and all data *******/
/*
Function name: data2sql()
Function function: convert table structure and data into SQL
Function parameter: $table: The table name to be extracted
Return Value: Returns 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].";\n\n";
$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 operation *******/
OK, since we have written all the code, how can we implement backups in specific programs? Let’s look at the following code.
/* Backup database */
// Note: Our database operation uses the phplib DB class
// Define the data table, prefix, and where to save it
$tables = array('us_sort', 'us_download', 'us_article', 'us_guestbook'); //Define the data table to be saved, an array
$prefix = 'us_'; // Prefix for the .sql file to be saved
$saveto = 'server'; // Where to save it, is it local or on the server, the default is the server
$back_mode = 'all'; // The way to save is to backup all or just save the database structure
$admin = 'heiyeluren'; //Admin name
$admin_email = 'heiyeluren@'; // Admin Email
// Define the file name for data storage
$local_filename = $('Ymd_His').'.sql"';
if (!$filename) { $filename = $db_backup_path . $prefix . date('Ymd_His_'). create_check_code(4) . ".sql"; }
$filename = $(Ymd_His). create_check_code(6).".sql"; // File name saved on the server
// Note the create_check_code() function below, which is a function that generates random codes. For details, please refer to:
// https:///article/
// Get database structure and data content
foreach($tables as $table)
{
if ($back_mode == 'all') { $sqldump .= data2sql($table); }
if ($back_mode == 'table') { $sqldump .= table2sql($table); }
}
// If the data content is not empty, start saving
if(trim($sqldump))
{
// Write the beginning information
$sqldump =
"# --------------------------------------------------------\n".
"# Datasheet Backup\n".
"#\n".
"# Server: $db->Host\n".
"# Database: $db->Database\n".
"# Backup number: ". create_sess_id() ."\n". // Here is a function that generates session id
"# Backup time: ".time_to_date('',6)."\n". // Here is the function to get the current time
"#\n".
"# Admin: $admin ($admin_email)\n". // Admin username and email address
"# $copyright\n".
"# --------------------------------------------------------\n\n\n".
$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 end locally
// Save on the 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 whether you have permission to write the directory attributes.");
}
else
{
exit_msg("Data is successfully backed up to the server <a href=\"$filename\">$filename</a>.");
}
}
else
{
exit_msg("Cannot open the directory you specified". $filename .", please determine whether 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 end of server
}
else
{
exit_msg("The data table has nothing");
}
/* The backup database ends */
Haha, basically this ends, and then the problem involved is how to restore data to the database. I think this is not complicated, but it is best to meet the functions of recovering data from the client and the server.