Backup MySql DB with PHP

Hi,

Is there any PHP script available that would take a complete backup of my database?

I have googled for this several times and everytime either I come up with a script that only takes data backup and not the db structure OR a script that uses mysqldump command (since my client is on a shared server, hence he is not authorized to run the execute command on his server),

So was wondering if there is a script that actually takes a complete backup of the db and not using the mysqldump command?


Thanx so much for your help.

 

Hi,

Basically any script shall be requested under Scripts section..

Anyways.

MySqlDumper

Works exactly for ur Need..

Thanks
-Xak

 

This is a function I personally use:

http://awesomephp.com/?Tutorials*18/Downloading-a-MySQL-database-to-a-file.html

Peace,

 

Does that script take a backup of the table structure too?


Thanx

 

This isn't made by me (Author: krakjoe) , but:

<?php
set_time_limit( 0 );
/**
 * Dump a mysql database to stdout table by table, row by row
 *
 */
class MYSQLDUMP
{
    var $mysql ;
    
    var $hostname ;
    var $username ;
    var $password ;
    var $database ;
    
    var $tables ;
    var $structures ;
    
    var $errors ;
    
    function MYSQLDUMP( $hostname, $username, $password, $database )
    {
        $this->errors = array( );
        
        if( ( $this->mysql = mysql_connect( $hostname, $username, $password ) ) )
        {
            $this->hostname = $hostname ;
            $this->username = $username ;
            $this->password = $password ;
            $this->database = $database ;
        
            if( ( mysql_select_db( $database, $this->mysql ) ) )
            {
                $this->GetTables( );
                
                if( count( $this->tables ) )
                {
                    if( !count( $this->errors ) )
                    {
                        header("Content-type: text/plain");
                        header("Content-disposition: attachment; filename=\"{$database}-dump.sql\";");
                        
                        printf( "--\r\n" );
                        printf( "-- Backup of %s\r\n", $database );
                        printf( "-- Found %d tables\r\n", count( $this->tables ) );
                        printf( "--\r\n" );
                        
                        foreach( $this->tables as $name => $create )
                        {
                            printf( "--\r\n" );
                            printf( "-- Creating %s\r\n", $name );
                            printf( "--\r\n" );
                            printf( "%s;\r\n", $create );
                            printf( "\r\n" );
                            
                            if( ( $result = mysql_query( "SELECT * FROM {$name};", $this->mysql ) ) )
                            {
                                if( mysql_num_rows( $result ) )
                                {
                                    printf( "--\r\n" );
                                    printf( "-- Dumping %s data: %d rows\r\n", $name, mysql_num_rows( $result ) );
                                    printf( "--\r\n" );
                                    printf( "\r\n" );
                                    
                                    while( $row = mysql_fetch_assoc( $result ) )
                                    {
                                        $keys = array( );
                                        $values = array( );
                                        
                                        foreach( $row as $key => $value )
                                        {
                                            $keys[]        = sprintf( '`%s`', $key );
                                            $values[]    = sprintf( "'%s'", $value );
                                        }
                                        printf( "INSERT INTO `%s` ( %s ) VALUES ( %s );\r\n", $name, implode( ', ', $keys ), implode( ', ', $values ) );
                                    }
                                }
                                mysql_free_result( $result );
                            }
                            else $this->errors[ ] = "Failed to query mysql for {$name} records";
                        }
                    }
                    else $this->errors[ ] = "Bailing out because of previous errors";
                }
                else $this->errors[ ] = "Failed to get table information from {$database}";
            }
            else $this->errors[ ] = "Failed to select {$database} at {$hostname}";
        }
        else $this->errors[ ] = "Failed to connect to mysql @ {$hostname}";
    }
    function GetTables( )
    {
        if( ( $result = mysql_query( "SHOW TABLES FROM {$this->database};", $this->mysql ) ) )
        {
            if( mysql_num_rows( $result ) )
            {
                while( $table = mysql_fetch_array( $result ) )
                {
                    if( ( $create = mysql_query( "SHOW CREATE TABLE {$table[0]};", $this->mysql ) ) )
                    {
                        if( mysql_num_rows( $create ) )
                        {
                            while( $structure = mysql_fetch_array( $create ) )
                            {
                                $this->tables[$table[0]]=$structure[1];
                            }
                        }
                        else $this->errors[ ] = "Failed to get create table statement for {$table[0]}" ;
                        
                        mysql_free_result( $create );
                    }
                    else $this->errors[ ] = "Failed to query mysql for create statement for {$table[0]}";
                }
            }
            else $this->errors[ ] = "Failed to retrieve tables from {$this->database}" ;
            
            mysql_free_result( $result );
        }
        else $this->errors[ ] = "Failed to query mysql for tables from {$this->database}" ;
    }
}
/** fill in this stuff and open in browser, or execute with cron **/
$backup = new MYSQLDUMP( "localhost", "dbuser", "dbpass", "dbname" );
?>

PHP:

Should be what you need. Just fill in the details at the bottom for the database and then open in the browser to launch a file download of the DB. (Password protect the directory for security.)

This should give you full data / structure backups. Also, it does the backup table by table so you can use it on large databases where PHPMyAdmin usually would stall. I've used this to back up 500+ MB databases with no issues.

BMR777

 

k i will take a look into this and get back to you.

EDIT: This really works. This is what I was looking for.


Thanx to you and to the Author of this script.

 

Nice script, thanks for the share.

 

Hey,

Apart from a FULL backup script, I also want a script that takes incremental backups. Any such available?


Sorry for asking too much

 

Call the previously posted script with a cron, buffer the output and save it to a file as such (eg) database-2008-12-15.txt. Make sure that the files are either saved to a protected area or below the root level.

 

Yes it does.

I'll try to work on one that does incremental change. However, wouldn't it be easier to delete the file and do it again. It seems that it will take more time finding the differences, specially if the database is too large.

Peace,