Hi There,

I'm looking to create a MySQL replication using MySQL 5.1 and SSL. We plan to have a master - slave replication. We are planning to run this replication over a 1.5mb T1 connection (yes this could hurt!!!!) and are only in a testing / feasibility phase.

One problem I foresee with our replication setup is that our software application has a nightly routine in which truncates two tables totaling 130mb of database data.

I was thinking of two options:

• Let the database truncate and replicate the 130mb every night
• Somehow schedule the replication to pause between the database truncate update (once per day)

Option one isn't really a great option imo as it will not allow our customers to view the information they need whilst the truncate and replication is working. This could take some time over a 1.5mb connection.

Is option two possible? Will the replication be happy if it is paused at say 6pm and back on at 8pm with two tables truncated and populated with data? Will MySQL try to replicate all truncated data or just changes it finds? Unfortunately our application has been written with the truncate to perform some updates on the database. These two tables that truncate to not change too much. I'm hoping MySQL would be intelligent enough to see that only 10 records have changed in the truncated tables and update only those 10 records.

We are working on rewriting the application as so it doesn't truncate but this is a while away. For now I just need some form of replication.

Thanks in advance for any input.

Regards,

Jon

Hi,

I use MySQL master-master replication between two servers in the same LAN so I'm not going to make any statements about replication off site using the velocity you indicated I only want to say that as I have it set up I can 'down' one server for a week and it will synchronize when I bring it up again. So I assume that if the replication is set up correctly (and thoroughly tested) you shouldn't have any problems when the slave does'nt replicate for just the short time frame you're indicating.

Kind regards,

Eric

Hi Eric,

Thanks for the feedback. I'm going to test the truncation issue and see what happens. It is good to know that MySQL can deal with long periods of disconnections without issue. .

Regards,

Jon

Forgot to update this.

In the end I setup a statement based master - slave replication without any pauses. It works well with the truncate and has proven to be very reliable. Over a T1 a full db replication (130mb) takes around 30-40 mins, the nightly tuncate takes 16-18.

Hi Jon,

I'm glad you got a solution that suits your needs.

Kind regards,

Eric