MySQL backup

Discussion in 'Tips/Tricks/Mods' started by trexen, May 7, 2009.

  1. trexen

    trexen Member

    Hi,

    I need a good MySQL script that logs in to mysql DB and reads out all DB to a txt file, that tha script goes in and make a backup on every DB it finds in that txt files.
    This becase that users/customers delete/add DB during the day

    Maybe someone already have a script like this and like to share?
    i will do some research and get back with more information, feel free to help me =)

    //M:)
     
  2. commentator

    commentator New Member

  3. falko

    falko Super Moderator

  4. choogendyk

    choogendyk New Member

    If you want real MySQL backup, and not to worry anymore, you should take a look at ZRM for MySQL (Zmanda Recovery Manager for MySQL). It is open source and also available with support contracts.

    http://www.zmanda.com/backup-mysql.html
     
  5. Jorem

    Jorem New Member

    Is you only have a few databases you can use the backup mysql function in Webmin. It writes the databases to .sql files or you can tar it.

    I do the backup every hour on the server, en sync it to my home pc every day and weekly with SyncBack.
     
  6. trexen

    trexen Member

    Solution!

    Hi
    found the solution i was asking about earlier =)

    make a mysql.sh that you need to logon to mysql shell:
    /usr/bin/mysql -h localhost -u root -pYourePasswd < list.sh


    And make list.sh and put:
    SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA INTO OUTFILE 'db-list';
    then you can find it under "/var/lib/mysql/db-list"

    My next part is to use the list i got whit all DB in my mysql server to make a individuel backup of all databases.
    plz help =)

    /regards
    Marcus:)
     
  7. commentator

    commentator New Member

    if you use the script I mentioned in my previous reply you can backup every db without doing difficult things
     
  8. trexen

    trexen Member

    just installed now, goint to try it tomorrow. thanks.
    //M
     
  9. madmucho

    madmucho ISPConfig Developer

    I using this script, that is most simply backup script i ever find

    MySQL Backup Script
    # VER. 2.5 - http://sourceforge.net/projects/automysqlbackup/
    just set root password
    DBNAMES="all"
    BACKUPDIR="/opt/mysql_backups"
    MAILADDR="xxx@yy.cz"
    put link into cron.daily and you have daily monthly weekly backup

    backups can be send to remote ftp server.
     
  10. trexen

    trexen Member

    I made this one, works greate but i now need to backup induvidual DB so i dont need to restore all DB if one failed.

    ##### DB Backup #####
    /usr/bin/mysqldump -h localhost -u root -pDBpasswd -c --add-drop-table --add-locks --all --quick --lock-tab
    les --all-databases > /root/database/sqldump-`date +%Y%m%d`.sql

    #### FTP to Backup Server ####
    cd /root/database
    HOST='192.168.250.10'
    USER='ftpuser'
    PASSWD='passwd'
    ftp -n -v $HOST <<END
    ascii
    user $USER $PASSWD
    prompt
    mput *.sql
    bye
    END


    It´s prety clear what this script does =)
    //Marcus :)
     
  11. madmucho

    madmucho ISPConfig Developer

    Try automysqlbackup in my post this will made dump per database, is realy simple to use.
    I using it in my production enviroment.
     

Share This Page