Last active
February 16, 2023 09:04
-
-
Save landzz/1c9dcdeafe7b9b5fa09b8d41e0e44c7d to your computer and use it in GitHub Desktop.
php database dump tool for mysql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| /** | |
| * php database dump tool for mysql | |
| * @author https://github.com/landzz | |
| * @version 0.1 | |
| */ | |
| error_reporting(E_ALL ^ E_NOTICE); | |
| @set_time_limit(0); | |
| ################################################# | |
| // dsn setting | |
| define('DMP_DB', '{databaase}' ); | |
| define('DMP_HOST', '{host}' ); | |
| define('DMP_USER', '{user}' ); | |
| define('DMP_PW', '{password}' ); | |
| ################################################# | |
| // allow ips & http-Authenticate | |
| $allow_ips = array(); | |
| $is_allow_ips = (in_array($_SERVER['REMOTE_ADDR'], $allow_ips)) ? true : false; | |
| if ( count($allow_ips) > 0 ) { | |
| if ( !in_array($_SERVER['REMOTE_ADDR'], $allow_ips) ) { | |
| die('halt!'); exit; | |
| } | |
| } | |
| function http_digest_parse($txt) { | |
| $needed_parts = array('nonce'=>1, 'nc'=>1, 'cnonce'=>1, 'qop'=>1, 'username'=>1, 'uri'=>1, 'response'=>1); | |
| $data = array(); | |
| preg_match_all('@(\w+)=(?:(?:\'([^\']+)\'|"([^"]+)")|([^\s,]+))@', $txt, $matches, PREG_SET_ORDER); | |
| foreach ($matches as $m) { | |
| $data[$m[1]] = $m[2]?$m[2]:($m[3]?$m[3]:$m[4]); | |
| unset($needed_parts[$m[1]]); | |
| } | |
| return $needed_parts ? false : $data; | |
| } | |
| function is_auth() { | |
| $users = array('username'=>'p@assword'); //auth user info | |
| if(empty($_SERVER['PHP_AUTH_DIGEST'])) return false; | |
| $data = http_digest_parse($_SERVER['PHP_AUTH_DIGEST']); | |
| if($data === false) return false; | |
| $username = $data['username']; | |
| if(!isset($users[$username])) return false; | |
| $ha1 = md5($username.':'.$data['realm'].':'.$users[$username]); | |
| $ha2 = md5($_SERVER['REQUEST_METHOD'].':'.$data['uri']); | |
| $response = md5($ha1.':'.$data['nonce'].':'.$data['nc'].':'.$data['cnonce'].':'.$data['qop'].':'.$ha2); | |
| if($data['response'] != $response) return false; | |
| return true; | |
| } | |
| if( !is_auth() ) { | |
| $realm = 'dump_auths'; | |
| header('HTTP/1.1 401 Unauthorized'); | |
| header('WWW-Authenticate: Digest realm="'.$realm.'",qop="auth",nonce="'.uniqid().'",opaque="'.md5($realm).'"'); | |
| die ("Not authorized"); | |
| exit; | |
| } | |
| ################################################# | |
| // helper functions | |
| function _format_size($size) { | |
| $size = intval(preg_replace("/[^0-9]*/s", "", $size)); | |
| $units = explode(' ', 'B KB MB GB TB PB'); | |
| $mod = 1024; | |
| $_tmp = ""; | |
| for ($i = 0; $size >= $mod; $i++) { | |
| $_tmp .= "<div>[$i : $size]</div>"; | |
| $size /= $mod; | |
| } | |
| $endIndex = strpos($size, ".")+3; | |
| //return $_tmp."($size / $endIndex , $i)".substr( $size, 0, $endIndex).''.$units[$i]; | |
| return substr( $size, 0, $endIndex).''.$units[$i]; | |
| } | |
| ################################################# | |
| // db class : mysqli | |
| class dump_db{ | |
| private $db_con = null; | |
| public function __construct(){ | |
| if ( !$db_con ) { | |
| $this->connect(); | |
| } | |
| } | |
| function connect(){ | |
| $this->db_con = @mysqli_connect(DMP_HOST, DMP_USER, DMP_PW, DMP_DB); | |
| if ( mysqli_connect_errno() ) { | |
| printf("DB Connect failed: %s\n", mysqli_connect_error() ); | |
| exit(); | |
| } | |
| mysqli_query($this->db_con, "set names utf8mb4"); | |
| } | |
| function query($sql, $error=true){ | |
| if ($error){ | |
| $result = @mysqli_query($this->db_con, $sql); | |
| if ( !$result ) { | |
| echo "<p>".$sql."<p>" | |
| .PHP_EOL.mysqli_errno($this->db_con) | |
| .PHP_EOL.mysqli_error($this->db_con); | |
| exit; | |
| } | |
| }else{ | |
| $result = @mysqli_query($this->db_con, $sql); | |
| } | |
| return $result; | |
| } | |
| function fetch_array($result){ | |
| $row = mysqli_fetch_assoc($result); | |
| return $row; | |
| } | |
| function fetch_row($result){ | |
| $row = mysqli_fetch_row($result); | |
| return $row; | |
| } | |
| function sql_fetch($sql, $error=true){ | |
| if ($error){ | |
| $result = @mysqli_query($this->db_con, $sql); | |
| if ( !$result ) { | |
| die("<p>$sql<p>" . mysqli_errno($this->db_con) . " : " . mysqli_error($this->db_con) ); | |
| } | |
| return $this->fetch_array($result); | |
| }else{ | |
| $result = @mysqli_query($this->db_con, $sql); | |
| return $this->fetch_array($result); | |
| } | |
| } | |
| function num_rows($result){ | |
| return mysqli_num_rows($result); | |
| } | |
| function free_result($result){ | |
| mysqli_free_result($result); | |
| } | |
| function sql_escape_string($_str){ | |
| mysqli_set_charset($this->db_con, 'utf8mb4'); | |
| return mysqli_real_escape_string($this->db_con, $_str); | |
| } | |
| } // end class | |
| $db = new dump_db(); | |
| ################################################# | |
| // dump execute | |
| if($_POST['mode'] == "_Dump"){ | |
| $_tables = $_POST['_tables']; | |
| if(!is_array($_tables)) $_tables = array(); | |
| $_tables = array_unique($_tables); | |
| $_tables = array_filter($_tables); | |
| $_ccnt = count($_tables); | |
| if($_ccnt < 1){ | |
| die("No Data Selected!! <input type='button' value='Go Back' onclick='history.go(-1)'>"); | |
| exit; | |
| } | |
| $filename = DMP_DB."_structure_".date("Y.m.d.Hi",time()).".sql" ; | |
| if ( intval($_POST['rowdata']) === 1 ) { | |
| $filename = DMP_DB."_structure_and_data_".date("Y.m.d.Hi",time()).".sql" ; | |
| } | |
| if( intval($_POST['download']) === 1 ){ | |
| header('Content-Type: application/octet-stream'); | |
| header('Content-Disposition: inline; filename="' . $filename . '"'); | |
| header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); | |
| header('Pragma: public'); | |
| }else{ | |
| //header("Content-type: text/x-sql;charset=utf-8"); | |
| //html view | |
| header("Content-type: text/html;charset=utf-8"); | |
| echo ' | |
| <!DOCTYPE HTML><html lang="ko"><head> | |
| <meta charset="utf-8"> | |
| <title> '.$_SERVER['HTTP_HOST'].'_ Backup.. </title> | |
| <style type="text/css">body,textarea,div{font-size:13px;}</style> | |
| </head> | |
| <body> | |
| <div >sql dump <span style="cursor:pointer; color:blue;text-decoration:underline;" onclick="location.replace(\'?\')">[back]</span> | |
| <br />- database : <em><strong>'.DMP_DB.'</strong></em> | |
| <br />- tables ('.$_ccnt.') : <em><strong>'.implode(', ', $_tables).'</strong></em> | |
| </div> | |
| <textarea wrap="off" style="margin:25px 0;padding:15px;width:calc(100vw - 70px);height:calc(100vh - 300px);" readonly>'; | |
| flush(); | |
| } | |
| $mysql_ver = $db->sql_fetch("SELECT VERSION() AS version;"); | |
| $mysql_version = $mysql_ver['version']; | |
| $_Script = PHP_EOL.PHP_EOL; | |
| $_Script .="--".PHP_EOL; | |
| $_Script .="-- SQL Dump by php dump system v0.1".PHP_EOL; | |
| $_Script .="-- Generation Time: ".date("Y-m-d H:i:s",time())."".PHP_EOL; | |
| $_Script .= PHP_EOL; | |
| $_Script .="-- host: ".DMP_HOST."".PHP_EOL; | |
| $_Script .="-- mysql server : ".$mysql_version."".PHP_EOL; | |
| $_Script .="-- php version: ".phpversion()."".PHP_EOL; | |
| $_Script .= PHP_EOL; | |
| $_Script .="SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';".PHP_EOL; | |
| $_Script .= PHP_EOL; | |
| $_Script .="--".PHP_EOL; | |
| $_Script .="-- Database: `".DMP_DB."` ".PHP_EOL; | |
| $_Script .="--".PHP_EOL; | |
| $_Script .= PHP_EOL; | |
| echo $_Script ; | |
| flush(); | |
| foreach($_tables AS $_table_name){ | |
| $_ddl = $db->sql_fetch("SHOW CREATE TABLE `".$_table_name."`"); | |
| $_res_ddl = $_ddl['Create Table']; | |
| $_Script = PHP_EOL.PHP_EOL; | |
| $_Script ="-- --------------------------------------------------------".PHP_EOL; | |
| $_Script .= PHP_EOL; | |
| $_Script .="--".PHP_EOL; | |
| $_Script .="-- Table structure for table `".$_table_name."` ".PHP_EOL; | |
| $_Script .="--".PHP_EOL; | |
| $_Script .= PHP_EOL; | |
| if ( intval($_POST['droptable']) === 1 ) { | |
| $_Script .="DROP TABLE IF EXISTS `".$_table_name."` ; ".PHP_EOL; | |
| } | |
| $_Script .= $_res_ddl." ".PHP_EOL;; | |
| $_Script .= PHP_EOL.PHP_EOL; | |
| echo $_Script; | |
| flush(); | |
| // include row data | |
| if ( intval($_POST['rowdata']) === 1 ) { | |
| $_Script = PHP_EOL.PHP_EOL; | |
| $_Script .= PHP_EOL."--"; | |
| $_Script .= PHP_EOL."-- Dumping data for table `".$_table_name."`"; | |
| $_Script .= PHP_EOL."--"; | |
| $_Script = PHP_EOL.PHP_EOL; | |
| echo $_Script; | |
| flush(); | |
| $_qry = "SELECT * FROM `".$_table_name."` "; | |
| $_res = $db->query($_qry); | |
| if($_res_cnt = $db->num_rows($_res)){ | |
| while($_res_rs = $db->fetch_row($_res) ){ | |
| $_col_cnt = count($_res_rs); | |
| $_Script = "INSERT INTO `".$_table_name."` VALUES (" ; | |
| for($_col=0 ; $_col <$_col_cnt ; $_col++ ){ | |
| //$_Col_Data = addslashes($_Col_Data); | |
| $_Col_Data = $db->sql_escape_string($_res_rs[$_col]); | |
| $_Script .= ($_col ==0 ) ? "'".$_Col_Data."'" : ",'".$_Col_Data."' " ; | |
| } | |
| $_Script .=" );\n" ; | |
| echo $_Script; | |
| flush(); | |
| } | |
| $db->free_result($_res); | |
| } | |
| } | |
| } | |
| // End of Dump | |
| if( intval($_POST['download']) !== 1 ){ | |
| echo "</textarea>"; | |
| } | |
| exit; | |
| }// end dump; | |
| ################################################# | |
| // table informations | |
| $_qry = "SELECT table_name, table_type, engine | |
| ,create_time, update_time,table_collation ,table_comment | |
| ,(data_length + index_length) as table_size | |
| ,table_rows | |
| FROM information_schema.tables AS tbl | |
| WHERE table_schema = '".DMP_DB."' | |
| ORDER BY table_name "; | |
| $_res = $db->query($_qry); | |
| $_infos = "<table cellspacing='1' cellpadding='1' bgcolor='#eaeaea' border='0'>"; | |
| $i = 0; | |
| $size_sum = 0 ; | |
| $row_sum = 0 ; | |
| $row_sum2 = 0 ; | |
| while ($_rs = $db->fetch_row($_res)){ | |
| $cc = count($_rs); | |
| if($i == 0){ | |
| $_infos .="<tr class='header'> | |
| <td> <input type='checkbox' class='check_all'> </td> | |
| "; | |
| for($co=0; $co < $cc ; $co++){ | |
| $fieldName = mysqli_fetch_field_direct($_res, $co)->name; | |
| //$fieldName =mysql_field_name($_res, $co); | |
| $_infos .="<td height='35'> ".$fieldName."</td>"; | |
| } | |
| $_infos .="<td height='35'> row count</td>"; | |
| $_infos .="</tr>"; | |
| } | |
| $_infos .="<tr>"; | |
| $_infos .="<td class='cell' height='' ><input type='checkbox' class='check_table' name='_tables[]' value='".$_rs[0]."' ></td>"; | |
| for($o=0 ; $o < $cc ; $o++){ | |
| $fieldName = mysqli_fetch_field_direct($_res, $o)->name; | |
| $col_vals = $_rs[$o]; | |
| if ( $fieldName == 'table_name' ) { | |
| $table_name = $col_vals; | |
| } | |
| if ( $fieldName == 'table_rows' ) { | |
| $row_sum += intval($col_vals); | |
| $col_vals = number_format($col_vals); | |
| } | |
| if ( $fieldName == 'table_size' ) { | |
| $size_sum += intval($col_vals); | |
| $_infos .="<td class='cell' > ".$col_vals." <span style='color:#978ee3;'>("._format_size($col_vals).")</span></td>"; | |
| }else{ | |
| $_infos .="<td class='cell' > ".$col_vals." </td>"; | |
| } | |
| } | |
| $qry_cnt = "SELECT COUNT(*) AS cnt FROM ".$table_name." "; | |
| $cnt_row = $db->sql_fetch($qry_cnt); | |
| $row_sum2 += intval($cnt_row['cnt']); | |
| $_infos .="<td class='cell' style='color:#978ee3;'> ".number_format($cnt_row['cnt'])." </td>"; | |
| $_infos .="</tr>"; | |
| $i++; | |
| } | |
| $_infos .="<tr class=''><td class='cell'> </td>"; | |
| for($o=0 ; $o < $cc ; $o++){ | |
| $fieldName = mysqli_fetch_field_direct($_res, $o)->name; | |
| switch($fieldName){ | |
| case 'table_rows' : | |
| $_infos .="<td class='cell' >".number_format($row_sum)."</td>"; | |
| break; | |
| case 'table_size' : | |
| $_infos .="<td class='cell' >"._format_size($size_sum)."</td>"; | |
| break; | |
| default : | |
| $_infos .="<td class='cell' > </td>"; | |
| } | |
| } | |
| $_infos .="<td class='cell' style='color:#978ee3;'>".number_format($row_sum2)."</td>"; | |
| $db->free_result($_res); | |
| $_infos .="</tr>"; | |
| $_infos .="</table>"; | |
| header("content-type:text/html; charset=utf-8;"); | |
| ?> | |
| <!DOCTYPE HTML> | |
| <html lang="ko"> | |
| <head> | |
| <meta charset="utf-8"> | |
| <title> <?=$_SERVER['HTTP_HOST']?>_ Backup.. </title> | |
| <style type='text/css'> | |
| div{ | |
| font-size:12px; | |
| font-family:Tahoma; | |
| } | |
| .header{ | |
| border:1px solid #D0D0D0; | |
| padding:0px; | |
| background-color:#DFDFDF; | |
| font-weight:bold; | |
| } | |
| .cell{ | |
| padding:2px 5px; | |
| background-color:#fff; | |
| } | |
| input[type="radio"]{ vertical-align: baseline; } | |
| </style> | |
| <script src="https://code.jquery.com/jquery-2.2.4.min.js" | |
| integrity="sha256-BbhdlvQf/xTY9gja0Dq3HiwQF8LaCRTXxZKRutelT44=" | |
| crossorigin="anonymous"></script> | |
| </head> | |
| <body bgcolor='' background='' leftmargin="0" topmargin="0" marginwidth="0" marginheight="0"> | |
| <div style='padding:10px;min-width:600px;'> | |
| <h4>DB Dump </h4> | |
| <form method='post' id="frms" action="?"> | |
| <input type="hidden" name='mode' value='_Dump' /> | |
| <div style='margin:5px 0;background-color:#ccc;padding:10px;border:1px solid #ccc;display:inline-block;' align='' > | |
| <label><input type="radio" name="rowdata" value="1" >structure + row data </label> | |
| <label><input type="radio" name="rowdata" value="0" checked >structure only </label> | |
| | | |
| <label><input type="checkbox" name="droptable" value="1">drop table </label> | |
| | | |
| <label><input type="radio" name="download" value="1" >Download </label> | |
| <label><input type="radio" name="download" value="0" checked >View </label> | |
| | |
| <input type="submit" value='Dump Now!' /> | |
| <input type="button" value='Reset' onclick="location.replace('?')" /> | |
| </div> | |
| <?=$_infos;?> | |
| </form> | |
| </div> | |
| <br> <br> | |
| <script type="text/javascript"> | |
| <!-- | |
| $(function(){ | |
| $('#frms').on('submit', function(e){ | |
| let len = $('#frms input.check_table:checked').length; | |
| if (len < 1 ){ | |
| alert('please selec table'); | |
| return false; | |
| } | |
| if(confirm('proceed?')){ | |
| return true; | |
| }else{ | |
| e.preventDefault(); | |
| } | |
| }); | |
| $('#frms').on('click',' input.check_all', function(e){ | |
| if($(this).prop('checked') == true){ | |
| $('#frms').find('input.check_table').prop('checked',true); | |
| }else{ | |
| $('#frms').find('input.check_table').prop('checked',false); | |
| } | |
| }); | |
| }); | |
| //--> | |
| </script> | |
| </body> | |
| </html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment