I want to share my solution on how you can share your MySQL database using Dropbox. When I started with my "photo enhancer" application, I used SQLite, which is already included in Python and extremely easy to share through Dropbox – just put the file in your Dropbox folder and that’s it.
Some background information why I needed to share my database, and why I switched from SQLite to MySQL:
I have an old MacBook Pro from 2014 with macOS High Sierra, and a Mac mini from 2020 with macOS Monterey. My Mac mini is fast, but my laptop is portable. Therefore, I like to use my Mac mini to import and edit the photos in the photo enhancer application, but mostly use the search function on my laptop.
SQLite worked well in the beginning and was a nice solution. But as soon as I started to introduce more and more data, and improved the search function leading to more complex queries, SQLite did get slow. The "breaking" point was, when I tried to get all different camera body types with the query "SELECT camera, count(id) AS count FROM photos GROUP BY camera ORDER BY camera". I executed this query on my laptop, both with SQLite and MySQL: it took SQLite 4 minutes to get result, but MySQL executes the same query, on the same dataset, in 0.07 seconds.
If you wonder why I decided to use Dropbox: I already use it to store and share all my photos, using it to share the database was a natural conclusion.
The last background information which could be important: I needed to use Python 3.7 on my laptop, and Python 3.8 on my Mac mini. For the old laptop, I couldn’t use any newer version without issues with the Pillow library, and Python 3.8 for the Mac mini because of the M1 chip and issues with tensorflow.
So, back on track: How did I manage to install MySQL on both machines, and share the database with Dropbox?
My solution needed to work for both Macs: a pretty old version of macOS 10.13 and the newest macOS 12.5. Therefore, I started to install everything on the old laptop, and after that, I tried to match the versions on the new Mac mini – and I was lucky enough that it worked!
Installation
Once I found out which version is needed, it was really simple. I installed MySQL 5.7 on both machines using brew:
$ brew install mysql@5.7
At this point, do not set a brew service for MySQL! It took me hours to find out that this was my main issue. When running the MySQL server with brew, it adds some strange access rights on newly created tables and therefore, the tables are not shareable between machines. We will do that later in the old school style, using the command line. But at this point, we don’t even want the server to be running.
If you wonder why this specific version, it was a lot of trial and error from the newest version downwards until I found a version which worked. It was an extremely slow process, because on the old machine the installation took almost an hour – even if it failed, as that usually didn’t happen at the beginning.
While installing this version, brew will install Python 3.10. No idea why, it states it is needed, and luckily even the newest version of Python can still be installed on my old machine. The only annoying part was, that I had to change the symlink of python3 back to my Python 3.7 version.
For my fellow Python users, I use mysql-connector-python to connect MySQL with Python, installed with pip:
$ pip install mysql-connector-python
You don’t have to, but I did install the MySQL Workbench. It helped me quite a lot while debugging.
Configuration
After the installation, we now need to tell our MySQL server that the data shall be stored in the Dropbox folder. In theory it is easy: find the config file my.cnf, and change the datadir path in all places.
Important: before you change the configuration, make sure your MySQL server is stopped ($ mysql.server stop). I had some issues with that, somehow brew was really into starting the server, even after I stopped it multiple times. In the end, I used the Workbench to double check that the server was really stopped.
In my case, the configuration files were in the following places:
- macOS 10:13: /user/local/etc/my.cnf
- macOS 12.5: /opt/homebrew/etc/my.cnf
After finding the configuration files, you now have to open the file, find the datadir option, copy the path for safe keeping (we will need it), and change it to your Dropbox folder.
I recommend using a search function to find all datadir places. When I did it, I started to get tired and I overlooked the second place in the config file and therefore wasted a lot of time debugging…
Now, you need to move the files from the original datadir path, to your Dropbox folder, in the end it is a simple command:
$ cp -R <old_path> <dropbox_folder>
In theory, that’s it. Start your MySQL server ($ mysql.server start) and it should work. If it doesn’t, the Workbench view "Server Status" under "Management" can help you quite a lot. You can first check if the right configuration file is used – and with "right" I mean the file you changed. If not, you know what to do. Important to mention here is, that the path looks like an absolute path, but it is not! (Again, took me way too long until I understood that detail.)
If the configuration file looks alright, you can check what is written under "Data Directory". There, your Dropbox folder path should be written. If it isn’t, but the right configuration file is used, my first guess would be: you didn’t edit all the placed in the configuration file. That was the issue in my case.
And that’s it. Now your MySQL database is shared!
In theory, you can run it simultaneously on both machines, but I only recommend it if you plan to only read from the database. And generally, I do highly recommend to stop the MySQL server when you don’t need it anymore, to avoid "merge" conflicts.
Conclusion
It is possible, it was totally worth the day I spend on finding this solution. But it has its limits, and I think it is only feasible if you either use it on your own and switch from one to another machine (my case), or the database is mostly used "read only" and new data is only added sporadically.
Also, the whole MySQL database is shared, not a single schema. I never tried it, but in theory it should be possible to run multiple MySQL servers /databases on the same machine.