Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2008
    Location
    /root/webhosting
    Posts
    884

    Lightbulb How to make Regular Backup of mySQL DB using crontab

    Hello,
    I have having shared hosting account enabled with crontabs and SSH

    I have a computer technology forum.
    Now what I wanna do is , I need to make regular backup (of 7 days, sunday to sunday) and put that ZIP file in my XYZ.com/dbbackup/ location.

    However it will not overwrite existing file but it will create new file with the date on which it is created. What command I should write in Crontabs ?

    Thank You
    ██ Website Hosting Mumbai | Web Hosting India
    ██ Web Hosting Unleashed
    ██ cPanel-Softaculous Shared Hosting | 99.9% Uptime
    ██ sales[at]linkwayhosting.com | +91-9930-3388-67

  2. #2
    Join Date
    May 2004
    Location
    Toronto, Canada
    Posts
    632
    You can write a simple php script that does this and make a cron job to run it run everynight.

    Search this forum, this is the third similar thread that is asking a similar question.
    Hussain Baig - 1-866-954-6747
    Toronto based VPS - Dedicated Servers - Colocation
    VPS Fusion - Providing scalable and reliable hosting solutions.

  3. #3
    here is a very simple copy command which will copy all your mysql data to a location on drive
    crontab -e //to start cron job

    0 0 * * * cp /var/lib/mysql /backup

    I assume your mysql directory is at following path
    this will copy all databases from /var/lib/mysql to /backup dir daily midnight
    hope this will give you some help

  4. #4
    Join Date
    Jul 2009
    Posts
    58
    I'm presently using this script by WebHostingHero to backup my sites on powermonster's DirectAdmin shared hosting service and it works very well

    http://www.thewebhostinghero.com/art...-released.html

  5. #5
    Join Date
    Mar 2003
    Location
    WebHostingTalk
    Posts
    16,960
    Moved > Technical & Security Issues.
    Specially 4 You
    .
    JoneSolutions.Com ( Jones.Solutions ) is on the net 24/7 providing stable and reliable web hosting solutions and services since 2001

  6. #6
    Join Date
    Oct 2008
    Location
    /root/webhosting
    Posts
    884
    I am Confused.
    You mean to say at particular time Crontab will execute a particular file xyz.php
    And that PHP file will manage backup and all stuff, right ?
    ██ Website Hosting Mumbai | Web Hosting India
    ██ Web Hosting Unleashed
    ██ cPanel-Softaculous Shared Hosting | 99.9% Uptime
    ██ sales[at]linkwayhosting.com | +91-9930-3388-67

  7. #7
    Quote Originally Posted by talkman View Post
    0 0 * * * cp /var/lib/mysql /backup

    this will copy all databases from /var/lib/mysql to /backup dir daily midnight
    no it won't. read that command again, its going to do fsck all.

    really you should use mysqldump, or at least the recursive and preserve owner/permissions flags on the cp command!

    and the op asked for a way *not* to overwrite the backups every day so needs to add something like `date` on the end of the filename, and tar would be better than a simple cp.

    and who said to use php to do this - wtf?!

  8. #8
    Join Date
    Mar 2009
    Location
    Gods Own Country
    Posts
    681
    You mean to say at particular time Crontab will execute a particular file xyz.php
    And that PHP file will manage backup and all stuff, right ?
    Yes, thats how cron works. More information here http://en.wikipedia.org/wiki/Cron

    Better to use mysqldump with --all-databases option for database backup. Still, see "man mysqldump" for more information.

  9. #9
    Join Date
    Oct 2004
    Location
    Ohio
    Posts
    1,641

  10. #10
    Join Date
    Dec 2006
    Location
    London, UK
    Posts
    628

  11. #11
    Join Date
    Apr 2003
    Location
    Burlington, WI
    Posts
    58
    This is the backup script that I use. You can add any amount of databases to it. I have this running every hour so I always have a recent backup.

    Code:
    #!/bin/bash
    
    # Script Function:
    # This bash script backups up the db everyday at 4am (dependent on when you set the cron job to run) with a file name time stamp and tar.gz zips the file. 
    # The db will be saved in /public_html/backups/database_backups/
    # Db backups older than 15 days will be deleted.
    
    #[Changes Directory]
    cd /home/site/backups/
    
    #[Old DB Deletion Script]
    find /home/site/backups/database_backups -name "*.tar.gz" -mtime +30 -exec rm -f {} \;
    
    #[Stamps the file name with a date]
    TIMESTAMP=`date +%m-%d-%y-%H%M`
    
    #[DB Backup Scripts]
    # DB Name
    HOST=localhost
    DBNAME="database"
    USER="username"
    PASSWORD="password"
    DUMP_PATH=/home/site/backups/database_backups/
    mysqldump --opt -c -e -Q -h$HOST -u$USER -p$PASSWORD $DBNAME > $DBNAME.sql
    tar czpf $DUMP_PATH/$DBNAME.$TIMESTAMP.tar.gz $DBNAME.sql
    rm -f $DBNAME.sql
    
    # DB Name
    HOST=localhost
    DBNAME="database"
    USER="username"
    PASSWORD="password"
    DUMP_PATH=/home/site/backups/database_backups/
    mysqldump --opt -c -e -Q -h$HOST -u$USER -p$PASSWORD $DBNAME > $DBNAME.sql
    tar czpf $DUMP_PATH/$DBNAME.$TIMESTAMP.tar.gz $DBNAME.sql
    rm -f $DBNAME.sql
    
    # DB Name
    HOST=localhost
    DBNAME="database"
    USER="username"
    PASSWORD="password"
    DUMP_PATH=/home/site/backups/database_backups/
    mysqldump --opt -c -e -Q -h$HOST -u$USER -p$PASSWORD $DBNAME > $DBNAME.sql
    tar czpf $DUMP_PATH/$DBNAME.$TIMESTAMP.tar.gz $DBNAME.sql
    rm -f $DBNAME.sql
    Asus RT-AC5300 / Motorola Arris SB6190 / MacBook Pro

  12. #12
    Join Date
    Oct 2008
    Location
    /root/webhosting
    Posts
    884
    ██ Website Hosting Mumbai | Web Hosting India
    ██ Web Hosting Unleashed
    ██ cPanel-Softaculous Shared Hosting | 99.9% Uptime
    ██ sales[at]linkwayhosting.com | +91-9930-3388-67

Similar Threads

  1. How do I make a regular site from wordpress
    By Nate77 in forum Web Design and Content
    Replies: 4
    Last Post: 01-21-2008, 11:25 AM
  2. Please help me make a daily backup of my mysql DB
    By mikey1090 in forum Programming Discussion
    Replies: 4
    Last Post: 01-30-2007, 05:09 AM
  3. Cpanel crontab backup help issue
    By ithica in forum Hosting Security and Technology
    Replies: 4
    Last Post: 03-24-2005, 11:28 AM
  4. backup small file with wget/crontab
    By sander815 in forum Hosting Security and Technology
    Replies: 11
    Last Post: 07-12-2004, 07:29 PM
  5. FreeBSD (crontab) Backup Scripts
    By Axcelx in forum Hosting Security and Technology
    Replies: 2
    Last Post: 04-04-2004, 10:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •