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 Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

  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 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 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