Automatic-scheduled MySQL backup on Windows

Discussion in 'Software' started by HTF, Feb 7, 2011.

  1. HTF

    HTF Byte Poster

    181
    0
    14
    Hello,

    I would like to ask about some advice.

    I'm looking for the best solution to backup MySQL databases and website files on a Windows machine and then send it to a remote site - daily.

    Is there a some script or maybe some software?

    I'm using Windows 2008.

    Regards
     
    Certifications: A+
  2. LukeP

    LukeP Gigabyte Poster

    1,194
    41
    90
    Schedule backups using MySQL Administrator (or the recent MySQL Workbench) and schedule PowerShell script in Task Scheduler to upload/send it to where you want.

    You could use Windows Backup to back up the files.

    Let me know if you need help writing a PowerShell script.
     
    WIP: Uhmm... not sure
  3. HTF

    HTF Byte Poster

    181
    0
    14
    Hi,

    Thanks for reply. Is it actually possible to schedule backups with MySQL workbench as I can't find this option? I know it's available in MySQL Administrator but what about MySQL workbench.

    I would like to upload backups to Amazon S3 storage. Is there any free tool to do so or maybe some directory synchronization option?
     
    Last edited: Feb 8, 2011
    Certifications: A+
  4. LukeP

    LukeP Gigabyte Poster

    1,194
    41
    90
    To be honest with you I'm not sure if this option is still there. I've always thought that MySQL Administrator was better. I will check it out when I'm back at home in the afternoon.

    MySQL Administrator is actually able to backup remote databases so that could be helpful.
    Not sure how uploading content to Amazon cloud works but after looking on the net it seems it's doable with PowerShell.

    By the way how big is the total data? I think with Amazon S3 will timeout the upload if longer than 20 minutes.
     
    WIP: Uhmm... not sure
  5. HTF

    HTF Byte Poster

    181
    0
    14
    I think there is no option for scheduled backups in 'MySQL workbench', anyway I set 'MySQL Administrator' to back up databases daily (I had to give admin rights to the user as it didn't work when user was in Backup operators group only).

    The problem is that there is no enough space to back up website directory and then upload to S3 so I was thinking to synchronize it.

    Do you think it will be possible if I map FTP directory to Amazon S3 and then set synchronization it.

    It's around 10GB of data.

    Regards
     
    Certifications: A+
  6. Consultant

    Consultant Bit Poster

    44
    11
    25
    I guess it depends on if you want "point-in-time" backups (i.e. the ability to rollback to a particular date/time 3 weeks ago) or if you just want the latest snapshot so you can recover quickly to the most recent state, when things go pear-shaped.

    You don't mention if you care about the location of the remote site, but MySQL has some great replication tools, and if you're not looking for incremental backups, then mysqldump would be the key (mysqldump performs a complete dump of the db).

    I'd say your immediate options would be either

    1. Replicate out to one (or more) MySQL slaves, and potentially using one of those to run mysqldump on thereby avoiding table lock issues. I have a web service running mysql backend and I run a slave of the DB on my desktop PC, since it does nothing else there is no overhead (other than disk space) on my PC.

    2. Perform regular mysqldump to somewhere then rsync (or windows equivalent) to another remote machine.

    Just as a separate note, all of the old MySQL Administrator, plus the functionality from some of the other tools can all be found in MySQL Workbench. You'll find the backup option in the "Manage Import/Export" in the bottom right hand corner under the "Server Administration" part of the tool.

    Hope this helps.

    Cheers

    Tony
     
    Certifications: ITIL v3 Foundation, MySQL OCA
    WIP: OCP MySQL DBA

Share This Page

Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.