how to create a backup database script in php and zip it

Question

what is the best way to backup a database using php and backing data from mysql,i have tried to look for tutorials online but i seem not to understand nicely well i wrote this though its not working nicely,im getting these errors and warnings

Warning: gzopen(backups/adminpanel/adminpanel_aggrement_1367616442.sql.gz): failed to open stream: No such file or directory in C:\xampp\htdocs\how are things\admin panel\backup.php on line 17

Notice: Undefined variable: time in C:\xampp\htdocs\how are things\admin panel\backup.php on line 30
The file--backups/adminpanel/aggrement_.sql.gz--could not be opened for writing.

the code on line 17 and 30 is this

if ($fp = gzopen ("$dir/{$db_name}_{$table}_{$bu_time}.sql.gz", 'w9')) {



  echo "<p>The file--$dir/{$table}_{$time}.sql.gz--could not be opened for writing.</p>\n";

this is my code

<?php 
$db_name = 'adminpanel';
$dir = "backups/$db_name";
if (!file_exists('path/to/directory')) {
    @mkdir('path/to/directory');
}
$bu_time = time();
$dbc = @mysqli_connect (localhost, root, nokiae71, adminpanel);
$q = 'SHOW TABLES';
$r = mysqli_query($dbc, $q);
if (mysqli_num_rows($r) > 0) {
    echo "<p>Backing up database '$db_name'.</p>\n";
    while (list($table) = mysqli_fetch_array($r, MYSQLI_NUM)) {
        $q2 = "SELECT * FROM $table";
        $r2 = mysqli_query($dbc, $q2);
        if (mysqli_num_rows($r2) > 0) {
            if ($fp = gzopen ("$dir/{$db_name}_{$table}_{$bu_time}.sql.gz", 'w9')) {
                                while ($row = mysqli_fetch_array($r2, MYSQLI_NUM)) {
                    foreach ($row as $value) { 

                        gzwrite ($fp, "'$value', ");
                    }
                    gzwrite ($fp, "\n"); 

                }               
                // Close the file:
                gzclose ($fp); 

            } else { // Could not create the file!
                echo "<p>The file--$dir/{$table}_{$time}.sql.gz--could not be opened for writing.</p>\n";
                break; 
            }   
        }
    } 
} else {
    echo "<p>The submitted database--$db_name--contains no tables.</p>\n";
}

?>

how can i make this work or is there any better script i can use out there...

Try this [one](http://www.dzone.com/snippets/back-mysql-database-php) — ROMMEL, May 03 '13 at 21:54

score 3 · Accepted Answer · edited May 23 '17 at 11:51

3

Now, this solution uses cron, but I find it very easy so I thought it was worth mentioning. Cron may already be installed on the server you're using.

This is the command that I schedule cron to run once a night:

mysqldump -h URL_TO_YOUR_DATABASE.com -u YOUR_MYSQL_USERNAME -pYOUR_MYSQL_PASSWORD --single-transaction YOUR_DATABASE_NAME | gzip > ~/PATH_WHERE_YOU_WANT_TO_PLACE_BACKUPS/db_backup_$(date +%Y-%m-%d).sql.gz

That's it.

This tells mysqldump to dump your whole database as sql. Then gzip compresses it. And then it's stored in a spot on your server with the date appended to the end of the filename.

If you really want to trigger it with php instead of cron you could try calling mysqldump from php.

edited May 23 '17 at 11:51

Community

1
1

answered May 03 '13 at 22:37

Andrew

8,363
8
43
71

How do i put this command into a PHP Script so that it executes – NaveenThally Feb 20 '15 at 11:44
@NaveenThally if you follow the link at the end of my answer you'll see how to call it from php. You can use the `exec` command. So it would be `exec('mysqldump -h URL_TO_YOUR_DATABASE.com -u YOUR_MYSQL_USERNAME -pYOUR_MYSQL_PASSWORD --single-transaction YOUR_DATABASE_NAME | gzip > ~/PATH_WHERE_YOU_WANT_TO_PLACE_BACKUPS/db_backup_$(date +%Y-%m-%d).sql.gz');` . Remember to put in your actual database information in place of the words in ALL_CAPS. And this will only run it on-demand. To schedule it you'll need to use cron. – Andrew Feb 21 '15 at 18:31

how to create a backup database script in php and zip it

1 Answers1

Linked