Backup MySQL database with CodeIgniter

问题:

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.

回答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);

回答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.

回答3:

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

回答4:

If you are lucky enough to have one of the exec(), shell_exec(), system() or passthru() enabled on your server. Maybe you would want to use the following:

public function db_backup()
{
    $DBUSER=$this->db->username;
    $DBPASSWD=$this->db->password;
    $DATABASE=$this->db->database;

    $filename = $DATABASE . "-" . date("Y-m-d_H-i-s") . ".sql.gz";
    $mime = "application/x-gzip";

    header( "Content-Type: " . $mime );
    header( 'Content-Disposition: attachment; filename="' . $filename . '"' );

    // $cmd = "mysqldump -u $DBUSER --password=$DBPASSWD $DATABASE | gzip --best";   
    $cmd = "mysqldump -u $DBUSER --password=$DBPASSWD --no-create-info --complete-insert $DATABASE | gzip --best";

    passthru( $cmd );

    exit(0);
}

回答5:

Try this one...it has been tested...if you are going to use mysqli then it will works fine...you may put your code in your controller or model but i suggest to keep this one in your_model & call this function from your_controller...

public function db_backup()
{
       $this->load->dbutil();   
       $backup =& $this->dbutil->backup();  
       $this->load->helper('file');
       write_file('your_file_path/your_DB.zip', $backup); 
}

回答6:

I am extremely new to CodeIgniter but I succeeded to do this backup. Try this, it will work successfully and is very easy to implement. Write the code in your controller and call the function from your view page which use for backup. Get set, go and you are done.

function dbbackup()
{
    $this->load->dbutil();   
    $backup =& $this->dbutil->backup();  
    $this->load->helper('file');
    write_file('<?php echo base_url();?>/downloads', $backup);
    $this->load->helper('download');
    force_download('mybackup.gz', $backup);
}

For your full application backup, do the same procedure with the following code:

function backup()
{
    $this->load->helper('download');
    $this->load->library('zip'); 
    $time = time(); 
    $this->zip->read_dir('D:xampp/htdocs/wms/');
    $this->zip->download('my_backup.'.$time.'.zip');
}

Here you can use any path of your choice.

回答7:

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

}`

回答8:

These lines have been grabbed from codeigniters documentation:

Important: In order to initialize the Utility class, your database driver must already be running, since the utilities class relies on it.

Please check if your database class is loaded or not when you call this function. Or you can put this line before loading the dbutil class $this->load->database();

回答9:

Try this!

$username = "root";
$password = "root";
$hostname = "localhost";
$dbname   = "raas";

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename='.basename($dbname . "_" .date("Y-m-d_H-i-s").".sql"));

$command = "C:\AppServ\MySQL\bin\mysqldump --add-drop-table --host=$hostname   --user=$username --password=$password ".$dbname;

system($command);

回答10:

The problem is that you are trying to backup the database very early during bootstrapping.

I ran into the same problem when I tried to hack CodeIgniter into backing up my database using:

$prefs = array(
            'ignore'=> array('codes_cdt','codes_cpt','codes_icd10_dx_order','codes_icd10_pcs_order','pharma'),
            'format'=>'gzip','filename','add_drop'=> TRUE,'add_insert'=>TRUE,'newline'=> "\n");

$filename = APPPATH.'\\backups\\' .'backup-' . date('d-m-Y') . ' .gz';
if(!file_exists($filename)){
    get_instance()->load->dbutil();
    file_put_contents( $filename, $this->dbutil->backup($prefs));
}

at the bottom of my config.php file.

Move this to a model an allow it to autoload, and you will be fine.

回答11:

 <?

// Try this one, this works FOR both codeigniter and core PHP

            public function Export_Database()
                {
                    date_default_timezone_set('GMT');
                   // Load the file helper in codeigniter
                    $this->load->helper('file');


            $con = mysqli_connect("localhost","username","password","databasename");

            $tables = array();
            $query = mysqli_query($con, 'SHOW TABLES');
            while($row = mysqli_fetch_row($query)){
                 $tables[] = $row[0];
            }

            $result = 'SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";';
            $result .= 'SET time_zone = "+00:00";';

            foreach($tables as $table){
            $query = mysqli_query($con, 'SELECT * FROM `'.$table.'`');
            $num_fields = mysqli_num_fields($query);

            $result .= 'DROP TABLE IF EXISTS '.$table.';';
            $row2 = mysqli_fetch_row(mysqli_query($con, 'SHOW CREATE TABLE `'.$table.'`'));
            $result .= "\n\n".$row2[1].";\n\n";

            for ($i = 0; $i < $num_fields; $i++) {
            while($row = mysqli_fetch_row($query)){
               $result .= 'INSERT INTO `'.$table.'` VALUES(';
                 for($j=0; $j<$num_fields; $j++){
                   $row[$j] = addslashes($row[$j]);
                   $row[$j] = str_replace("\n","\\n",$row[$j]);
                if(isset($row[$j])){
                       $result .= '"'.$row[$j].'"' ; 
                    }else{ 
                        $result .= '""';
                    }
                    if($j<($num_fields-1)){ 
                        $result .= ',';
                    }
                }
                $result .= ");\n";
            }
            }
            $result .="\n\n";
            }

            //Create Folder
            $folder = 'database/';
            if (!is_dir($folder))
            mkdir($folder, 0777, true);
            chmod($folder, 0777);

            $date = date('m-d-Y'); 
            $filename = $folder."db_filename_".$date; 

            $handle = fopen($filename.'.sql','w+');
            fwrite($handle,$result);
            fclose($handle);
            redirect('Dashboard');           


                } // end Export_Database function
            ?>

回答12:

// to intialize the path split the real path by dot .
        public function init_path($string){
            $array_path =  explode('.', $string);
            $realpath  = ''; 
            foreach ($array_path as $p)
            {

                $realpath .= $p;
                $realpath .= '/';
            }
            return $realpath;

        }

        // backup database function         
        public function archive_database($host = '',$user ='',$pass ='',$name ='', $path = '' , $download_allow = false ,       $tables=false, $backup_name=false){ 
            $CI = &get_instance();
            $CI->load->database();
            if($path != '')
            {
                $path = realpath($this->init_path($path));

            }else{
                if (!is_dir('archives/'))
            mkdir('archives/', 0777);
                $path = realpath($this->init_path('archives'));
            }
            if($host == '')
            {
                $host = $CI->db->hostname;

            }
            if($user == '')
            {
                $user = $CI->db->username;

            }
            if($pass == '')
            {
                $pass = $CI->db->password;

            }
            if($name == '')
            {
                $name = $CI->db->database;

            }

            set_time_limit(3000); 
            $mysqli = new mysqli($host,$user,$pass,$name); 
            $mysqli->select_db($name);
            $mysqli->query("SET NAMES 'utf8'");
            $queryTables = $mysqli->query('SHOW TABLES');
            while($row = $queryTables->fetch_row()) { $target_tables[] = $row[0]; } 
            if($tables !== false)
                { $target_tables = array_intersect( $target_tables, $tables); } 
            $content = "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\r\nSET time_zone = \"+00:00\";\r\n\r\n\r\n/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\r\n/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\r\n/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\r\n/*!40101 SET NAMES utf8 */;\r\n--\r\n-- Database: `".$name."`\r\n--\r\n\r\n\r\n";
            foreach($target_tables as $table){
                if (empty($table)){ continue; } 
                $result = $mysqli->query('SELECT * FROM `'.$table.'`');
                $fields_amount=$result->field_count; 
                $rows_num=$mysqli->affected_rows; 
                $res = $mysqli->query('SHOW CREATE TABLE '.$table);
                $TableMLine=$res->fetch_row(); 
                $content .= "\n\n".$TableMLine[1].";\n\n";
                for ($i = 0, $st_counter = 0; $i < $fields_amount;   $i++, $st_counter=0) {
                    while($row = $result->fetch_row())  { //when started (and every after 100 command cycle):
                        if ($st_counter%100 == 0 || $st_counter == 0 )  {$content .= "\nINSERT INTO ".$table." VALUES";}
                            $content .= "\n(";    for($j=0; $j<$fields_amount; $j++){ $row[$j] = str_replace("\n","\\n", addslashes($row[$j]) ); if (isset($row[$j])){$content .= '"'.$row[$j].'"' ;}  else{$content .= '""';}     if ($j<($fields_amount-1)){$content.= ',';}   }        $content .=")";
                        //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
                        if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num) {$content .= ";";} else {$content .= ",";} $st_counter=$st_counter+1;
                    }
                } $content .="\n\n\n";
            }
            $content .= "\r\n\r\n/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\r\n/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;\r\n/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;";
            $backup_name = $backup_name ? $backup_name : $name."___(".date('H-i-s')."_".date('d-m-Y').")__rand".rand(1,11111111).".sql";

            $fileLocation = $path .'\\'. $backup_name;
            $file = fopen($fileLocation,"w");
            fwrite($file,$content);
            fclose($file);


            if($download_allow){
            ob_get_clean(); header('Content-Type: application/octet-stream');   header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\"".$backup_name."\"");
            }

            echo $content; exit;
        }

回答13:

 public function backup(){
    $this->load->dbutil();
    $config = array(     
        'format'      => 'zip',             
        'filename'    => 'insert-file-name.sql'
    );

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

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

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

}

回答14:

No need to add base_url() at path