Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2005
    Posts
    523

    backing up database

    I was just wondering what methods admins here use to backup their website?

    I normally used phpmyadmin, but for larger databases it does not help as much.

    What methods do you use to copy your database, and save it to your server and/or PC machine at home/office?

  2. #2
    Join Date
    Jul 2009
    Posts
    70
    Hello,

    I think in case of linux server with cpanel the cpanel backup option would suffice as it would take the backup of the domain as well as the corresponding mysql databases. If you want mysql backup option it would be better if you make your own backup scipt for mysql alone and take backup either remote or locally(provided that shell access is available)

    I prefer the second method as it is more fast, saves a lot of the system resources and time.

    cheers

  3. #3
    Join Date
    Mar 2009
    Location
    Israel
    Posts
    1,204
    if you have SSH access for your hosting account , you can use the mysqldump command.
    beast5.com - Managed Hosting Solutions 2004 - 2016

  4. #4
    Join Date
    Oct 2002
    Location
    State of Disbelief
    Posts
    22,947
    I use this for some sites. Creates daily backups, rotates weekly, can email or FTP an off-site copy. Enjoy!

    Code:
    #!/bin/sh
    
    # This script will backup one or more mySQL databases
    # and then optionally email them and/or FTP them
    
    # This script will create a different backup file for each database by day of the week
    # i.e. 1-dbname1.sql.gz for database=dbname1 on Monday (day=1)
    # This is a trick so that you never have more than 7 days worth of backups on your FTP server.
    # as the weeks rotate, the files from the same day of the prev week are overwritten.
    # name it backup.sh (or whatever), and set up a cron (daily):
    # /bin/sh /path/to/script/backup.sh > /dev/null
    ############################################################
    #===> site-specific variables - customize for your site
    
    # List all of the MySQL databases that you want to backup in here, 
    # each seperated by a space
    databases="database_name"
    
    # Directory where you want the backup files to be placed
    backupdir=/home/user/backup
    
    # MySQL dump command, use the full path name here
    mysqldumpcmd=/usr/bin/mysqldump
    
    # MySQL Username and password use root mysql info if many dbs are being backed up
    userpassword=" --user=mysql_username --password=*****"
    
    # MySQL dump options
    dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables"
    
    # Unix Commands
    gzip=/bin/gzip
    uuencode=/usr/bin/uuencode
    mail=/bin/mail
    
    # Send Backup?  Would you like the backup emailed to you?
    # Set to "y" if you do
    sendbackup="n"
    subject="My Backup"
    mailto="[email protected]"
    
    #===> site-specific variables for FTP
    ftpbackup="y"
    ftpserver="ip.address.goes.here"
    ftpuser="ftp_username"
    ftppasswd="*****"
    # If you are keeping the backups in a subdir to your FTP root
    ftpdir="/"
    
    #===> END site-specific variables - customize for your site
    ############################################################
    
    # Get the Day of the Week (0-6)
    # This allows to save one backup for each day of the week
    # Just alter the date command if you want to use a timestamp
    DOW=`date +%w`
    
    # Create our backup directory if not already there
    mkdir -p ${backupdir}
    if [ ! -d ${backupdir} ] 
    then
       echo "Not a directory: ${backupdir}"
       exit 1
    fi
    
    # Dump all of our databases
    echo "Dumping MySQL Databases"
    for database in $databases
    do
       $mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${DOW}-${database}.sql
    done
    
    # Compress all of our backup files
    echo "Compressing Dump Files"
    for database in $databases
    do
       rm -f ${backupdir}/${DOW}-${database}.sql.gz
       $gzip ${backupdir}/${DOW}-${database}.sql
    done
    
    # Send the backups via email
    if [ $sendbackup = "y" ]
    then
       for database in $databases
       do
          $uuencode ${backupdir}/${DOW}-${database}.sql.gz > ${backupdir}/${database}.sql.gz.uu
          $mail -s "$subject : $database" $mailto < ${backupdir}/${DOW}-${database}.sql.gz.uu
       done
    fi
    
    # FTP it to the off-site server
    echo "FTP file to $ftpserver FTP server"
    if [ $ftpbackup = "y" ]
    then
       for database in $databases
       do
          echo "==> ${backupdir}/${DOW}-${database}.sql.gz"
    ftp -n $ftpserver <<EOF
    user $ftpuser $ftppasswd 
    bin
    prompt
    cd $ftpdir
    lcd ${backupdir}
    put ${DOW}-${database}.sql.gz
    quit
    EOF
       done
    fi
    
    # And we're done
    ls -l ${backupdir}
    echo "Dump Complete!"
    exit
    Having problems, or maybe questions about WHT? Head over to the help desk!

  5. #5
    Yes, Use mysqldump

  6. #6
    If you can't run that personally you always can relay on your web hosting provider and ask for the help

  7. #7
    Normally I'd do a manual mysqldump over SSH. But bear's script looks interesting, maybe I'll give it a try, thanks for sharing!

  8. #8
    yes, mysqldump through ssh

  9. #9
    Join Date
    Nov 2003
    Location
    USA
    Posts
    784
    i use a software
    WHMCS Services - sales (at) whmcsservices.com
    WHMCS Development | WHMCS Addons / Modules

  10. #10
    in linux

    it would be better to write a script for managing mysql backup

    suppose you have a database 'shopcart' and you want to backup this to /user1/db_backup

    for that create a file db_backup.sh and add the following content in to it

    dt=$(date +%T-%d_%m_%Y)
    backup_path="/user1/db_backup";
    backup_name="$backup_path/shopcart_$dt";
    mysqldump shopcart>$backup_name.sql

    save the file and run

    #sh db_backup.sh
    it will create a db backup of the databae shopcart in the directory /usr1/db_backup

    the format of the backup file is something like 'shopcart_12:53:04-20_10_2009.sql'


    if you need automated daily backup you can do it using cron


    hope it helps you
    Rasin
    Ezeelogin - The ultimate multiple server administration software.
    * Parallel shell * rm -rf protection * SSH logging * automated password changes * encrypted storage *
    AdMod.com - Delivering innovative web hosting solutions

Similar Threads

  1. Backing up database data
    By pergesu in forum Hosting Security and Technology
    Replies: 2
    Last Post: 10-25-2005, 02:39 PM
  2. Backing up a MySQL Database
    By GameStudioD in forum Programming Discussion
    Replies: 9
    Last Post: 09-08-2004, 04:51 PM
  3. backing up a database woes
    By aqi32 in forum Web Hosting Lounge
    Replies: 7
    Last Post: 04-06-2004, 05:15 PM
  4. Backing Up Database Problem
    By NetGeek in forum Hosting Security and Technology
    Replies: 9
    Last Post: 07-12-2002, 02:43 PM

Posting Permissions

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