如何备份php数据库数据库语句,如何在PHP中备份MySQL数据库？

DevWL..

5

如果您想从php脚本中备份数据库，则可以使用一个类，例如调用它MySQL。此类将使用PDO(在php类中构建，它将处理与数据库的连接)。该类可能如下所示：

define('DBUSER','root');

define('DBPASS','');

define('SERVERHOST','localhost');

?>

class MySql{

private $dbc;

private $user;

private $pass;

private $dbname;

private $host;

function __construct($host="localhost", $dbname="your_databse_name_here", $user="your_username", $pass="your_password"){

$this->user = $user;

$this->pass = $pass;

$this->dbname = $dbname;

$this->host = $host;

$opt = array(

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC

);

try{

$this->dbc = new PDO('mysql:host='.$this->host.';dbname='.$this->dbname.';charset=utf8', $user, $pass, $opt);

}

catch(PDOException $e){

echo $e->getMessage();

echo "There was a problem with connection to db check credenctials";

}

} /*end function*/

public function backup_tables($tables = '*'){ /* backup the db OR just a table */

$host=$this->host;

$user=$this->user;

$pass=$this->pass;

$dbname=$this->dbname;

$data = "";

//get all of the tables

if($tables == '*')

{

$tables = array();

$result = $this->dbc->prepare('SHOW TABLES');

$result->execute();

while($row = $result->fetch(PDO::FETCH_NUM))

{

$tables[] = $row[0];

}

}

else

{

$tables = is_array($tables) ? $tables : explode(',',$tables);

}

//cycle through

foreach($tables as $table)

{

$resultcount = $this->dbc->prepare('SELECT count(*) FROM '.$table);

$resultcount->execute();

$num_fields = $resultcount->fetch(PDO::FETCH_NUM);

$num_fields = $num_fields[0];

$result = $this->dbc->prepare('SELECT * FROM '.$table);

$result->execute();

$data.= 'DROP TABLE '.$table.';';

$result2 = $this->dbc->prepare('SHOW CREATE TABLE '.$table);

$result2->execute();

$row2 = $result2->fetch(PDO::FETCH_NUM);

$data.= "\n\n".$row2[1].";\n\n";

for ($i = 0; $i < $num_fields; $i++)

{

while($row = $result->fetch(PDO::FETCH_NUM))

{

$data.= 'INSERT INTO '.$table.' VALUES(';

for($j=0; $j

{

$row[$j] = addslashes($row[$j]);

$row[$j] = str_replace("\n","\\n",$row[$j]);

if (isset($row[$j])) { $data.= '"'.$row[$j].'"' ; } else { $data.= '""'; }

if ($j

}

$data.= ");\n";

}

}

$data.="\n\n\n";

}

//save filename

$filename = 'db-backup-'.time().'-'.(implode(",",$tables)).'.sql';

$this->writeUTF8filename($filename,$data);

/*USE EXAMPLE

$connection = new MySql(SERVERHOST,"your_db_name",DBUSER, DBPASS);

$connection->backup_tables(); //OR backup_tables("posts");

$connection->closeConnection();

*/

} /*end function*/

private function writeUTF8filename($filenamename,$content){ /* save as utf8 encoding */

$f=fopen($filenamename,"w+");

# Now UTF-8 - Add byte order mark

fwrite($f, pack("CCC",0xef,0xbb,0xbf));

fwrite($f,$content);

fclose($f);

/*USE EXAMPLE this is only used by public function above...

$this->writeUTF8filename($filename,$data);

*/

} /*end function*/

public function recoverDB($file_to_load){

echo "write some code to load and proccedd .sql file in here ...";

/*USE EXAMPLE this is only used by public function above...

recoverDB("some_buck_up_file.sql");

*/

} /*end function*/

public function closeConnection(){

$this->dbc = null;

//EXAMPLE OF USE

/*$connection->closeConnection();*/

}/*end function*/

} /*END OF CLASS*/

?>

现在，您只需在backup.php中使用它：

include ('config.php');

include ('myclass.php');

$connection = new MySql(SERVERHOST,"your_databse_name_here",DBUSER, DBPASS);

$connection->backup_tables(); /*Save all tables and it values in selected database*/

$connection->backup_tables("post_table"); /*Saves only table name posts_table from selected database*/

$connection->closeConnection();

这意味着访问此页面将导致备份文件...当然，不必这样做:)您可以在数据库的每条帖子上都调用此方法，以便始终保持最新状态。 ，我建议您始终将其写入一个文件，而不是像上面那样使用time()创建新文件。

希望对您有帮助，祝您好运！：>