backup MySQL using PHP only (with server restrictions)

Question

I am trying to backup my MySQL Database using PHP. I want to backup it to an .SQL so i can import it back in MySQL if anything goes wrong.

The only problem I found out when i tried to use Exec of System, that these functions are turned of by my host, and their not gonna activate it because of security reasons.

Also I cannot use : SELECT ... INTO OUTFILE.

What my thoughts are : Is it possible to use SELECT * FROM [Table] and get a php array from that query. And then push that data into an SQL file?

I cannot find information about this even though I tried some things. I know you can make a .txt file with the right text in it and call it a .sql but then i need to write everything by hand, thats gonna take a while.

Or is their a better way to do this?

Thanks in advance!

Why not to ask your hosting provider to backup your database? It is a common request. — BlitZ
– BlitZ, Commented Aug 21, 2014 at 11:07
Generating a SQL script from a table in PHP is very complex. It can be done (see phpMyAdmin) but takes loads of effort. Why not just import/export as CSV ? — ToBe
– ToBe, Commented Aug 21, 2014 at 11:08
@HAL9000 because they are already making backups, but i don't want to be dependant on them, Because when i want support now it takes 20 - 30 hours, i don't want my customers to wait hours :) — Jean-Paul
– Jean-Paul, Commented Aug 21, 2014 at 11:12
If you have real customers, then you should host that stuff with a provider that offers proper support with defined response times. — C3roe
– C3roe, Commented Aug 21, 2014 at 12:12

Rikudou_Sennin · Accepted Answer · 2014-08-21 11:14:01Z

1

$tables can be * or array of table names or comma separated list of table names

function backup_tables($host,$user,$pass,$name,$tables = '*') {

    $link = mysql_connect($host,$user,$pass); 
    mysql_select_db($name,$link); 
  mysql_query('SET CHARACTER SET cp1250');
    mysql_query('SET NAMES cp1250');


    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);
    }

    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";

        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] = ereg_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";
    }

    $handle = fopen('db-backup-'.date('Y-m-d-H-i-s').'-'.(md5(implode(',',$tables))).'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
}

answered Aug 21, 2014 at 11:14

Rikudou_Sennin

1,38510 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ToBe Over a year ago

Please never use anything other than utf8 or one of it's children in any code or even code sample. People will have LOADS of trouble with it...

Rikudou_Sennin Over a year ago

ToBe: I know it, but I had to use it, 'cause I made this backup script on a very old CMS which stores data in db in cp1250.

Collectives™ on Stack Overflow

backup MySQL using PHP only (with server restrictions)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related