Backup MySQL database with CodeIgniter

Question

I have been looking into the user guide which came with CodeIgniter. I became very interested with the dbutil() method. Particularly the following line of code:

// Load the DB utility class
$this->load->dbutil();

// Backup your entire database and assign it to a variable
$backup =& $this->dbutil->backup(); 

// Load the file helper and write the file to your server
$this->load->helper('file');
write_file('/path/to/mybackup.gz', $backup); 

// Load the download helper and send the file to your desktop
$this->load->helper('download');
force_download('mybackup.gz', $backup); 

It is supposed to backup the currently loaded MySQL database. But unfortunately, it is not working and I get the following message:

A PHP Error was encountered

Severity: Notice

Message: Undefined property: CI_Loader::$dbutil

Filename: views/view.php

Line Number: 10

Fatal error: Call to a member function backup() on a non-object in C:\xampp\htdocs\CodeIgniter\application\views\view.php on line 10

What am I missing here? Any help would be really appreciated.

Solution 1

Try this, You can change format zip to gz if you like :)

$this->load->dbutil();

$prefs = array(     
    'format'      => 'zip',             
    'filename'    => 'my_db_backup.sql'
    );


$backup =& $this->dbutil->backup($prefs); 

$db_name = 'backup-on-'. date("Y-m-d-H-i-s") .'.zip';
$save = 'pathtobkfolder/'.$db_name;

$this->load->helper('file');
write_file($save, $backup); 


$this->load->helper('download');
force_download($db_name, $backup);

Solution 2

doing that using php will only work for very small databases. You will very fast run into memory limits - if you increase that other performance problems.

What works best is to create a dump using mysqldump:

header('Content-type: application/force-download');
header('Content-Disposition: attachment; filename="dbbackup.sql.gz"');
passthru("mysqldump --user=xx --host=xx --password=xx dbname | gzip");

of course you must have the required permissions to do that.

Solution 3

//load helpers

$this->load->helper('file');
$this->load->helper('download');
$this->load->library('zip');

//load database
$this->load->dbutil();

//create format
$db_format=array('format'=>'zip','filename'=>'backup.sql');

$backup=& $this->dbutil->backup($db_format);

// file name

$dbname='backup-on-'.date('d-m-y H:i').'.zip';
$save='assets/db_backup/'.$dbname;

// write file

write_file($save,$backup);

// and force download
force_download($dbname,$backup);

Solution 4

function backup($fileName='db_backup.zip'){
    // Load the DB utility class
    $this->load->dbutil();

    // Backup your entire database and assign it to a variable
    $backup =& $this->dbutil->backup();

    // Load the file helper and write the file to your server
    $this->load->helper('file');
    write_file(FCPATH.'/downloads/'.$fileName, $backup);

    // Load the download helper and send the file to your desktop
    $this->load->helper('download');
    force_download($fileName, $backup);
}

Easy way to backup database using codeigniter

Solution 5

public function db_backup()
{
    $this->load->helper('url');
    $this->load->helper('file');
    $this->load->helper('download');
    $this->load->library('zip');
    $this->load->dbutil();
    $db_format=array('format'=>'zip','filename'=>'my_db_backup.sql');
    $backup=& $this->dbutil->backup($db_format);
    $dbname='backup-on-'.date('Y-m-d').'.zip';
    $save='assets/db_backup/'.$dbname;
    write_file($save,$backup);
    force_download($dbname,$backup);

}`

View more solutions

Comments

[muttalebm]

Thanks but I am working with a small database over a office network

[muttalebm]

Thanks a lot Saleem. I was looking for the zip format. But my original problem was fixed. I was trying to call the dbutil() method from the view which should have been done from the controller. A very idiotic moment for me :( Anyways thanks for your help buddy :D

[Saleem]

No problem, Good to hear that you have managed to solve the issue. Take care

[Chords]

Why use passthru over exec here?

[Raham]

@CybernatiC the question is not say about base_url();.Your answer is not related to the question asked.

[Freddy Sidauruk]

Thanks a lot, well now i have another issue how can i update database from one to another one,

[Ganesh Aher]

@Saleem I coded same as above, but it works me for test environment which contains data in KB's, when I'm applying same for my actual database it wont take backup that having data in MB's (25+ MB). Is there any memory limit to take a backup ?

[Saleem]

@GaneshAher Please refer to docs at codeigniter.com/user_guide/database/utilities.html and note from docs "Due to the limited execution time and memory available to PHP, backing up very large databases may not be possible. If your database is very large you might need to backup directly from your SQL server via the command line, or have your server admin do it for you if you do not have root privileges."

[prashant kushwah]

my normal database size is 32 mb but with this method my database downloaded 97mb and showing error when uploading form 'localhost/phpmyadmin'

[Saleem]

That's quite much difference. You can try gz or refer to the official user guide codeigniter.com/userguide3/database/…

[cursorrux]

Provide some clarification on your answer