All,

I found some code that is supposed to sync databases, which I posted on the AD free pastebin at:
But in finding it I found these problems:

1. It only works for development to online production,
2. It assumes the local DEV DB is the only one with changes,
3. It therefore does not actually sync but deletes & replaces the online DB,
4. It is not modular in design.

I found though it was a good starting point, so started editing it to add:

1. Sync Local DB1 to Local DB2,
2. Sync local DB table1 to Table2,
3. Sync local dev to local test,

Since I'm just learning how to use MySQL and other DBs in Bash thinking that a collaboration to make this a full service DB sync routine would be great. I hope you agree and I want to start by changing to modular design, adding all processing into subroutine "FUNCTIONS", so the main processing can have a very fluid flow and from it call the processing functions that are needed.

Cheers!

TBNK

I am not a SQL database expert by any means but.

By syncing I am guessing you mean update only changes. So the question is how do you determine just changes which could be either updates or new rows. Do the database and tables have identical structures? If db2 or table2 are just for backups and do not change themselves you could possibly use a transaction log. The logs are binary so then you would need to be able to read the log and perform the same operation.

Otherwise about the only way would be to use SQL to compare the files. For new rows using a unique primary key or maybe a date but for changes to existing rows you need to compare each column.

michaelk,

Yes! record by record has to be pulled and any changes need to be updated. Of course that means the DBs and/or tables have to be identified which is source and which is target and if target has changes they have to be added to a special copy of the target, with only those conflicting changes added thereto, so a post process can find those and query to user/operator as to whether they are to be in the update or deleted.

Hope that explains my thinking of this.

Also I would write this in PHP, but you have seen my issues with that. Additionally I added WAMP Server to my Win11 laptop, but also experiencing issues there so not able to copy the DB sources needed for this there to get around the processing problems on the Linux server so have turned to BASH as an alternative.

Cheers!

TBNK

michaelk,

Also if you know an OpenSource Collaboration web site allowing anyone interested in a project to join in, please let me know.

My searches keep getting either collaboration tools or limited to team member collaboration.

Cheers!


TBNK

Interesting subject.
Are we talking about syncing the structure of a database or the contents?
Changes to structure should be built in dev, installed to test (and tested), then installed to production along with the programming changes that required the change in structure.

I’m sure that processes exist for mirroring changes in content between database copies. It’s certainly possible to extract content from one database and load it into another. Think Update else Insert. Not sure I’d bother to determine which field(s) had changed, as long as it was possible to know that a change had happened in the source. I’d use a timestamp and only update records changed since the last mirror…or do it live if possible.

I’m not clear on the “post process” requirement. Where did the change come from that it was not already “approved”?

I used to write ETL in Perl for a living.

You'd definitely need to sit down and spec out all the requirements + restrictions, so that you can automate as many updates as possible - see the qns/comments in above posts.
You can also have the program log any it can't handle, for later perusal by humans.
Definitely have a 'test' copy of the both src + tgt systems.

BTW, Perl would give you detailed control & data munging capabilities and it has modules for all kinds of DBs ...

1 members found this post helpful.

database files are binary, cannot be easily updated. Sometimes database is stored on the disk even without filesystem. Different database engines work differently. Therefore you must read the official documentation (and best practices) about the official way of syncing databases.
for example: https://dev.mysql.com/doc/workbench/...onization.html
Additionally different databases may have different requirements and different ways of syncing.

ETL is what I thought of, too. I became an ETL programmer after architecting and building a data migration…a “one time” process that took over a year to build and test…from a mainframe hierarchal database to a Unix-based relational database. The next jobs were ETL. Eventuality using Informatica, which is almost not a “language” at all, but is a pretty good tool for ETL.

I agree that perl would be an excellent tool for “syncing” databases, if one can’t find a good fit off-the-shelf.

michaelk,

Got WAMP installed and working on my Win11 Laptop, but phpmyadmin shows, but not allowing login, as config not set right just yet.

All HOWTOs on this are majorly confusing and they refer to php.ini so researching the laptop I have "php.ini" in the following versions:
So you can see why this is so confusing, unless you assume (never supposed to assume anything), that the correct file to edit is the 8.2.0 version file.

So looking for a better HOWTO on config for phpmyadmin on Win11.

Cheers!

TBNK

PS
Was supposed to convert my phone to Straight Talk, where I will have unlimited 5G internet via USB tethering, but local WalMart Rep took a job in FT. Worth, so till I make a trip to the DFW metro will still not have iNet.

TBNK

All,

The first part of file syncing is easy as you:

1. Set the target directory where the synced files will reside,
2. Look for duplicate files by name,
3. Check the date and size,
4. If all are the same, move one file to the target directory,
5. Delete any duplicate files,
6. Record the good file in the MySQL DB.

I'm working one a series of functions to do that.

Cheers!

TBNK

Your original post topic was about syncing databases, your last post is about syncing files. I am confused.

scasey,

For now just content as serveral servers are involved but not clustered so update happen to which ever a user is logged into at the time, and the domain control server needs to get all the changes, so it can propagate to and sync with the remote servers.

Hope that explains it better.

Cheers!

TBNK

michaelk,

You are right, I have 3 sync projects going so lost where I was at!

Thanks!

Cheers!

TBNK

All,

Hope this further info helps with understanding the issue.

There has to be several queries and processing steps:

1. First all DBs in MySQL have to be queried,
2. DBs with the same or similar names must be displayed (HTML),
3. The user must select the DBs to process (Multi Select List),
4. The target DB, where results will be stored must be selected,
5. These DBs must be queried and loaded into arrays,
6. The records in the arrays must be compared,
7. Records identical to the target records will be deleted from their source DB,
8. Dates of the changes must compared,
9. Only newer records will be processed,
10. Records not equal must be put into a new process array identifying the source DB and date,
11. An update on the target DB will run, updating the target record with the newer data,
12. The duplicate DBs will be deleted after all processing,

Hope this gives a complete list of the processes needed!

Cheers!

TBNK

Sounds like an interesting project.

Do it one step at a time and ask as you go.

I could enjoy doing that, except I can't program a web page to save my life; always been a cli guy