Mysql database backup cron job

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Mysql database backup cron job

    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 :


    Code:
     0 0 * * *  /usr/bin/mysqldump -u dbusername dbname -p password > /home/uesrname/dbbackup.sql
    You can change the time as per your requirements .


    Cheers
    Patrick

    Dedicated Servers | Shared Hosting | Reseller Hosting

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

    Code:
    /usr/bin/mysqldump -u dbuser -pdbpass --databases dbname > /home/accountname/dbbackup.sql
    Note that there is no space between -p and dbpass
    Last edited by JonoB; 29-01-2009, 13:22.

    Comment


      #3
      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
      Code:
      #!/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

      Comment


        #4
        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.

        Comment


          #5
          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:

          PHP Code:
          <?
          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!=falsefclose($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 Code:
          <?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.
          Martin
          Chief R & D Officer.

          - -

          Comment


            #6
            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.

            Comment


              #7
              Originally posted by JonoB View Post
              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
              Martin
              Chief R & D Officer.

              - -

              Comment


                #8

                Comment


                  #9
                  Thanks, this topic helped me!

                  Comment


                    #10
                    what is the difference betwen mysql & sql server?

                    Comment


                      #11
                      Originally posted by anurdh65 View Post
                      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.

                      Comment


                        #12
                        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

                        Comment


                          #13
                          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.
                          eUK_Ralf

                          ||

                          Comment


                            #14
                            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 ?
                            eUKhost

                            Comment


                              #15
                              Originally posted by _Chris_ View Post
                              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 ?
                              eUKhost

                              Comment

                              Working...
                              X