Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2005
    Location
    Northern VA
    Posts
    1,582

    Help Backing up multiple mySQL databases

    I have a VPS account with about 6 accounts that have mySQL dB's that I want to back up. There are about 30 accounts total so I I'd like to selectively back up just those dB's.

    I have some backup space on another server. I've already figured out how to RSync the home directories of the accounts to keep them backed up and it's easy to do in one fell swoop.

    Is there a way to back up the dB's easily by some sort of cron run? I know I can dump and then tar and gz the files but those are each separate steps and I'm not the strongest Unix guy. Add to that I would have to then do that for each dB I'm interested in. I know where these mySQL folders are located as I have root access but it doesn't seem efficient to Rsync those folders on another server (waste of space and bandwidth).

    I'm sure somebody has figured all of this out but I'm honestly a Unix novice and don't have a good idea on how to create scripts that might do all the steps I'd need to do.

    Bottom line: Ideas are welcome
    Rich
    Husband, Father, Retired Marine, Geek

  2. #2
    Join Date
    Aug 2006
    Location
    Milton Keynes, UK
    Posts
    52
    I had the same problem that I only wanted to backup selected databases. I used the following commands in crontab to backup each database to the users webspace. This has the advantages that the user can then download their own dbs easily and also rsync will back it up. I would strongly suggest the you dump the file outside of the web root.

    Code:
     
    0 1 * * * root mysqldump -uroot -pxxxxx database1 > /home/site1/dbdumps/database1.sql
    0 2 * * * root mysqldump -uroot -pxxxxx database2 > /home/site2/dbdumps/database2.sql
    where xxxxx is the root mysql password

    You could put all the commands in one script file with a gzip command after each to save space.

  3. #3
    Join Date
    Feb 2005
    Location
    Northern VA
    Posts
    1,582
    duplu,

    Great information. Can I ask you to expand on the "How to" for my situation.

    I'm not really a host, per se, but just provide service pro bono for some organizations that don't even use their CPanel. With the above I could definitely dump the mySQL dB's to one place.

    Let me ask you a few questions:

    1. How do I set up a script file? I know that is an awfully basic question but my expertise does not lie in server admin.
    2. Let's assume I want to do this:
    a. Dump each dB to a sql file
    b. gzip it
    c. rsync it to an external site

    Q: Could I put that in a script file - 3 commands for each dB?

    I've worked with a couple of cron scripts to set up cron jobs but don't really know how to create one myself.
    Last edited by DevilDog; 09-21-2006 at 07:40 AM.
    Rich
    Husband, Father, Retired Marine, Geek

  4. #4
    Join Date
    Aug 2006
    Location
    Milton Keynes, UK
    Posts
    52
    Create your script file:

    nano -w /root/dbbackups.sh

    Paste in the following text and amend as necessary for your database names and site locations and also the MySQL root password. You can also add your rsync commands at the bottom:

    Code:
     
    #! /bin/sh
    # Database backup script
     
    # Dump Dbs
     
    mysqldump -uroot -pxxxxx database1 > /home/site1/dbdumps/database1.sql
    mysqldump -uroot -pxxxxx database2 > /home/site2/dbdumps/database2.sql
     
    # ZIP dbs
     
    gzip /home/site1/dbdumps/database1.sql
    gzip /home/site1/dbdumps/database2.sql
     
    #rsync command
    Make the file executable:

    chmod 755 /root/dbbackups.sh

    Amend the crontab to run the script at 1am.

    nano -w /etc/crontab

    Add the following line at the bottom:

    Code:
    1 0 * * * root /root/dbbackups.sh 1> /dev/null
    You can test the script from the command line by typing:

    /root/dbbackups.sh

  5. #5
    Join Date
    Feb 2005
    Location
    Northern VA
    Posts
    1,582
    Awesome.

    Thanks duplu
    Rich
    Husband, Father, Retired Marine, Geek

  6. #6
    Join Date
    Jul 2005
    Posts
    529

    Lightbulb

    Hi there guys, sorry to bring back this topic. I need some help on this matter. I have the databases backedup.. however, I'm trying to figure out HOW I can send them to another server? You guys talk about resyncing... how would I do this and get more info about it?

    Thanks,

    Freshfroot.

  7. #7
    Join Date
    May 2006
    Location
    /home/India/Kolkata
    Posts
    314
    try phpmybackup

  8. #8
    Join Date
    Nov 2001
    Location
    Philadelphia, Pa
    Posts
    948
    You could probably setup a second script (or ad it to the first one) to tar it, then scp it.

  9. #9
    Join Date
    Jul 2005
    Posts
    529
    Quote Originally Posted by derek.bodner
    You could probably setup a second script (or ad it to the first one) to tar it, then scp it.
    what would I put in the second script?

  10. #10
    Join Date
    Jun 2006
    Location
    Tucson
    Posts
    23
    Here's one idea. If you are sending the dumps to another box, you should just install mysql and set up replication on the second machine, and only have it sync up once per day. That way if there's a catastrophe you can just bind the old database server's IP address to the recovery box and let it fly. Also you can do offline dumps on the second machine without it impacting production environment.

    Just a different way about doing it. Here's info from mysql:

    http://dev.mysql.com/doc/refman/5.1/en/replication.html

  11. #11
    Join Date
    Feb 2005
    Location
    Northern VA
    Posts
    1,582
    Assume you want to sync a folder on host1 with a folder on host2.

    log into host 1 and from the command line:

    rsync -zaHlvp --safe-links --progress -e /usr/bin/ssh path/to/folderonhost1 username@host2.com:path/to/folderonhost2
    It will prompt you for the password for host2.com (for the username you entered above)

    This will rsync the contents of folderonhost1 (all subdirectories included) with the folderonhost2.

    rsync is nice if you're syncing contents between folders. You can use it to copy individual files too but it's probably easier to just scp if you're doing a few files.

    rsync is nice if you have more than one
    Rich
    Husband, Father, Retired Marine, Geek

  12. #12
    Join Date
    Jul 2005
    Posts
    529
    I assume the code above is only used in SSH?

    I have found a script that allows you to FTP, upload mysql then log out.

    The only issue I'm having, is getting it to work right.

    Code:
    #!/bin/sh
    # Backup description
    fdesc='FORUMNAME'
    # Backup filename
    fname='BACKUPNAME'
    # Destination directory (with trailing slash)
    fdir=/BACKUPDIR
    # Database Settings:
    fdb=DBNAME
    fuser=DBUSERNAME
    fpw=DBPASSWORD
    # FTP address
    ftph=ftp.otherwebhost.org
     
    echo "Backing up $fdesc database..."
    tempname=$fdir$fname-`eval date +%Y-%m-%d`.sql
    mysqldump --opt -quick -u$fuser -p$fpw $fdb > $tempname
    echo "Gzipping $fdesc database..."
    gzip $tempname
    a=`eval date +%d`
    a=`expr $a - 2`
    echo "Deleting old backups... (Error messages are normal)"
    tempname=$fdir$fname-`eval date +%Y-%m-`$a.sql.gz
    rm $tempname
    tempname=$fdir$fname-`eval date +%Y-%m-`0$a.sql.gz
    rm $tempname
     
    # FTP backup
    cd $fdir
    echo "Connecting to FTP, will attempt to upload..."
    ftp <<**
    open $ftph
    put $fname-`eval date +%Y-%m-%d`.sql.gz
    delete $fname-`eval date +%Y-%m-`$a.sql.gz
    delete $fname-`eval date +%Y-%m-`0$a.sql.gz
    bye
    **
    echo "Upload Complete!"
    now I know you need a .netrc file which, should be in /root and chmod 600

    The issue is, I have a back.sql file.. I already have it on my server, but I want to FTP it over to another server. I'm just figuring out how to use this script to do just that.

  13. #13
    Join Date
    Sep 2006
    Posts
    53
    take this way

    what about tar & gzip the directory /var/lib/mysql
    & then post it to http access place
    & wget it in the other server & decompress it there in the same place
    if u interest in this tell me to give u the instructions

  14. #14
    Join Date
    Jul 2005
    Posts
    529
    Quote Originally Posted by gotzaway
    take this way

    what about tar & gzip the directory /var/lib/mysql
    & then post it to http access place
    & wget it in the other server & decompress it there in the same place
    if u interest in this tell me to give u the instructions
    that's the issue.. getting it on the other server... is what I'm trying to do.
    Without SSH and downloading/uploading myself.. so the next best thing I can think of is cron

Posting Permissions

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