Backing up MySQL from the C:\ProgramData Folder

Discussion in 'backup, imaging & disk mgmt' started by HAN, Jun 26, 2014.

Thread Status:
Not open for further replies.
  1. HAN

    HAN Registered Member

    Joined:
    Feb 24, 2005
    Posts:
    2,080
    Location:
    USA
    I have been asked to help a friend set up some type backup routine for her new small business. The previous owner was using only Carbonite (personal version I think). I stopped by yesterday and after a quick look, I discovered the store software uses MySQL. I also learned Carbonite cannot back any files in the C:\ProgramData folder (Windows 7). So, this means there is no backup of any of the MySQL data that lives there.

    In the interim, in order to have something/anything, I plan to image the hard drive using Clonezilla. But she needs something easier that works with little to no user input.

    Are there any cloud backup programs that can deal with a SQL database and make reliable backups from the ProgramData folder?
     
  2. TheRollbackFrog

    TheRollbackFrog Registered Member

    Joined:
    Mar 1, 2011
    Posts:
    3,041
    Location:
    The Pond - USA
    Han, Bvckup2 is an excellent set-and-forget file replicator that uses VSS (Microsoft's VOLUME SHADOW SERVICE) to copy files that are open at the time of backup. Most databases respect the VSS WARNING to "writers" and put the database into a known consistent state before the backup occurs... I said most, but not all.

    I don't know whether MySQL respects the Microsoft VSS warning or not. If it does, the database can be replicated by Bvckup2 very easily to any sort of internal/external storage element, and if that element is replicated in the CLOUD (DropBox, for example) then you should be fine. The Bvckup2 replication process does what's called a DELTA COPY... basically replicating only the portions of the file that have changed since the last replication. It's definitely worth a try...
     
  3. Peter2150

    Peter2150 Global Moderator

    Joined:
    Sep 20, 2003
    Posts:
    17,039
    Han

    2 major points.

    1. I use Crashplan to back up data. I can't check now, but I believe I do backup data from Programdata. I won't be able to check if I do until saturday. But it does backup open files, you can set it to check frequently, and you have it backup both to the cloud and another drive at the same time. Also it retains multiple versions, and you can set it to never delete. Not that expensive either.

    2. I can speak as a small business owner. I have clients, employees, accounting and I keep no paper. I have no choice, but to understand my clients, how to deal with my employees, my accounting and how to protect my data. To say I don't have time, or the inclination to deal with anyone of them would be fatal to my business. If her data is vital to the business, she has no choice but to learn how to protect it. To do otherwise will be a fatal mistake.

    Pete

    PS. I will get back to you on Crashplan this weekend.
     
  4. TheRollbackFrog

    TheRollbackFrog Registered Member

    Joined:
    Mar 1, 2011
    Posts:
    3,041
    Location:
    The Pond - USA
    Han, a quick look at the net shows that MySQL is not very Microsoft VSS friendly. One of the only ways to insure the consistency of a MySQL database is to stop the server prior to the Microsoft VSS system lock then restart it. This is pretty difficult if you want set-and-forget backups to occur in the background.

    Some folks have used other system/file VSS backup tools that have the capability of running a pre backup/lock command followed by the backup with lock then running a post backup/unlock command... this guarantees the consistency of the database being backup up. I believe BVCKUP2 has this pre/post command capability. The pre command that would be run would be a FLUSH of that database, insuring the on-disk consistency of all data involved. Once the now consistent database has been backed up, all should be normal once again.

    These types of problems lie in the database servers themselves. Most professional database servers that are designed to run under Windows are designed to ask Windows for a WARNING when it intends to LOCK the file system for backup purposes. It takes that warning and flushes its memory-based caches to disk, then tells Windows it's OK to put the LOCK on, which Windows will do as soon as it hears back from all who have asked for this service. This demand/response warning system works very well with databases designed primarily for use under Windows. I believe MySQL was primarily designed, originally, for Linux then ported over to Windows... that's why I think this mechanism doesn't exist in MySQL. Either that or MySQL evolved way before the Microsoft VSS mechanism became mainstream, which also may have bee the case.
     
    Last edited: Jun 27, 2014
  5. HAN

    HAN Registered Member

    Joined:
    Feb 24, 2005
    Posts:
    2,080
    Location:
    USA
    Thanks for the replies!

    I had wondered if some programs could place a good copy of the database into another location other than ProgramData. Once the data is somewhere else, Carbonite should be able to grab it from there. I have several years worth of experience with GoodSync. And I have read that it might be able to do something to what you describe for Bvckup2. So great food for thought!

    Of course, if a program can deal directly with the ProgramData folder, that would solve the need for an interim step. So Pete, I am definitely interested to know if Crash Plan can access that folder.

    The RollbackFrog... You have noted a big unknown for me! Can VSS deal with a running MySQL database? Your investigation is very helpful but not the news I hoped for. :( But one step at a time. An offline backup may be the only simple, inexpensive way to do this. I sincerely hope not!
     
  6. TheRollbackFrog

    TheRollbackFrog Registered Member

    Joined:
    Mar 1, 2011
    Posts:
    3,041
    Location:
    The Pond - USA
    Han, just remember... the database, wherever it is, MUST BE CONSISTENT to be able to be backed up properly by whatever mechanism you use (CrashPlan, Bvckup/DropBox, whatever)... it matters not where it lives. It's the database server's responsibility to insure that, not Windows... otherwise your backup will not be useful when you need it.
     
  7. HAN

    HAN Registered Member

    Joined:
    Feb 24, 2005
    Posts:
    2,080
    Location:
    USA
    Yes, the state of the database is why I am thinking that something like a cold image (like Clonezilla uses) may well end up being the best reliable solution. I'm definitely going to have to do more home work before this is finished.
     
  8. TheRollbackFrog

    TheRollbackFrog Registered Member

    Joined:
    Mar 1, 2011
    Posts:
    3,041
    Location:
    The Pond - USA
    Han, in looking around a bit, I discovered that ZMANDA CLOUD BACKUP does support consistent MySQL database backup both incrementally and differentially.
     
  9. HAN

    HAN Registered Member

    Joined:
    Feb 24, 2005
    Posts:
    2,080
    Location:
    USA
    Thanks! I saw it. Too expensive for the size of this business.
     
  10. TheRollbackFrog

    TheRollbackFrog Registered Member

    Joined:
    Mar 1, 2011
    Posts:
    3,041
    Location:
    The Pond - USA
    Another option would be to schedule, periodically, the MYSQLDUMP utility to copy the database to a place in the CLOUD. The copy would be consistent. If your database is huge, this may take some time.
     
  11. HAN

    HAN Registered Member

    Joined:
    Feb 24, 2005
    Posts:
    2,080
    Location:
    USA
    I have no idea how large the database is. Looking into MYSQLDUMP looks like it would definitely give us a good copy. But, it shows many, many parameter options and I have no idea which may or may not be appropriate to her situation. The cold image approach still looks good because in addition to the database back up, we also get hard drive failure protection.

    I have a couple other directions I've asked for opinions and we'll make a decision once all thoughts are in...
     
  12. HAN

    HAN Registered Member

    Joined:
    Feb 24, 2005
    Posts:
    2,080
    Location:
    USA
    Just thought I'd check back in on this.

    If we don't use the MYSQLDUMP command, it appears the only way to back up the PC in question is when Windows is not running. Even using something like Macrium (which uses VSS in Windows) would not guarantee success. (Pete: I spoke to someone using CrashPlan and while it supports backing up the ProgramData folder, it still won't handle a live DB.) I considered using MYSQLDUMP to facilitate cloud backup but in the end, I just don't see the point. Imaging the drive seems easiest to me. That way we get it all and it's at a very recent point.

    I'm going to speak with the PC's owner and my recommendation will be to use something like Clonezilla once a week and rotate portable hard drives from another site location. Clonezilla can backup (and verify the resulting image) this system in under 20 minutes.

    Thanks for all the thoughts and input! I appreciate it!
     
  13. TheRollbackFrog

    TheRollbackFrog Registered Member

    Joined:
    Mar 1, 2011
    Posts:
    3,041
    Location:
    The Pond - USA
    Han, if you're not trying to do this AUTOMATICALLY (and it appears now you are not), if you simply STOP the MySQL server, allow Macrium to then create the VSS LOCK for backup purposes (this can be seen on the Macrium UI), then RESTART the server... all will be well. Macrium will then image the LOCKed system which now includes a consistent MySQL database as a result of the STOP, and MySQL may continue serving the DB community well before Macrium is finished with its backup. All that's needed is a consistent version of the MySQL database prior to Macrium's (or anybody else's) VSS LOCK (which occurs in a few seconds or so). Once the LOCK is on, the server may be set loose once again.

    You get your consistent MySQL DB backup as well as total system protection, and it only interrupts the user community for a few seconds or so... no OFFLINE imaging required.
     
    Last edited: Jul 2, 2014
  14. Peter2150

    Peter2150 Global Moderator

    Joined:
    Sep 20, 2003
    Posts:
    17,039
    Another thought: And this might be work up front but pay dividends down the road. Switch Databases. MySql, looks like a subscription service and not that cheap. I also use a database in my business, but I use Access as part of Office 2010. I let Crashplan check every 5 minutes, and when you make any change in access it is written to disk and crashplan picks it up. Don't even have to close access. I've tested an all the files are good.

    It will be a bit of work up front, but her data will be much safer.

    Pete

    -http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-access-import.html-
     
  15. HAN

    HAN Registered Member

    Joined:
    Feb 24, 2005
    Posts:
    2,080
    Location:
    USA
    The stopping and starting of MYSQL appears to be pretty simple. In Windows, it's "net stop MYSQL" or "net start MYSQL". Easier than dumping. No extra parameters or path commands to be concerned with. The question is how it interacts with the program using the DB. If it has issues, then this would not work. Food for thought...

    Pete: After the replies I've gotten over the last few days of investigation, trying to back up the DB live just makes me really nervous. I don't think I can recommend that path to her. Thanks anyway!!
     
  16. TheRollbackFrog

    TheRollbackFrog Registered Member

    Joined:
    Mar 1, 2011
    Posts:
    3,041
    Location:
    The Pond - USA
    Han, there is no difference for a MySQL client between aborting a current database operation in progress and not being able to start one at all. The client should be able to handle both as they are very normal occurrences when either the SERVER is NOT RUNNING or the Windows SERVER SERVICE is stopped for some reason. If the client cannot handle those occurrences, it will not work well with any database.

    The NET STOP SQL is a very clean way to shut down the server as well as flush the necessary caches and make consistent the on-disk database itself. I really see no problems with any client designed to work with that database.
     
  17. HAN

    HAN Registered Member

    Joined:
    Feb 24, 2005
    Posts:
    2,080
    Location:
    USA
    I can't disagree. I will speak with the program's tech support in the near future to get their feedback on starting and stopping the DB. If they don't have any serious heart ache over it, that will give us additional options...
     
  18. HAN

    HAN Registered Member

    Joined:
    Feb 24, 2005
    Posts:
    2,080
    Location:
    USA
    The Rollback Frog: You mention starting VSS manually and then continuing on (both image creation and restarting the db) after VSS engages. Would there be any difference if one stops the db, starts creating the image (which to me means VSS is running) and then restarting the db. The only difference is leaving out the separate VSS initialization. Thoughts?

    (Or is this what you said? I may not be reading it right! ;) )
     
    Last edited: Jul 2, 2014
  19. TheRollbackFrog

    TheRollbackFrog Registered Member

    Joined:
    Mar 1, 2011
    Posts:
    3,041
    Location:
    The Pond - USA
    That's basically what I said :)

    The Server can be stopped, Macrium can be fired up and in the GUI it shows the steps it's going through with, I believe, getting the VSS LOCK first, followed by some analysis then the backup process. As soon as the LOCK has been granted in that process, the admin can restart the server... all will be well, the database will be on-disk consistent as soon as the DB STOP is complete. The following Macrium VSS LOCK will guarantee that it is in the Shadow Copy that Macrium will be using for the system backup. At that point, the server may be restarted since it doesn't affect the Shadow Copy at this time.
     
    Last edited: Jul 7, 2014
  20. NGRhodes

    NGRhodes Registered Member

    Joined:
    Jun 23, 2003
    Posts:
    2,331
    Location:
    West Yorkshire, UK
    You could also script MySQL to set a global read-only before backup starts, and turn it off again. Will be slightly quicker than stopping/starting (but most of the time for both is in waiting for existing transactions to finish and clearing buffers/cache) and the db will be online (if keeping online is important).

    Its strongly recommended to use the DB servers native tools to do backups and restores from differential/incremental backups.

    Doing a file system based backup (e.g. VSS), you need to also be aware that any changes to DB structure (new DB file, fields, indexes, permissions) will invalidate previous snapshots - so cannot be trusted for backup purposes, make a mistake with a change and you can't roll back ! - Database files are far more complex than regular files (think of them as a file system within a file system)

    A full file backup should be OK using VSS AFAIK.
     
Loading...
Thread Status:
Not open for further replies.