0

I have been using Google Backup and Sync for some time but I just decided it would be nice if I could use it for properly syncing so that data on all my Windows machines could essentially be the same, i.e. all use the same Gmail account Drive.

Everything has been working nicely. But today I tackled the files used by MySQL (in fact MariaDB MySQL fork).

If I modify some data on one machine (machine "A"), several files get changed: ibdata1, ib_logfile0 and ib_logfile1. These then get mirrored to the Cloud location (i.e. GDrive remote site). Unfortunately, these 3 files (and a couple of other MySQL files) don't then replace the existing files on machine B.

When I look at the reason why GB&S is saying "can't sync" for these files on machine B it says "You don't have permission to sync these files".

I take this to mean because the MySQL service is being run by the owner SYSTEM.

I've tried a couple of times to understand how Windows file ownership and permissions work in reality (it seems to be different to some of the explanations out there). But is there a quick or not-so-quick fix for this problem?

A few more details

Looking at the existing ibdata1 file on machine B (where the incoming replacement of this file is not being allowed to replace it), I find that in fact the owner is said to me "M17A/Mike", where M17A is the name of the computer.

Under the "Security" tab for the file, there are four entries under "Group or user names": Authenticated Users, SYSTEM, Administrators (M17A\Administrators) and Users (M17A\Users). All 4 of these have "Full control" ticked (I set this myself earlier today trying to solve this problem).

Rather than ownership/control, could it be something to do with the fact that the MySQL service has these files under its control as long as it is operating? Again is there any solution to this?

PS there's another problem with this, which is the size of these MySQL files: a tiny modification to one record in one dbase triggers the uploading of over 100 MB of files (the 3 main files mentioned above) by GB&S, followed by the downloading of them to sync the other computers attached to the same account. But this is a separate and less urgent problem.

mike rodent
  • 665
  • I can't comment on Google Backup & Sync, but have you considered the opensource software FileSync? – JW0914 Oct 06 '19 at 13:54
  • Thanks very much. I had never heard of it, but from a first glance it appears great, and unlike GB&S it works for Linux as well (on my Linux machines I have been experimenting with rclone and cron jobs to do a similar thing). I see that FileSync says it syncs to Google Drive. I take it your experience with it has been positive? – mike rodent Oct 06 '19 at 14:00
  • I installed it a year or so ago, but never ended up using it. It's had positive responses from users on the FreeNAS forum who've utilized it, and if you need it on Android, FolderSync can be used. – JW0914 Oct 06 '19 at 14:20

3 Answers3

2

The googledrivesync.exe executable runs as the user who starts the application. This would typically be the user currently signed in to the computer. This means your user accounts needs to have full control permissions on any files / folders you want Google Backup and Sync to access. Best practice would be to make sure that full control permissions are granted to your user account on the google drive folder and those permissions are propagated to all subfolders and files.

Rather than ownership/control, could it be something to do with the fact that the MySQL service has these files under its control as long as it is operating? Again is there any solution to this?

Yes. This is why Google Backup and Sync cannot modify the file. This is not a Google Backup and Sync limitation. This is a limitation (or rather a protection) in the operating system that prevents one process from modifying a file that is open in another process.

There is not a solution for that.

Unfortunately, you're running in to problems with all cloud sync tools and to a greater extent the problem of file sharing in general. You're trying to use it to sync an in-use database file. Even if the application that is accessing the file is designed in such a way that multiple processes can share the file, it still wouldn't work well with a cloud sync tool. You are not working with live data on cloud synced folders. Instead, one machine modifies the file and the changes are uploaded to the cloud, then the second machine recognizes the changes and downloads the file. There is lag, and no guarantee that both machines will always have a reliable internet connection. There is no mechanism to insuring the integrity between these file level changes. The two machines are never accessing the "same" file as is the case with an application that may be designed to work together with multiple users on the same local network.

For that reason, use Google Backup and Sync to sync files that a have a low likelihood of being used by multiple machines at the same time.

A work around in your situation would be to setup a manual process where Machine A backs up the MySQL database to a file and then allow that file to sync to Machine B. Then, Machine B restores that backup file to the MySQL database on Machine B. Perform this process when you want to "refresh" the data on Machine B.

Appleoddity
  • 11,730
  • Thanks very much for confirming what I was beginning to suspect. I've done precisely that: setting up a TaskScheduler job (i.e. cron for Windows) which will do a mysqldump into to the MyDocuments (i.e. local GDrive) location, and in fact give the filename of the dump .sql file a time stamp based on the date and time. This then gets synched up to the Cloud and back down to any other machines. – mike rodent Oct 06 '19 at 16:33
  • This also means that I can keep the actual MySQL files (ibdata, etc.) outside the local GDrive location, which is just as well, as each tiny modification to one record in one database would otherwise mean over 100 MB would have to be synced, totally clogging the GB&S traffic. Apparently the only real "sync" solution is to have your dbase app and files in a remote server... but then you run into the problem of dependency on your Net connection, etc. – mike rodent Oct 06 '19 at 16:36
  • Actually an intriguing thought occurs: this has some similarities to the conundrum solved by git's entire-repository distribution idea, so that I'm tempted to see whether I can workaround the problem by pushing my MySQL files to a gitlab repo... and then pulling this from all other computers. Again this is somewhat stymied by the large size of the MySQL files (ibdata again), but there might be a solution to this (I think it is the choice of InnoDB which causes this problem). In this scenario you would probably keep deleting old commits to just preserve the last 2 or 3... – mike rodent Oct 06 '19 at 16:41
0

Backup & Sync will refuse to sync certain files, if they are marked "hidden" or "system" or other flags.

Unfortunately some hidden/system files are required for the operation of some software. Especially if you're trying to sync an AppData folder for an app running on your machine.

FWICT, Backup & Sync has no setting to change whether or not it will synchronize hidden files. There were a large number of people complaining 2 years ago that they wanted to turn off this feature, and, rather than making it a setting or switch, it appears it's now the default and only behavior.

So, it's possible that Backup & Sync will simply not sync your files.

Erik Aronesty
  • 524
0

Solution that worked for me is to delete the file from the computer and upload it directly to google drive via the browser and let it sync back to the computer.

Sondre K. Jacobsen
  • 1