Mysql - Keeping database up-to-date when using 2 different machines

[Martin109]

I'm a very, very basic user of mysql.

I understand some aspects of 'mysqldump', and have successfully used it to load an existing database from one machine onto another machine, so that they currently hold identical instances of the database in question.  The machines are in different locations.

My question is about subsequently keeping both instances up-to-date.  I could work on Machine1, resulting in the database being changed.  Then I think I would have to remove the exiting database from Machine2 (using 'drop...') and then load the up-to-date version from Machine1 in the form of a *.sql file.

Obviously, I'd have to keep doing this procedure once one of the versions had been changed, so that the other machine would have it in the same state.  Quite a bit of this could be facilitated by the use of a script or scripts.

Is there an easier way of accomplishing this, and am I missing something obvious?  For example, a common storage location for the relevant files, instead of /var/lib/mysql/[database name]?

[PaulF]

Can you put the database in the cloud somewhere so that both machines work from the same physical database?

[Pickled Onion]

Are you saying you only ever make changes on database 1, and database 2 is basically a readonly copy? If so, then look at MySQL Replication: you make one the primary and the other the secondary. The secondary must be able to access the primary, as it requests updates and applies them.

[ScumOfTheRoad]

I second what PickledOnion says about replication. I have played around with this a bit in the past. As I remember you can get some quite detailed instructions on how to set it up and it is relatively easy to understand.

[Martin109]

Are you saying you only ever make changes on database 1, and database 2 is basically a readonly copy? If so, then look at MySQL Replication: you make one the master and the other the slave. The slave must be able to access the master, as it requests updates and applies them.

Actually, no.  The two machines are at two locations which I visit on a weekly commute (at least, in normal times!).  I would be using and updating the database on either machine, and then the other one would need to be updated before it, in turn, could be used.

I can see the advantage of having the data stored in the cloud (eg Dropbox, my own webspace), but what files would have to sit there (ie not just the *.ibd files, I would imagine), and how would I connect to those files?

[ScumOfTheRoad]

Martin, do you have a network link between the machines? If so you should be able to promote the one you are working with to the master, and vice versa.
By the way I apologise for using master/slave terminology. It is outdated.


Regarding the cloud:  https://aws.amazon.com/getting-started/hands-on/create-mysql-db/

[Martin109]

Martin, do you have a network link between the machines? If so you should be able to promote the one you are working with to the master, and vice versa.
By the way I apologise for using master/slave terminology. It is outdated.


Regarding the cloud:  https://aws.amazon.com/getting-started/hands-on/create-mysql-db/

The answer re a network link  is 'no' - they are each on separate connections in different locations 100 miles apart.  I know nothing about VPNs; might it be possible to connect them in this way?

I'll have a look at the info on the cloud.

[Martin109]

Looks like TeamViewer might be a solution.

[Pickled Onion]

By the way I apologise for using master/slave terminology. It is outdated.

Opps, sorry, me too, I was reading off the documentation - post corrected.

[Pickled Onion]

Looks like TeamViewer might be a solution.

Have a look at scum's link - AWS Free tier would do what you need and be much more reliable than connecting to what I am guessing is your home office.

[Chris S]

When I need to access one of the MySQL dbs at our remote (to me) offices, I use an SSH tunnel and port-forwarding.

[quixoticgeek]

When I need to access one of the MySQL dbs at our remote (to me) offices, I use an SSH tunnel and port-forwarding.

I was having similar thoughts.

J

[Afasoas]

You can do multi-master replication with MySQL.
https://www.ryadel.com/en/mysql-master-master-replication-setup-in-5-easy-steps/

I've never done this, so I can't vouch for it.

[Afasoas]

Another option which I hear works quite well is MariaDB with something called Galera.