Results 1 to 7 of 7

Thread: mySQL Backups

  1. #1
    Join Date
    Sep 2003
    Posts
    6

    mySQL Backups

    I have read about and tested the various backup/recovery methods for mySQL. From my testing, it seems that InnoDB tables are the most transaction safe but are the hardest to backup while the database is up. Has anybody ever used the InnoDB hotbackup scripts in a production environment? How do most people backup their mySQL production environments?

  2. #2
    Join Date
    Jan 2004
    Location
    Cincinnati, OH
    Posts
    30
    I do not use innodb, but as for the way we perform our database backup (myisam) and recovery, we simply use mysqldump to dump all of the databases. We then compress the files and save them. Then if we need them recovered, just unzip them and import them using the "mysql" command.

  3. #3
    Join Date
    May 2004
    Posts
    4
    Little script that I grabbed pieces here and there that I run from crontab.

    I'm using LFTP to do the ftp of the backup to an offsite server for additional backup. If you have /usr/local/bin in your path you don't have to export it in the script.

    #! /bin/bash
    mydate=$(date +%m%d%Y%I%M%p)
    PATH=$PATH:/usr/local/bin
    export PATH

    /usr/local/mysql/bin/mysqldump --flush-logs --opt -u USERNAME -pPASSWORD DBNAME > /dir1/dir2/dir3/backup.sql
    cd /dir1/dir2/dir3/

    if [ -e backup.sql ]
    then
    tar -czvf $mydate-sqlback.tar.gz backup.sql
    fi
    if [ -e $mydate-sqlback.tar.gz ]
    then
    lftp <<EOF
    open FTP SERVER
    user USERNAME PASSWORD
    pwd
    put $mydate-sqlback.tar.gz
    bye
    EOF
    fi
    if [ -e backup.sql ]
    then rm /dir1/dir2/dir3/backup.sql

  4. #4
    Join Date
    Sep 2003
    Posts
    6
    Nobody is doing binary backups of their datafiles?

  5. #5
    Join Date
    May 2004
    Posts
    4
    errr... well no. I use this to backup my db's for hosted websites.

  6. #6
    Join Date
    Jan 2004
    Location
    Cincinnati, OH
    Posts
    30
    When you say "binary backups", I hope you don't mean the binary logs as they just keep track of queries that make updates and/or changes.

    If you are keeping some other form of binary backup, if you could please shed some light into what you are doing and the benefits of doing so, that would be greatly appreciated. Thanks.

  7. #7
    Join Date
    Sep 2003
    Posts
    6
    A binary backup of your data files is the quickest way to get your database back in case of a disaster. Re-creating your database structures and re-loading the data for anything bigger than about a 10G database would just take too long to meet any kind of SLA.

Posting Permissions

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