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.
Mysql – Sync two MySQL databases in two different locations
data synchronizationMySQL
Related Question
- MySQL DB import/export command line in Windows
- Mysql – How to keep two databases on different sites in sync
- Mysql – Sync MySQL database and Quickbooks Database
- ODBC configuration
- Mysql – Query to compare the structure of two tables in MySQL
- Sql-server – Sync two databases on different servers
- Mysql – Update two tables, each on separate MySQL servers, on two different machines in one query
- Sql-server – what are the best ways to sync two databases in sql server or azure sql db
Best Answer
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.