backup MySQL database

The database backup is a very important task for every web developer. Regular database backup protect your data from loss and it helps to restore the database if any issue occurred.

There many ways available to backup MySQL database. You can backup database in one click from phpMyAdmin. But if you want to create auto MySQL database backup without login to your hosting server or phpMyAdmin, you can do it from the script using PHP.

In this tutorial, we will create a PHP script to backup MySQL database and save in a SQL file.

Backup MySQL Database using PHP

Using backup_database()function you can backup all table from a database. In this script, you just need to replace your database username, password & database name on the following line:

backup_database('YourDatabaseHost', 'YourDatabaseUsername', 'YourDatabasePassword', 'YourDatabaseName');

Full Source Code

<?php


backup_database('YourDatabaseHost', 'YourDatabaseUsername', 'YourDatabasePassword', 'YourDatabaseName');



/* Backup Database Function */

function backup_database($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);
        
    }
    
    
    
    //cycle through
    
    foreach ($tables as $table) {
        
        $result = mysql_query('SELECT * FROM ' . $table);
        
        $num_fields = mysql_num_fields($result);
        
        
        
        
        
        $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";
        
    }
    
    
    
    //save backup file
    
    $handle = fopen('db-backup(' . date("Y-m-d") . ')-' . time() . '.sql', 'w+');
    
    fwrite($handle, $return);
    
    fclose($handle);
    
}



?>

If you want a daily backup of your website database just setting up daily cronjob command this script.

Also, if you need my help to set up this code in your website, Contact me.

3 COMMENTS

  1. Pretty nice post. I just discovered your weblog and wished to say
    that I’ve really enjoyed searching your site posts.

    In any case
    I’ll be subscribing to your feed (hopefully I could think
    it is) and I am hoping
    you write again soon!

  2. I was suggested this web site by my cousin. I’m not sure whether this post is written by him as no one else know such detailed about my problem.
    You’re amazing! Thanks!

LEAVE A REPLY

Please enter your comment!
Please enter your name here