I would like to update data for every action (insert, update, delete) that happens on a localhost MySQL server to a remote MySQL server. How would I do that?
Add a comment
|
3 Answers
Enable database replication. It's not a great idea to flush frequently for performance reasons, but maybe it's an acceptable tradeoff?
Comments
Can't you use Triggers?
22.5.5: Is it possible for a trigger to update tables on a remote server? Yes. A table on a remote server could be updated using the FEDERATED storage engine.
From : http://dev.mysql.com/doc/refman/5.0/en/faqs-triggers.html#qandaitem-22-5-1-5
I wouldn't recommend this because you will be consuming bandwidth for every small change you do. You can try a scheduled job instead.
Comments
Here is a simple and safe solution using a git server and works fine if the two db_tables don't change that much and don't have to be identical at all times.
- Create a repository on your git server (GitHub, Bitbucket, other)
- Clone the repository to both your servers
On the server you want to export from run those terminal commands
$ cd /my/repository/path
$ mysqldump -h localhost -u User_Id -pPassword DB_name Table_Name > Table_Name.sql
$ git add .
$ git commit -m "Table Update"
$ git push https://git_server/repository/name
The db table is now on the git server.
On the server you want to import to
$ cd /my/repository/path
$ git pull https://git_server/repository/name
$ mysql -h localhost -u User_Id -pPassword DB_name < Table_Name.sql
answered Oct 2, 2015 at 23:55
Peter Korinek TellusTalk
2571 gold badge4 silver badges10 bronze badges
3 Comments
Peter Korinek TellusTalk
A special thanks to my coworker Peter F who came up with the idea. We are running this solution our self's in our production environment. This way we always have a db_server ready to take the load when necessary.
Jim Redmond
I'd advise against this approach for long-term use. It doesn't scale; it takes too long to complete; and it doesn't offer any benefit over replication.
Peter Korinek TellusTalk
We were looking for these benefits: Databases can be in different locations, completely independent of each other, no need to interact with firewalls. Version handling is also good to have, offers us to start up with an older version if the database gets corrupted. All scenarios and needs are different and this solution is a little out of the box.