Results 1 to 11 of 11

Thread: Backup a Mysql database (php 5.3.0)

  1. #1

    Thread Starter
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Backup a Mysql database (php 5.3.0)

    Hi,

    This is a php script I made because I was curious how the automated backup of the mysql databases was achieved.

    PHP Code:
    <?php
    //Name of host you want to use
    $db_host "localhost";

    //Name of database you want to use
    $db_name "students";
    //User
    $db_user "root";
    //Password
    $db_pass "";
    //Name of table or tables
    $table "students";

    mysql_connect($db_host,$db_user,$db_pass);

    mysql_select_db($db_name) or die("Unable to select database.");

    function 
    datadump ($table) {
         
    $result ="";
    $row="";

        
    $result .= "# Dump of $table \n";

        
    $result .= "# Dump DATE : " date("d-M-Y") ."\n\n";
     
        
    $query mysql_query("select * from $table");

        
    $num_fields = @mysql_num_fields($query);
    //Retrieve the data from the original database 
        
    for ($i =0$i <$num_fields$i++) {
          
    $result .= "INSERT INTO ".$table." VALUES(";
          
    $data mysql_query("SELECT * FROM $table");
    while(
    $info mysql_fetch_array$data,MYSQL_ASSOC )){
          
    $comma_separated implode(",",  $info);
          
    //Insert the data from the original array in to sql  statement
               
    if ($i<($num_fields)) $result .= "$comma_separated";
             
          
    $result .= ");\n";
         }
         return 
    $result "\n\n\n";
         }
    }


    $table1 datadump ($table); //Add more datadumps if needed


    $content $table1// Add additional datadumps to the string 

    //Creates an .sql file for download to your computer 
    $file_name "MySQL_Database_Backup.sql";

    Header("Content-type: application/octet-stream");

    Header("Content-Disposition: attachment; filename=$file_name");

    echo 
    $content;

    exit;

    ?>
    If you have any comments or suggestions on how I can improve the code please post them. By the way, I'm hoping to create a totally automated back script sometime.

    Edit:

    Forgot to mention the php code version which is 5.3.0.



    Nightwalker
    Last edited by Nightwalker83; Jan 14th, 2010 at 11:48 PM. Reason: Fixed spelling
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  2. #2

    Thread Starter
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Backup a Mysql database (php 5.3.0)

    Here is version 1.1 of the database backup script:

    PHP Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    </head>
    <body>

    <form action="<?php $_SERVER['PHP_SELF'];?>" method="POST" enctype="multipart/form-data" name="backup">
    <label>Host:</label> <br />
    <input name="host" type="text" maxlength="20" /><br />
    <label>Database name:</label> <br />
    <input name="db_name" type="text" maxlength="20" /><br /><label>Table:</label> <br />
    <input name="table" type="text" maxlength="20" /><br /><label>User:</label> <br />
    <input name="user" type="text" maxlength="20" /><br /><label>Password:</label> <br />
    <input name="pass" type="password" maxlength="20" /><br />
    <input name="submit" type="submit" /> &nbsp; <input name="reset" type="reset" />
    </form>
    </body>
    </html>
    <?php
    if($_SERVER['REQUEST_METHOD'] == "POST"){
      
    $db_host "";
    $db_name "";
    $db_user "";
    $db_pass "";
    $table "";
      
    //Name of host you want to use
    $db_host $_POST["host"];

    //Name of database you want to use
    $db_name $_POST["db_name"];
    //User
    $db_user $_POST["user"];
    //Password
    $db_pass $_POST["pass"];
    //Name of table or tables
    $table $_POST["table"];
    mysql_connect($db_host,$db_user,$db_pass);

    mysql_select_db($db_name) or die("Unable to select database.");

    function 
    datadump ($table) {
         
    $result ="";
    $row="";

        
    $result .= "# Dump of $table \n";

        
    $result .= "# Dump DATE : " date("d-M-Y") ."\n\n";

        
    $query mysql_query("select * from $table");

        
    $num_fields = @mysql_num_fields($query);
    //Retrieve the data from the original database
        
    for ($i =0$i <$num_fields$i++) {
          
    $result .= "INSERT INTO ".$table." VALUES(";
          
    $data mysql_query("SELECT * FROM $table");
    while(
    $info mysql_fetch_array$data,MYSQL_ASSOC )){
          
    $comma_separated implode(",",  $info);
          
    //Insert the data from the original array in to sql  statement
               
    if ($i<($num_fields)) $result .= "$comma_separated";
            
          
    $result .= ");\n";
        
         return 
    $result "\n\n\n";
         }
    }
    }
      
    $table1 datadump ($table); //Add more datadumps if needed


    $content $table1// Add additional datadumps to the string

    //Creates an .sql file for download to your computer
    $file_name "MySQL_Database_Backup.sql";
        
    header('Content-Disposition: attachment; filename='.basename($file_name));
        
    //Remove the form from the $content data  
        
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
        
    ob_clean();
        
    //flush();
    echo $content;
    exit;
    }
    ?>
    Last edited by Nightwalker83; Mar 31st, 2010 at 02:42 AM. Reason: Fixed spelling
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  3. #3
    Frenzied Member I_Love_My_Vans's Avatar
    Join Date
    Jan 2005
    Location
    In the PHP compiler
    Posts
    1,275

    Re: Backup a Mysql database (php 5.3.0)

    1) Your opening HTML docytpe needs a <
    2) You should probably check for the data type of each field before constructing the INSERT string, I ran this code on my test box and found that a long string which contained a few commas, needless to say if I were to attempt to execute this data I would probably get errors.
    3) If you decide to echo the SQL out onto the screen afterwards consider escaping HTML characters, in on of my records I have an <img> tag that has been executed in the browser.

    Edit:

    Just read kows post in the PHP forum, he's right, take a look at mysqldump
    Last edited by I_Love_My_Vans; Jan 15th, 2010 at 10:37 AM.

  4. #4

    Thread Starter
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Backup a Mysql database (php 5.3.0)

    Quote Originally Posted by I_Love_My_Vans View Post
    1)
    Just read kows post in the PHP forum, he's right, take a look at mysqldump
    Thanks! Although, the reason I have created the backup using php is so I could get an understanding of how the backup system works not for the purpose of performing an actual backup. I know I can use programs such as mysql dump to backup the database but in it that case mysql dump (or another program) would be called to preform the backup where as my code above runs independently of any of those database programs.

    I also noticed for the next version of the code I would need a function so the user doesn't send the data until all the required fields are filled in. Doing so causes an error with the code.
    Last edited by Nightwalker83; Jan 16th, 2010 at 06:41 AM. Reason: Fxed spelling!
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  5. #5
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Wink Re: Backup a Mysql database (php 5.3.0)

    night walker
    it will be great help to the community if you provide some automation for MySQL back Up
    operation through vb.net
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  6. #6

    Thread Starter
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Backup a Mysql database (php 5.3.0)

    Here is version 1.2 of the above script which I created a while ago.

    PHP Code:
    <!-- Change Log
    www.aaronspehr.net
    Version 1.0  2nd January 2010 - Created the basic database backup function.
    Version 1.1  15th  January 2010 -  Added a form to allow the user to enter the details of the database they want to backup.
    Version 1.2 24th  January 2010 - Form validation added to ensure the user enters the correct information so the system does not fail.

    -->

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Assegai Database Backup</title>
    <script type="text/javascript">
    //The function checks to see whether or not the form fields have been filled-in correctly and if not then it displays a message telling the user to fill-in that information.
    function ValidateInputs(objForm) {
        //objForm. is used for Firefox validation purposes
        var sHost = objForm.host.value;
        var sName = objForm.db_name.value;
        var sTable = objForm.table.value;
        var sUser = objForm.user.value;
        var sErr = "";
        var sInstr = "";
        
        //Check the fields
        
    //Checks if all fields have been filled in
            if (sHost == "") {
            sErr = "You must enter a host name!\n";
        }       
        
        if (sName == "") {
            sErr = sErr + "You must enter a database name!\n";
        }

        if (sTable  == "") {
            sErr = sErr + "You must enter a table!\n";
        }

        if (sUser == "") {
            sErr = sErr + "You must enter your database username!\n";
        }
        
        if (sErr == "")    {
            return true;
        }
        else {
            sInstr = "The following errors were found with your submission."
            sInstr = sInstr + "\nPlease correct them and try again.\n\n"
            sErr = sInstr + sErr

            //inform the user of the problem
            alert(sErr);
            return false;
        }

    }


    </script>
    </head>
    <style type="text/css">
    body{
        margin-top: 150px;
        margin-left: 300px;
        }
        form{
            margin-left: 100px;
            
            }
    </style>
    <body>
    <h1>Assegai Database Backup</h1>
    <noscript>To ensure the backup system works for you please enable javascript!</noscript>
    <form action="<?php $_SERVER['PHP_SELF'];?>" method="POST" enctype="multipart/form-data" name="backup" onsubmit="return ValidateInputs(this);">
    <label>Host:</label> <br />
    <input name="host" type="text" maxlength="20" /><br />
    <label>Database name:</label> <br />
    <input name="db_name" type="text" maxlength="20" /><br /><label>Table:</label> <br />
    <input name="table" type="text" maxlength="20" /><br /><label>User:</label> <br />
    <input name="user" type="text" maxlength="20" /><br /><label>Password:</label> <br />
    <input name="pass" type="password" maxlength="20" /><br />
    <input name="submit" type="submit" value="Submit" /> &nbsp; <input name="reset" type="reset" value="Reset" />
    </form>
    </body>
    </html>
    <?php
    if($_SERVER['REQUEST_METHOD'] == "POST"){
      
    $db_host "";
    $db_name "";
    $db_user "";
    $db_pass "";
    $table "";
      
    //Name of host you want to use
    $db_host $_POST["host"];

    //Name of database you want to use
    $db_name $_POST["db_name"];
    //User
    $db_user $_POST["user"];
    //Password
    $db_pass $_POST["pass"];
    //Name of table or tables
    $table $_POST["table"];
    mysql_connect($db_host,$db_user,$db_pass);

    mysql_select_db($db_name) or die("Unable to select database.");

    function 
    datadump ($table) {
         
    $result ="";
    $row="";

        
    $result .= "# Dump of $table \n";

        
    $result .= "# Dump DATE : " date("d-M-Y") ."\n\n";
    //Retrieve the data from the original database
         
    $data mysql_query("SELECT * FROM $table");
      
          while(
    $info mysql_fetch_assoc($data)){
        foreach(
    $info as $col => $value)
      {
      
    $result .= "INSERT INTO ".$table.$col

           VALUES("
    ;
          
    $comma_separated implode(",",  $info);
          
    //Insert the data from the original array in to sql  statement
               
    $result .= "$comma_separated";
          
    $result .= ");\n";
         return 
    $result "\n\n\n";

    }
    }
    }

    $table1 datadump ($table); //Add more datadumps if needed


    $content $table1// Add additional datadumps to the string

    //Creates an .sql file for download to your computer
    $file_name "MySQL_Database_Backup.sql";
        
    header('Content-Disposition: attachment; filename='.basename($file_name));
        
    //Remove the form from the $content data  
        
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
        
    ob_clean();
        
    //flush();
    echo $content;
    exit;
    }
    ?>
    Edit:

    Quote Originally Posted by make me rain View Post
    night walker
    it will be great help to the community if you provide some automation for MySQL back Up
    operation through vb.net
    Unfortunately, I do not know vb.net sorry. I might try automating the MYSQL back up using C# though.
    Last edited by Nightwalker83; Apr 6th, 2011 at 05:31 AM. Reason: Adding more!
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  7. #7
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Smile Re: Backup a Mysql database (php 5.3.0)

    any how thanks for your reply
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  8. #8
    Fanatic Member
    Join Date
    Sep 2005
    Posts
    540

    Re: Backup a Mysql database (php 5.3.0)

    This is an excellent script. I will use it to backup some modem statistics I've been logging!

  9. #9

    Thread Starter
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Backup a Mysql database (php 5.3.0)

    Quote Originally Posted by Slyke View Post
    This is an excellent script. I will use it to backup some modem statistics I've been logging!
    Feel free to adjust the above as you see fit and if you can make any improvements on the above code please post them here.
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  10. #10
    New Member faustjonson's Avatar
    Join Date
    Mar 2016
    Location
    USA New York
    Posts
    4

    Re: Backup a Mysql database (php 5.3.0)

    A good skript, but it build only table "students"
    It would be cool to write the automatic backup there every week for example. And duplicate database on the cloud

  11. #11

    Thread Starter
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Backup a Mysql database (php 5.3.0)

    There is a better code based on the one above written by someone else on the forums. Try searching for "MySql Backup".
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width