how to backup and restore mysql database (localhost) to cloud service in PHP

Question

how to backup and restore mysql database (localhost) to Dropbox in PHP I try this save to my HDD Drive but how to save to online drive and retrieve from online drive and also show all saved datas using PHP

Answer 1

First save the backup on server using the following code:

<?php
try {
    backup_tables('localhost','root','','database');
}
catch(Exception $e) {
  echo 'Error: ' .$e->getMessage();
}
    /* backup the db OR just a table */
    function backup_tables($host,$user,$pass,$name,$tables = '*')
    {

        $link = new mysqli($host, $user, $pass, $name);
        // Check connection
        if ($link->connect_error) {
            throw new Exception("Connection failed: " . $link->connect_error);
        }
        $return='';
        //get all of the tables
        if($tables == '*')
        {
            $tables = array();
            $result = mysqli_query($link,'SHOW TABLES');
            while($row = mysqli_fetch_row($result))
            {
                $tables[] = $row[0];
            }
        }
        else
        {
            $tables = is_array($tables) ? $tables : explode(',',$tables);
        }

        //cycle through
        foreach($tables as $table)
        {
            $result = mysqli_query($link,'SELECT * FROM '.$table);
            $num_fields = mysqli_num_fields($result);

            $return.= 'DROP TABLE '.$table.';';
            $row2 = mysqli_fetch_row(mysqli_query($link,'SHOW CREATE TABLE '.$table));
            $return.= "\n\n".$row2[1].";\n\n";

            for ($i = 0; $i < $num_fields; $i++) 
            {
                while($row = mysqli_fetch_row($result))
                {
                    $return.= 'INSERT INTO '.$table.' VALUES(';
                    for($j=0; $j < $num_fields; $j++) 
                    {
                        $row[$j] = addslashes($row[$j]);
                        $row[$j] = str_replace("\n","\\n",$row[$j]);
                        if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                        if ($j < ($num_fields-1)) { $return.= ','; }
                    }
                    $return.= ");\n";
                }
            }
            $return.="\n\n\n";
        }
        
        //save file
        $time=time();
        $file_name='db-backup-'.$time.'-'.(md5(implode(',',$tables))).'.sql';
        $sql="INSERT INTO `backups` (`file_name`,`time`) VALUES ('$file_name','$time')";
        if(mysqli_query($link,$sql)){
            $handle = fopen($file_name,'w+');
            fwrite($handle,$return);
            fclose($handle);
        }
        else
        {
            throw new Exception("Unable to insert into backups database!");
        }
    }

?>

And now use Dropbox API to save it on your dropbox. For more info visit: Dropbox API

Finally, delete the file on server using unlink() method in php.