Tweet
problem in mysql backup
سلام دوستان
با اين كلاس از يك DB بك آپ گرÙتم . موقع restore كردن به مشكل برمي خورم Ùˆ phpmyadmin تقريبا به اكثر خط ها گير ميده ...
مشكل از phpmyadmin منه يا ورژن php , mysql مشكل ساز شده ؟ يا ... ؟
نمونه backup رو ضميمه كردم ...
ضميمه : از IAM براي backup Ùˆ restore استÙاده ميكنم، اما در كل مي خوام مشكل اين كلاس Ùˆ backup رو متوجه بشم !
ضميمه 2 : اگه بخوام يه كلاس بنويسم كه backup همين كلاس رو واسم restore كنه بايد Øتما "/n" ها رو Øذ٠كنم از backup Ùˆ بعد با توجه به علامت ";" string رو بشكونم Ùˆ دونه دونه ÙŠ query ها رو اجرا كنم ØŸ روش بهتري وجود نداره ØŸ
ممنون
با اين كلاس از يك DB بك آپ گرÙتم . موقع restore كردن به مشكل برمي خورم Ùˆ phpmyadmin تقريبا به اكثر خط ها گير ميده ...
مشكل از phpmyadmin منه يا ورژن php , mysql مشكل ساز شده ؟ يا ... ؟
نمونه backup رو ضميمه كردم ...
کد PHP:
<?PHP
class backup{
// private variables which can be access able after creating a new object of this class
// and it is access able to objects of this class such as functions
// Server or Host name
private $host;
// Server Username
private $user;
// Server Username's Password
private $pass;
// Database name for backing up
private $db;
// Backup string
private $str;
//
private $mysqli;
// constructor methods for classes when creating new object of this class
function __construct($host,$user,$pass,$db){
// check if variables are avilable and not empty, and add to private variables
if((isset($host) && $host != "") || (isset($user) && $user != "") || (isset($pass) && $pass != "") || (isset($db) && $db != "")){
$this->host = $host;
$this->user = $user;
$this->pass = $pass;
$this->db = $db;
// Call function for connecting to server and to database
$this->connection();
}
}
// function for connection
function connection(){
// connect to mysql database using private variables
$mysqli = @new mysqli($this->host,$this->user,$this->pass,$this->db);
$this->mysqli = $mysqli;
// if connection faild than print error message
if($mysqli->connect_error)
die("Error connecting to server: ".$mysqli->connect_error);
// return true if connected to server and database
return true;
}
// Get tables information such as Fields, Null Values, Default Values, Keys, Extra and etc...
function get_table_info($tblname){
$mysqli = $this->mysqli;
// table name
$tbl = $tblname;
// String variable for storing table information
$str = "";
$str.="-----------------------------------\n\n--\n--Creating table `$tbl`\n--\n\n";
// query for creating table
$str .="CREATE TABLE IF NOT EXISTS $tbl (\n";
// string query for getting fields from table
$str_fields = "SHOW FIELDS FROM $tbl";
// executing query
$fields = $mysqli->query($str_fields)or die($mysql->error);
// array for storing keys of fields
$keys_str = array();
// getting fields from table
while($fetch = $fields->fetch_assoc())
{
// adding field name and field type
$str .="`".$fetch['Field']."` ".$fetch['Type']." ";
// if Null property is not yes or Null property is no
if($fetch['Null'] != "YES"){
// add NOT NULL
$str .= " NOT NULL ";
}
// if default value is assigned or default value is not empty then add default value
if((isset($fetch['Default']) && (!empty($fetch['Default'])) || ($fetch['Default'] == "0")))
$str .= "DEFAULT '".$fetch['Default']."' ";
// if Extra property is not empty
if($fetch['Extra'] != ""){
// then change to upper case and add Extra property
// You can remove strtoupper if you don't want to change it to upper case
$str .= " ".strtoupper($fetch['Extra']);
}
// check for keys
if($fetch['Key']=="PRI")
$keys_str[] .="PRIMARY KEY (`".$fetch['Field']."`)";
if($fetch['Key']=="UNI")
$keys_str[] .="UNIQUE KEY (`".$fetch['Field']."`)";
//add coma separator after each field
$str .=",\n";
}
// run loop on keys (array) variable and add to str
for($i=0;$i<(count($keys_str));$i++){
$str.=$keys_str[$i];
// if it is not end of keys then add coma
if($i<(count($keys_str)-1)){
$str .=",";
}
$str.="\n";
}
// end table creation query
$str .=")";
// query for getting AUTO_INCREMENT nuber
$auto_query = $mysqli->query("SELECT COUNT(*)+1 AS `Auto` FROM $tbl")or die($mysqli->error);
$auto_fetch = $auto_query->fetch_assoc();
$auto = $auto_fetch['Auto'];
$str .="ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=$auto ;\n\n";
//create database
$str.="--\n-- Inserting data into table `$tbl`\n--\n\n";
$this->str .=$str;
}
// get table contents
function get_table_content($table){
$mysqli = $this->mysqli;
// array variable for storing fields name
$field = array();
// string variable for storing table content
$str = "";
//execute query
$con_query = $mysqli->query("SELECT * FROM $table")or die($mysqli->error);
// get row numbers of table
$rows = $con_query->num_rows;
// inisalize a variable and assing 0 value;
$r=0;
// if records found in table then get all data
if($rows>0){
// get fields from query
while($fields = $con_query->fetch_field()){
// add field name to field variable
$field[] .= $fields->name;
}
// insert query for table
$str .="INSERT INTO `$table` (";
// append table fields into string query
for($i=0;$i<=(count($field)-1);$i++){
$str .= "`".$field[$i]."`";
// if current is not last field then add coma
if($i<(count($field)-1)){
$str .=",";
}
}
// add values
$str .=") VALUES \n";
// get result from query
while($fetch = $con_query->fetch_assoc()){
$str .= "(";
// run loop on fields variable, get data and add to $str
for($i=0;$i<=(count($field)-1);$i++){
// add escap caracher "\" for sql injection
$str .= "'".addslashes($fetch[$field[$i]])."'";
if($i<(count($field)-1)){
$str .=",";
}
}
$str .=")";
if($r<$rows -1){
$str .=",";
}else{
$str .=";";
}
$r++;
$str .="\n";
}
$str .="\n\n";
$this->str .=$str;
}
}
// function for getting tables from database and table information from get_table_info function
function get_tables(){
$mysqli = $this->mysqli;
// get all tables from database
$tables = $mysqli->query("SHOW TABLES FROM ".$this->db)or die($mysqli->error);
// checking for table(s) in database if found then execute the code
if($tables->num_rows>0){
//if table(s) found then get tables name from database
while($tables_fetch = $tables->fetch_assoc()){
$table = $tables_fetch["Tables_in_".$this->db];
// and then get table information
$this->get_table_info($table);
$this->get_table_content($table);
}
}else{
// print error message if no table(s) found
die("No table found in database: ".$this->db);
}
}
// function for executing executing backup
function DoBackup(){
// for slow connection or for larg My SQL injection
ini_set("max_execution_time","1000");
$this->str .= $this->Extra()."\n\n";
// get tables informtaion from tables
$this->get_tables();
echo $this->str;
// create .sql file for backup
$this->create_file();
}
// function for creating and downloading file from we page (every thing will print from page)
function create_file(){
header("Content-disposition: filename=".$this->db.".sql");
header("Content-type: application/octetstream");
header("Pragma: no-cache");
header("Expires: 0");
}
function Extra(){
$str = "";
$str = "-- Author: Noor Ahmad Feroozi\n";
$str.= "-- Last Updated: 02-Nov-2009\n\n\n";
$str.= "-- Host: ".$_SERVER['HTTP_HOST']."\n";
$str.= "-- PHP Version".phpversion()."\n";
$str.= "-- Database: ".$this->db."\n";
$str.= "-- Creation Time: ".date('Y-M-d')." at ".date('H:i:s')."\n\n";
$str.='SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";'."\n\n";
$str.= "--\n--Database: `".$this->db."`\n--\n\n";
$str.="CREATE DATABASE IF NOT EXISTS `".$this->db."`;\n";
return $str;
}
}
$obj = new backup("localhost","root","mysql","wp");
$obj->DoBackup();
?>
ضميمه 2 : اگه بخوام يه كلاس بنويسم كه backup همين كلاس رو واسم restore كنه بايد Øتما "/n" ها رو Øذ٠كنم از backup Ùˆ بعد با توجه به علامت ";" string رو بشكونم Ùˆ دونه دونه ÙŠ query ها رو اجرا كنم ØŸ روش بهتري وجود نداره ØŸ
ممنون
Comment