Best practice for MySQL Master/Slave replication?

[skydvrz]

I want to improve my app's performance by setting up local MySQL slave servers at each office and a master MySQL server at headquarters.

Since I have more than one external office to cover, I don't think I can do Master/Master replication (but I would be happy to find out I am wrong here )

I understand the best way to do Master/Slave is to "update/insert/delete" on the Master and "select" on the local slave. Changes to the master are propagated to all the slaves.

In general, what is the best way to accomplish this with MyDAC? Is there a way to specify the connection you want to use for updates versus reads?

Any help would be greatly appreciated!

[AndreyZ]

Hello,

You can set up replication on one MySQL server (the master server) and data will be automatically replicated on all your other MySQL servers (the slave servers). You can find more information about replication here:
http://dev.mysql.com/doc/refman/5.0/en/replication.html
http://dev.mysql.com/doc/refman/5.0/en/ ... howto.html
You can use two connections in your application: one for reading data and another for writing data. The connection that will be reading data should work with the slave server, and the connection that will be writing data should work with the master server.

[skydvrz]

You can set up replication on one MySQL server

Yes, I already have replication set up and it is functional.

You can use two connections in your application: one for reading data and another for writing data.

My original post asked how to do this... I already know I have to make separate connections to the master and slave servers.

My question:

Is there a semi-automated way to do *connection switching* with MyDAC components, or do I have to insure my TMyQuery component is initialized with the correct connection prior to SQL execution?

The trouble is that I use a lot of DB grids (DevExpress Quantum Grids) and they can both read and write using my TMyQuery components. I may not have a chance to set the connection correctly if the user edits a record within a grid.

It would be nice if the TMyQuery component could expose 2 connection properties, one for writing (or both R/W) and another for just reading. Perhaps the TMyQuery component can decide which connection to use, based on the type of query?

Maybe this could be better done through an Update component connected to a TMyQuery?

[AndreyZ]

You can use the UpdateObject property of the TMyQuery component in the following way:

Code: Select all

MyQueryUpdate.Connection := MyConnection2; // MyConnection2 is a connection to the master server 
MyQueryDelete.Connection := MyConnection2;
MyQueryInsert.Connection := MyConnection2;
MyQueryLock.Connection := MyConnection2;
MyUpdateSQL.ModifyObject := MyQueryUpdate;
MyUpdateSQL.DeleteObject := MyQueryDelete;
MyUpdateSQL.InsertObject := MyQueryInsert;
MyUpdateSQL.LockObject := MyQueryLock;
MyUpdateSQL.RefreshObject := MyQueryRead;
MyQueryRead.Connection := MyConnection1; // MyConnection1 is a connection to the slave server
MyQueryRead.UpdateObject := MyUpdateSQL;
MyQueryRead.Open;

In this case you will read and refresh data from the slave server, but all changes you make will be sent to the master server. Note that you will have to write your own SQL statements for all operations. For more information about the UpdateObject property and the TMyUpdateSQL component, please read the MyDAC documentation.

[skydvrz]

You can use the UpdateObject property of the TMyQuery component in the following way:

Cool! You guys thought of everything!

Great product, BTW!