How to update the Main Office MySQL database using DropBox?

Most of my MS Access + MySQL clients have multiple branches.  One of their requirement is to consolidate the transaction/database of their branches to the main office.  My previous solution was to use FTP+MySQLDump, but now we have a better solution, the DropBox.

The first step is of course to download the DropBox utility through www.dropbox.com and install it to all the branches including the main office.  Just follow the steps on installation, what is important, is that the main dropbox directory and the branch must be in sync.

Branch

At the branch create a batch file to Dump your database into the dropbox directory.  Here is a sample batch file content:

@echo off
[PATH]\mysqldump --user=XXX --password=XXX --database DB > [PATH]\DB01.sql
[PATH]\gr [PATH]\DB01.sql DB DB01

The first line is to dump the database DB to a file called DB01.sql.  The second line is to change the name of the database from DB to DB01 – this is very important because you might overwrite your main database when you restore the database.  For the second line I am using a gr utility which can be downloaded here.

Create a shortcut for this batch file so that the user can just click this from time to time.

Main Office

At the Main Office we need to create an upload batch file.  Here is a sample batch file content:

@echo off
[PATH]\mysqldump --user=XXX --password=XXX --database DB > [PATH]\DB.sql
[PATH]\gr [PATH]\DB01.sql DB DB01
[PATH]\mysql --verbose --user=XXX --password=XXX DB01 < [PATH]\DB01.sql

The first line is to dump the Main Office database, DB, to secure the main database from overwrites.  The second line is a redundant gr command from the branch to change the name of the database from DB to DB01, because even if the file name is DB01 and you upload it in DB01, if you don’t change the content it will still overwrites the DB database.  The third line is the restoration command, this command uses MySQL.EXE file.

Create a shortcut for this batch file so that the user can just click this from time to time.

Thats it.

If you find this blog helpful, you can make the donation by clicking the Paypal button below.

Advertisements
About

Software developer living in the Philippines.

Posted in Microsoft Access

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Author

Harold Glenn P. Minerva
Software Developer / Tech Enthusiast
Living in the Philippines

View Harold Glenn Minerva's profile on LinkedIn

Instagram

Software Engineer - Seasonal and Range Trading Software. Magenta Trader is a powerful stock market visualization software that increases your probability of trading success.

Software Architect and Founder - Easyfis.com is a multi-tenant cloud-based Software-as-a-Service (SaaS) business app that caters to micro, small and medium trading businesses.

CTO and Co-Founder - We give your company the leverage by providing innovative software solutions products such as Point-of-Sales (POS), Financial Information System (FMIS), Payroll and DTR (HRIS), and many more.

%d bloggers like this: