I have two identical MySQL databases one in an internal server and the other in a web hosting server. I want to update the database on the web host each day with the database on the internal server. Is there a way to automate this process also how can I do this manually? If I'm to do it manually, does it require me to get a SQL dump of the Database on the internal server and then import it on the database on the web host? Can someone advice please.
-
You can refer existing answer posted here in StackOverflow stackoverflow.com/questions/52583/…– Mahesh PatilCommented May 22, 2014 at 10:02
-
about #2 if i do full dump from internal and import on production it would overwrite the online database, i mean i have online buyers on database web hosted and i want to migrate the internal database buys into live , can you please clarify BTW what will happen to auto increaments between two if they both were reserving same id it will be messy rigth !?– shareefCommented May 6, 2018 at 16:04
Add a comment
|
5 Answers
You have a few options:
Set up MySQL replication between the servers. Your internal server can act as the Master, and the web host server as the slave. Any updates performed on the Master will be replicated to the slave immediately (assuming a working connection). This will most likely be the easiest and most effective option to choose. To use replication, your internal database would need to be accessible over the network from the web host.
You can read more about replication here.
Each day, you can perform a mysqldump on the internal server, upload the dump file to the web host, and import the data. Since this is a full dump, if you have a very large database, this might not be feasible. If you like, this procedure could potentially be scripted to avoid having to do it manually.
You can set up binary logging on the internal server. You can then ship the binary logs to the web host and apply them to the database, effectively playing all transactions that occurred that day to the web server. In effect, this is what happens with replication anyway, so you would nearly always go with the replication set up instead of this option.
If there is no connection between the two databases, taking mysqldumps each day will be the easiest path to take.
answered May 19, 2014 at 4:52
-
about #2 if i do full dump from internal and import on production it would overwrite the online database, i mean i have online buyers on database web hosted and i want to migrate the internal database buys into live , can you please clarify BTW what will happen to auto increaments between two if they both were reserving same id it will be messy rigth !?– shareefCommented May 6, 2018 at 16:02
You can also use options like symmetricDS which can help in synchronizing two databases. With this you will be able to select the tables which needs to be synchronized that way you can save internet bandwidth. This would be also suit in the scenario when there is lack of connectivity between two locations.
How to set up database replication with MariaDB: https://www.techrepublic.com/article/how-to-set-up-database-replication-with-mariadb/
-
2Welcome to DBA.SE. We appreciate your contribution/participation. However, the community does expect a certain quality in the answers posted. Please consider reading the following article: How do I write a good answer? (Help Centre) In the section Answer the question there is a short sentence which reads: Links to external resources are encouraged, but please add context around the link so your fellow users will have some idea what it is and why it’s there Commented Sep 10, 2018 at 6:26
You could use Navicat. It's primarily a db manager but it has data transfer and data synchronization functionality as well as a scheduler so you can automate. Its not free but there is a 30 full function trial.
-
Navicat's Structure Synchronization feature is exactly what I searched for!– minyorCommented Dec 1, 2022 at 11:45
The accepted answer covers it mostly, including the manual sync using mysqldump. Another alternative that is automated to sync it daily is to use an ETL tool. You can choose what fits best to your requirements. You can use Apache Nifi, Skyvia, SSIS, or whatever tool is at your disposal. With Skyvia, you can try it for free, and there’s nothing to install.
I have tried Skyvia for a similar requirement as yours. This is how I did it:
Register and login to Skyvia.
Create a Skyvia Agent and follow the steps to install it in your internal server. This is the secured way to connect to your on-premise MySQL server.
Download the Skyvia Agent and install it in a server where you can have a connection to MySQL.
Download the Agent Key file and place it on the folder where you installed the Skyvia Agent.
Run the Skyvia Agent.
Create a connection to MySQL utilizing the Skyvia agent you made in #2 to #4. Test the connection and see that it works.
From here, it’s either I create a Skyvia Synchronization or Skyvia Control Flow. I chose the Synchronization but this required that the target have empty tables for the initial synchronization. If you can’t allow this, choose Skyvia Control Flow and arrange the Data Flows based on what tables need to sync first and then it’s dependencies. This will respect any foreign key constraints you have and avoid the error.
Schedule the job daily.
Monitor the logs
I find it easy to setup the connections and the tasks for each table that needs to sync.
Here’s what it looks like to setup the Skyvia Agent:
Below is a sample of a running Skyvia Agent:
Here’s a sample Skyvia connection to the internal server using the agent above:
Here’s the other connection to MySQL. You need to specify the host IP and other credentials:
And below is the Control Flow sample:
For more information on Control Flows, you can check out this link. I hope it helps.
