Remote MySQL Database Backup and Encrypt

1) Connect to remote mysql database using mysqli 2) Backup database 3) Transfer backup file and save as encrypted compressed file 4) Delete stored backup files older than 5 days Here is sample code that can be modified to work as decribed above. <?php $dbhost = '[login to view URL]'; $dbuser = 'my_user'; $dbpass = 'my_pwd'; $dbname = 'database_name'; $tables = '*'; backup_tables($dbhost, $dbuser, $dbpass, $dbname, $tables); function backup_tables($host, $user, $pass, $dbname, $tables = '*') { $link = mysqli_connect($host,$user,$pass, $dbname); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); exit; } mysqli_query($link, "SET NAMES 'utf8'"); //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); } $return = ''; //cycle through foreach($tables as $table) { $result = mysqli_query($link, 'SELECT * FROM '.$table); $num_fields = mysqli_num_fields($result); $num_rows = mysqli_num_rows($result); $return.= 'DROP TABLE IF EXISTS '.$table.';'; $row2 = mysqli_fetch_row(mysqli_query($link, 'SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; $counter = 1; //Over tables for ($i = 0; $i < $num_fields; $i++) { //Over rows while($row = mysqli_fetch_row($result)) { if($counter == 1){ $return.= 'INSERT INTO '.$table.' VALUES('; } else{ $return.= '('; } //Over fields 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.= ','; } } if($num_rows == $counter){ $return.= ");\n"; } else{ $return.= "),\n"; } ++$counter; } } $return.="\n\n\n"; } //save file $fileName = 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql'; $handle = fopen($fileName,'w+'); fwrite($handle,$return); if(fclose($handle)){ echo "Done"; exit; } }