Sometimes you need to make a mysqldump backup without having SSH access or PHPMyAdmin. It can be done with PHP by means of SHOW TABLES/SHOW CREATE TABLE mysql queries. Below is an improved solution proposed here.
Improvements:
[list style=“upper-alpha tick“]
[list_item]Got rid of memory_limit – writing to file on every iteration[/list_item]
[list_item]gzip support – you don’t have to download huge uncompressed .sql[/list_item]
[list_item]PHP notice fixed[/list_item]
[list_item]added IF EXISTS to DROP TABLE[/list_item]
[list_item]ereg_replase -> str_replace[/list_item]
[list_item]added set_time_limit(0) to work with large DB’s[/list_item]
[/list]
In order to make a dump:
[list style=“upper-alpha tick“]
[list_item]change mysql login/password/database on this line:
backup_tables('localhost','user','password','db_name');
[/list_item]
[list_item]upload the file to your hoster[/list_item]
[list_item]Create „backup“ folder with write permissions to web server. If you’re not sure what permissions to set, just set 777 – the folder should be deleted after you’re done with backup[/list_item]
[list_item]Navigate to host.com/backup.php – assuming no rewrite urls[/list_item]
[list_item]Download your backup from „backup“ folder[/list_item]
[/list]
Here is the script:
<?php error_reporting(E_ALL); ini_set('display_errors',1); ini_set('memory_limit','1500M'); set_time_limit(0); backup_tables('localhost','user','xxxxxxx','xxxxxxxxxx'); /* backup the db OR just a table */ function backup_tables($host,$user,$pass,$name,$tables = '*') { //save file $handle = gzopen(getcwd() . DIRECTORY_SEPARATOR . 'backup' . DIRECTORY_SEPARATOR . 'db-backup-'.time().'.sql.gz','w9'); $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 IF EXISTS '.$table.';'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return .= "\n\n".$row2[1].";\n\n"; gzwrite($handle,$return); 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] = str_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $return.= ");\n"; gzwrite($handle,$return); } } $return ="\n\n\n"; gzwrite($handle,$return); } gzclose($handle); }