Geek Answers Handbook

Synchronizing a large local database with a server database (MySQL)

Every week I need to synchronize a large (3 GB + / 40 + tables) local MySQL database with the server database. The two databases are exactly the same. The local database is constantly updated, and every week or so the server database must be updated with local data. You can call it “mirrored DB” or “master / master", but I'm not sure if this is correct.

Currently, the database exists only locally. So:

1) First I need to copy the database from the local to the server. With PHPMyAdmin, export / import is not possible due to DB size and PHPMyAdmin restrictions. Exporting a DB to a gzip file and uploading it via FTP is likely to break in the middle of a transfer due to a connection to server problems or due to a server file size limit. Exporting each table separately will be painful, and the size of each table will also be very large. So what is the best solution for this?

2) After the local database is fully loaded on the server, I need to update the server database weekly. What is the best way to do this?

I have never worked with such a scenario, I don’t know the various ways to achieve this, and I am not very strong with SQL, so please explain how this is possible.

Many thanks.

+6
sql database php mysql
source share
2 answers

This article should help you get started.

Basically, get Maatkit and use the synchronization tools there to complete the master-master-synchronization:

mk-table-sync --synctomaster h=serverName,D=databaseName,t=tableName
+3
source share

You can use DataComparer for mysql. Set up synchronization of templates that define the data that tables should synchronize. Schedule a weekly template update. I have two servers synchronized daily with dbForge Data Comparer via the command line.

+2
source share

More articles:


All Articles