Linux [CronJob/Crontab] MYSQL Auto Database Backup

[Migxxx]

Required STUFF:
1-: php5-cli (Not exactly php5 but php cli)
2-: Crontab
3-: A Brain


First step:
make a file named: auto_backup.sh
Inside this file put this:

#!/usr/bin/php5 -q
 
<?php
    $startTime = array_sum(explode(" ",microtime()));
    //Credits: www.dagondesign.com and Migxxx
 
    /*
        @Mysql Configuration.
    */
    $mysql_host = 'localhost';
    $mysql_user = 'root';
    $mysql_password = 'your_mysql_password';
 
 
    /*
        @Backup Destination.
    */
    $backup_dest = '/home/migxxx/db_backups';
 
 
 
    ignore_user_abort(1);
    set_time_limit(0);
    $n = "\x0A"; //newline
    $spacer = str_repeat('-',82);
 
    echo($spacer.$n.'Making BACKUP:'.$n);
 
    if(!is_dir($backup_dest))
        mkdir($backup_dest);
 
    if(!is_dir('/tmp/db_bp_'.$unique = uniqid()))
        mkdir('/tmp/db_bp_'.$unique);
 
    $db_conn = @mysql_connect($mysql_host,$mysql_user,$mysql_password) or die("Coudn't stablish connection with MYSQL.".$n);
    $db_result = mysql_list_dbs($db_conn);
    $db_auth = ' --host="'.$mysql_host.'" --user="'.$mysql_user.'" --password="'.$mysql_password.'"';
 
    while($db_row = mysql_fetch_object($db_result))
    {
        $db_time = array_sum(explode(" ",microtime()));
        $db = $db_row->Database;
 
        echo($n.$db);
 
        exec('/usr/bin/mysqldump '.$db_auth.' --opt '.$db.' 2>&1 >/tmp/db_bp_'.$unique.'/'.$db.'.sql');
        exec('nice -n 19 bzip2 /tmp/db_bp_'.$unique.'/$db.sql 2>&1');
 
        if(is_file('/tmp/db_bp_'.$unique.'/'.$db.'.sql'))
            echo(' --DONE ('.round(( array_sum(explode(' ',microtime())) - $db_time),4).' sec)');
    }
 
    mysql_free_result($db_result);
    mysql_close($db_conn);
 
    exec('cd /tmp/db_bp_'.$unique.'/ ; nice -n 19 tar cf '.$backup_dest.'/db_backup'.date('Y-m-d-h:i:s_M_D').'.tar * 2>&1');
 
    echo($n.$n.'Backup Done in: '.round(( array_sum(explode(' ',microtime())) - $startTime),4).' sec.'.$n.$spacer.$n.'Removing TMP files/folders:'.$n);
 
    if($dir = @opendir('/tmp/db_bp_'.$unique.'/'))
        while(($file = readdir($dir)) !== false)
            if(!is_dir($file))
            {
                echo($n.$file);
                if(unlink('/tmp/db_bp_'.$unique.'/'.$file))
                    echo(' --DONE');
            }
 
 
    closedir($dir);
    echo($n.'/tmp/db_bp_'.$unique.'/');

    if(rmdir('/tmp/db_bp_'.$unique.'/'))
        echo(' --DONE');
 
    echo($n.$n.'Files and folder removed, Script END in a total of: '.round(( array_sum(explode(' ',microtime())) - $startTime),4).' sec.'.$n.$n.'Date: '.date("l d F Y, h:i:s A").$n.$spacer.$n);
?>

Note: This file needs executable permission, for more information read this tutorial: http://otland.net/f138/permissions-chmods-71960/

Second step:
Execute the following command:

crontab -e

Your computer should have open an empty file with a command text editor named nano
There you have to write:

1 1 * * * /home/migxxx/auto_backup.sh

Then click F3 to save and F2 to exit and thats all! From now your computer will make a backup everyday.


Also i would like to thank Kornholijo for always answering my n00b questions (Noob for him, hardcore for me xD).


--EDIT

If you get any error (Not about permissions) try changing:

[COLOR=#000000][FONT=Courier New]#!/usr/bin/php5 -q

[/FONT][/COLOR]
for

[COLOR=#000000]#!/usr/bin/php -q

[/COLOR]​

 

[ThaLeeeS]

I installed php5-cli (apt-get install php5-cli), but when I execute "auto_backup.sh" show this error:

-bash: ./auto_backup.sh: /usr/bin/php5: bad interpreter: No such file or directory

Help ?

 

[Kekox]

Why everything on linux has to be hard??!
You were doing good until:

Note: This file needs executable permission, for more information read this tutorial: Permissions - Chmods

Gosh! Can you just say wich command do we have to execute?
^_^.

 

[Migxxx]

Why everything on linux has to be hard??!
You were doing good until:

Note: This file needs executable permission, for more information read this tutorial: Permissions - Chmods

Gosh! Can you just say wich command do we have to execute?
^_^.

sudo chmod a+rwx auto_backup.sh

I installed php5-cli (apt-get install php5-cli), but when I execute "auto_backup.sh" show this error:



Help ?

What distro are you using?

 

[anto]

I installed php5-cli (apt-get install php5-cli), but when I execute "auto_backup.sh" show this error:



Help ?

the reason to that is because /usr/bin/php5 dosent exist.

i could almost bet on /usr/bin/php is the correct path.

or try

which php

it will show where the php binary is

 

[Migxxx]

the reason to that is because /usr/bin/php5 dosent exist.

i could almost bet on /usr/bin/php is the correct path.

gali@debian:~/cron-jobs$ file /usr/bin/php5
/usr/bin/php5: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.8, stripped

anyway.... i'll edit the main post, just in case.

 

[kornholi]

#!/usr/bin/env php -q

 

[Kekox]

I tried:

#!/usr/bin/env php

#!/usr/bin/php5

#!/usr/bin/php

But none of them worked... ._.

Also tried which php and there was nothing returned.

 

[Migxxx]

I tried:

#!/usr/bin/env php

#!/usr/bin/php5

#!/usr/bin/php

But none of them worked... ._.

Also tried which php and there was nothing returned.

What do you mean with "which php"?

Are you sure you have php5-cli installed? and that the user you have set up the cron can access

/usr/bin/*

You may also try checking this:

dpkg -s php5-cli

This gives you plenty of information about the packages (Including the path where it is installed)...


And try,

mail

If the cron was successfully executed, it should have had sent you a mail to the user's account where it is installed.

 

[Kungenn]

Possible to make it backup at a certain time like lets say 08:00

 

[kito2]

00 06 * * * mysqldump -u USER -pSQLPASSWORD DATABASENAME | bzip2$

That will be easier, create a mysqldump at 6 AM every day