dongmangsha7354
2016-05-30 05:52 阅读 47
已采纳

从Web应用程序中的PHPMyAdmin导出SQL结构和数据

I have a client that use my web application for debts control. He wants to save and backup all data so if something happened to his laptop, he could repair his data.

I told him to enter to phpmyadmin and click on export, but he think that it is little bit complicated for him and for his employees.

So I need to add a new button to the app so he can click on it and export an sql file that contain all data and all database tables and structure using PHP PDO of a specific database (not all phpmyadmin dbs).

Is it possible ? I searched and couldn't find something like that.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    duanji1610 duanji1610 2016-05-30 05:58

    Add a button and on the back-end execute this MYSQL command line command using exec of php on button click.

    mysqldump -u'username' -p'password' database_name > /path/backup.sql
    

    You can download the file directly in browser using the code given below

          $fileName =  "/path/backup.sql";
           if (file_exists($fileName)) {
               header('Content-Description: File Transfer');
               header('Content-Type: application/octet-stream');
               header('Content-Disposition: attachment; filename="'.basename($fileName).'"');
               header('Expires: 0');
               header('Cache-Control: must-revalidate');
               header('Pragma: public');
               header('Content-Length: ' . filesize($fileName));
               readfile($fileName);
               exit;
           }
    
    点赞 评论 复制链接分享
  • dream_wu2015 dream_wu2015 2016-05-30 06:06

    Try the following:

    <?php
    error_reporting(0);
    
    backup_tables('localhost','root','password','your_database');
    
    /* backup the db OR just a table */
    function backup_tables($host,$user,$pass,$name,$tables = '*')
    {
    
        $link = mysql_connect($host,$user,$pass);
        mysql_select_db($name,$link);
    
        //get all of the tables
        if($tables == '*')
        {
            $tables = array();
            $result = mysql_query('SHOW TABLES');
            while($row = mysql_fetch_row($result))
            {
                $tables[] = $row[0];
            }
        }
        else
        {
            $tables = is_array($tables) ? $tables : explode(',',$tables);
        }
    
        //cycle through
        foreach($tables as $table)
        {
            $result = mysql_query('SELECT * FROM '.$table);
            $num_fields = mysql_num_fields($result);
    
            $return.= 'DROP TABLE '.$table.';';
            $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
            $return.= "
    
    ".$row2[1].";
    
    ";
    
            for ($i = 0; $i < $num_fields; $i++)
            {
                while($row = mysql_fetch_row($result))
                {
                    $return.= 'INSERT INTO '.$table.' VALUES(';
                    for($j=0; $j < $num_fields; $j++)
                    {
                        $row[$j] = addslashes($row[$j]);
                        $row[$j] = ereg_replace("
    ","\
    ",$row[$j]);
                        if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                        if ($j < ($num_fields-1)) { $return.= ','; }
                    }
                    $return.= ");
    ";
                }
            }
            $return.="
    
    
    ";
        }
    
        //save file
        $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
        fwrite($handle,$return);
        fclose($handle);
    }
    

    You can download the sql file by running the following code:

    <?php
    
    $files = glob(dirname(__FILE__) . "/*.sql");
    
    
    foreach ($files as $file) {
        if (file_exists($file)) {
            header('Content-Description: File Transfer');
            header('Content-Type: application/octet-stream');
            header('Content-Disposition: attachment; filename="'.basename($file).'"');
            header('Expires: 0');
            header('Cache-Control: must-revalidate');
            header('Pragma: public');
            header('Content-Length: ' . filesize($file));
            readfile($file);
        }
    }
    

    This code use mysql api, I think you can easily convert it into mysqli. This code will backup all the tables of your database.

    点赞 评论 复制链接分享

相关推荐