Auto Backup MySQL database with CodeIgniter

Question

I have created a backup option on Codeigniter project. Now I want to the backup database automatically each day with a fixed time.

Here is my backup code:

function dbbackup(){
    $this->load->dbutil();
    $prefs = array(     
        'format'      => 'zip',             
        'filename'    => 'my_db_backup.sql'
        );

    $backup =& $this->dbutil->backup($prefs); 
    $db_name = "backup_on_" . date("d_m_Y_h_i_s_a") . ".zip";
    $save = 'uploads/dbbackup/'.$db_file_name;
    $this->load->helper('file');
    write_file($save, $backup); 
    $this->load->helper('download');
    force_download($db_name, $backup);
}

Is there any specific query to backup automatically?

Answer 1

Why don't you just use crontab and mysqldump to take backup at desired time, there is actually no need to use dbutil class, mysqldump will be faster and reliable compare to dbutil class.

If you read source code of mysqli_utility, _backup method, you will understand how it affects performance.

Say example to take backup everyday at 14:30

crontab -e

Make a entry like below

30 14 * * * /usr/bin/mysqldump --user=USER --password=PASSWORD --databases DBNAME1 DBNAME2 | gzip > /home/someuser/backup.$(date +"%d_%m_%Y_%H_%M_%S").sql.gz

Save crontab