Extractor: 一個MySQLDatabase Backup工具

因為要在Ant中匯出MySQL資料庫，寫了mysql_extractor類，供大家參考。

 

extractor.php： 

if (!empty($_SERVER['argv'])) {    for ($i = 1; $i < $_SERVER['argc']; $i++) {        list($k, $v) = explode('=', $_SERVER['argv'][$i]);        $_GET[$k] = $v;    }}if (empty($_GET['table_prefix'])) {    $_GET['table_prefix'] = '';}if (empty($_GET['output_file'])) {    $_GET['output_file'] = '';}include_once(dirname(__FILE__) . '/../config.php');$db = mysql_connect(DB_HOST . ':' . DB_PORT, DB_USER, DB_PASS);if (!$db) {    die('[' . mysql_errno() . '] ' . mysql_error());}if (!mysql_select_db(DB_NAME, $db)) {    die('[' . mysql_errno() . '] ' . mysql_error());}        mysql_query("SET NAMES 'utf8'", $db);mysql_query("SET NAMES 'utf8'", $db);$extractor = new mysql_extractor($db, $_GET['output_file']);$extractor->write_start(DB_PREFIX);$result = mysql_query("SHOW TABLE STATUS WHERE name like '" . DB_PREFIX . $_GET['table_prefix'] . "%'", $db);while (false !== ($row = mysql_fetch_array($result, MYSQL_NUM))) {    $extractor->write_table($row[0]);    $extractor->write_data($row[0]);}mysql_free_result($result);$extractor->write_end();class mysql_extractor{    var $db;    var $fp;    public function __construct($db, $output_file)    {        $this->db = $db;                if (!empty($output_file)) {            $this->fp = fopen($output_file, 'w');            if (!$this->fp) {                trigger_error('FILE_WRITE_FAIL', E_USER_ERROR);            }        }    }    public function write_start($table_prefix)    {        $sql_data = "#\n";        $sql_data .= "# Omnitrix Backup Script\n";        $sql_data .= "# Dump of tables for $table_prefix\n";        $sql_data .= "# DATE : " . gmdate("d-m-Y H:i:s", time()) . " GMT\n";        $sql_data .= "#\n\n";        $this->flush($sql_data);    }    public function write_table($table_name)    {        $sql = 'SHOW CREATE TABLE ' . $table_name;        $result = mysql_query($sql, $this->db);        $row = mysql_fetch_array($result, MYSQL_ASSOC);        mysql_free_result($result);        $sql_data = "#\n";        $sql_data .= "# Source for table $table_name \n";        $sql_data .= "#\n";        $sql_data .= "DROP TABLE IF EXISTS `$table_name`;\n";        $this->flush($sql_data . $row['Create Table'] . ";\n\n");    }    public function write_data($table_name)    {        $sql_data = "#\n";        $sql_data .= "# Dumping data for table $table_name \n";        $sql_data .= "#\n";        $this->flush($sql_data);                $sql = "SELECT * FROM $table_name";        $result = mysql_query($sql, $this->db);        if ($result != false) {            $fields_cnt = mysql_num_fields($result);            // Get field information            $field = $field_set = array();            for ($j = 0; $j < $fields_cnt; $j++) {                $info = new stdClass();                $info->name = mysql_field_name($result, $j);                $info->type = mysql_field_type($result, $j);                $info->flags = mysql_field_flags($result, $j);                $field[$j] = $info;                                $field_set[] = $info->name;            }            $search            = array("\\", "'", "\x00", "\x0a", "\x0d", "\x1a", '"');            $replace        = array("\\\\", "\\'", '\0', '\n', '\r', '\Z', '\\"');            while (($row = mysql_fetch_row($result)) !== false) {                $values    = array();                for ($j = 0; $j < $fields_cnt; $j++) {                    if (!isset($row[$j]) || is_null($row[$j])) {                        $values[$j] = 'NULL';                    } else if (($field[$j]->flags & 32768) && !($field[$j]->flags & 1024))                    {                        $values[$j] = $row[$j];                    } else {                        $values[$j] = "'" . str_replace($search, $replace, $row[$j]) . "'";                    }                }                                $query = 'INSERT INTO `' . $table_name . '` VALUES (' . implode(', ', $values) . ');';                $this->flush($query . "\n");            }            mysql_free_result($result);                        $this->flush("\n");        }    }        public function write_end()    {        if ($this->fp) {            fclose($this->fp);        }        }        public function flush($data)    {        if ($this->fp) {            fwrite($this->fp, $data);        } else {            echo $data;                    }    }}

 

在命令列執行：

php -f extractor.php #直接輸出php -f extractor.php output_file=omnitrix.sql #輸出到檔案php -f extractor.php output_file=omnitrix.sql table_prefix=system_user #限定指定開頭的表

 

在瀏覽器執行：

http://localhost/omnitrix/utilities/extractor.php #直接輸出http://localhost/omnitrix/utilities/extractor.php?output_file=omnitrix.sql #輸出到檔案http://localhost/omnitrix/utilities/extractor.php?output_file=omnitrix.sql&table_prefix=system_user #限定指定開頭的表