function empty_single_db($db_name) { $db = mysql_select_db($db_name); if (!$db) { echo PMBP_addOutput(sprintf(PMBP_EX_NO_AVAILABLE, $db_name), "red"); } else { $res = mysql_list_tables($db_name); $tables_and_views = PMBP_list_tables_and_view(); startTransaction(); foreach ($tables_and_views['views'] as $view) { mysql_query("drop view `" . $view['Name'] . "`"); echo mysql_error(); } foreach ($tables_and_views['tables'] as $table) { mysql_query("drop table `" . $table['Name'] . "`"); echo mysql_error(); } endTransaction(); $error = mysql_error(); if ($error) { echo $error; } else { echo PMBP_addOutput(B_EMPTIED, "green"); } } }
function PMBP_dump($db, $tables, $data, $drop, $zip, $comment) { global $CONF; global $PMBP_SYS_VAR; $error = FALSE; // set max string size before writing to file if (@ini_get("memory_limit")) { $max_size = 500000 * ini_get("memory_limit"); } else { ini_set("memory_limit", $PMBP_SYS_VAR['memory_limit'] / 1024000); $max_size = 500000 * ($PMBP_SYS_VAR['memory_limit'] / 1024000); } // set backupfile name $time = time(); if ($zip == "gzip") { $backupfile = $db . "." . $time . ".sql.gz"; } else { $backupfile = $db . "." . $time . ".sql"; } $backupfile = PMBP_EXPORT_DIR . $backupfile; if ($con = PMBP_mysql_connect()) { // don't backup tables in $PMBP_SYS_VAR['except_tables'] $except_tables = explode(",", $PMBP_SYS_VAR['except_tables']); //create comment $out = "# MySQL dump of database '" . $db . "' on host '" . $CONF['sql_host'] . "'\n"; $out .= "# backup date and time: " . strftime($CONF['date'], $time) . "\n"; $out .= "# LugrawibeBackup " . PMBP_VERSION . "\n"; $out .= "# " . PMBP_WEBSITE . "\n\n"; // write users comment if ($comment) { $out .= "# comment:\n"; $comment = preg_replace("'\n'", "\n# ", "# " . $comment); foreach (explode("\n", $comment) as $line) { $out .= $line . "\n"; } $out .= "\n"; } // set and log character set $characterSet = PMBP_set_character_set(); $out .= "### used character set: " . $characterSet . " ###\n"; $out .= "set names " . $characterSet . ";\n\n"; // print "use database" if more than one database is available if (count(PMBP_get_db_list()) > 1) { $out .= "CREATE DATABASE IF NOT EXISTS `" . $db . "`;\n\n"; $out .= "USE `" . $db . "`;\n"; } // select db @mysql_select_db($db); // get tables and views $tables_and_views = PMBP_list_tables_and_view(); $all_tables = $tables_and_views['tables']; $all_views = $tables_and_views['views']; // get table structures $all_tables_new = array(); foreach ($all_tables as $key => $table) { if (!in_array($table['Name'], $except_tables)) { $res1 = mysql_query("SHOW CREATE TABLE `" . $table['Name'] . "`"); $tmp = mysql_fetch_array($res1); $table_sql[$table['Name']] = $tmp["Create Table"]; $all_tables_new[$key] = $table; } } $all_tables = $all_tables_new; unset($all_tables_new); // find foreign keys $fks = array(); if (isset($table_sql)) { foreach ($table_sql as $tablenme => $table) { $tmp_table = $table; // save all tables, needed for creating this table in $fks while (($ref_pos = strpos($tmp_table, " REFERENCES ")) > 0) { $tmp_table = substr($tmp_table, $ref_pos + 12); $ref_pos = strpos($tmp_table, "("); // cut of '`' at the begining and end of fk-name $fks[$tablenme][] = trim(str_replace('`', '', substr($tmp_table, 0, $ref_pos))); } } } // order $all_tables and check for ring constraints $all_tables_copy = $all_tables; $all_tables = PMBP_order_sql_tables($all_tables, $fks); $ring_constraints = false; // ring constraints found if ($all_tables === false) { $ring_constraints = true; $all_tables = $all_tables_copy; $out .= "\n# ring constraints workaround\n"; $out .= "SET FOREIGN_KEY_CHECKS=0;\n"; $out .= "SET AUTOCOMMIT=0;\n"; $out .= "START TRANSACTION;\n"; } unset($all_tables_copy); // as long as no error occurred if (!$error) { if ($drop) { $out .= "\n### drop all tables first ###\n"; foreach (array_reverse($all_tables) as $row) { $out .= "\nDROP TABLE IF EXISTS `" . $row['Name'] . "`;"; } $out .= "\n"; } foreach ($all_tables as $row) { $tablename = $row['Name']; $auto_incr[$tablename] = $row['Auto_increment']; $out .= "\n\n"; // export tables if ($tables) { $out .= "### structure of table `" . $tablename . "` ###\n\n"; $out .= $table_sql[$tablename]; // add auto_increment value if ($auto_incr[$tablename]) { $out .= " AUTO_INCREMENT=" . $auto_incr[$tablename]; } $out .= ";"; } $out .= "\n\n\n"; // export data if ($data && !$error) { $out .= "### data of table `" . $tablename . "` ###\n\n"; // check if field types are NULL or NOT NULL $res3 = mysql_query("show columns from `" . $tablename . "`"); $res2 = mysql_query("select * from `" . $tablename . "`"); if ($res2) { $number_of_rows = mysql_num_rows($res2); if ($number_of_rows > 0) { for ($j = 0; $j < $number_of_rows; $j++) { $out .= "insert into `" . $tablename . "` values ("; $row2 = mysql_fetch_row($res2); // run through each field for ($k = 0; $k < ($nf = mysql_num_fields($res2)); $k++) { // identify null values and save them as null instead of '' if (is_null($row2[$k])) { $out .= "null"; } else { $out .= "'" . mysql_real_escape_string($row2[$k]) . "'"; } if ($k < $nf - 1) { $out .= ", "; } } $out .= ");\n"; // if saving is successful, then empty $out, else set error flag if (strlen($out) > $max_size) { if ($out = PMBP_save_to_file($backupfile, $zip, $out, "a")) { $out = ""; } else { $error = TRUE; } } } } else { $out .= "-- table is empty\n"; // no data } } else { echo "MySQL error: " . mysql_error(); @unlink(PMBP_EXPORT_DIR . $backupfile); return FALSE; } // an error occurred! Try to delete file and return error status } elseif ($error) { @unlink(PMBP_EXPORT_DIR . $backupfile); return FALSE; } // if saving is successful, then empty $out, else set error flag if (strlen($out) > $max_size) { if ($out = PMBP_save_to_file($backupfile, $zip, $out, "a")) { $out = ""; } else { $error = TRUE; } } } // views // get all view definitions $all_views_definitions = array(); foreach ($all_views as $row) { $viewname = $row['Name']; if (!in_array($viewname, $except_tables)) { $res4 = mysql_query("show create view `" . $viewname . "`"); if ($res4) { $row4 = mysql_fetch_array($res4); $all_views_definitions[$viewname] = $row4['Create View']; } else { echo "MySQL error: " . mysql_error(); @unlink(PMBP_EXPORT_DIR . $backupfile); return FALSE; } } } if (sizeof($all_views_definitions) > 0) { $out .= "\n\n### views ###\n\n"; } // order $all_views and check for ring constraints $all_views_definitions_copy = $all_views_definitions; $all_views_definitions = PMBP_order_sql_views($all_views_definitions); $ring_constraints = false; // ring constraints found if ($all_views_definitions === false) { $all_views_definitions = $all_views_definitions_copy; // if not already written because of tabel ring constraints if (!$ring_constraints) { $out .= "\n# ring constraints workaround\n"; $out .= "SET FOREIGN_KEY_CHECKS=0;\n"; $out .= "SET AUTOCOMMIT=0;\n"; $out .= "START TRANSACTION;\n"; } $ring_constraints = true; } unset($all_views_definitions_copy); // view drop statements (in reverse order) if ($drop && count($all_views_definitions) > 0) { $out .= "### drop all views first ###\n\n"; foreach (array_reverse($all_views_definitions) as $view_name => $view_definition) { $out .= "DROP VIEW IF EXISTS `" . $view_name . "`;\n"; } $out .= "\n\n"; } // view definitions foreach ($all_views_definitions as $view_name => $view_definition) { $out .= "### create statement of view `" . $view_name . "` ###\n\n"; $out .= $view_definition; $out .= ";\n\n"; } // if saving is successful, then empty $out, else set error flag if (strlen($out) > $max_size) { if ($out = PMBP_save_to_file($backupfile, $zip, $out, "a")) { $out = ""; } else { $error = TRUE; } } // an error occurred! Try to delete file and return error status } else { @unlink($backupfile); return FALSE; } // if db contained ring constraints if ($ring_constraints) { $out .= "\n\n# ring constraints workaround\n"; $out .= "SET FOREIGN_KEY_CHECKS=1;\n"; $out .= "COMMIT;\n"; } // save to file if ($backupfile = PMBP_save_to_file($backupfile, $zip, $out, "a")) { if ($zip != "zip") { return basename($backupfile); } } else { @unlink($backupfile); return FALSE; } // create zip file in file system include_once "pclzip.lib.php"; $pclzip = new PclZip($backupfile . ".zip"); $pclzip->create($backupfile, PCLZIP_OPT_REMOVE_PATH, PMBP_EXPORT_DIR); // remove temporary plain text backup file used for zip compression @unlink(substr($backupfile, 0, strlen($backupfile))); if ($pclzip->error_code == 0) { return basename($backupfile) . ".zip"; } else { // print pclzip error message echo PMBP_addOutput("pclzip: " . $pclzip->error_string, "red"); // remove temporary plain text backup file @unlink(substr($backupfile, 0, strlen($backupfile) - 4)); @unlink($backupfile); return FALSE; } } else { return "DB_ERROR"; } }