Autor | Zpráva | ||
---|---|---|---|
okolojdouci Profil * |
#1 · Zasláno: 17. 9. 2011, 19:57:55
Chtěl bych automatizovat zálohování databází na webech.
Existuje způsob, jak z php přikázat databázi, aby vygenerovala dump a uložila jako soubor? |
||
pcmanik Profil |
#2 · Zasláno: 17. 9. 2011, 20:03:53
okolojdouci:
„Existuje způsob“ Ano existuje. |
||
okolojdouci Profil * |
#3 · Zasláno: 20. 9. 2011, 07:50:12
pcmanik:
„Ano existuje.“ Super. |
||
Medvídek Profil |
#4 · Zasláno: 20. 9. 2011, 09:09:23
okolojdouci:
Třeba takto: :) <?php include "db.php"; /** * PHP version of mysqldump tool * * @author Honza Odvarko, http://odvarko.cz * @copyright Honza Odvarko * @license http://www.gnu.org/copyleft/gpl.html GNU General Public License * @version 1.0.1 */ /* * Prints the dump of the MySQL database to specified file descriptor * * @param resource $fp Destination stream pointer * @param resource $link The MySQL connection. If null, default * connection assumed. * @param mixed $data_for Array of tables whose data will be dumped. * TRUE means all tables. * @param mixed $struct_for Array of tables whose structure will be dumped. * TRUE means all tables. * @return bool */ function fmysqldump($fp, $link=null, $data_for=true, $struct_for=true) { list($database) = mysql_fetch_row($link===null ? mysql_unbuffered_query('SELECT DATABASE()') : mysql_unbuffered_query('SELECT DATABASE()',$link)); $host_info = $link===null ? mysql_get_host_info() : mysql_get_host_info($link); $q = 'SELECT '. '@@character_set_client,'. '@@character_set_results,'. '@@character_set_connection,'. '@@version'; $conn = mysql_fetch_assoc($link===null ? mysql_unbuffered_query($q) : mysql_unbuffered_query($q,$link)); fwrite($fp, "/****************************************\n". " Dumping database `$database`\n". " ---\n". " MySQL server version: {$conn['@@version']}\n". " Host: $host_info\n". ' Date: '.date('r')."\n". " ****************************************/\n". "\n". "/*!40101 SET @old_character_set_client=@@character_set_client */;\n". "/*!40101 SET @old_character_set_results=@@character_set_results */;\n". "/*!40101 SET @old_character_set_connection=@@character_set_connection */;\n". "/*!40101 SET character_set_client={$conn['@@character_set_client']} */;\n". "/*!40101 SET character_set_connection={$conn['@@character_set_connection']} */;\n". "/*!40101 SET character_set_results={$conn['@@character_set_results']} */;\n". "/*!40014 SET @old_unique_checks=@@unique_checks, unique_checks=0 */;\n". "/*!40014 SET @old_foreign_key_checks=@@foreign_key_checks, foreign_key_checks=0 */;\n". "/*!40103 SET @old_time_zone=@@time_zone, time_zone='+00:00' */;\n". "/*!40101 SET @old_sql_mode=@@sql_mode, sql_mode='NO_AUTO_VALUE_ON_ZERO' */;\n". "/*!40111 SET @old_sql_notes=@@sql_notes, sql_notes=0 */;\n\n" ); if(($tables = $link===null ? mysql_query('SHOW TABLES') : mysql_query('SHOW TABLES',$link)) === false) { trigger_error('Cannot get list of tables', E_USER_WARNING); return false; } while((list($table)=mysql_fetch_row($tables))!==false) { # Table structure if($struct_for===true || is_array($struct_for) && array_search($table,$struct_for)!==false) { fwrite($fp, "/*\n * Table structure for table `$table`\n */\n\nDROP TABLE IF EXISTS `$table`;\n"); list(,$struct) = mysql_fetch_row($link===null ? mysql_unbuffered_query("SHOW CREATE TABLE `$table`") : mysql_unbuffered_query("SHOW CREATE TABLE `$table`",$link)); if($struct!==false) fwrite($fp, "$struct;\n"); else trigger_error("Unable to get structure of table '$table'", E_USER_WARNING); fwrite($fp, "\n"); } # Table data if($data_for===true || is_array($data_for) && array_search($table,$data_for)!==false) { # If you are going to use the mysql_unbuffered_query() here, keep in mind that: # 1) all writes to the table will be deferred during the time of reading it # 2) it is not possible to use mysql_result() along with unbuffered queries, # wherefore you have to employ some of the mysql_fetch_* functions if(($res = $link===null ? mysql_query("SELECT * FROM `$table`") : mysql_query("SELECT * FROM `$table`",$link)) === false) { trigger_error("Cannot get data from table '$table'", E_USER_WARNING); } else { fwrite($fp, "/*\n * Dumping data for table `$table`\n */\n\n"); if($rows=mysql_num_rows($res)) { $fields = mysql_num_fields($res); fwrite($fp, "LOCK TABLES `$table` WRITE;\n"); for($r=0; $r<$rows; $r++) { fwrite($fp, "INSERT INTO `$table` VALUES ("); for($f=0; $f<$fields; $f++) { $cell = mysql_result($res, $r, $f); fwrite($fp, $cell===null ? 'NULL' : "'".($link===null ? mysql_real_escape_string($cell) : mysql_real_escape_string($cell,$link))."'" ); $f<$fields-1 && fwrite($fp, ', '); } fwrite($fp, ");\n"); } fwrite($fp, "UNLOCK TABLES;\n"); } fwrite($fp, "\n"); mysql_free_result($res); } } } # END WHILE fwrite($fp, "/*!40101 SET character_set_client=@old_character_set_client */;\n". "/*!40101 SET character_set_results=@old_character_set_results */;\n". "/*!40101 SET character_set_connection=@old_character_set_connection */;\n". "/*!40014 SET unique_checks=@old_unique_checks */;\n". "/*!40014 SET foreign_key_checks=@old_foreign_key_checks */;\n". "/*!40103 SET time_zone=@old_time_zone */;\n". "/*!40101 SET sql_mode=@old_sql_mode */;\n". "/*!40111 SET sql_notes=@old_sql_notes */;" ); return true; } /* * Prints or returns the dump of the MySQL database * * Note: Since DB dumps are often very large, it is advisable not to get data * via return value. * * @param resource $link The MySQL connection. If null, default * connection assumed. * @param mixed $data_for Array of tables whose data will be dumped. * TRUE means all tables. * @param mixed $struct_for Array of tables whose structure will be dumped. * TRUE means all tables. * @param bool $return Give the result via return value? * (instead of printing it to the output) * @return mixed */ function mysqldump($link=null, $data_for=true, $struct_for=true, $return=false) { if($return) ob_start(); $fp = fopen('php://output','w'); fmysqldump($fp, $link, $data_for, $struct_for); fclose($fp); return $return ? ob_get_flush() : true; } print_r(mysqldump()); ?> |
||
okolojdouci Profil * |
#5 · Zasláno: 20. 9. 2011, 09:51:35
Medvídek:
Díky, to vypadá dobře. I když mě trochu překvapuje, že pro to nemá mysql svoje vlastní nástroje. |
||
Medvídek Profil |
#6 · Zasláno: 20. 9. 2011, 09:52:31
okolojdouci:
Zkus command line :) |
||
Časová prodleva: 13 let
|
0