Using PHP-script to make a SQL-dump

Hey guys!

I’m running a few WordPress-sites, and want to be able to do a full backup of these.
My host, one.com, don’t allow running shell commands like exec() for making a backup of the database.

I did find a free, but non-working, PHP-code, which I’ve modified a bit. It does work, but not perfectly.

Export is working, but importing (via phpMyadmin) is worse. It seems like blog posts, containing “\n” in the database, is making a line break when exporting the database to a textfile.

I’d greatly appreciate any help of clearing this!

Thanks!

[php]

<?php ini_set(”max_execution_time”, 0); //Set your site credentials. $host = ”your.hostname.here”; //host name $username = ”DB_username_here”; //username $password = ”PASSWORDhere”; // your password $dbname = ”DB_name_here”; // database name //We are going to save our backup in zip format so create an object of zip. $zip = new ZipArchive(); //Call the function backup_tables. backup_tables($host, $username, $password, $dbname); //Define the function backup_tables which will create a database sql file. /* backup the db OR just a table */ function backup_tables($host,$user,$pass,$name,$tables = ‘*’) { $con = mysql_connect($host,$user,$pass); mysql_select_db($name,$con); //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); } $return = ””; //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”.$row2[1].”;\n”; 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] = preg_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”; } //save file $handle = fopen(‘db-backup-’.date(‘Y-m-d’).’.sql’,'w+’); fwrite($handle,$return); fclose($handle); } //Convert .sql file in .sql.zip file and remove the .sql file. if (glob(”*.sql”) != false) { $filecount = count(glob(”*.sql”)); $arr_file = glob(”*.sql”); for($j=0;$j<$filecount;$j++) { $res = $zip->open($arr_file[$j].”.zip”, ZipArchive::CREATE); if ($res === TRUE) { $zip->addFile($arr_file[$j]); $zip->close(); unlink($arr_file[$j]); } } } echo ”Arkivet har skapats!”; ?>

[/php]

unless i’m missing something, if you can do a phpmyadmin import, why do you need a script to export?

I want to use my NAS to make a daily FTP backup of my sites.
My web host won’t allow outside connections, no shell access, no cron jobs and alot of other stuff.

So, what I can see my only option is to have a WordPress plugin working as a stand-in for cron jobs, running this PHP-script once every day and my plan with FTP backup still works.

ahh i see, i like the idea. i’ll give it some thought but hopefully someone a little more knowledgeable will come along soon :wink:

Why not make a script (bash, python, php, whatever) that executes the mysql command to dump to a backup file?

mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

[quote author=JimL link=topic=22022.msg76417#msg76417 date=1386187974]
Why not make a script (bash, python, php, whatever) that executes the mysql command to dump to a backup file?

I tried with following php-file.

[php]<?php
exec(mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]);
?>[/php]

The exec() command is concidered “shell access” according to my web host.
So, if PHP won’t do it I’m guessing that it doesn’t matter which language I try it in…

Sponsor our Newsletter | Privacy Policy | Terms of Service