Skip to main content
Database Administrators

Return to Answer

Provide rsync option
Source Link
Old Pro
Old Pro
  • 141
  • 3

In general, you can safely read from a slave but you cannot safely write to a slave. If you insert data into a table in the slave, the master data will not overwrite it, it will fail to insert due to a duplicate key.

However, with MySQL you can setup a master-master (a.k.a dual master) configuration that will allow both the Trailer and the Office read/write access to the local database. The big caveat there is that now you can have replication failures in either direction and replication failures are both surprisingly easy to generate yet hard to recover from. You will need a professional DBA or Systems Administrator to set it up and to maintain it when it inevitably breaks.

In practice what I've done in this kind of situation is setup up periodic database dumps from the master and use rsync to keep that dump in sync with dumps stored on any read/reporting databases and then periodically replace the reporting databases with the dump.

In general, you can safely read from a slave but you cannot safely write to a slave. If you insert data into a table in the slave, the master data will not overwrite it, it will fail to insert due to a duplicate key.

However, with MySQL you can setup a master-master (a.k.a dual master) configuration that will allow both the Trailer and the Office read/write access to the local database. The big caveat there is that now you can have replication failures in either direction and replication failures are both surprisingly easy to generate yet hard to recover from. You will need a professional DBA or Systems Administrator to set it up and to maintain it when it inevitably breaks.

In general, you can safely read from a slave but you cannot safely write to a slave. If you insert data into a table in the slave, the master data will not overwrite it, it will fail to insert due to a duplicate key.

However, with MySQL you can setup a master-master (a.k.a dual master) configuration that will allow both the Trailer and the Office read/write access to the local database. The big caveat there is that now you can have replication failures in either direction and replication failures are both surprisingly easy to generate yet hard to recover from. You will need a professional DBA or Systems Administrator to set it up and to maintain it when it inevitably breaks.

In practice what I've done in this kind of situation is setup up periodic database dumps from the master and use rsync to keep that dump in sync with dumps stored on any read/reporting databases and then periodically replace the reporting databases with the dump.

Fix terminology: master-master not multi-master
Source Link
Old Pro
Old Pro
  • 141
  • 3

In general, you can safely read from a slave but you cannot safely write to a slave. If you insert data into a table in the slave, the master data will not overwrite it, it will fail to insert due to a duplicate key.

However, with MySQL you can setup a multimaster-master (a.k.a dual master) configuration that will allow both the Trailer and the Office read/write access to the local database. The big caveat there is that now you can have replication failures in either direction and replication failures are both surprisingly easy to generate yet hard to recover from. You will need a professional DBA or Systems Administrator to set it up and to maintain it when it inevitably breaks.

In general, you can safely read from a slave but you cannot safely write to a slave. If you insert data into a table in the slave, the master data will not overwrite it, it will fail to insert due to a duplicate key.

However, with MySQL you can setup a multi-master configuration that will allow both the Trailer and the Office read/write access to the local database. The big caveat there is that now you can have replication failures in either direction and replication failures are both surprisingly easy to generate yet hard to recover from. You will need a professional DBA or Systems Administrator to set it up and to maintain it when it inevitably breaks.

In general, you can safely read from a slave but you cannot safely write to a slave. If you insert data into a table in the slave, the master data will not overwrite it, it will fail to insert due to a duplicate key.

However, with MySQL you can setup a master-master (a.k.a dual master) configuration that will allow both the Trailer and the Office read/write access to the local database. The big caveat there is that now you can have replication failures in either direction and replication failures are both surprisingly easy to generate yet hard to recover from. You will need a professional DBA or Systems Administrator to set it up and to maintain it when it inevitably breaks.

Post Migrated Here from stackoverflow.com (revisions)
Source Link
Old Pro
Old Pro
  • 141
  • 3

In general, you can safely read from a slave but you cannot safely write to a slave. If you insert data into a table in the slave, the master data will not overwrite it, it will fail to insert due to a duplicate key.

However, with MySQL you can setup a multi-master configuration that will allow both the Trailer and the Office read/write access to the local database. The big caveat there is that now you can have replication failures in either direction and replication failures are both surprisingly easy to generate yet hard to recover from. You will need a professional DBA or Systems Administrator to set it up and to maintain it when it inevitably breaks.