Mysql database backup cron job

[eUK-Patrick]

You can easily setup a cron job to generate mysql database backup in your hosting account.


Below given command is an example to generate mysql database backup daily at midnight , you need to set the below given command in cPanel >> cronjobs :

 0 0 * * *  /usr/bin/mysqldump -u dbusername dbname -p password > /home/uesrname/dbbackup.sql

You can change the time as per your requirements .


Cheers

[JonoB]

Not sure if things have changed, but what worked for me is:

/usr/bin/mysqldump -u dbuser -pdbpass --databases dbname > /home/accountname/dbbackup.sql

Note that there is no space between -p and dbpass

[stukelly]

I use a bash script to create a compressed daily backup. The backups are on a weekly loop, so I have seven days of backup available.

dbbackup.sh

#!/bin/bash

suffix=$(date +%w%a)

rm /home/username/dbbackup/$suffix.sql.gz
mysqldump -h localhost -u username -p password databasename | gzip > /home/username/dbbackup/$suffix.sql.gz

[JonoB]

Yeah, Ive actually gone the same route now; its much better.

I have a php script run from cron (5 mins later than bash script) to automatically email the (non sensitive) databases off to a gmail account too.

[eUK-Martin]

PHP Script for users without shell access

Here is a PHP script for those who do not have Shell access, like the one on shared servers:

You will have to first create a script with the name mysqlbackup.h this will have the function to backup MySQL DB:

<?
function mysqlbackup($host,$dbname, $uid, $pwd, $output, $structure_only)  
{  

    //this function creates a text file (or output to a HTTP connection), that when parsed through MYSQL's telnet client, will re-create the entire database  

    //Parameters:  
    //    $host: usually "localhost" but depends on where the MySQL database engine is mounted  
    //    $dbname : The MySQL database name  
    //    $uid : the database's username (not your account's), leave blank if none is required  
    //    $pwd : the database's password  
    //    $output : this is the complete filespec for the output text file, or if you want the result SQL to be sent back to the browser, leave blank.  
    //    $structure_only : set this to true if you want just the schema of the database (not the actual data) to be output.   

    if (strval($output)!="") $fptr=fopen($output,"w"); else $fptr=false;  

    //connect to MySQL database  
    $con=mysql_connect("localhost",$uid, $pwd);  
    $db=mysql_select_db($dbname,$con);  

    //open back-up file ( or no file for browser output)  

    //set up database  
    out($fptr, "create database $dbname;\n\n");  

    //enumerate tables  
    $res=mysql_list_tables($dbname);  
    $nt=mysql_num_rows($res);  

    for ($a=0;$a<$nt;$a++)  
    {  
        $row=mysql_fetch_row($res);  
        $tablename=$row[0];  

        //start building the table creation query  
        $sql="create table $tablename\n(\n";  

        $res2=mysql_query("select * from $tablename",$con);  
        $nf=mysql_num_fields($res2);  
        $nr=mysql_num_rows($res2);  

        $fl="";  

        //parse the field info first  
        for ($b=0;$b<$nf;$b++)  
        {  
            $fn=mysql_field_name($res2,$b);  
            $ft=mysql_fieldtype($res2,$b);  
            $fs=mysql_field_len($res2,$b);  
            $ff=mysql_field_flags($res2,$b);  

            $sql.="    $fn ";  

            $is_numeric=false;  
            switch(strtolower($ft))  
            {  
                case "int":  
                    $sql.="int";  
                    $is_numeric=true;  
                    break;  

                case "blob":  
                    $sql.="text";  
                    $is_numeric=false;  
                    break;  

                case "real":  
                    $sql.="real";  
                    $is_numeric=true;  
                    break;  

                case "string":  
                    $sql.="char($fs)";  
                    $is_numeric=false;  
                    break;  

                case "unknown":  
                    switch(intval($fs))  
                    {  
                        case 4:    //little weakness here...there is no way (thru the PHP/MySQL interface) to tell the difference between a tinyint and a year field type  
                            $sql.="tinyint";  
                            $is_numeric=true;  
                            break;  

                        default:    //we could get a little more optimzation here! (i.e. check for medium ints, etc.)  
                            $sql.="int";  
                            $is_numeric=true;  
                            break;  
                    }  
                    break;  

                case "timestamp":  
                    $sql.="timestamp";  
                    $is_numeric=true;  
                    break;  

                case "date":  
                    $sql.="date";  
                    $is_numeric=false;  
                    break;  

                case "datetime":  
                    $sql.="datetime";  
                    $is_numeric=false;  
                    break;  

                case "time":  
                    $sql.="time";  
                    $is_numeric=false;  
                    break;  

                default: //future support for field types that are not recognized (hopefully this will work without need for future modification)  
                    $sql.=$ft;  
                    $is_numeric=true; //I'm assuming new field types will follow SQL numeric syntax..this is where this support will breakdown  
                    break;  
            }  

            //VERY, VERY IMPORTANT!!! Don't forget to append the flags onto the end of the field creator  

            if (strpos($ff,"unsigned")!=false)  
            {  
                //timestamps are a little screwy so we test for them  
                if ($ft!="timestamp") $sql.=" unsigned";  
            }  

            if (strpos($ff,"zerofill")!=false)  
            {  
                //timestamps are a little screwy so we test for them  
                if ($ft!="timestamp") $sql.=" zerofill";  
            }  

            if (strpos($ff,"auto_increment")!=false) $sql.=" auto_increment";  
            if (strpos($ff,"not_null")!=false) $sql.=" not null";  
            if (strpos($ff,"primary_key")!=false) $sql.=" primary key";  

            //End of field flags  

            if ($b<$nf-1)  
            {  
                $sql.=",\n";  
                $fl.=$fn.", ";  
            }  
            else  
            {  
                $sql.="\n);\n\n";  
                $fl.=$fn;  
            }  

            //we need some of the info generated in this loop later in the algorythm...save what we need to arrays  
            $fna[$b]=$fn;  
            $ina[$b]=$is_numeric;  
              
        }  

        out($fptr,$sql);  

        if ($structure_only!=true)  
        {  
            //parse out the table's data and generate the SQL INSERT statements in order to replicate the data itself...  
            for ($c=0;$c<$nr;$c++)  
            {  
                $sql="insert into $tablename ($fl) values (";  

                $row=mysql_fetch_row($res2);  

                for ($d=0;$d<$nf;$d++)  
                {  
                    $data=strval($row[$d]);  
                  
                    if ($ina[$d]==true)  
                        $sql.= intval($data);  
                    else  
                        $sql.="\"".mysql_escape_string($data)."\"";  

                    if ($d<($nf-1)) $sql.=", ";  
      
                }  

                $sql.=");\n";  

                out($fptr,$sql);  

            }  

            out($fptr,"\n\n");  

        }  

        mysql_free_result($res2);      

    }  
      
    if ($fptr!=false) fclose($fptr);  
    return 0;  

}  

function out($fptr,$s)  
{  
    if ($fptr==false) echo("$s"); else fputs($fptr,$s);  
}
?>

Now the actual script that will call the function and which has to be mentioned in your cron:

<?php  
    include("mysqlbackup.h");  
    mysqlbackup("localhost","yerdatabase","yerusername","yerpassword","/home/sites/site90/web/backup/sqldata.txt", false);  
?>

Though I have used these script when I use to work on Linux Servers but I am have not created it, one of the friends did. This scripts has all the required information that will be needed to be edited for the scripts to work for your DB eg: username password etc.

Nate: The file in which you will dump your data should have 777 permissions if you are running the script from browser.

[JonoB]

Those on shared hosting can still execute a bash script via cron.

I'm sure mysqldump is also far more efficient than iterating through all tables and fields.

[eUK-Martin]

Those on shared hosting can still execute a bash script via cron.

Yes, but not all hosting companies like eUKhost allow executing mysqldump command to their users and you never have a privilege to backup your SQL database through browser with a cron job

[JonoB]

[a1phanumeric]

Thanks, this topic helped me!

[anurdh65]

what is the difference betwen mysql & sql server?

[Merrick]

what is the difference betwen mysql & sql server?

The basic difference between MySQL and SQL Server is the price tag. MySQL is free under the GNU Public License while we need to pay for MSSQL licenses. Both are RDBMS of different companies and and they have their own advantages and disadvantages.

[nero11]

sorry for my question but really i need help in this , i used this line

/usr/bin/mysqldump -u dbuser -pdbpass --databases dbname > /home/accountname/dbbackup.sql

i change this to my config (dbuser -pdbpass dbname /home/accountname)
but now the cpanel send me email with this massage

(/bin/sh: 3!s: No such file or directory)

do i have to change this also (/usr/bin/mysqldump -u)

thanks

[eUK-Ralf]

Hi,

/usr/bin/mysqldump -u dbuser -pdbpass --databases dbname > /home/accountname/dbbackup.sql

Above mentioned syntax is fine to generate mysql database backup with the help of cron job.
I suggest you to PM me the exact syntax you are using in the cron job and primary domain hosted with us.
So, I will check the issue and update you the same.

[_Chris_]

This is an excellent thread - just to clarify - is there a way to automatically backup the mysql database and have it sent to you by email (in a compressed format) on a daily basis ? Is that right please ?

[_Chris_]

Is there a way to automatically backup the mysql database and have it sent to you by email (in a compressed format) on a daily basis ?

Can anyone help on this at all please ?